Modifying the Default PostgreSQL Helm Chart to Emit Logging

Photo by Aleksandar Radovanovic on Unsplash

While using the PostgreSQL Helm Chart I wanted to take a look at the queries which were running. I quickly realized I needed to enable the logging to see all the DML goodness to later feed into pgBadger to review the usage patterns of the queries. Below are three different methods for enabling the logging of all the queries to local logs:

Solution 1 – Custom values.yaml file

Copy the default values.yaml file from here.

In the file uncomment postgresqlExtendedConf and add the following lines:

postgresqlExtendedConf:  loggingCollector: on
  logDirectory: mylogs4pgbadger
  logMinDurationStatement: 0

A quick note: don’t use postgresqlConfiguration, this will simply wipe the ENTIRE postgresql.conf file and replace it with the 3 configurations above and is not what we want.

If you are running minikube take a moment and update the service: section of the yaml

service:
  ## PosgresSQL service type
  type: ClusterIP
  clusterIP: None
  port: 5432

to

service:
  ## PosgresSQL service type
  type: NodePort
  port: 5432

Now to deploy an instance of this, from the same folder as your customized values.yaml file run:

helm install --name pg88c -f ./values.yaml stable/postgresql

Specify a name other than pg88c which makes sense to you. From the output of the helm create command will be the command to retrieve the password. Run it so you have the password available to you later on:

kubectl get secret --namespace default pg88c-postgresql -o \
  jsonpath="{.data.postgresql-password}" | base64 --decode

To view the contents of the log files, connect to the container and tail the logs:

kubectl exec -it pg88c-postgresql-0 -- bash
cd /bitnami/postgresql/data/mylogs4pgbadger
tail -f *.log

To view the contents of the configuration file which will be processed AFTER the postgresql.conf file, connect to the container and output the file:

kubectl exec -it pg88c-postgresql-0 -- bash
cd /opt/bitnami/postgresql/conf/conf.d
cat override.conf

with the contents of the file being:

log_directory=mylogs4pgbadger
log_min_duration_statement=0
logging_collector=true

Note that the parameters are converted from camel case to the snake case PostgreSQL needs.

Solution 2 – Supply the Configs as set Parameters

This is the shorter version that accomplishes the same thing as Solution 1 without having to manually edit and populate a values.yaml file. Simply provide the extended PostgreSQL configurations as set commands when calling helm install:

helm install --name pg88g \
--set postgresqlExtendedConf.logDirectory=mylogs4pgbadger \
--set postgresqlExtendedConf.logMinDurationStatement=0 \
--set postgresqlExtendedConf.loggingCollector=true \
--set service.type=NodePort \
stable/postgresql

A couple notes:

• You still have to provide the options in camel case
• This will populate the override.conf file just like the first solution
• Remove the service.type=NodePort if you are running somewhere other than minikube

Solution 3 – Use a ConfigMap to for the extendedConfigMap parameter

Create a file called override.conf in the current working folder with these contents:

log_directory=mylogs4pgbadger
log_min_duration_statement=0
logging_collector=true

Create a ConfigMap called pg-log-configs which references the override.conf file:

kubectl create configmap pg-log-configs \
  --from-file=override.conf

Now deploy the helm chart with the ConfigMap:

helm install --name pg88i \
  --set extendedConfConfigMap=pg-log-configs \
  --set service.type=NodePort \
  stable/postgresql

To view the ConfigMap:

kubectl get configmaps pg-log-configs -o yaml

To view the contents of the configuration file which will be processed AFTER the postgresql.conf file, connect to the container and output the file:

kubectl exec -it pg88c-postgresql-0 -- bash
cd /opt/bitnami/postgresql/conf/conf.d
cat override.conf

with the contents of the file being:

log_directory=mylogs4pgbadger
log_min_duration_statement=0
logging_collector=true

Summary

If you need a single one line helm command without the need to create and manage separate yaml files, solution #2 is the way to go. This is most useful if you only need a handful of customizations.

If you have a longer list of customizations either of the other solutions are viable. Solution #3 took a bit of trial and error with the trick being the name of the ConfigMap file must be override.conf, other file names did not work in my testing.

Happy Postgres!