Heroku Postgres Restore to Staging and Development
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
andproduction
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"