Upgrade PostgreSQL from 13 to 14 on Ubuntu 22.04

Howto guide for upgrading PostgreSQL from version 13 to 14 on Ubuntu, after its upgrade from version 21.10 to 22.04 (Jammy Jellyfish).

© 2022 Paolo Melchiorre “Photo of Pineto beach in Abruzzo, Italy”

TL;DR¶

After upgrade Ubuntu from version 21.10 to 22.04:

$ sudo pg_dropcluster 14 main --stop
$ sudo pg_upgradecluster 13 main
$ sudo pg_dropcluster 13 main

Goal¶

This article is aimed at those like me who use Ubuntu and PostgreSQL to develop locally on their computer and after the last update to Ubuntu 22.04 they have two versions of PostgreSQL installed.

Upgrade PostgreSQL¶

During Ubuntu updgrade to 22.04 you receive this message “Configuring postgresql-common”:

Obsolete major version 13

The PostgreSQL version 13 is obsolete, but the server or client packages are still installed.

Please install the latest packages (postgresql-14 and postgresql-client-14) and upgrade the existing clusters with pg_upgradecluster (see manpage).

Please be aware that the installation of postgresql-14 will automatically create a default cluster 14/main.

If you want to upgrade the 13/main cluster, you need to remove the already existing 14 cluster (pg_dropcluster --stop 14 main, see manpage for details).

The old server and client packages are no longer supported.

After the existing clusters are upgraded, the postgresql-13 and postgresql-client-13 packages should be removed.

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.

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

ii  postgresql                14+238                 all    object-relational SQL database (supported version)
ii  postgresql-13             13.6-0ubuntu0.21.10.1  amd64  The World's Most Advanced Open Source Relational Database
ii  postgresql-14             14.2-1ubuntu1          amd64  The World's Most Advanced Open Source Relational Database
ii  postgresql-client         14+238                 all    front-end programs for PostgreSQL (supported version)
ii  postgresql-client-13      13.6-0ubuntu0.21.10.1  amd64  front-end programs for PostgreSQL 13
ii  postgresql-client-14      14.2-1ubuntu1          amd64  front-end programs for PostgreSQL 14
ii  postgresql-client-common  238                    all    manager for multiple PostgreSQL client versions
ii  postgresql-common         238                    all    PostgreSQL database-cluster manager

Run pg_lsclusters, your 13 and 14 main clusters should be “online”.

Ver  Cluster  Port  Status  Owner     Data directory               Log file
13   main     5432  online  postgres  /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log
14   main     5433  online  postgres  /var/lib/postgresql/14/main  /var/log/postgresql/postgresql-14-main.log

There already is a cluster “main” for 14 (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 13/main when 14/main also exists. The recommended procedure is to remove the 14 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 14 cluster and drop it.

$ sudo pg_dropcluster 14 main --stop

Upgrade the 13 cluster to the latest version.

$ sudo pg_upgradecluster 13 main

Your 13 cluster should now be “down” and you can verifity running pg_lsclusters

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

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

$ sudo pg_dropcluster 13 main

After all your data check you can remove your old packages.

$ sudo apt-get purge postgresql-13 postgresql-client-13

Disclaimer¶

There is no warranty for the program, to the extent permitted by applicable law. Except when otherwise stated in writing the copyright holders and/or other parties provide the program “as is” without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the program is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair or correction.

Meta