11

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?

nathany
  • 539
  • 5
  • 16
  • Because the (highest voted) solution is applicable to 9.2 and 9.3, you might consider removing the version number from your question title. – Jared Beck Jan 07 '14 at 16:25

2 Answers2

17

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.)

Ortwin Gentz
  • 52,648
  • 24
  • 135
  • 213
Turadg
  • 7,471
  • 2
  • 48
  • 49
1

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Craig is correct, they are streaming replicas: you can't use this route for upgrading. Currently, the best approach would be to use the pgbackups:transfer command available in the pg-extras plugin (https://github.com/heroku/heroku-pg-extras#pgbackupstransfer) to transfer the data quickly (while you're app is in maintenance mode). In the future, we have plans to support easier upgrade options. – hgmnz Feb 01 '13 at 22:55
  • @hgmnz Why not use [heroku-pgupgrade](https://github.com/hgmnz/heroku-pgupgrade)? – thejaz Feb 17 '13 at 12:40
  • @thejaz aha! Well, because it is currently in beta, and it happens to be broken (server side) until further notice. Once it's ready for prime time, I'll make sure to note it on that repo. – hgmnz Feb 18 '13 at 16:58
  • Logical followers may arrive in PostgreSQL 9.3 https://twitter.com/MattRogish/status/279625792877641730 – Turadg Mar 04 '13 at 17:03