The snippet below is an idempotent script that:
1) Drops ON_PREM_DB
using ON_PREM_PSQL_*
connection to psql
on machine that is reachable via ssh
using ON_PREM_SSH
.
2) Pulls database from HEROKU_APP
to ON_PREM_DB
using the same ssh
and psql
information as above.
It tries to inform you what will happen, but use at your own risk as this will destroy the on-prem database first.
#!/bin/bash
ON_PREM_PSQL_PORT='5432'
ON_PREM_PSQL_HOST='localhost' # Do not change
ON_PREM_PSQL_USER='postgres'
ON_PREM_PSQL_PASS='xxx'
ON_PREM_DB='my-app-db'
ON_PREM_SSH='root@acme.com'
HEROKU_APP='production-acme-com'
ssh_sessions_pids=()
are_you_sure() {
echo "🍕 This WILL drop whole db on ${ON_PREM_SSH}. Type in 'YES' to continue"
read user_input
if [ "$user_input" = "YES" ]; then
echo "🍕 Continuing"
else
echo "🍕 Exiting"
exit 1
fi
}
start_ssh_proxy() {
echo "🍕 Starting SSH proxy"
ssh -f -N -L${ON_PREM_PSQL_PORT}:${ON_PREM_PSQL_HOST}:${ON_PREM_PSQL_PORT} ${ON_PREM_SSH}
ssh_sessions_pids+=($!)
}
drop_on_prem_db() {
echo "🍕 Dropping on prem DB"
PGPASSWORD=${ON_PREM_PSQL_PASS} dropdb ${ON_PREM_DB} -p ${ON_PREM_PSQL_PORT} -U ${ON_PREM_PSQL_USER} -h ${ON_PREM_PSQL_HOST}
}
pull_from_heroku() {
echo "🍕 Pulling from heroku"
PGPASSWORD=${ON_PREM_PSQL_PASS} PGUSER=${ON_PREM_PSQL_USER} heroku pg:pull DATABASE_URL postgres://${ON_PREM_PSQL_HOST}:${ON_PREM_PSQL_PORT}/${ON_PREM_DB} --app=${HEROKU_APP}
}
cleanup() {
echo "🍕 Cleaning up SSH sessions..."
kill -- -$$
}
trap cleanup EXIT
are_you_sure
start_ssh_proxy
drop_on_prem_db
pull_from_heroku
Title image generated via DALL-E using prompt: Migrate heroku database to on-prem --ar 100:42
Top comments (2)
Very good code !!!
Bash simple and powerful!!!