Heroku Postgres Restore to Staging and Development

I use these bash scripts to restore Heroku Postgres data from production to staging and from production to development environments. They depend only on standard Unix tools and Postgres and Heroku CLIs. They can be customized per-project for pre- and post-processing.

Restore to staging

The db-restore-stag-from-prod-backup script pre- and post-processes data to prevent accidents:

heroku maintenance:on --app app-staging
heroku pg:copy app-production::$prod_db $stag_db --app app-staging --confirm app-staging
heroku pg:psql --app app-staging -c "
  UPDATE ar_internal_metadata
  SET value = 'staging'
  WHERE key = 'environment';

  UPDATE users
  SET active = false
  WHERE email != 'me@example.com';
heroku run --app app-staging rake db:migrate
heroku maintenance:off --app app-staging

Restore to development

The db-download-prod-backup script downloads to the tmp/latest.backup file on my filesystem:

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

I restore tmp/latest.backup, pre- and post-process in the db-restore-dev-from-downloaded-backup script:

dropdb --if-exists "$db"
createdb "$db"
psql "$db" -c "
  CREATE EXTENSION pg_stat_statements;
  CREATE SCHEMA heroku_ext;
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'