upgrading to postgres on Heroku

Question

What is the recommended way to upgrade a Heroku Postgres production database to 9.2 with minimal downtime? Is it possible to use a follower, or should we take the pgbackups/snapshots route?

Answer 1

Until logical followers in 9.4, you'll have to dump and restore (for the reasons Craig describes). You can simplify this with pgbackups:transfer. The direct transfer is faster than dump and restore, but know that you won't have a snapshot to keep.

The script below is basically Heroku's Using PG Backups to Upgrade Heroku Postgres Databases with modification for pgbackups:transfer. (If you have multiple instances, say a staging server, add "-a" or "--remote" to each Heroku line to specify which server.)

# get the pgbackups plugin
heroku plugins:install git://github.com/heroku/heroku-pg-extras.git

# provision new db
heroku addons:add heroku-postgresql:crane --version=9.2

# wait for it to come online, make note of new color
heroku pg:wait

# prevent new data from arriving during dump
heroku ps:scale worker=0 web=0
heroku maintenance:on

# copy over the DB. could take a while.
heroku pgbackups:transfer OLDCOLOR NEWCOLOR

# promote new database as default for DATABASE_URL
heroku pg:promote NEWCOLOR

# start everything back up and test
heroku ps:scale worker=N web=N
heroku maintenance:off
heroku open

# remove old database
heroku addons:remove HEROKU_POSTGRESQL_OLDCOLOR

Note that if you compare your data size between them, the new one may be much smaller because of efficiencies in 9.2. (My 9.2 was about 70% of the 9.1.)

Answer 2

Heroku followers are, AFAIK, just PostgreSQL streaming replica servers. This means you can't use them across versions, you must have binary-compatible databases.

The same techniques should apply as ordinary PostgreSQL, except that you may not be able to use pg_upgrade on Heroku. This requires shell (ssh, etc) access as the postgres user on the system that hosts the database, so I doubt it's possible on Heroku unless they've provided a tool to run pg_upgrade for you. I can't find much information on this.

You will probably have to look at using Slony-I, Bucardo, or another trigger-based replication solution to do the upgrade unless you can find a way to run pg_upgrade on a Heroku database instance. The general idea is that you set up a new 9.2 instance, use Slony to clone data from the 9.1 instance into it, then once they're fully in sync you stop the 9.1 instance, remove the Slony triggers, and switch clients over to the 9.2 instance.

Search for more information on "postgresql low downtime upgrade slony" etc, see how you go.