Heroku recent announced they are nudging out their shared-database offerings, the 5MB (free) and 20GB ($15/mo) plans. For modest app requirements, their new ‘dev’ (free) and ‘basic’ ($9/mo) plans are pretty attractive and seem to offer more reliability, easier management at a comparable price point. The basic plan is brand-new, described here.

For a couple of our apps, I wanted to take the new databases for a spin. I particularly like that they are Postgres 9.1, run on the production infrastructure, and offer a Postgres CLI.

In terms of fun ways to pass time, I’ve always put migrating databases in the same camp as getting a root canal. However, the Heroku folks have made it very easy to migrate – we migrated three databases for three apps in less than 10 minutes.

We migrated our 20GB shared-database apps to the ‘basic’ plan, and actually saved a few bucks per month. Haven’t run benchmarks yet, but our app performance seemed faster on some of the more database-intensive reporting screens.

The steps we took are fairly well documented here by Heroku. (However, the Heroku instructions fail to explain how to get rid of the old shared-database:20gb to prevent continued billing at $15/mo.)

For each app, we cd to the app’s local directory on our dev machine, and use the Heroku CLI to make the following changes. (We ran gem update heroku first to ensure we were using the uptodate CLI.)

First, we installed the postgres add-on:
heroku addons:add heroku-postgresql:basic

When you run the command it created a new database:
Attached as HEROKU_POSTGRESQL_SOMECOLOR
You can see it by typing heroku config

Most of our apps were already running pgbackups:monthly (free), on one of them we ran:
heroku addons:add pgbackups

Put the app in maintenance mode:
heroku maintenance:on

Capture a backup of the current database:
heroku pgbackups:capture --expire
(Took under 2 minutes for a 12 MB database)

Now push that data into the new database:
heroku pgbackups:restore HEROKU_POSTGRESQL_SOMECOLOR

Now make the new database the current database:
heroku pg:promote HEROKU_POSTGRESQL_SOMECOLOR
(Replaces the heroku config variable DATABASE_URL with the URL for the new database. The old shared-database and it’s URL are still there, but they are not the default anymore.)

Check the new database is working:
heroku pg:psql HEROKU_POSTGRESQL_SOMECOLOR
=> select count(*) from users;
(To quit the CLI type \q )

Turn off maintenance mode:
heroku maintenance:off

After you do some testing and make sure everything is fine (it was for us), if you are using the share-database:20gb plan ($15/mo), you’ll want to get rid of that add-on. You cannot, however, remove it, you need to ‘downgrade’ it to the freebie version:
heroku addons:downgrade shared-database
Should display a message the shared-database is downgraded to the free version.

Hmmm, as of August 1st it looks like you CAN remove the shared-database plan…
heroku addons:remove shared-database