postgres updated from 9.1 to 9.2, required database update fails

Question

On going from postgres 9.1 to 9.2, it says that the data bases need to be upgraded so on giving the upgrade command I give and get the following

$ sudo service postgresql upgrade
Stopping postgresql service:                               [  OK  ]
Upgrading database:                                        [FAILED]
See /var/lib/pgsql9/pgupgrade.log for details.

The contents of the log file /var/lib/pgsql9/pgupgrade.log are

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok

connection to database failed: fe_sendauth: no password supplied


could not connect to old postmaster started with the command:
"/usr/lib64/pgsql/postgresql-9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql9/data-old" -o "-p 5432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql9'" start
Failure, exiting

I used the postgres with password enable something like this pg_connect("host=localhost port=5432 dbname=pgdbname user=postgres password=postgrespassword");

I had yum updated my system and fell into this. Can you even suggest a way in which I can revert back everything then it will be great!

Answer 1

Workaround

Edit your pg_hba.conf and set connections on local to trust for the duration of the upgrade.

Background

Amazon's Amazon Linux team have made the bizarre decision to do a major version upgrade of PostgreSQL on a simple "yum update". Most vendors use different package names for different major versions so users have to make the explicit decision to upgrade. Amazon has not done so and do not take feedback about this issue seriously.

Upgrading users to a new PostgreSQL version on "yum update" is the wrong thing to do for exactly the reason you've encountered: pg_upgrade has limitations, and you can't do a dump and reload unless you have the old server version's binaries around.

I wrote a note about this a while ago.

The Amazon Linux FAQ entry on this topic has improved since I wrote about it, but still doesn't acknowledge that they're creating the problem with their bad packaging policies.

Seriously consider using a distro that takes more care with packaging PostgreSQL. The PGDG team isn't producing packages for Amazon Linux at this point, otherwise I'd recommend using them.