Upgrading PostgreSQL service from 9.x or 10.x to 11 version on Ubuntu server

upgrade-postgresql-service-ubuntu.md

TL;DR

Create a backup:

sudo -Hiu postgres pg_dumpall > mybackup.sql

Install Postgres 11, and then:

sudo pg_dropcluster 11 main --stop
sudo pg_upgradecluster 9.x main
sudo pg_dropcluster 9.x main

(x is a sub-version number)

---

Install PostgreSQL 11:

# install **wget** if not already installed:
sudo apt install -y wget

# import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# add repository contents to your system:
RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

# install and launch the postgresql service:
sudo apt update
sudo apt -y install postgresql-11

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

ii  postgresql-11                                 object-relational SQL database, version 11 server
ii  postgresql-9.6                                object-relational SQL database, version 9.6 server
ii  postgresql-client-11                          front-end programs for PostgreSQL 11
ii  postgresql-client-9.6                         front-end programs for PostgreSQL 9.6
ii  postgresql-client-common                      manager for multiple PostgreSQL client versions
ii  postgresql-common                             PostgreSQL database-cluster manager
ii  postgresql-contrib-9.6                        additional facilities for PostgreSQL

In my case, I will be performing the upgrade from 9.6 to 11 version, so at the next steps, I will replace the 9.x version with the actual 9.6 version.

Note: guide can be also used to upgrade PostgreSQL 10 version. Upgrade steps fully the same.

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

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
 11 main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-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.6/main when 11/main also exists. The recommended procedure is to remove the 11 cluster with pg_dropcluster and then upgrade the 9.6/main with pg_upgradecluster.

Stop the 11 cluster and drop it.

sudo pg_dropcluster 11 main --stop

Upgrade the 9.6 cluster to the latest version.

sudo pg_upgradecluster 9.6 main

Your 9.6 cluster should now be "down".

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

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

sudo pg_dropcluster 9.6 main

Lastly, please make sure that in the postgresql.conf for new cluster cluster_name parameter is equals to '11/main', otherwise replace the existing value with '11/main':

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

change cluster_name = '9.6/main' to cluster_name = '11/main'.