Error upgrading postgresql cluster from 9.1 to 9.2

Question

I'm on Ubuntu 12.04 64 bit on Amazon EC2. Trying to upgrade postgresql from 9.1 to 9.2.

$ uname -a
Linux db2 3.2.0-32-virtual #51-Ubuntu SMP Wed Sep 26 21:53:42 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

$ apt-cache policy postgresql
postgresql:
  Installed: 9.1+136~precise
  Candidate: 9.1+136~precise
  Version table:
 *** 9.1+136~precise 0
    500 http://ppa.launchpad.net/pitti/postgresql/ubuntu/ precise/main amd64                 Packages
    100 /var/lib/dpkg/status
 9.1+129ubuntu1 0
    500 http://us-east-1.ec2.archive.ubuntu.com/ubuntu/ precise-updates/main amd64 Packages
 9.1+129 0
    500 http://us-east-1.ec2.archive.ubuntu.com/ubuntu/ precise/main amd64 Packages

The upgrade process I'm following is:

$ sudo add-apt-repository ppa:pitti/postgresql
$ sudo apt-get update
$ sudo apt-get install postgres-9.2
$ sudo pg_dropcluster --stop 9.2 main
$ sudo pg_upgradecluster 9.1 main /var/lib/postgresql/9.2
Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster (configuration: /etc/postgresql/9.2/main, data: /var/lib/postgresql/9.2)...
Moving configuration file /var/lib/postgresql/9.2/postgresql.conf to /etc/postgresql/9.2/main...
Moving configuration file /var/lib/postgresql/9.2/pg_hba.conf to /etc/postgresql/9.2/main...
Moving configuration file /var/lib/postgresql/9.2/pg_ident.conf to /etc/postgresql/9.2/main...
Configuring postgresql.conf to use port 5433...
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
ERROR:  cannot set transaction read-write mode during recovery
Error: Could not fix library paths
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Error during cluster dumping, removing new cluster

Any help would be appreciated. Thank you.

Answer 1

The root cause of your issue is that hot_standby is on in postgresql.conf, so the server is read-only.

In general, if you're having issues with pg_upgradecluster from the pg_wrapper tools typically packaged in Debian and Ubuntu you can do a manual cluster upgrade instead:

• Start the old server
• sudo -i -u postgres
• for db in $(psql --tuples-only template1 -c "select datname from pg_database where datname not in ('template0','template1','postgres','template_postgis');"); do pg_dump -Fc -f $db.backup $db; done
• pg_dumpall --globals-only > globals.sql
• Stop the old server
• initdb a new cluster on the new server if you've removed it. With pg_wrapper I think you use pg_createcluster for this.
• Start the new server; and still as the postgres user:
• psql -f globals.sql
• for backup in *.backup; do pg_restore --dbname postgres --create $backup; done

Alternately, use the pg_upgrade tool tool to in-place convert your DB, but that might confuse pg_wrapper.

These steps can be simplified by using the pg_dumpall command to make a whole cluster dump, but I don't like it much. I think restoring pg_dumpall dumps leaves much to be desired in terms of error handling, it's hard to extract individual DBs or tables from the dump, and it can't all be restored in a single transaction. I strongly prefer using pg_dumpall only for globals like users/groups/roles, and pg_dump per-database custom-format backups for individual databases as shown above.