How to upgrade a PostgreSQL 16 instance to PostgreSQL 17

When I started my instance, Mastodon’s prerequisites for the platform beneath the Fediverse-service was either Ubuntu or Debian in iteration 11 - even while Debian 12 „Bookworm“ was already released. Over the time, I checked those system requirements as well as I did update my Debian-system regularly. Yesterday, while doing standard maintenance-work on my Proxmox-cluster, I discovered that Mastodon finally supports Debian 12 so (after doing a backup and a parallel snapshot) I started my work.

Problem

The result was somewhat unexpected - while all packages were successfully upgraded and all three Mastodon-services started without problems, the PostgreSQL database was apparently down. Checking the status via the “pg_lsclusters” command, which lists my current clusters, the output showed that the latest system update had not only brought Debian iteration 12, but also a parallel installation of PostgreSQL 16 and 17, causing the apparent problems.

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
17  main    5433 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

While the cluster in version 16 contained all my current data, the fresh version 17 was empty, so for a working system the task was clear: Migrate the data from PostgreSQL 16 to 17, remove the previous version and run the new version on my updated Debian 12 under the default port 5432. After some reading, this was quite easy to accomplish so let me share this information with you in a short and quick walkthrough!

Solution

First of all, we need to drop the empty cluster by typing

pg_dropcluster 17 main --stop

Then we have to verify that that only the old version is left by using the „pg_lsclusters“-command again:

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

Now we can start the upgrade to the new version by migrating the existing data with the command “pg_upgradecluster 16 main” and watch the output which should look similar to:

Stopping old cluster...
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 17/main ...
[....]
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 16 main

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

Last but not least, the old cluster must be dropped with

pg_dropcluster 16 main

and the (now) unneeded packages must be removed from our system with the command

apt purge postgresql-16 postgresql-client-16

should finalize the magic!

Verdict

The problem came up unexpectedly and shouldn’t happen very often but if you run into this or a similar challenge, you’ll probably have two coexisting PostgreSQL-versions on your system causing the error shown here. Although the problem sounds quite complex, it took only a few commands on my system to migrate the data to PostgreSQL 17 and get the Mastodon instance up and running on my Debian 12 system in just a few minutes!