Skip to content

Instantly share code, notes, and snippets.

Forked from delameko/
Last active September 8, 2023 03:26
Show Gist options
  • Save Clark-G/01fcb84c658c44dcec4af1a965e2c3b4 to your computer and use it in GitHub Desktop.
Save Clark-G/01fcb84c658c44dcec4af1a965e2c3b4 to your computer and use it in GitHub Desktop.
Upgrading PostgreSQL from 9.5 to 11 on Ubuntu 16.04


Install Postgres 11, and then:

sudo pg_dropcluster 11 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 apt update

sudo apt install postgresql-11 postgresql-client-11 postgresql-contrib-11

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-11                                             - front-end programs for PostgreSQL 9.6
i A postgresql-contrib-9.5                                            - additional facilities for PostgreSQL
i A postgresql-contrib-11                                            - 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
11 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 11 (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 11/main also exists. The recommended procedure is to remove the 11 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.6 cluster and drop it.

sudo pg_dropcluster 11 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
11 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 = '11/main'.

Copy link

Thank you

Copy link

Pomax commented Apr 6, 2023

Unfortunately this fails on the very first line in 2023, as "Install Postgres 11" doesn't appear to be possible on Xenial using the standard PPA approach:

$ sudo sh -c 'echo "deb $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - | sudo apt-key add -
$ sudo apt-get update
Hit:1 xenial-backports InRelease
Reading package lists... Done
$ sudo apt -y install postgresql-11
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgresql-11

So.... how does one install 11?

Copy link

gorakaunhai commented Apr 24, 2023

@Pomax, I encountered the same problem. The good news is I found the solution here: API - PostgreSQL Wiki

Under the "News" section, the entry on 2022-07-06 says:

Ubuntu groovy (20.10) and hirsute (21.04) have been migrated to xenial (16.04) has been copied as well ...

All I had to do was edit "/etc/apt/sources.list.d/pgdg.list" and change "" to "". After that, just followed the rest of the steps as given in the OP.

The bad news is the archive does not have the xenial packages for all architectures (use "uname -m" to find out which architecture you need). I needed "x86_64" which thankfully is in the archive; but "arm64" for example (macos) is not. You will find out pretty quickly if the one you need is not there as "sudo apt update" will give a warning saying as much.

Good luck.

Copy link

Pomax commented Apr 24, 2023

Super, thanks so much!

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