I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do?

Question

I just ran yum update on my Amazon Linux instance as part of an upgrade to 2012.09 and it upgraded PostgreSQL from 9.1 to 9.2. Now PostgreSQL won't start. What do I do?

(This is a very incomplete question because it's just here to provide somewhere to post the answer).

Answer 1

Amazon's packages for PostgreSQL will upgrade from 9.1 to 9.2 as if that was a normal minor update. It isn't; see the PostgreSQL version policy, which makes it clear that the major version is "x.y", eg "9.1", "9.2", etc. This version policy may be weird and perhaps unwise, but it's also widely understood and something you would expect packagers to understand. The documentation for upgrading a PostgreSQL cluster explains this point very clearly.

The Amazon packages don't seem to get that, and just upgrade PostgreSQL. This is a very bad idea. Upgrades can involve downtime and backward-compatibility-breaking changes. C extension functions must be recompiled. A dump and reload may be required if pg_upgrade can't be used with your database. Even if pg_upgrade can be used, downtime can be significant.

See Amazon's severe PostgreSQL packaging blunder by Andrew Dunstan, and this bug report on the AWS forums.

Amazon has a FAQ entry for this, but it's really the wrong answer. The right answer is "don't upgrade without asking!". Not only that, but doing a pg_upgrade can take some preparation and won't work for all databases. This is really bad advice.

Nonetheless, pg_upgrade is your only easy option once this has happened. If you can't use it, you'll need to roll back to PostgreSQL 9.1, pg_dump your DBs and pg_dumpall --globals-only as well, then install 9.2 and load the dumps. See upgrading a PostgreSQL cluster