How to enable TimescaleDB for a cluster?

[bergkvist]

Logging into the container

$ kubectl exec -it acid-test-0 sh
# psql -U postgres

postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION

postgres=# ALTER EXTENSION timescaledb UPDATE;
ERROR:  extension "timescaledb" does not exist

postgres=# CREATE EXTENSION timescaledb;
ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/timescaledb.control": No such file or directory

From the README:

I've followed the quickstart guide, and added the following:

postgres-operator

git clone https://github.com/zalando/postgres-operator.git
cd postgres-operator

kubectl create -f manifests/configmap.yaml  # configuration
kubectl create -f manifests/operator-service-account-rbac.yaml  # identity and permissions
kubectl create -f manifests/postgres-operator.yaml  # deployment
kubectl create -f manifests/api-service.yaml  # operator API to be used by UI

postgres-operator-ui

kubectl apply -f ui/manifests/
kubectl port-forward "$(kubectl get pod -l name=postgres-operator-ui --output='name')" 8081

A cluster:

[FxKu]

Afaik, in the used Spilo image timescale extension is not available for Postgres 12. Use 11 and you should be able to add it. However, the next Spilo release will have it.

[bergkvist]

Yeah, you were right about the postgres version being the reason.

It seems to work with Postgres 11

When will the new Spilo release be available?

[FxKu]

Use registry.opensource.zalan.do/acid/spilo-cdp-12:1.6-p115. This is the image we will also ship with the new operator release.

[andy-v-h]

@bergkvist I'm struggling to get timescaledb too, what was the prepared db yaml you added?

[manuel-koch]

I created a new cluster using latest helm chart (v1.7.1) and don's see a timescaledb extension in the running postgres instances.

The used image is registry.opensource.zalan.do/acid/spilo-14:2.1-p3

testdb=# SHOW extwlist.extensions;
                                                    extwlist.extensions
---------------------------------------------------------------------------------------------------------------------------
 btree_gin,btree_gist,citext,hstore,intarray,ltree,pgcrypto,pgq,pg_trgm,postgres_fdw,tablefunc,uuid-ossp,hypopg,pg_partman

 testdb=# SHOW shared_preload_libraries ;
                             shared_preload_libraries
----------------------------------------------------------------------------------
 bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache

Update Using a different image ( see issue ) resulted in being able to manually add extension in database.

Thus connecting to my database testdb I can now manually run CREATE EXTENSION timescaledb;

But requested extension is not applied automatically to my prepared db when postgres-operator spins up my cluster:

apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
  namespace: default
  name: acid-timescaledb
  labels:
    team: acid
spec:
  dockerImage: registry.opensource.zalan.do/acid/spilo-cdp-14:2.1-p210
  databases:
    testdb: testuser
  enableMasterLoadBalancer: true
  numberOfInstances: 3
  postgresql:
    version: "14"
  preparedDatabases:
    testdb:
      extensions:
        timescaledb: ""

Update

Tried to alter the last line of the cluster manifest - still the timescaledb extension is not automatically added to my database automatically.

....
  preparedDatabases:
    testdb:
      extensions:
        timescaledb: "public"

[ffais]

I'm using the latest version of the operator (1.8.2) installed with helm. I have tried both solutions proposed by @manuel-koch , but the automatic loading of timescaledb doesn't work. Adding the extension with psql works!

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: postgresql-test-cluster
  namespace: playngo-dev
spec:
  teamId: "postgresql"
  volume:
    size: 128Gi
  numberOfInstances: 1
  users:
    gamificationstats:  # database owner
    - superuser
    - createdb
  databases:
    gamificationstats: gamificationstats
  preparedDatabases:
    gamificationstats:
      extensions:
        timescaledb: "public"
  postgresql:
    version: "14"

[jflambert]

I have the same problem as @ffais

I both expect the extension to be created and also upgraded if applicable.

[JulesLalu]

Hi,

I don't know if anyone is still watching this issue, but I was able to automatically add timescaledb extension at db startup by adding the fields schemas: public: defaultRoles: false In the preparedDatabases yaml field. That way, the owner of the db (here, zalando) is also the owner of the schema 'public' and can add the extension timescaledb to the schema

Full yaml for postgresql manifest :

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
spec:
  teamId: "acid"
  volume:
    size: 1Gi
  numberOfInstances: 2
  users:
    zalando:  # db owner
    - superuser
    - createdb
  databases:
    foo: zalando
  preparedDatabases:
    zalando:
      extensions:
        timescaledb: public
      schemas:
        public:
          defaultRoles: false
  postgresql:
    version: "14"

[jflambert]

Hi Jules! that's a great workaround and I'm glad you took the time to leave your manifest here for future readers.

In my case we went with cloudnative-pg instead of postgres-operator after all.