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:

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.