Upgrading PostgreSQL from 9.5 to 11 on Ubuntu 16.04

upgrade-postgres-9.5-to-11.md

TL;DR

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

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

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

Lastly:

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

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

Thanks for the gist, albeit there is still some 9.6 references.

Thank you

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 http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
$ sudo apt-get update
Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease
...snip...
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?

@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 apt-archive.postgresql.org. xenial (16.04) has been copied as well ...

All I had to do was edit "/etc/apt/sources.list.d/pgdg.list" and change "http://apt.postgresql.org/pub/repos/apt/" to "http://apt-archive.postgresql.org/pub/repos/apt/". 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.

Super, thanks so much!