- Expose your RDS instance to outside world.
- Use pgloader to copy over your MySQL db to a local Postgres instance on your development machine.
- Dump your local Postgres db.
- Use
heroku pg:backups:restore
to upload your Postgres db dump.
- 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/
- Install Postgres on your local machine.
- Install
pgloader
on your local machine. - 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. - 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. - 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 runDROP SCHEMA public
and thenALTER SCHEMA <RDS_DATABASE_NAME> RENAME TO public
in psql on your local machine. - 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. - Run
heroku maintenance:on
to disable reads/writes to your db before you migrate. - 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. - Run
heroku maintenance:off
to bring your Heroku db back online. - 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.
- Be sure to delete your dumpfile and reset any modified security settings when you're done.
- Use
mysqldump
to dump your RDS db as per AWS' instructions, then import to a local MySQL db, then usepgloader
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 runningmysqldump
on your local machine, or else you'll get amysqldump: Got error: 1045: Access denied for user
error.) - Use a tool other than
pgloader
. (Didn't work for me.) - 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 usingpgloader
for me.)