In the last post we’ve created a more detailed version of the cluster definition for CloudNativePG. This gave us a PostgreSQL cluster with enabled data checksum, the correct locale, an increased size of the WAL segments and the possibility to run custom statements or scripts after initdb. What we didn’t touch at all, was the configuration of PostgreSQL, and this is the scope of this post.
This is the last configuration we’ve used in previous post:
minicube@micro-minicube:~> cat pg.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: my-pg-cluster
spec:
instances: 3
bootstrap:
initdb:
database: db1
owner: db1
dataChecksums: true
walSegmentSize: 32
localeCollate: 'en_US.utf8'
localeCType: 'en_US.utf8'
postInitSQL:
- create user db2
- create database db2 with owner = db2
storage:
size: 1Gi
There is nothing in it which configures the PostgreSQL instance as of now. Doing this is quite simple, it just requires an additional section called “postgresql” on the same level as “instances” or “bootstrap”:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: my-pg-cluster
spec:
instances: 3
bootstrap:
initdb:
database: db1
owner: db1
dataChecksums: true
walSegmentSize: 32
localeCollate: 'en_US.utf8'
localeCType: 'en_US.utf8'
postInitSQL:
- create user db2
- create database db2 with owner = db2
postgresql:
parameters:
work_mem: "8MB"
storage:
size: 1Gi
This will change work_mem from the default of 4MB to 8MB:
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=# show work_mem;
work_mem
----------
8MB
(1 row)
postgres=#
\q
could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory
postgres@my-pg-cluster-1:/$
exit
Changing a parameter is done in the same way: Change the value in yaml file, and re-apply it:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
minicube@micro-minicube:~> grep work_mem pg.yaml
work_mem: "12MB"
minicube@micro-minicube:~> kubectl apply -f pg.yaml
cluster.postgresql.cnpg.io/my-pg-cluster configured
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=# show work_mem;
work_mem
----------
12MB
(1 row)
What you shouldn’t do is to change parameters either directly in the PostgreSQL configuration files (postgresql.conf, postgresql.auto.conf) or by using “alter system“. The new feature in PostgreSQL 17, which allows to disable the “alter system” command came in exactly for this reason: In such a setup, where all the configuration is driven externally, this should be the only source of truth. Changing a configuration directly in PostgreSQL must be avoided.
Another topic you need to think about is access control, meaning pg_hba.conf. Same principle here: This needs to be done in the yaml, in an additional section under the “postgresql” section, not directly in the hba file itself:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: my-pg-cluster
spec:
instances: 3
bootstrap:
initdb:
database: db1
owner: db1
dataChecksums: true
walSegmentSize: 32
localeCollate: 'en_US.utf8'
localeCType: 'en_US.utf8'
postInitSQL:
- create user db2
- create database db2 with owner = db2
postgresql:
parameters:
work_mem: "12MB"
pg_hba:
- host all all 192.168.122.0/24 scram-sha-256
storage:
size: 1Gi
Re-apply, and it’s done:
minicube@micro-minicube:~> kubectl apply -f pg.yaml
cluster.postgresql.cnpg.io/my-pg-cluster configured
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 database,user_name,address,netmask from pg_hba_file_rules where address like '%122%';
database | user_name | address | netmask
----------+-----------+---------------+---------------
{all} | {all} | 192.168.122.0 | 255.255.255.0
(1 row)
postgres=#
There are some parameters which cannot be changed and are managed entirely by the operator, e.g. the archive_command. Check the documentation for the full list. This is mostly to guarantee archiving is working and streaming replication is properly setup.
That’s it for this post. In the next post we’ll look at which PostgreSQL extensions are available in CloudNativePG and how you can use and configure them.