Migrating from AWS RDS MySQL to Heroku Postgres

migrate_from_aws_rds_mysql_to_heroku_postgres.md

Overview

1. Expose your RDS instance to outside world.
2. Use pgloader to copy over your MySQL db to a local Postgres instance on your development machine.
3. Dump your local Postgres db.
4. Use heroku pg:backups:restore to upload your Postgres db dump.

Steps

1. Follow the basic instructions for preparing your AWS RDS instance for data export (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html). The most important part is to make sure that you have all your security settings (e.g., VPC, subnet, db security group if applicable) set up to expose port 3306 to the outside world and have your db root username and password. If you've forgotten your db's root password, follow these instructions to reset it: https://aws.amazon.com/premiumsupport/knowledge-center/reset-master-user-password-rds/
2. Install Postgres on your local machine.
3. Install pgloader on your local machine.
4. Create a database on your local machine that will receive the data from your RDS instance. A simple CREATE DATABASE in psql should suffice. I recommend naming it the same db name as the one that you use on RDS.
5. Run pgloader mysql://<RDS_ROOT_USERNAME>:<RDS_ROOT_PASSWORD>@<RDS_ENDPOINT>/<RDS_DATABASE_NAME> postgresql:///<LOCAL_DATABASE_NAME on your local machine. Note that the RDS root username is accessible via AWS console (go to RDS -> Instances -> your MySQL instance). Also take care to look through pgloader's logs. You'll notice that due to incompatibilities between MySQL and Postgres, pgloader automatically changes some parts of your db schema, perhaps the most common behavior being to truncate index names that are too long. You may need to modify your app accordingly.
6. RDS stores all tables in a schema named after your database name, whereas by default Postgres expects tables to be in the public schema. Unless you've configured your app to use a non-default schema, you'll want to move your tables over to the default schema. The easiest way to do this is simply to run DROP SCHEMA public and then ALTER SCHEMA <RDS_DATABASE_NAME> RENAME TO public in psql on your local machine.
7. Run pg_dump -Fc --no-acl --no-owner -h localhost <LOCAL_DATABASE_NAME> > dumpfile as per the instructions in https://devcenter.heroku.com/articles/heroku-postgres-import-export#create-dump-file.
8. Run heroku maintenance:on to disable reads/writes to your db before you migrate.
9. Upload your dumpfile somewhere publicly accessible and import it into Heroku with heroku pg:backups:restore '<DUMPFILE_URL>' <TARGET_HEROKU_DB_URL_ALIAS> as per the instructions in https://devcenter.heroku.com/articles/heroku-postgres-import-export#import-to-heroku-postgres.
10. Run heroku maintenance:off to bring your Heroku db back online.
11. Monitor your Heroku app to make sure that everything's OK. It's not uncommon to have little errors here and there due to inconsistencies in the copying process. You may need to manually fix these in your Heroku db or in your app.
12. Be sure to delete your dumpfile and reset any modified security settings when you're done.

Alternatives

1. Use mysqldump to dump your RDS db as per AWS' instructions, then import to a local MySQL db, then use pgloader with your local MySQL db as the source. (Seems unnecessarily complicated. Note that if you go this route that you shouldn't leave a space between the -u/-p options and the RDS root username and password when running mysqldump on your local machine, or else you'll get a mysqldump: Got error: 1045: Access denied for user error.)
2. Use a tool other than pgloader. (Didn't work for me.)
3. Use AWS' Database Migration Service to migrate from your MySQL RDS instance to a Postgres RDS instance, then use pg_dump on the Postgres RDS instance. (Didn't work nearly as well as using pgloader for me.)