Migrate heroku database to on-prem PostgreSQL

#heroku #postgres

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