TimescaleDB issues with PostgreSQL 15

devops-matriz commented 7 months ago

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.

Gasoid commented 7 months ago

yes, we've got the same issue

hemraj43 commented 7 months ago

yes, we are also getting the same issue.

berlincount commented 2 months ago

Running into the same issue.

nikolaigut commented 1 month ago

we also have the same issue

zalando / postgres-operator