Heroku Postgres Restore to Staging and Development

last updated March 13, 2021

I use these scripts to copy Heroku Postgres data between production, staging, and development environments:

db-restore-stag-from-prod-backup
db-backup-stag
db-download-stag-backup
db-restore-dev-from-downloaded-backup

db-restore-stag-from-prod-backup

heroku maintenance:on --app app-staging
prod_follower_db=HEROKU_POSTGRESQL_BROWN_URL
staging_db=HEROKU_POSTGRESQL_ROSE_URL
heroku pg:copy app-production::$prod_follower_db $staging_db --app app-staging --confirm app-staging
heroku pg:psql --app app-staging -c "
  UPDATE ar_internal_metadata
  SET value = 'staging'
  WHERE key = 'environment'
"
heroku pg:psql --app app-staging -c "
  UPDATE users
  SET active = false
  WHERE email NOT = 'me@example.com'
"
heroku run --app app-staging rake db:migrate
heroku maintenance:off --app app-staging

Specific to each project, that script can sanitize data or disable users or feature flags to help prevent accidental notifications.

db-backup-stag

heroku pg:backups:capture HEROKU_POSTGRESQL_ROSE_URL --app app-staging

db-download-stag-backup

mkdir -p tmp
curl -o tmp/latest.backup "$(heroku pg:backups:url --app app-staging)"

I keep the tmp/latest.backup file on my filesystem so I can restore locally at any time using the next script.

db-restore-dev-from-downloaded-backup

db="app_development"
dropdb --if-exists "$db"
createdb "$db"
pg_restore tmp/latest.backup --verbose --no-acl --no-owner --dbname "$db"
psql "$db" -c "
  UPDATE ar_internal_metadata
  SET value = 'development'
  WHERE key = 'environment'
"

This script is another opportunity to run SQL commands to flip feature flags, make my user an admin user, etc.

Why not Parity?

I am the original author of the Parity Ruby gem, which provided similar functionality. I switched to these scripts because:

some projects shouldn't have a Ruby dependency
shell scripts can be customized easily for post-processing
improve security and avoid bugs by hard-coding Heroku app names instead of indirectly using staging and production Git remotes

For the same reasons, I switched from Parity's production and staging commands to these scripts:

stag
prod

stag

heroku "$@" --app="app-staging"

prod

heroku "$@" --app="app-production"