Posted on Mar 1, 2024

Migrate heroku database to on-prem PostgreSQL

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