Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Upgrading PostgreSQL from 9.5 to 9.6 on Ubuntu 16.04


Install Postgres 9.6, and then:

sudo pg_dropcluster 9.6 main --stop
sudo pg_upgradecluster 9.5 main
sudo pg_dropcluster 9.5 main

Install PostgreSQL:

sudo echo 'deb xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - | \
  sudo apt-key add -
sudo aptitude update

sudo aptitude install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

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

Run pg_lsclusters, your 9.5 and 9.6 main clusters should be "online".

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

There already is a cluster "main" for 9.6 (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.5/main when 9.6/main also exists. The recommended procedure is to remove the 9.6 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.6 cluster and drop it.

sudo pg_dropcluster 9.6 main --stop

Upgrade the 9.5 cluster to the latest version.

sudo pg_upgradecluster 9.5 main

Your 9.5 cluster should now be "down".

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

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

sudo pg_dropcluster 9.5 main


sudo nano /etc/postgresql/9.6/main/postgresql.conf

Change cluster_name = '9.5/main' to cluster_name = '9.6/main'.

Copy link

machinsk commented Dec 12, 2016

The server seems to crash when I sudo pg_upgradecluster 9.5 main
psql: FATAL: the database system is shutting down
and version 9.6 disappears from pg_lsclusters

Copy link

minac commented Mar 14, 2017

Great solution and explanation. Works as advertised. No need to change /etc/postgresql/9.6/main/postgresql.conf anymore. Thank you.

Copy link

akabraham commented Apr 10, 2017

In the TL;DR, Install Postgres 9.5 should be Install Postgres 9.6

Copy link

ashishpandey001 commented Apr 11, 2017

Ensure that postgres 9.5 main cluster is down before dropping it by supplying --stop argument to the pg_dropcluster command as follows:
sudo pg_dropcluster 9.5 main --stop

Copy link

pdaoust commented May 15, 2017

I don't know why, but pg_upgradecluster does logical backups by default (using pg_dump and pg_restore). It seems to me that it would be perfectly acceptable for it to default to using pg_upgrade.

You can specify this behaviour explicitly, though, and dramatically speed things up as a result, by going

pg_upgradecluster -m upgrade 9.5 main

More info here:

Copy link

bimmerlabs commented Sep 22, 2017

I had to stop the 9.5 server before I could run pg_upgrade cluster:
sudo /etc/init.d/postgresql stop
Otherwise I got an error:
psql: FATAL: the database system is shutting down

Copy link

JordanP commented Oct 11, 2017

I tested this to upgrade from 9.6 to 10, it worked flawlessly (I had to s/9.5/9.6/ and s/9.6/10/ obviously).

Copy link

nickclyde commented Oct 12, 2017

Same, upgrading from 9.6 to 10 worked perfectly

Copy link

ruipaulo commented Oct 17, 2017

Thanks a lot! It worked for 10 perfectly.

Copy link

tpberntsen commented Nov 4, 2017

When upgrading to 9.6.x on Ubuntu 17.04, I also had to stop the 9.5 server as per the description by @bimmerlabs

Copy link

mdesantis commented Dec 13, 2017

Here is the 9.6 -> 10 version (it's the same as this one with some search & replace, just for preventing confusion ;-) )

Copy link

lzambarda commented Mar 27, 2018

Hello, just adding some support!

If some of you are having permission issues running this (Got that on an AWS EC2 instance):
sudo echo 'deb xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list
Please try using this:
echo 'deb xenial-pgdg main' | sudo tee -a /etc/apt/sources.list.d/pgdg.list

Also, if running sudo pg_upgradecluster 9.5 main is resulting in this hideous message:
Error: Could not create target cluster

Try executing this command first:
export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8

Copy link

DanielFRB commented May 21, 2021

Thanks my friend, it works without problems

Copy link

adebisi-fa commented Jan 6, 2022

Flawless! v9.5 to v13. Thank you, please.

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