How to Migrate a Rails App from MySQL to Postgres

I recently switched from developing Rails apps on Windows to macOS. In doing so, I had a ton of difficulty getting the mysql2 gem to install. That turned out to be the last straw, and I migrated all of my apps from MySQL to Postgres.

Moving data from a MySQL database to a Postgres database is ridiculously easy. The open-source pgloader script does all of it for you in just one line. For me, the migration of one of my apps looked like this:

brew install pgloader
pgloader mysql://MYSQL_USERNAME:[email protected]_DB_HOST/MYSQL_DB postgresql://POSTGRES_USER_NAME:[email protected]_DB_HOST/POSTGRES_DB

Obviously, I replaced MYSQL_USERNAME with the username for my MySQL database, MYSQL_DB_HOST with the DNS address of my MySQL database, MYSQL_DB with the name of the database I wanted to migrate from, and all of the POSTGRES_* things were replaced similarly.

On my biggest database, this took a couple hours, and the rest of my databases were able to complete in just a few minutes. Obviously, this all depends on the amount of data you're transferring, the speed of your databases, and the bandwidth of the link between them.

Once I had transferred my data over, I just had to update my database.yml files to point to the new database, install the pg gem, and test it all out.

I had dreaded this migration for a long time, but I was surprised to find that I was able to complete it in just one weekend! The hardest part of the migration was standing up duplicate staging instances of all my apps to enable testing the new database cnofigurations without affecting the currently running apps.

Photo by Gareth Davies