Skip to content

Instantly share code, notes, and snippets.

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

TL;DR

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 http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  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".

pg_lsclusters 
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".

pg_lsclusters 
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

Lastly:

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

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

@machinsk

This comment has been minimized.

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

@minac

This comment has been minimized.

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.

@akabraham

This comment has been minimized.

Copy link

akabraham commented Apr 10, 2017

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

@ashishpandey001

This comment has been minimized.

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

@pdaoust

This comment has been minimized.

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: https://dba.stackexchange.com/a/173400/124650

@bimmerlabs

This comment has been minimized.

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

@JordanP

This comment has been minimized.

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).

@ClydeDroid

This comment has been minimized.

Copy link

ClydeDroid commented Oct 12, 2017

Same, upgrading from 9.6 to 10 worked perfectly

@ruipaulo

This comment has been minimized.

Copy link

ruipaulo commented Oct 17, 2017

Thanks a lot! It worked for 10 perfectly.

@tpberntsen

This comment has been minimized.

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

@mdesantis

This comment has been minimized.

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 ;-) )

@lzambarda

This comment has been minimized.

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 http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list
Please try using this:
echo 'deb http://apt.postgresql.org/pub/repos/apt/ 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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.