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):
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.
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
Drop the default 9.4
pg_dropcluster 9.4 main.
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/nameaccording 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.