Upgrading PostgreSQL 14 to 16 on Ubuntu

When I started working on my pet project to scratch an itch I had (and still have), the newest PostgreSQL version was 14. I picked it with a full confidence that it will serve me for a long time - and it did. Recently, however, I started stumbling upon posts extolling upcoming PostgreSQL 17 more and more often, which made me feel like I’m lagging behind.

To be honest, there’s is no pressing need for me to upgrade - PostgreSQL 14 serves me just fine, and would most likely continue to do so indefinitely. It’s actually already a huge overkill, since I do not really have a lot of requirements that could only be fulfilled by a top-class relational database. However, I do like my tools fresh and sharp, even if they’re way too sophisticated for the job, so I decided to upgrade either way.

TL;DR

For those in the rush, here’s the outline of the whole upgrade procedure I did:

# install postgresql
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# drop the newest cluster
sudo pg_dropcluster 16 main --stop

# upgrade the old cluster to the new version
sudo pg_upgradecluster 14 main

# drop the old cluster
sudo pg_dropcluster 14 main

# remove old PostgreSQL version from the system
sudo apt purge postgresql-14 postgresql-client-14

Background

If you take a few moments to research how to upgrade a PostgreSQL version, you’ll find that there are at least a few ways to do it [1]. Some are safer than others, some are easier but riskier. To evaluate which fit me the best, I had a few simple requirements in my mind:

I want it to be uncomplicated (I can afford downtime if it makes things easier)
I don’t want to lose any data (though I can deal with it if it happens)

To be perfectly honest, if you’re fine with downtime and okay with risking your data (either because you don’t care or, in my case, you have a backup and don’t mind restoring data in the worst case scenario), you can basically wing the upgrade anyway way you like. With that in mind, I chose the simplest approach I could find: using pg_upgradecluster which comes from a postgresql-common package containing a bunch of tools to maintain and manage your PostgreSQL installation(s).

Note, though, that I have not tried this method on a cluster that’s under serious production load, with hundreds of gigabytes of data, leader-follower setup and all the other fancy bells and whistles. In my case, my database is just a few tables with a small amount of data and no special extensions and/or customizations. Thus, if you have something more serious, I would advise to triple check whether this procedure would fit your needs.

With that in mind, let’s see how the process of upgrade from PostgreSQL 14 to 16 looks like.

Specs

To start of, I began from the following state.

Ubuntu version:

> lsb_release -a

No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 22.04.4 LTS
Release:	22.04
Codename:	jammy

PostgreSQL version:

# PostgreSQL server version:
> pg_config --version
PostgreSQL 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1)

# PostgreSQL client version:
> psql --version
psql (PostgreSQL) 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1)

For reference, database size was a measly 11 MB - practically nothing compared to even moderately sized production databases.

dbuser=> \l+ <database_name>
List of databases
Name  |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules | Access privileges | Size  | Tablespace | Description
--------+----------+----------+-----------------+---------+---------+------------+-----------+-------------------+-------+------------+-------------
dbname | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |                   | 11 MB | pg_default |
(1 row)

Steps

Installing `postgresql`

First of all, I had to install postgresql-common tools, which automatically configures the necessary repository [2]:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

This allowed me to install postgresql:

apt install postgresql

Without the postgresql-common installation and setup, I was getting a bunch of errors that various PostgreSQL-related packages could not be found.

Upgrading PostgreSQL clusters

postgresql installation should create and start a new PostgreSQL cluster. You can do a sanity check to verify that you now have two clusters running [3]: one for the old PostgreSQL version (14), and one for the new one (16).

> pg_lsclusters

14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
16  main    5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Note that “cluster” in this case has nothing to do with the common definition of a group of servers working together to achieve higher performance on some task. In PostgreSQL world, it’s defined as:

A database cluster is a collection of databases that is managed by a single instance of a running database server. […] In file system terms, a database cluster is a single directory under which all data will be stored. [4]

Turning to the output above, notice that the new cluster (16) is running on port 5433. Thus, if you have some apps that are connected to the database on port 5432, they’ll still be using the old version. This is expected for now as we have not performed an upgrade yet.

Once PostgreSQL 16 is installed, we can begin moving our old server to the new PostgreSQL version. First of all, we need to drop the new cluster (16) [5], since we’ll be upgrading our old cluster (14) to a newer version:

sudo pg_dropcluster 16 main --stop

We include --stop flag to force a server shutdown before files are removed, as normally a cluster which has a running server will not be deleted.

Once we’ve dropped the cluster with the new PostgreSQL version (16), we can go for the meat of the upgrade: pg_upgradecluster [6]:

> sudo pg_upgradecluster 14 main

Stopping old cluster...
Restarting old cluster with restricted connections...

[ ... a lot of PostgreSQL config related logs follow ... ]

Upgrading database <dbname>...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5432...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database <dbname>: Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database <dbname>: Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database <dbname>: Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 14 main

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

With this, we can see that our database version has been upgraded to 16. Last few lines detail that now we have a PostgreSQL 16 cluster listening on port 5432 (default PostgreSQL port) which used to be occupied by PostgreSQL 14 cluster. The latter is now listening on 5433 - or would be, if it wasn’t down.

The next steps are sanity checks to see if all works well, if our apps can talk to PostgreSQL. If nothing seems amiss, we can drop the old (14) cluster:

sudo pg_dropcluster 14 main

And verify that we don’t have it anymore:

> pg_lsclusters

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

Clean Up

Now that we’ve upgraded the database, there’s no need to keep the old version around, so we might as well delete it:

sudo apt purge postgresql-14 postgresql-client-14

And with that, we’ve concluded the upgrade.

Summary

To be perfectly honest, I expected the upgrade to be much more involved, which is why I postponed it in multiple times. In the end, though, it turned out surprisingly easy - at least for my trivial, uncomplicated database setup.