Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Upgrading PostgreSQL from 9.3 to 9.4 when upgrading Ubuntu 14.04 to 14.10


Create a backup:

pg_dumpall > mybackup.sql

Perform the upgrade:

sudo pg_dropcluster 9.4 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

While upgrading Ubuntu 14.04 to 14.10, I got the following warning message:

The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster

Once the Ubuntu upgrade finished, I used aptitude search to check which versions of postgres I have installed.

i   postgresql                                       - object-relational SQL database (supported version)
i A postgresql-9.3                                   - object-relational SQL database, version 9.3 server
i A postgresql-9.4                                   - object-relational SQL database, version 9.4 server
i A postgresql-client-9.3                            - front-end programs for PostgreSQL 9.3
i A postgresql-client-9.4                            - front-end programs for PostgreSQL 9.4
i A postgresql-contrib-9.3                           - additional facilities for PostgreSQL
i A postgresql-contrib-9.4                           - additional facilities for PostgreSQL

Looks like the Ubuntu upgrade included PostgreSQL 9.4, but I still need to upgrade from 9.3 to 9.4.

Run pg_lsclusters, your 9.3 and 9.4 main clusters should be "online".

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

There already is a cluster "main" for 9.4 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.3/main when 9.4/main also exists. The recommended procedure is to remove the 9.4 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.4 cluster and drop it.

sudo pg_dropcluster 9.4 main --stop

Upgrade the 9.3 cluster to the latest version.

sudo pg_upgradecluster 9.3 main

Your 9.3 cluster should now be "down".

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5433 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Check that the upgraded cluster works, then remove the 9.3 cluster.

sudo pg_dropcluster 9.3 main

Looks good.

devton commented Apr 19, 2015


Thank you!

Super helpful, thanks!


sdvcrx commented Jun 30, 2015


mro commented Aug 8, 2015




mzatko commented Oct 26, 2015

great! Thank you!

boblail commented Nov 8, 2015

thanks! πŸ‘

jgrocha commented Nov 30, 2015

Great! But I need to stop 9.3 before the pg_upgradecluster.

sudo pg_dropcluster 9.4 main --stop
sudo /etc/init.d/postgresql stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

thanks! πŸ‘

This work for 9.5 too

JChrist commented Mar 19, 2016

πŸ‘ same steps for upgrading from 9.4 to 9.5

Thank you!

same for 9.3 to 9.6

Can I recommend that as your very first step, you run pg_dumpall > mybackup.sql so you have a backup of all your databases... especially do this before running any of the pg_dropcluster stuff... this saved my bacon.

TangMonk commented Mar 6, 2017

If you face locale error, like this:

Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = "en_US.UTF-8",
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: Could not create target cluster

You can use following command to fix it:

export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8

ismailakbudak commented Apr 27, 2017

thanks.. πŸ‘

Kobold commented Oct 9, 2017

πŸ‘ Thanks!


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment