Upgrading PostgreSQL from 9.3 to 9.4 on Ubuntu 14.04 LTS

We’re using Ubuntu 14.04 LTS at work and we wanted to use the new JSONB feature in PostgreSQL 9.4.

Updated article for 9.4 to 9.5:

https://medium.com/@tk512/upgrading-postgresql-from-9-4-to-9-5-on-ubuntu-14-04-lts-dfd93773d4a5

After doing the upgrades, I felt I could write a quick easy-to-read guide for people who want to do a safe upgrade.

The first thing I do is to stop the current Postgres database:

$ sudo /etc/init.d/postgresql stop

Create a new file called /etc/apt/sources.list.d/pgdg.list and add the following to it:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

This is as per http://www.postgresql.org/download/linux/ubuntu/

Now do:

$ wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Followed by:

$ sudo apt-get update
$ sudo apt-get install postgresql-9.4

(Do however note that if PostGIS is installed, you need to also install the postgresql-9.x-postgis-scripts package. Thanks Jibu James!)

As you can see from pg_lsclusters, we have a new 9.4 cluster:

$ sudo pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

This is an empty cluster, so let’s drop it. We’re going to upgrade the old cluster to become a 9.4 cluster.

$ sudo pg_dropcluster --stop 9.4 main

Let’s start PostgreSQL again (note, it may not be necessary to start it, according to feedback I received from others who upgraded).

$ sudo /etc/init.d/postgresql start

And then upgrade the 9.3 cluster to become a 9.4 cluster. This will take some time depending on the size of your cluster. On a 17GB cluster it took an hour so be aware of this.

$ sudo pg_upgradecluster 9.3 main

We can now drop the old cluster, as the new 9.4 one will be running on port 5433.

$ sudo pg_dropcluster 9.3 main

The upgraded cluster will now be running on port 5433.

$ sudo pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Connect to it and check that everything is working 100%. You’re now on 9.4 and all your databases, roles, schemas and ACL’s have been migrated.