How to convert MySQL database to Postresql on Heroku

I had a Rails app running on Heroku with the ClearDB MySQL add-on. This add-on is expensive and there are a number of reasons why Postgres is a better fit for my project. Here is my quick-and-dirty recipe for how to make the switch with minimal downtime.

Setup

In the Heroku UI, attach a Postgres add-on. It will add a config var that looks like this: HEROKU_POSTGRESQL_SILVER. Copy the value of the new database URL and make a new config var: NEW_DATABASE_URL. For now, the production database config var (probably DATABASE_URL) should say in place.

Do this next stuff in the local development environment to make sure the app is ready to run with Postgres.

Create a local Postgres database: $ createdb import_from_heroku

Add the pg gem to the Gemfile

Update the adapter in the Rails database configurations. You probably need to change the adapter value and the port. Mine looks something like this:

default: &default 
  adapter: postgresql 
  encoding: unicode 
  pool: 5 
  host: localhost 
  port: 5432

Also update the production database configs to the new config var we created above. It should look something like this:

production: url: <%= ENV['NEW_DATABASE_URL'] %>

The exciting part

Rehearse this locally fist to make sure all the commands work! If you have multiple Heroku apps, you’ll need to add --app your_app_name to the end of each heroku command.

Maintenance mode

heroku maintentance:on

Import the production database

The first URL in the following command should be the MySQL database URL from the production Heroku config var. Substitute the real one in the command below.

$ pgloader mysql://abc:123@blablabla.cleardb.net/heroku12345 postgresql://postgres:postgres@localhost/import_from_heroku

NOTE: use mysql prefix even if you’re using mysql2 adapter.

This command will dump the Heroku database into your local, but it puts all the tables into a new schema named after the original DB. I’m not sure if this matters, but to be safe I did the following to match the remote structure. In the second line, substitute the name of your database/schema.

BEGIN TRANSACTION;
  ALTER SCHEMA public RENAME TO public_original;
  ALTER SCHEMA heroku_6420df23258fdf2 RENAME TO public;
  DROP SCHEMA  public_original CASCADE;
COMMIT;

Push converted postgres database to Heroku

heroku pg:push import_from_heroku HEROKU_POSTGRESQL_SILVER

To prevent accidental data overwrites and loss, the remote database must be empty. You’ll be prompted to pg:reset a remote database that isn’t empty.

Now your Heroku app has a Postgres version of your MySQL database. But it’s just sitting there unused…

Push updated code to Heroku

$ git push heroku whatever-branch-you-updated-the-database-configs:master

Maintenance mode off

heroku maintentance:off 

Cleanup

Now your app should be running the new Postgres database. You probably want to delete the MySQL database add-on so you don’t keep paying for it. Maybe take a backup first to be safe? It’s also probably a good idea to copy the value of NEW_DATABASE_URL to the standard DATABASE_URL config var, then update the configs again in your app. (I find that old leftover config vars make things messy and confusing if you ever have to troubleshoot)