Upgrading your PostgreSQL cluster from 9.3 to 9.4 back to frontpage

For some reason unknown to me, Ubuntu decided to ship PostgreSQL 9.4 beta 3 instead of the stable version 9.3 with the release of Ubuntu 14.10 (which seems to be an odd decision to me).

Anyway, for all those of you who want to upgrade their existing PostgreSQL 9.3 cluster to 9.4 on a freshly installed Ubuntu 14.10 (this article is not limited to Ubuntu 14.10, of course), here is how you do it using pg_upgradecluster (you could also try upgrade using pg_upgrade which should usually be faster, but never tried):

  1. In order to upgrade, you need the old 9.3 binaries. You need both the postgresql-9.3 as well as the postgresql-client-9.3 package (install using dpkg -i postgresql*.deb).

  2. Make a backup of your existing 9.3 cluster you want to upgrade (always good). If anything goes wrong during the process, you still have your data backup.

  3. Inspect your existing clusters using pg_lscluster. It should list two existing clusters, very similar to this:

     Ver Cluster Port Status Owner    Data directory   Log file
    9.3 main    5432 online postgres /data_ssd/psql   /var/log/postgresql/...-9.3-main.log
    9.4 main    5433 online postgres /data_ssd/psql2/ /var/log/postgresql/...-9.4-main.log

    If the 9.4 cluster shows up only, make sure you’ve copied your 9.3 cluster configuration to /etc/postgresql.

  4. Drop the default 9.4 main cluster using pg_dropcluster 9.4 main.

  5. Start upgrading using pg_upgradecluster 9.3 main /your/new/location. You can drop the new location if you want to stick to the default one (which is /var/lib/postgresql/newversion/name according to the man pages).

Upgrading might take a while depending on your cluster size. I got a 41 GB cluster with about 500 million entities which took a few hours to upgrade.

After the upgrade succeeded and you’ve inspected the new cluster on any upgrade errors you can remove the previously installed 9.3 binaries and your cluster backup.

New comment

Comments are moderated and therefore are not published instantly.


No comments yet. Be the first! :-)