[Bug]: PostgreSQL upgrade fails when databases with different TimescaleDB versions are used

[jnidzwetzki]

What type of bug is this?

Configuration, Unexpected error

What subsystems and features are affected?

Packaging, Other

What happened?

Hello,

When using multiple databases with different TimescaleDB versions, upgrading Postgres to another major version (tested with 12.10 -> 13.6, and 13.6 -> 14.2) fails with "could not load library "$libdir/timescaledb-2.X.X": ERROR: attempt to redefine parameter "YYY"".

Workaround: When all TimescaleDB versions are upgraded to the same version, the PostgreSQL upgrade can be performed.

TimescaleDB version affected

At least 2.5.0 - 2.7.1

PostgreSQL version used

12, 13

What operating system did you use?

Debian 11

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

$ pg_upgrade --check --old-datadir "/home/jan/postgresql-sandbox/data/REL_12_10" --new-datadir "/home/jan/postgresql-sandbox/data/REL_13_6" --old-bindir "/home/jan/postgresql-sandbox/bin/REL_12_10/bin" --new-bindir "/home/jan/postgresql-sandbox/bin/REL_13_6/bin"
Welcome to the Postgres sandbox for /home/jan/postgresql-sandbox
Starting PostgreSQL version REL_12_10
waiting for server to shut down....
 done
server stopped
The files belonging to this database system will be owned by user "jan".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/jan/postgresql-sandbox/data/REL_13_6 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Berlin
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/jan/postgresql-sandbox/data/REL_13_6 -l logfile start

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting


jan@debian11-work:~/timescaledb$ cat loadable_libraries.txt 
could not load library "$libdir/timescaledb-2.7.0": ERROR:  attempt to redefine parameter "timescaledb.enable_optimizations"
In database: db2

How can we reproduce the bug?

# Init PostgreSQL 12 environment
mkdir /home/jan/postgresql-sandbox/data/REL_12_10
export PATH=~/postgresql-sandbox/bin/REL_12_10/bin:$PATH
initdb -D /home/jan/postgresql-sandbox/data/REL_12_10
echo "shared_preload_libraries = 'timescaledb'" >> /home/jan/postgresql-sandbox/data/REL_12_10/postgresql.conf 
~/postgresql-sandbox/postgres-sandbox.sh postgres_start REL_12_10

# Create two databases with different TimescaleDB versions
psql template1

create database db1;
create database db2;

\c db1
create extension timescaledb version '2.6.1';

\c db2
create extension timescaledb version '2.7.0';

# Stop the PostgreSQL 12 server and prepare the upgrade to PostgreSQL 13
pg_ctl -D /home/jan/postgresql-sandbox/data/REL_12_10 stop
export PATH=~/postgresql-sandbox/bin/REL_13_6/bin:$PATH
initdb -D /home/jan/postgresql-sandbox/data/REL_13_6
echo "shared_preload_libraries = 'timescaledb'" >> /home/jan/postgresql-sandbox/data/REL_13_6/postgresql.conf 

pg_upgrade --check --old-datadir "/home/jan/postgresql-sandbox/data/REL_12_10" --new-datadir "/home/jan/postgresql-sandbox/data/REL_13_6" --old-bindir "/home/jan/postgresql-sandbox/bin/REL_12_10/bin" --new-bindir "/home/jan/postgresql-sandbox/bin/REL_13_6/bin"


Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting


cat loadable_libraries.txt 
could not load library "$libdir/timescaledb-2.6.0": ERROR:  attempt to redefine parameter "timescaledb.enable_optimizations"
In database: db2

[daFlo]

+1

[Saphora]

+1

[naveenkumarsp]

i am also facing same issue. Is there any workaround to fix the issue?

[jflambert]

+1 with the same issue :(

[arunas-devoro]

+1 upgrade from v14 to v15 or v16 fails "... FATAL: could not access file "timescaledb": No such file or directory ..."

[jflambert]

I ended up using timescaledb-ha which has several of the older extensions bundled.

[arunas-devoro]

I ended up using timescaledb-ha which has several of the older extensions bundled.

will need to look into timescaledb-ha option. However i manage to workaround the problem. Simply remove timescaledb extension from postgresql.conf run the upgrade and add it back once upgrade is finished.