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 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