Move Heroku Postgres to Amazon AWS RDS

move_heroku_pg_to_aws_rds.md

1. Prepare your RDS instance and authorize access to it (basically make it public).

2. Put your app in maintenance mode:

   heroku maintenance:on

3. Save the latest SQL dump of the DB:

   psql -h HEROKU_DB_HOST -U HEROKU_DB_USER -d HEROKU_DB_NAME > latest.sql

   If that fails (long time no response etc.), an alternate method is pulling to local first:

   # Use DATABASE_URL literally
   heroku pg:pull DATABASE_URL awesome_project_production_local
   psql -d awesome_project_production_local > latest.sql

4. Upload the latest DB to RDS:

   psql -h RDS_DB_HOST -U RDS_DB_USER -d RDS_DB_NAME < latest.sql

5. Remove your Postgres add-on from Heroku after making sure you have a backup, otherwise you will not be able to modify DATABASE_URL.

6. Set your new DATABASE_URL:

    heroku config:set DATABASE_URL=postgres://RDS_DB_USER:RDS_DB_PASS@RDS_DB_HOST:5432/RDS_DB_NAME?sslmode=require

7. Get your app out of maintenance:

    heroku maintenance:off

Resources

1. Amazon RDS on Heroku

heroku pg:backups:capture
heroku pg:backups:download

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

pg_restore --verbose --clean --no-acl --no-owner -h $NAME.$ID.$DATACENTER.rds.amazonaws.com -U $RDS_ROOT_USER -d $DATABASE_NAME /tmp/latest.dump

https://medium.com/@sebastien.carceles/how-to-migrate-your-postgresql-database-from-heroku-to-amazon-rds-358f9e45c10e#75e9

psql$ create role whiteunicorn1234 with password 'PASTE_P'W'D_HERE' login;
psql$ create database whiteunicorn1234;
psql$ grant all on database whiteunicorn1234 to whiteunicorn1234;
psql$ \q

$ cd your_app
$ mkdir -p config
$ curl https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem > ./config/rds-combined-ca-bundle.pem
$ git add config/rds-combined-ca-bundle.pem
$ git commit -m "Add RDS certificate to app files"
$ git push heroku master

https://www.lewagon.com/blog/how-to-migrate-heroku-postgres-database-to-amazon-rds

$ heroku addons:destroy heroku-postgresql
$ heroku config:set \
    DATABASE_URL="postgres://whiteunicorn1234:$PASSWORD@$NAME.$ID.$DATACENTER.rds.amazonaws.com/whiteunicorn1234?sslca=config/rds-combined-ca-bundle.pem"
$ heroku maintenance:off