Upfront bias: I prefer MariaDB.

Truly don’t mean to start a flame war here. If postgreSQL is your way, that’s OK! Seriously, much like your favorite flavor of Linux (mine’s Debian), ice cream, or TV… I just don’t care. You be you.

Normally, MariaDB handles my needs… which means I don’t have a lot of experience with postgreSQL. Friends swear by it. I’ve heard it’s the best thing since sliced bread.

Anyway…

Synapse requires postgreSQL so I run it on the Matrix homeserver nodes we maintain. And other than that, I rarely ever touch a postgreSQL instance.

With the upcoming version of Synapse, support for postgreSQL 13 will be dropped. So, to prepare for that update I needed to upgrade our instances from postgreSQL 13 to 17.

Here’s what I did… maybe, if you’re also rarely doing this type of update, these steps might save you some time and stress.

Step 1: Become Postgres

Kind of zen… to upgrade postgres you need to be postgres. ;)

su - postgres

Step 2: Do a backup (optional)

It’s optional. If you have a death wish or just want to be really dumb, skip this step.

pg_dump [DATABASE] | gzip > FILENAME.gz

In my case, since I was backing up the synapse databse, I ran:

pg_dump synapse | gzip > 20251119_synapse.gz

Step 3: Stop and Drop 17

If 17 is running, got to stop and also since we’re upgrading, we’re going to dropcluster.

pg_dropcluster 17 main --stop

You can check if 17 is running by using the pg_lsclusters command, such as:

pg_lsclusters

The output would be along the lines of:

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

Here, 17 is online and needs to be stopped. After running, the status changed from online to down.

Step 4: Upgrade the 13 cluster

pg_upgradecluster 13 main

Output should start off with:

Upgrading cluster 13/main to 17/main ...
Stopping old cluster...

When it’s done, the last few lines should be something like:

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

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
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

That’s It!

That’s all that happened. Luckily all went well… bigger databases will take longer; as expected. When you’re all done, you can run:

pg_dropcluster 13 main

Remember… always be kind. =)