TimescaleDB issues during update from Postges v14--> v15

[Thorsten-Totemic]

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.10.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift? AWS EKS K8s
• Are you running Postgres Operator in production? YES
• Spilo Image: registry.opensource.zalan.do/acid/spilo-15:3.0-p1
• Replica nodes in environment: 0, only one master instance running (our staging environment)
• Type of issue? Bug report
• Operator manifest: https://gist.github.com/Thorsten-Totemic/f6a64f2dfc36ac73ec7f4e910b566da6
• Operator configuration: https://gist.github.com/Thorsten-Totemic/0e73ce38eb0aaa0c4ba8d62f9b569603
• cluster manifest: https://gist.github.com/Thorsten-Totemic/b405ba15f9943e97f16eddfc61fc87d4

I followed the update procedure from the section "In Place Major version upgrade" here: https://postgres-operator.readthedocs.io/en/latest/administrator/#minor-and-major-version-upgrade

I updated the operator configuration and the cluster manifest to the latest PG version and the latest spilo image for Postgres 15 and applied those changes. After that I updated the postgres operator to v1.10.0. Once the DB restarted it complained about TimescaleDB not found, so I logged into the pod and updated the extension which resolved the issue. Than I switched to the postgres user in the pod and executed python3 /scripts/inplace_upgrade.py 1 to trigger the update. However it always fails with this error messages:

postgres@apiservice-db2-staging-0:~$ python3 /scripts/inplace_upgrade.py 1
2023-04-26 18:15:05,647 inplace_upgrade INFO: No PostgreSQL configuration items changed, nothing to reload.
2023-04-26 18:15:05,746 inplace_upgrade INFO: establishing a new patroni connection to the postgres cluster
2023-04-26 18:15:05,856 inplace_upgrade INFO: Cluster apiservice-db2-staging is ready to be upgraded
2023-04-26 18:15:05,858 inplace_upgrade INFO: initdb config: [{'locale': 'en_US.UTF-8'}, {'encoding': 'UTF8'}, 'data-checksums']
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

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

Data page checksums are enabled.

creating directory /home/postgres/pgdata/pgroot/data_new ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
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
initdb: hint: 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:

    /usr/lib/postgresql/15/bin/pg_ctl -D /home/postgres/pgdata/pgroot/data_new -l logfile start

2023-04-26 18:15:09,124 inplace_upgrade INFO: Dropping extensions from the cluster which could be incompatible
2023-04-26 18:15:09,133 inplace_upgrade INFO: Executing "DROP EXTENSION IF EXISTS amcheck_next" in the database="template1"
2023-04-26 18:15:09,134 inplace_upgrade INFO: Executing "DROP EXTENSION IF EXISTS pg_repack" in the database="template1"
2023-04-26 18:15:09,142 inplace_upgrade INFO: Executing "DROP EXTENSION IF EXISTS amcheck_next" in the database="postgres"
2023-04-26 18:15:09,145 inplace_upgrade INFO: Executing "DROP EXTENSION IF EXISTS pg_repack" in the database="postgres"
2023-04-26 18:15:09,146 inplace_upgrade INFO: Executing pg_upgrade --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
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:
    /home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20230426T181509.154/loadable_libraries.txt

Failure, exiting
2023-04-26 18:15:10,539 inplace_upgrade ERROR: pg_upgrade --check failed, more details in the /home/postgres/pgdata/pgroot/data_upgrade

The folder /home/postgres/pgdata/pgroot/data_upgrade is empty, but I did follow the instructions in the file home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20230426T181509.154/loadable_libraries.txt (linked to a gist) and added timescaledb to the shared_preload_libraries definition in the File /home/postgres/pgdata/pgroot/data_new/postgresql.conf. I am setting shared_preload_libraries = 'timescaledb, pg_stat_statements,pg_cron,set_user,pgextwlist,pg_stat_kcache'

After that I restart postgres with patronictl -c postgres.yml restart apiservice-db2-staging apiservice-db2-staging-0 which runs fine. But when I then execute the update script again python3 /scripts/inplace_upgrade.py 1, the same error comes up and the file /home/postgres/pgdata/pgroot/data_new/postgresql.conf is missing again the timescaledb in the shared_preload_libraries string.

Some more logs: Kubernetes pod logs from postgres Postgres operator logs

DB outputs: Select version();: PostgreSQL 14.7 (Ubuntu 14.7-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit SELECT extversion FROM pg_extension where extname = 'timescaledb'; : 2.10.0

How can I get past this issue to have the update succeed?

[maboehm]

The current spilo image does not fully support Timescale DB for PG15 (it was not included in the shared_preload_libraries, because it did not support v15 yet), but since Timescale 2.10 supports it, the latest master branchis now updated: https://github.com/zalando/spilo/pull/873

[Thorsten-Totemic]

Thanks for the update, do you know when we can expect a release on the spilo side?

[maboehm]

I'm just a user, so I don't 🙈

[RobertoChiosa]

Any updates on this issue? I am facing the same problem when deploying Postgres v15 and timescaledb extension

[berlincount]

Running into the same issue.