Now that we know how we can configure the PostgreSQL instance and access control, we’re going to look at what you can do when it comes to PostgreSQL extensions with CloudNativePG. As we have a running cluster, let’s see what we have available:

minicube@micro-minicube:~> kubectl exec my-pg-cluster-1 -i -t -- /bin/bash
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
postgres@my-pg-cluster-1:/$ psql
psql (16.2 (Debian 16.2-1.pgdg110+2))
Type "help" for help.

postgres=$ select name, installed_version from pg_available_extensions order by 1;
        name        | installed_version 
--------------------+-------------------
 adminpack          | 
 amcheck            | 
 autoinc            | 
 bloom              | 
 btree_gin          | 
 btree_gist         | 
 citext             | 
 cube               | 
 dblink             | 
 dict_int           | 
 dict_xsyn          | 
 earthdistance      | 
 file_fdw           | 
 fuzzystrmatch      | 
 hstore             | 
 insert_username    | 
 intagg             | 
 intarray           | 
 isn                | 
 lo                 | 
 ltree              | 
 moddatetime        | 
 old_snapshot       | 
 pageinspect        | 
 pg_buffercache     | 
 pg_freespacemap    | 
 pg_prewarm         | 
 pg_stat_statements | 
 pg_surgery         | 
 pg_trgm            | 
 pg_visibility      | 
 pg_walinspect      | 
 pgaudit            | 
 pgcrypto           | 
 pgrowlocks         | 
 pgstattuple        | 
 plpgsql            | 1.0
 postgres_fdw       | 
 refint             | 
 seg                | 
 sslinfo            | 
 tablefunc          | 
 tcn                | 
 tsm_system_rows    | 
 tsm_system_time    | 
 unaccent           | 
 uuid-ossp          | 
 vector             | 
 xml2               | 
(49 rows)

This are the PostgreSQL standard extensions plus pg_audit and pg_vector but nothing more by default. To use them, you can just add them to the desired database as usual:

postgres=# create extension bloom;
CREATE EXTENSION
postgres=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 bloom   | 1.0     | public     | bloom access method - signature file based index
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

In the previous post we’ve seen that you must not use the “alter system” command in such a setup. How can this work then for extensions which require to load an additional library like e.g. pg_stat_statements or pg_prewarm? Those libraries need to be added to shared_preload_libraries and you can do this only by editing the configuration files or by using “alter system”. Both are not supported by CloudNativePG.

Having a look at shared_preload_libraries right now, we see that it’s empty:

postgres=# show shared_preload_libraries ;
 shared_preload_libraries 
--------------------------
 
(1 row)

postgres=#

Same story here: shared_preload_libraries is managed in the cluster configuration file, but you need to be careful: If you mistype a library or do anything which PostgreSQL cannot understand, you effectively bring down the cluster as PostgreSQL will not be able to start. Any self-healing capabilities of CloudNativePG will not help in this case.

Some extensions, currently four of them, are “managed” by CloudNativePG. What that means is, that the operator will manage the extension as soon as there is a parameter which requires it. You can easily test this by e.g. adding one of the pg_stat_statements parameter to the cluster configuration file:

minicube@micro-minicube:~> grep statement pg.yaml 
      pg_stat_statements.max: "2500"
minicube@micro-minicube:~> kubectl apply -f pg.yaml
cluster.postgresql.cnpg.io/my-pg-cluster configured

You need to be aware that this will trigger a restart of the pods:

minicube@micro-minicube:~> kubectl get pods
NAME              READY   STATUS    RESTARTS   AGE
my-pg-cluster-1   1/1     Running   0          4m35s
my-pg-cluster-2   0/1     Running   0          4s
my-pg-cluster-3   1/1     Running   0          4m22s
minicube@micro-minicube:~> kubectl get pods
NAME              READY   STATUS    RESTARTS   AGE
my-pg-cluster-1   1/1     Running   0          4m41s
my-pg-cluster-2   0/1     Running   0          10s
my-pg-cluster-3   1/1     Running   0          4m28s
minicube@micro-minicube:~> kubectl get pods
NAME              READY   STATUS        RESTARTS   AGE
my-pg-cluster-1   1/1     Running       0          4m43s
my-pg-cluster-2   1/1     Running       0          12s
my-pg-cluster-3   0/1     Terminating   0          4m30s

Once this completed, the extension is there:

minicube@micro-minicube:~> kubectl exec my-pg-cluster-1 -i -t -- /bin/bash
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
postgres@my-pg-cluster-1:/$ psql
psql (16.2 (Debian 16.2-1.pgdg110+2))
Type "help" for help.

postgres=$ \d pg_stat_statements
                      View "public.pg_stat_statements"
         Column         |       Type       | Collation | Nullable | Default 
------------------------+------------------+-----------+----------+---------
 userid                 | oid              |           |          | 
 dbid                   | oid              |           |          | 
 toplevel               | boolean          |           |          | 
 queryid                | bigint           |           |          | 
 query                  | text             |           |          | 
 plans                  | bigint           |           |          | 
 total_plan_time        | double precision |           |          | 
 min_plan_time          | double precision |           |          | 
 max_plan_time          | double precision |           |          | 
 mean_plan_time         | double precision |           |          | 
 stddev_plan_time       | double precision |           |          | 
 calls                  | bigint           |           |          | 
 total_exec_time        | double precision |           |          | 
 min_exec_time          | double precision |           |          | 
 max_exec_time          | double precision |           |          | 
 mean_exec_time         | double precision |           |          | 
 stddev_exec_time       | double precision |           |          | 
 rows                   | bigint           |           |          | 
 shared_blks_hit        | bigint           |           |          | 
 shared_blks_read       | bigint           |           |          | 
 shared_blks_dirtied    | bigint           |           |          | 
 shared_blks_written    | bigint           |           |          | 
 local_blks_hit         | bigint           |           |          | 
 local_blks_read        | bigint           |           |          | 
...

postgres=$ show shared_preload_libraries ;
 shared_preload_libraries 
--------------------------
 pg_stat_statements
(1 row)

Another popular extension for PostgreSQL is PostGIS. If you want to have this, you need to change the container image which is used by the operator to build your PostgreSQL cluster. As this is well described in the documentation we’ll not look into this into more details.

For any other extension, you would need to create your own image which comes with it, the requirements for building such a container are documented here.

In the next post we’ll look at the CloudNativePG kubectl plugin.

Post Views: 660
Thumbnail [60x60]
by
Daniel Westermann