Sometimes it’s time to upgrade your primary database (used for example by Zabbix) to a new major release. Especially when using PostgreSQL with TimescaleDB, it’s necessary to do some extra steps and check things extra.

PostgreSQL can run different versions at the same server (bare metal or VM), as long as you have them listening on different (and unique) ports.

Goal

My goal was to upgrade PostgreSQL 16 to 17 (although 18 was already available, one of my pieces of software aren’t certified for 18 yet) on the same VM.

Zabbix is one of the products which is using this PostgreSQL database and it also used the TimescaleDB extension for more performance with the large history and trends tables.

Another goal: almost no downtime, but… backing out of the upgrade should be possible!

PostgreSQL 16 (current) situation

• Operating System: Ubuntu 24.04.3 LTS (running on a VM within Proxmox)
• PostgreSQL 16 instance is listening on the default port (5432)
• PostgreSQL repository used as explained here: https://www.postgresql.org/download/linux/ubuntu/
• TimescaleDB version: 2.23.0
• TimescaleDB repository used as explained here: https://docs.tigerdata.com/self-hosted/latest/install/installation-linux/
• PostgreSQL 16 binaries are installed in: /usr/lib/postgresql/16/bin
• PostgreSQL 16 databases are installed in: /data/postgresql/16/main

PostgreSQL 17 (new and wanted) situation

• Same operating system 🙂
• PostgreSQL 17 instance listening on port 5432 (during upgrade this will be at port 5433)
• PostgreSQL 17 binaries will be installed in: /usr/lib/postgresql/17/bin
• PostgreSQL 17 databases will be copied and upgraded into: /data/postgresql/17/main
• TimescaleDB version: 2.23.0

TimescaleDB steps

At first it’s very important to have the exact same TimescaleDB version running in PostgreSQL 16 and 17. So first check what the current installed version is AND (not unimportant!) if this is also activated for your database. How to upgrade your TimescaleDB (and check which version your database is using) can be found in this article Minor version Upgrade TimescaleDB for Zabbix

I checked the current used TimescaleDB extension of my Zabbix database:

postgres@pgsql:~$ psql -X -d zabbix -c "\dx timescaledb;"

    Name     | Version | Schema |                                      Description                                      
-------------+---------+--------+---------------------------------------------------------------------------------------
 timescaledb | 2.23.0  | public | Enables scalable inserts and complex queries for time-series data (Community Edition)

As the extension matches the installed package, I could go on further with the next step of the migration

Install PostgreSQL 17

At first I had to install PostgreSQL 17 with TimescaleDB on the same server, these are the commands I used (I was running the most current TimescaleDB version, so didn’t have to specify the exact version number):

# apt install postgresql-17 postgresql-client-17
# apt install timescaledb-2-postgresql-17

After installing this and before doing some extra steps, make sure the default PostgreSQL 17 server is deleted and shutdown.

First check if PostgreSQL 17 is already running
# pg_lsclusters

If there is a PostgreSQL 17 server running, you can drop and delete it:
# pg_dropcluster 17 main

After this I copied the relevant configfiles of PostgreSQL 16 to the PostgreSQL 17 config-directory:

# cp -rp /etc/postgresql/16 /etc/postgresql/17

In the copied configfiles change everything what’s specific for PostgreSQL 16 to PostgreSQL 17. Especially the data-directory and the port! For this occasion these lines should read this:

data_directory = '/data/postgresql/17/main'
port = 5433

Upgrade 16 to 17!

Don’t forget to backup everything before starting the upgrade!

Shutdown the running PostgreSQL 16 server:

# systemctl stop postgresql@16-main.service

Before upgrading, it’s a good thing to check if everything is in good order for performing the upgrade. So the command that has to be run (as the postgres-user!), first has to be expanded with a check option:

postgres@pgsql:~$/usr/lib/postgresql/17/bin/pg_upgrade --old-datadir "/data/postgresql/16/main" --new-datadir "/data/postgresql/1
7/main" --old-bindir "/usr/lib/postgresql/16/bin" --new-bindir "/usr/lib/postgresql/17/bin" -d /etc/postgresql/16/main 
-D /etc/postgresql/17/main --check

This command uses the old and new data and binary-directories. So always have enough storage available to accommodate the copy of all your databases!

When everything is in order, the same command without –check can be run and from that moment, you’ll just have to have patience until this is finished!

postgres@pgsql:~$/usr/lib/postgresql/17/bin/pg_upgrade --old-datadir "/data/postgresql/16/main" --new-datadir "/data/postgresql/1
7/main" --old-bindir "/usr/lib/postgresql/16/bin" --new-bindir "/usr/lib/postgresql/17/bin" -d /etc/postgresql/16/main 
-D /etc/postgresql/17/main

Cleanup all databases after the upgrade is recommended:

postgres@pgsql:~$ /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages -p 5433

Upgrade is finished

When the upgrade is succesfully finished, you can start the PostgreSQL 17 server. Mind you that it will be running at port 5433.

When you’re ready to use this upgraded databaseserver and databases, change the port to 5432 and restart the PostgreSQL 17 server

# systemctl restart postgresql@17-main.service

Make sure that the PostgreSQL 16 server isn’t running anymore:

# systemctl disable --now postgresql@16-main.service

Clean up PostgreSQL 16

After this you can (at a moment which suits you) delete the PostgreSQL 16 instance:

# pg_dropcluster 16 main

This command will delete all the configurationfiles and database files from your server.