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.