Heroku DB Migration.mdown

Recently, I had a staging database on Heroku that was running on the Ronin database (which was originally the lowest-sized DB you could get at Heroku). Since they added two new options, Crane and Kappa, we wanted to take advantage of the cost savings. Here's how you can migrate your Ronin DB to Crane (or any other plan).

The old database was named BROWN while the new one is CRIMSON. You can determine this by running:

heroku pg:info --app myapp-staging

1. Add Crane database

    heroku addons:add heroku-postgresql:crane --app myapp-staging
    heroku pg:wait --app myapp-staging
   

2. Turn on maintenance mode and capture a backup

    heroku maintenance:on --app myapp-staging
    heroku pgbackups:capture --expire --app myapp-staging
   

3. Figure out what the name of the new database is

    heroku config --app myapp-staging | grep POSTGRESQL
   

4. Restore the latest backup to the new database and verify it worked.

    heroku pgbackups:restore HEROKU_POSTGRESQL_CRIMSON --app myapp-staging
    heroku pg:psql HEROKU_POSTGRESQL_CRIMSON --app myapp-staging
    select count(*) from users;
    => 1
   

5. Update the app to use the new database URL

    heroku pg:promote HEROKU_POSTGRESQL_CRIMSON --app myapp-staging
   

6. Turn off maintenance mode:

    heroku restart --app myapp-staging
    heroku maintenance:off --app myapp-staging
   

7. Verify everything worked the way you expect (i.e. create a new user):

    heroku pg:psql HEROKU_POSTGRESQL_CRIMSON --app myapp-staging
    select count(*) from users;
    => 2
   
    heroku pg:psql HEROKU_POSTGRESQL_BROWN --app myapp-staging
    select count(*) from users;
    => 1
   

8. Remove the old database:

    heroku addons:remove heroku-postgresql:ronin --app myapp-staging
   

Check out the following sites for details

• Getting Started with the Heroku Postgres Add-on
• Migrating Between Databases