Upgrading PostgreSQL to 18 with TimescaleDB installed

A major PostgreSQL upgrade is not an upgrade in the usual sense. You install the new version alongside the old one and move the data across with pg_upgrade. Nothing is upgraded in place, which is why the new cluster starts with default configuration and none of your extensions.

With TimescaleDB in the picture there are two additional things that must be true before pg_upgrade will run, and one of them is new in PostgreSQL 18 and will catch anyone who has done this before. The good news is that the pre-flight check finds both, if you run it.

Before anything: the backup, and the disk

A VM snapshot is the best backup here, because the failure you are protecting against is not corruption of one table - it is the whole procedure going sideways, and a snapshot restores the entire machine state including both installations.

On bare metal, pg_dumpall is the classic route, and it needs roughly as much free space again as your data occupies.

Step 1: install the new version alongside

Add the PGDG repository, disable the distribution's own PostgreSQL module so it does not fight you, and install the new server and contrib packages:

# PGDG repo, then keep the distro module out of the way
sudo dnf -qy module disable postgresql
sudo dnf update
sudo dnf install -y postgresql18-server postgresql18-contrib

Initialise the new cluster:

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

Your old cluster is still running and still serving traffic at this point. Nothing has been touched.

Step 2: match the TimescaleDB version exactly

Find what you are running:

sudo -u postgres psql -c "SELECT name, default_version, installed_version
                          FROM pg_available_extensions WHERE name = 'timescaledb';"

Then install that same version built for the new PostgreSQL. Not the latest - the same one. The extension's binary must match what the data expects, and upgrading PostgreSQL and TimescaleDB in one move gives you two variables and no way to tell which one broke.

sudo dnf install timescaledb-2-postgresql-18-2.26.2 \
                 timescaledb-2-loader-postgresql-18-2.26.2

Upgrade TimescaleDB itself afterwards, as a separate exercise, once the PostgreSQL upgrade is confirmed good.

Step 3: carry your configuration over

This is the step that follows from "installing, not upgrading". The new cluster has stock postgresql.conf and pg_hba.conf. Every tuning decision - shared buffers, work memory, WAL settings, whatever anyone spent an afternoon on - is in the old files and nowhere else.

sudo cp /var/lib/pgsql/OLD/data/postgresql.conf /var/lib/pgsql/NEW/data/
sudo cp /var/lib/pgsql/OLD/data/pg_hba.conf     /var/lib/pgsql/NEW/data/

Copying wholesale is the pragmatic move, but read the release notes for the versions you are crossing: parameters are occasionally removed or renamed, and a setting the new version does not recognise will stop it starting.

Step 4: stop the old cluster and run the check

sudo systemctl stop postgresql-OLD

sudo -u postgres /usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-OLD/bin \
  --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/OLD/data \
  --new-datadir=/var/lib/pgsql/18/data \
  --check

Failure one: data checksums, and this one is new

old cluster does not use data checksums but the new one does
Failure, exiting

PostgreSQL 18 changed the initdb default: data checksums are now enabled. Every cluster created on 17 or earlier almost certainly has them off, because the old default was off. pg_upgrade will not bridge that mismatch, so a perfectly ordinary upgrade path now fails on a fresh default install.

Confirmed against the PostgreSQL 18 initdb documentation: "This is enabled by default; use --no-data-checksums to disable checksums."

You have two ways out. The quick one is to re-initialise the new cluster to match the old:

sudo rm -rf /var/lib/pgsql/18/data
sudo -u postgres /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/data --no-data-checksums
# then copy postgresql.conf and pg_hba.conf across again

Note that this discards the config you copied in step 3 - do it again afterwards.

The better one, if you can afford the time, is to enable checksums on the old cluster instead with pg_checksums --enable while it is stopped, and keep the new default. Checksums catch silent I/O corruption, which is exactly the class of fault you want caught in a metrics database that nobody reads row by row. Enabling them rewrites every page, so on a large database this is a maintenance window of its own rather than a quick fix.

Failure two: the preload library

Checking for presence of required libraries    fatal

TimescaleDB is installed but not loaded. The extension has to be in shared_preload_libraries in the new cluster's config:

echo "shared_preload_libraries = 'timescaledb'" \
  | sudo tee -a /var/lib/pgsql/18/data/postgresql.conf

If you copied your old postgresql.conf across in step 3 this may already be set - check before appending a duplicate. Re-run the check and both clusters should report compatible.

Step 5: the actual upgrade

Same command, without --check. Adding --link uses hard links instead of copying, which is dramatically faster on a large database and needs far less disk:

sudo -u postgres /usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-OLD/bin \
  --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/OLD/data \
  --new-datadir=/var/lib/pgsql/18/data \
  --link

Start and enable the new service:

sudo systemctl start postgresql-18
sudo systemctl enable postgresql-18

Step 6: the two steps everyone skips

pg_upgrade finishes by telling you to do these, and it is easy to declare victory when the database accepts connections. Both matter.

Statistics do not survive the upgrade. The planner starts blind, which means bad plans and a database that feels far slower than the one you replaced. This is the single most common reason people conclude an upgrade "made things worse":

sudo -u postgres /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages

The staged form produces rough statistics quickly and refines them, so the system becomes usable early rather than after a full pass.

Reindex. PostgreSQL 18 explicitly recommends rebuilding indexes used for full-text search and by pg_trgm after a major upgrade, because of collation and internal format changes:

sudo -u postgres /usr/pgsql-18/bin/reindexdb --all

On a large production database this takes real time and holds locks. Plan it, and consider --concurrently if you cannot take the outage.

Step 7: verify, tune, clean up

sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "\dx timescaledb"

Confirm both the PostgreSQL version and that the extension is present and at the version you expect.

On tuning: your copied configuration is a reasonable starting point, but a config tuned for a much older version is not automatically right for the new one. If you use TimescaleDB, timescaledb-tune will generate settings appropriate to the machine and the new version - compare its output against what you carried over rather than applying either blindly.

Only once you are confident should you remove the old packages, binaries and data directory. There is no rush, and disk is cheaper than regret. If you used --link, be careful what you delete - the data files are shared.

If this is a Zabbix database

Nothing above changes, but two things are worth adding. Zabbix keeps writing during the outage window in the sense that its own queues fill up, so a long upgrade means a gap in history and a burst of catch-up load when the database returns. And a Zabbix database is unusually write-heavy and unusually index-heavy, which makes step 6 more important here than on a typical OLTP system, not less. Do not skip the reindex because the database "seems fine" - it will seem fine right up until a housekeeping run.

Watch the walkthrough

Sixteen minutes, including both pre-flight failures happening live - which is more useful than a run where everything works first time.

Related