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
prod_db=HEROKU_POSTGRESQL_BROWN_URL # follower
stag_db=HEROKU_POSTGRESQL_ROSE_URL
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:
db="app_development"
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'
"