I have a TimescaleDB container running on my home server, and recently I wanted to upgrade it from PostgreSQL 13 to PostgreSQL 16. This post documents the process I settled on (and hints at the false starts along the way!)

I have a handful of sensors around my house, monitoring things like temperature, air quality, broadband speed and latency, etc. Those are all linked back to Home Assistant, but by default it only stores 24 hours of data. I use the excellent LTSS (Long Term State Storage) module to push that data into Timescale DB, and then I can display and query it much further back in Grafana.

I use Docker Compose to deploy the Home Assistant/Grafana/Timescale DB combo, and had it set to use the timescale/timescaledb-ha:pg13 docker image - naturally the first thing I tried was just to bump the version to pg16 (something the manual hints at doing).

This broke my database, in quite a big way! Unsurprisingly, you can't just go up 3 major versions of the database and expect it to work: it fails to start complaining the database is an unsupported version. Fortunately I did this on my "staging environment" (as set of VMs I use to test stuff like this), so I rolled back to a backup and read up on pg_upgrade which got me to this process. Since initially testing this it's been better documented in the TimescaleDB docs, but I thought the step by step process was still useful.

The Process

Update current. Check how far it went (2.5 to 2.10 for me), find newest version. Upgrade to there, and repeat

The basic process is to upgrade your existing TimescaleDB extension to the latest version supported by your version of PostgreSQL, then upgrade your database to the latest version that supports that extension version, and repeat the process until you reach the version you want!

To actually upgrade PostgreSQL you start a new, empty database running the new version. Then you install the old version's binaries into it, and use pg_upgrade to move the old DB content into the new DB.

In theory this table in the docs lets you figure out how many steps you'll need to take: upgrade TimescaleDB to the newest version with a green tick for your PostgreSQL version, then upgrade PostgreSQL to a version that still has a green tick, then upgrade TimescaleDB, and so on. However I had some trouble in that my PostgreSQL 13/TimescaleDB 2.5 install wouldn't go higher than 2.10, so I had to take two steps: to PG15/Timescale2.10 and then to PG16/Timescale2.16.

For the step-by-step instructions, I'm working based on my Docker Compose environment, using a Compose file like the one below.

timescaledb:
  container_name: timescaledb
  image: timescale/timescaledb-ha:pg13-all
  environment:
    - POSTGRES_USER=usr
    - POSTGRES_PASSWORD=NOTMYPASSWORD
    - POSTGRES_DB=ha
  volumes:
    - '/opt/homeassistant/timescaledb/pgdata:/home/postgres/pgdata'
  restart: unless-stopped
  networks:
    - default

Here it is step by step.

Step 0: Make a backup!

Before you start, take a backup of your database unless you really, really don't care about the data. But if that's the case, why bother upgrading?

Step 1: Upgrade TimescaleDB

First upgrade TimescaleDB. With the server running, run the statements below on all your databases:

ALTER EXTENSION timescaledb UPDATE;
CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit;
ALTER EXTENSION timescaledb_toolkit UPDATE;

In the Docker Compose environment shown above, you could run something like this to do them all in one go:

export DBNAMES=$(docker compose exec timescaledb psql \
    -U usr -d postgres --no-align --tuples-only \
    -qc 'SELECT datname from pg_database;')
for DBNAME in $DBNAMES; do
    docker compose exec timescaledb psql -U usr -d ${DBNAME} \
        -qc 'ALTER EXTENSION timescaledb UPDATE; CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit; ALTER EXTENSION timescaledb_toolkit UPDATE;';
done

Step 2: Move your existing data elsewhere, start an empty database

Next, update your docker-compose.yml to move the database mount somewhere else inside the container, then bump the version. Stop the database for now and don't start it yet.

From the snippet of Compose file above the important modification is to change the volume for the data to be somewhere else inside the container, while also mounting the original location. That way, you can still access the old DB, but the container can start from clean. In the example below, I've moved it to /mnt/olddata

I use mounts on the host filesystem for my persistent data, so notice I've changed the host mount path too - I've also run mv /opt/homeassistant/timescaledb/pgdata /opt/homeassistant/oldtimescale in preparation.

timescaledb:
  container_name: timescaledb
  image: timescale/timescaledb-ha:pg15-all
  environment:
    - POSTGRES_USER=usr
    - POSTGRES_PASSWORD=NOTMYPASSWORD
    - POSTGRES_DB=ha
  volumes:
    - '/opt/homeassistant/timescaledb/pgdata:/home/postgres/pgdata'
    - '/opt/homeassistant/oldtimescale:/mnt/olddata'
  restart: unless-stopped
  networks:
    - default

Notice I've also bumped the version of the database, ready to start a new, empty one.

Note: You don't want anything to be writing to the database while you're doing this, so if you have other services that access the database, make sure they aren't running.

Next, create the directory structure for the new database and fix the permissions. Matching the example above:

sudo mkdir -p /opt/homeassistant/timescaledb/pgdata/data
sudo chown -R 1000 /opt/homeassistant/timescaledb/pgdata

Step 3: Install the old version into the container, use it to run the upgrade

Next, get a shell inside the container to install the old version of PostgreSQL. You'll want the new database to be empty for this, so make sure the container hasn't been started since the last step.

Run something like docker compose run --user root --entrypoint /bin/bash timescaledb to start a shell inside the container.

Then run the following commands to install the old PostgreSQL binaries and then run the upgrade command.

apt update

# Update the version as needed (your Docker image may include it already)
apt install postgresql-13

# Become the postgres user
su postgres

# Initialise a new empty database
/usr/lib/postgresql/15/bin/initdb --username usr

# Run the actual upgrade - you may need to tweak the user
/usr/lib/postgresql/15/bin/pg_upgrade \
    -b /usr/lib/postgresql/13/bin/ -d /mnt/olddata/data \
    -D ${PGDATA} -U usr

If that worked, you should see something like:

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/16/bin/vacuumdb -U usr --all --analyze-in-stages

If it told you to run an extension upgrade, run it as suggested (to do that you'll need to start the database as well). If it doesn't tell you, you can just exit the container.

Inside the container, run something like this (note the trailing & on the first command to make it run in the background):

/usr/lib/postgresql/15/bin/pg_ctl -D /home/postgres/pgdata/data -l logfile start &
psql -U usr -d postgres -f update_extensions.sql
psql -U usr -d postgres -c '\dx'

That last command will list the version of the extensions installed: make a note of what it is.

Finally you can type exit twice to get out of the container

Step 4: Repeat as needed

If you couldn't upgrade to your desired version in a single step, you'll now need to repeat steps 2 and 3 until you do.

Step 5: Tidy up

Finally, undo the changes you made to the Compose file, and delete the old database. You can bring the rest of your services up too, but add the --remove-orphans flag the first time to tidy away the containers used for the upgrade.

docker compose up --remove-orphans