Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save delameko/bd3aa2a54a15c50c723f0eef8f583a44 to your computer and use it in GitHub Desktop.
Save delameko/bd3aa2a54a15c50c723f0eef8f583a44 to your computer and use it in GitHub Desktop.
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

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

Copy link

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

Same, upgrading from 9.6 to 10 worked perfectly

Copy link

Thanks a lot! It worked for 10 perfectly.

Copy link

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

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

Thanks my friend, it works without problems

Copy link

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