Sunday, December 28, 2014

Upgrading Postgres to 9.4 beta3

I've been running Debian testing for quite a while and have recently had an error when attempting to update my Postgresql database
The on-disk format of the PostgreSQL 9.4 data files has changed between
beta2 and beta3 (and as a consequence, the catalog version number). For that
reason, existing PostgreSQL 9.4 clusters need to be dumped using the old
package version, and reloaded after upgrading the packages.

The postgresql-9.4 package will now refuse to upgrade because version 9.4
clusters exist on the system.

Per default, a "main" cluster is created. Run "pg_lsclusters" to check if
other clusters exists, and repeat the steps below appropriately.

To resolve the situation, before upgrading, execute:
# su - postgres
$ pg_lsclusters
$ pg_ctlcluster 9.4 main start
$ pg_dumpall --cluster 9.4/main | gzip > 9.4-main.dump.gz
$ cp -a /etc/postgresql/9.4/main 9.4-main.config
$ pg_dropcluster 9.4 main --stop

Then after the upgrade, execute:
# su - postgres
$ pg_createcluster 9.4 main
$ cp 9.4-main.config/* /etc/postgresql/9.4/main
$ pg_ctlcluster 9.4 main start
$ zcat 9.4-main.dump.gz | psql -q --cluster 9.4/main
$ rm -rf 9.4-main.config 9.4-main.dump.gz
So I did this :) only to get the following message on the zcat | psql step
ERROR:  role "postgres" already exists

Googling for the error message gave the suggestion that I should use the command dropuser postgres, but that just produced the message
dropuser: removal of role "postgres" failed: ERROR:  current user cannot be dropped

At this point I tried a little poking around and close to the top of the dump file I discovered the line
CREATE ROLE postgres;
A quick test of dropping the CREATE ROLE line worked first time. This meant that the zcat line now reads
zcat 9.4-main.dump.gz | grep -v 'CREATE ROLE postgres'|psql -q --cluster 9.4/main

With this change the upgrade's worked without obvious errors and psql is responding to simple queries. Unfortunately I don't know enough about postgres to be able to say that everything is working.

No comments: