TimescaleDB Extension Upgrade Quirks and Preload Ordering

A field reference for upgrading TimescaleDB safely: why the update must be the first statement in a fresh session, how the versioned loader and shared_preload_libraries interact, and which catalog objects behind hypertables break a naive ALTER EXTENSION UPDATE.

Up: Compatibility Matrix Synchronization — this page is the TimescaleDB-specific cell of that matrix, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies

TimescaleDB is unusual among extensions because it is mandatory-preload: it will not install or load unless timescaledb appears in shared_preload_libraries, which is set in postgresql.conf and therefore requires a server restart to change. It also ships a two-part library — a small version-independent loader (timescaledb.so) plus a version-pinned implementation (timescaledb-2.14.2.so) — so a package upgrade drops a new implementation .so alongside the old one, and the loader chooses which to load based on the catalog version. That design makes the upgrade sequence strict: this reference applies whenever you move TimescaleDB between 2.x releases on PostgreSQL 13–17, and it is the prerequisite reading before you schedule the maintenance window with threshold tuning for downtime windows.

The consequences of getting preload ordering wrong are cluster-level, not database-level: a missing or misordered shared_preload_libraries entry aborts startup for every database in the whole cluster, not just the one running TimescaleDB. Rehearse the exact restart-and-update sequence on a topology-matched clone through test environment routing before touching production.

TimescaleDB upgrade ordering with the versioned loader The TimescaleDB upgrade runs in four ordered steps. Step one installs the new package, which drops a new version-pinned implementation library alongside the old one. Step two restarts PostgreSQL so the version-independent loader is reloaded from shared_preload_libraries. Step three opens a brand-new connection and runs ALTER EXTENSION timescaledb UPDATE as the very first statement, before any hypertable is touched, because the loader binds a single implementation version per session. Step four verifies the installed version and that background workers restarted. A wrong order aborts cluster startup or raises a loader-version mismatch. 1 · Install package new timescaledb-2.x.so dropped beside the old 2 · Restart server reloads the loader from shared_preload_libraries 3 · UPDATE first fresh connection, before any hypertable query 4 · Verify version + background workers restarted a wrong order aborts cluster startup or raises a loader-version mismatch

Concept: The Loader Binds One Version Per Session

TimescaleDB’s loader reads the catalog to decide which implementation .so to dlopen when a backend first touches the extension. Once bound, that backend uses that version for its whole lifetime. This is why the upgrade must run as the first statement in a new connection: if the session has already loaded the old implementation (by querying a hypertable, for instance), ALTER EXTENSION timescaledb UPDATE raises the loader-mismatch error and refuses to proceed. It is also why the update cannot run inside an explicit transaction block — TimescaleDB’s update path performs actions that must commit immediately, exactly the non-transactional hazard catalogued in ALTER EXTENSION automation.

The second concept is preload immutability during runtime. shared_preload_libraries is a postmaster-context GUC: it is read once at startup and cannot be changed by SET or ALTER SYSTEM ... reload. Any change to the preload list — including reordering entries so timescaledb sits alongside other C extensions — takes effect only on the next restart, which is why preload ordering belongs in your maintenance-window plan and must be validated against the four compatibility surfaces the compatibility validation pipeline defines.

Runnable Implementation

The upgrade is a shell-plus-psql sequence, not pure SQL, because a restart sits in the middle. The psql invocation uses -X (skip startup files) and -c so the ALTER EXTENSION is unambiguously the first statement on a clean connection.

#!/usr/bin/env bash
# TimescaleDB minor-version upgrade. Fails closed at every step.
set -euo pipefail

DB="${1:?usage: upgrade-timescaledb.sh <database>}"

# 1. Install the new package (distro-specific); this drops the new .so alongside
#    the old implementation but does NOT change the running server.
#    e.g. apt-get install --only-upgrade timescaledb-2-postgresql-16

# 2. Restart so the loader is re-read from shared_preload_libraries.
sudo systemctl restart postgresql

# 3. Run the UPDATE as the FIRST statement on a brand-new connection.
#    -X avoids ~/.psqlrc running a hypertable query first and binding the
#    old version into this session.
psql -X -d "$DB" -c 'ALTER EXTENSION timescaledb UPDATE;'

# 4. Verify the installed version advanced and workers came back.
psql -X -d "$DB" -Atc \
  "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

Because step 2 is a full restart, its downtime dominates the window; size it with threshold tuning for downtime windows and keep a restore point through snapshot and point-in-time recovery in case the new implementation fails to load.

Expected Output & Verification

Step 4 prints only the new version string on success:

2.14.2

Confirm the loader actually bound the new implementation and that background workers (the jobs scheduler, continuous-aggregate refreshers) restarted cleanly:

-- Loaded library version must equal the catalog version.
SELECT extversion AS catalog_version,
       (SELECT setting FROM pg_settings
        WHERE name = 'timescaledb.last_tuned') IS NOT NULL AS tuned;

-- Background jobs should be scheduled again after the restart.
SELECT job_id, application_name, scheduled, next_start
FROM   timescaledb_information.jobs
ORDER  BY job_id;

Record the shipped version in compatibility matrix synchronization so downstream nodes gate on the exact loader/implementation pair.

Edge Cases & Gotchas

Running the UPDATE second in a session raises a loader mismatch. If any statement touched a hypertable first, you get:

ERROR:  the loaded TimescaleDB library (2.13.1) does not match the installed
        extension version (2.14.2); this can cause errors
HINT:  Restart the database and connect with a fresh session.

Reconnect and issue the ALTER EXTENSION as the first statement. This is a SCHEMA_DRIFT-adjacent state that the error categorization framework should route to a reconnect-and-retry rather than an abort.

Wrapping the update in a transaction fails. BEGIN; ALTER EXTENSION timescaledb UPDATE; COMMIT; raises ALTER EXTENSION ... UPDATE cannot run inside a transaction block because the update spawns and configures background workers that must commit immediately. Run it in autocommit mode.

A misordered preload list aborts the whole cluster at startup. If timescaledb is removed from, or shadowed in, shared_preload_libraries, PostgreSQL refuses to start every database:

FATAL:  extension "timescaledb" must be preloaded via shared_preload_libraries

Keep the preload directive in a version-controlled postgresql.conf fragment tracked under version control and branching, and never let an automation step rewrite the list without a validated restart.

Crossing a PostgreSQL major requires dump/reload, not pg_upgrade in place for some versions. TimescaleDB historically restricted pg_upgrade across certain server majors; consult the version matrix and prefer the logical path described in pg_upgrade vs a logical replication upgrade path when the in-place route is unsupported for your pair.