Restore a Heroku DB from an external DB source

restore_heroku_db.md

Restore Heroku DB from an external DB source

1. Create an instance of a Heroku DB

$ heroku addons:create heroku-postgresql:plan -a app-name
# heroku addons:create heroku-postgresql:standard-0 -a cool-app-prod

2. Export external DB locally

$ pg_dump -Fc --no-acl --no-owner DATABASE_URL > DESTINATION
# pg_dump -Fc --no-acl --no-owner postgresql://postgres:[email protected]:5432/database-name?sslmode=require > ./db/exports/prod.dump

3. Upload exported DB to an AWS s3 bucket

$ aws s3 cp LOCAL_FILE S3_BUCKET/FILENAME
# aws s3 cp ./db/exports/prod.dump s3://bucket-name/prod.dump

4. Sign the bucket object

$ aws s3 presign s3://bucket-name/object-name
# aws s3 presign s3://MY-DBS/prod.dump

5. Restore the URL to Heroku DB

$ HEROKU_DEBUG=1 heroku pg:backups:restore 'WHATEVER_URL_S3_RETURNS' HEROKU_POSTGRESQL_COLOR -a app-name
# HEROKU_DEBUG=1 heroku pg:backups:restore 'https://xxxxxxxx.s3.us-east-1.amazonaws.com/xxxxxxxxx.dump?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=XXXXXXXXXXXus-east-1%2Fs3%2Faws4_request&X-Amz-Date=XXXXXXXX&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=XXXXXXX' HEROKU_POSTGRESQL_PINK -a cool-app-prod

6. Promote Heroku DB to primary DB

$ heroku pg:promote HEROKU_POSTGRESQL_COLOR -a app-name
# heroku pg:promote HEROKU_POSTGRESQL_PINK -a cool-app-prod