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.
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
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
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:firstname.lastname@example.org/heroku12345 postgresql://postgres:postgres@localhost/import_from_heroku
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
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)