If you're developing an application on Heroku and you need user defined functions you may be alarmed to find out that [as of April 2012] you get error messages while attempting a CREATE OR REPLACE FUNCTION on the Heroku shared database. Fortunately you don't have to go very far to get your functions working - but you will have to be willing to use Heroku's public beta replacement shared database. Obviously this means its not fit for productions systems - so for a production system you may need to look at one of the other Postgres database providers for Heroku (and the Rails gem ActiveAdmin won't work with JustOneDB, I know, I've tried).

If you're reading this after April 2012 there's an increased likelihood that Heroku has made the new shared database available as default and this article probably will no longer apply.

In order to migrate your existing shared database to the new public beta version with user defined function support you'll want to backup your existing database - assuming that data is important to you.

We'll use pgbackups for backing up and restoring as this leaves the data on Heroku/Amazon. Note: You'll probably want to disable your application if its in a running state before backing up as any data committed after the backup but before the restore by your users would be effectively lost.

# shell  
$ heroku addons:add pgbackups:basic
$ heroku pgbackups:capture

If this is your first backup you'll likely end up with a backup that can be identified as "b001" - backup identifiers beginning with a "b" signify that they are a manual backup. Next we'll add the new heroku-shared-postgresql addon which automatically creates a database for us:

# shell  
$ heroku addons:add heroku-shared-postgresql

In the output you'll see the identifier of the new shared database all in caps. Mine was HEROKU_SHARED_POSTGRESQL_COBALT; yours may be as well (or perhaps some other _METAL name). Next restore the new database from your backup:

# shell  
heroku pgbackups:restore HEROKU_SHARED_POSTGRESQL_COBALT b001

You'll be asked to confirm. Finally switch your application from the old database to the new database:

# shell  
$ heroku pg:promote HEROKU_SHARED_POSTGRESQL_COBALT

Now you'll be able to run those migrations that create user defined functions and even connect to your database instance using PSQL!

# shell  
$ heroku pg:psql HEROKU_SHARED_POSTGRESQL_COBALT