Upgrading PostgreSQL from 9.4 to 9.5 on Ubuntu 14.04 LTS
This is a straight forward guide for people who want to safely upgrade from 9.4 to 9.5, in order to get functionality like UPSERT.
Let’s first stop PostgreSQL:
sudo pg_ctlcluster 9.4 main stop
Edit /etc/apt/sources.list.d/pgdg.list and make sure it contains this line:
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
(If it already contains a similar line with ‘utopic-pgdg’, just replace it with ‘trusty-pgdg’)
Also make sure we have the repository key installed:
$ wget -q -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
Now run the following:
$ sudo apt-get update
$ sudo apt-get install postgresql-9.5
If the above gave you a choice like shown below, use ‘keep the local version’.
As you can see from running pg_lsclusters, we now have a new 9.5 cluster:
The “9.5 main” one is an empty cluster, so it’s safe to drop it. We’re going to upgrade the old cluster to become a 9.5 cluster.
$ sudo pg_dropcluster --stop 9.5 main
And then upgrade the 9.4 cluster to become a 9.5 cluster.
$ sudo pg_upgradecluster 9.4 main
This may fail with “pg_dump: WARNING: out of shared memory”, in which case you may have to stop the 9.4 cluster, edit postgresql.conf and increase the max_locks_per_transaction to 128 or 256.
The upgrade can take a while, depending on the size of your cluster and other factors. A 10GB cluster took me 2hrs.
We can now drop the old cluster, as the new 9.5 one will be running on port 5432 and the old one on port 5433.
$ sudo pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.5 main 5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
Connect to it and check that everything is working 100%. When you’re happy, you can drop the old 9.4 cluster:
$ sudo pg_dropcluster 9.4 main
Congrats. You’re now on 9.5 and all your databases, roles, schemas and ACL’s have been safely upgraded.