TimescaleDB issues with PostgreSQL 15

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.10.1
• Where do you run it - cloud or metal? Kubernetes or OpenShift? OpenShift | k8s bare-metal | GKE
• Are you running Postgres Operator in production? Yes
• Type of issue? Bug report

Hi, i'm not sure if this issue is related to the operator itself so please tell me if I need to move to the Spilo or Patroni issues. Also maybe this is related to https://github.com/zalando/postgres-operator/issues/2307.

Using the latest spilo image (spilo-15:3.0-p1), which includes timescaledb 2.10.0 (which add support for PostgreSQL 15), the creation of the postgres cluster fails when trying to create the timescale extension in DB, because it's not included in the shared_preload_libraries configuration.

Operator log

{"cluster-name":"app-postgres/primary-postgres-cluster","level":"error","msg":"could not create cluster: could not sync prepared databases: error(s) while syncing prepared databases: error(s) while syncing database extensions: could not execute create extension: driver: bad connection","pkg":"controller","time":"2023-11-23T19:14:24Z","worker":0}

Postgres log

time=2023-11-23 19:14:24.724 UTC pid=229 error=00000 sess_id=655fa490.e5 tag=idle usr=postgres db=matrizoms app=[unknown] LOG:  statement: CREATE EXTENSION IF NOT EXISTS "timescaledb" SCHEMA "public"
time=2023-11-23 19:14:24.819 UTC pid=229 error=XX000 sess_id=655fa490.e5 tag=CREATE EXTENSION usr=postgres db=matrizoms app=[unknown] FATAL:  extension "timescaledb" must be preloaded
time=2023-11-23 19:14:24.819 UTC pid=229 error=XX000 sess_id=655fa490.e5 tag=CREATE EXTENSION usr=postgres db=matrizoms app=[unknown] HINT:  Please preload the timescaledb library via shared_preload_libraries.
	
	This can be done by editing the config file at: /home/postgres/pgdata/pgroot/data/postgresql.conf
	and adding 'timescaledb' to the list in the shared_preload_libraries config.
		# Modify postgresql.conf:
		shared_preload_libraries = 'timescaledb'
	
	Another way to do this, if not preloading other libraries, is with the command:
		echo "shared_preload_libraries = 'timescaledb'" >> /home/postgres/pgdata/pgroot/data/postgresql.conf 
	
	(Will require a database restart.)
	
	If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with: 
		SET timescaledb.allow_install_without_preload = 'on';

shared_preload_libraries config on postgres pod

root@primary-postgres-cluster-0:/home/postgres# cat pgdata/pgroot/data/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache'

With the same Spilo image but with PostgreSQL 14 everything works fine.

Start with the operator log and the PostgreSQL log showing the TimescaleDB preload failure, then compare the PostgreSQL 15 and 14 configurations in the reported Spilo image. Inspect how shared_preload_libraries is generated in postgresql.conf and how prepared database extensions are synchronized. Done means TimescaleDB clusters can be created successfully on PostgreSQL 15 without the preload error.