Upgrading Mastodon from PostgreSQL 15 to PostgreSQL 17

I recently upgraded my Mastodon Ubuntu host from Ubuntu 22.04 to 24.04 and noticed that this comes with PostgreSQL 17 installed side-by-side with version 15.

So I thought I’d actually make use of PostgreSQL 17, since I already got it installed.

Backup!

Firstly, take a backup. Always a good idea. In my case I took a snapshot through the Hetzner console, because I host my instance on a Hetzner VPS.

Upgrade PostgreSQL 15 to 17

We start the update by stopping all mastodon services, to ensure that there are no writes to the DB during the process, and thus reduce the risk of errors:

systemctl stop mastodon-sidekiq
systemctl stop mastodon-streaming
systemctl stop mastodon-web

In my case the PostgreSQL 17 cluster was already running, so I first had to stop it. You can confirm this by looking at the output of pg_lsclusters:

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

As you can see both versions 15 and 17 are running, so lets stop version 17:

pg_dropcluster 17 main --stop

Confirm that only the PostgreSQL 15 cluster is now running:

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

Finally, it’s time for the actual upgrade. PostgreSQL offers the pg_upgrade tool, which on Ubuntu is wrapped in pg_upgradecluster.

By default this will pg_dump your database to disk from version 15, then pg_restore it into version 17. That means you need a lot fo free disk space, which I don’t have on my system. (It’s also pretty slow,but that was less of a concern for me.) Thankfully pg_upgradecluster gives us the option to upgrade the existing files and simply place a hard link. To quote from the docs:

If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.)

I’m fine with these drawback, so I’m using link mode. The command to run in order to upgrade using link mode is:

pg_upgradecluster -m link 15 main

This took about a minute in my case, so it was very quick. This will crucially also copy your database config files, so it will make sure that the PostgreSQL 17 cluster is now running on the same port as the version 15 cluster was running previously. This can again by confirmed by running pg_lsclusters:

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

Note the swapped ports.

At this stage we can bring our mastodon services back online:

systemctl start mastodon-sidekiq
systemctl start mastodon-streaming
systemctl start mastodon-web

You can now navigate to the Mastodon admin dashboard on your instance at /admin/dashboard and at the bottom it should show you are using PostgreSQL 17 now.

Tidying up

Once you have verified that this is all working, you can do some tidy up. First drop the PostgreSQL 15 cluster:

pg_dropcluster 15 main

Then remove the PostgreSQL 15 packages:

apt remove postgresql-15 postgresql-client-15

Summary

Really, these are a lot of words to say: Upgrading your PostgreSQL 15 cluster to PostgreSQL is very simple, if you are hosting your Mastodon instance on Ubuntu. The actual upgrade boils down to just two commands:

pg_dropcluster 17 main --stop
pg_upgradecluster -m link 15 main

It’s really quick too, taking just about a minute on my database.