Tech Couch

A Comfy Corner for Software, Security, Cloud, and AI

Operating postgres clusters on Kubernetes

Table of contents


Postgresql is one of the most widely used databases in the world, battle-tested over decades of production deployment and handling mission-critical data workloads everywhere from small company servers to large cloud services. While getting a single instance up and running is quick and requires almost no configuration, managing a database system in production comes with additional considerations: high availability, recurring backups and authentication management being just a few. The kubegres operator helps automate these processes to let operators focus on the kubernetes cluster, rather than a single service within.

Getting started

To get the benefits of cluster automation, we first need the kubegres operator to be deployed on the cluster.

kubectl apply -f https://raw.githubusercontent.com/reactive-tech/kubegres/v1.17/kubegres.yaml

This will install the operator into the kubegres-system namespace, to separate it from other resources. Note that this only counts for the operator itself; postgres clusters can be deployed into any namespace you like.

To verify your installation, check that the controller pods and service exist:

kubectl get all -n kubegres-system

If some pods are missing or not ready, check the logs of the kubegres-controller-manager-* pod and look for errors in kubectl events.

The kubegres controller automates many tasks implicitly, such as managing failover and promoting secondary replicas to primary, scaling replica counts up and down, or placing pods intelligently, prefering one pod per node to increase durability, but allowing multiple pods per node if not enough nodes are available to fully spread out the deployment.

Deploy a postgres cluster

A postgresql cluster consists of one primary and any number of secondary replicas. The primary is the only one that can process write queries, while reading operations are available from all replicas. The data from the primary replica is copied to the secondary replicas through streaming replication.

To deploy a postgres cluster with kubegres, we first need a secret to store the passwords for the admin account and replication.

secrets.yml

apiVersion: v1
kind: Secret
metadata:
  name: demo-pg-passwords
  namespace: default
type: Opaque
stringData:
  superUserPassword: admin123
  replicationUserPassword: replicationPass

Apply the secret to the cluster:

kubectl apply -f secrets.yml

This creates the secret with the required passwords for replication and the initial superuser account.

Next, deploy the cluster using the demo-pg-passwords secret:

kubegres.yml

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
 name: demo-pg
 namespace: default
spec:
  replicas: 3
  image: postgres:16.1
  database:
     size: 200Mi
  env:
     - name: POSTGRES_PASSWORD
       valueFrom:
          secretKeyRef:
             name: demo-pg-passwords
             key: superUserPassword
     - name: POSTGRES_REPLICATION_PASSWORD
       valueFrom:
          secretKeyRef:
             name: demo-pg-passwords
             key: replicationUserPassword

Create the resource in the cluster:

kubectl apply -f kubegres.yml

Since the kind is set to Kubegres, the previously deployed controller will manage the state of this resource instead of the kubernetes cluster directly. This simple deployment will create a highly-available postgres cluster with 3 replicas: one primary and two secondary, read-only replicas. If the primary replica fails, one of the secondaries will be promoted to primary, and a new second replica is created to maintain exactly 3 cluster members.

The deployed cluster consists of several resources: 3 pods (one per replica), 2 services and a kubegres resource:

kubectl get pods,services,kubegres

The output should look like this (omitting other things you may have running in your cluster):

NAME             READY  STATUS   RESTARTS  AGE
pod/demo-pg-1-0  1/1    Running  0         4m7s
pod/demo-pg-2-0  1/1    Running  0         3m56s
pod/demo-pg-3-0  1/1    Running  0         3m43s

NAME                     TYPE       CLUSTER-IP  EXTERNAL-IP  PORT(S)   AGE
service/demo-pg          ClusterIP  None        <none>       5432/TCP  3m56s
service/demo-pg-replica  ClusterIP  None        <none>       5432/TCP  3m43s
service/kubernetes       ClusterIP  10.96.0.1   <none>       443/TCP   10m

NAME                                        AGE
kubegres.kubegres.reactive-tech.io/demo-pg  4m8s

The services represent the primary and secondary replicas, respectively. Note that the service/demo-pg will always point to the current primary pod. If it fails and a secondary is promoted to primary, the service will then point to that newly promoted pod instead, ensuring the service always forwards to the current master node in the cluster. If you don't need write access to the cluster, using the service/demo-pg-replica will provide better performance and availability, as requests can be load-balanced between secondary replicas.

Kubegres has many more configurable parameters, you can check their online documentation for a complete list with descriptions.

Managing the postgres cluster

Now that the cluster is deployed, the kubegres controller will keep monitoring the demo-pg kubegres resource and adjust the cluster to the desired state described in the resource. Edits can be made at any time, for example to scale the cluster to 5 replicas, simply edit the demo-pg manifest:

kubectl edit kubegres demo-pg

and change the replica count from 3 to 5:

# ...
spec:
  replicas: 5
# ...

The controller will pick up on this change automatically and create new pods to match the new desired state. You can watch every step live with

kubectl events -w

Managing the database contents can be done by executing the psql command in the primary pod of the cluster. To find it, filter by the replicationRole label, the log in using the admin password set in the demo-pg-passwords secret. You can do all of this automatically in a single command:

kubectl exec -it \
  $(kubectl get pods  -l replicationRole=primary -o jsonpath='{.items[0].metadata.name}') \
  -- \
  env PGPASSWORD=$(kubectl get secret demo-pg-passwords -o jsonpath='{.data.superUserPassword}' | base64 -d) \
  psql -U postgres 

Make sure to adjust the name of the secret and superuser account in the last two lines if you have changed them.

Custom postgres images

Although the default postgres installation is plenty flexible, sometimes you need more specific features or optimizations. For postgres, these can be added in the form of extensions. Kubegres is built entirely by relying on the tools shipped in the default postgres docker image, thus it is compatible with any other image that extends it (for example by installing an extension). To switch to the postgis image, extending the default postgres image with the popular postgis extension to enable geospatial querying, all you need to do is change the image line in the previous deployment manifest to the postgis image:

# ...
spec:# ...image: postgis:16-3.4
# ...

Similarly, running a different version is a matter of changing the image tag to the version that fits your needs.

Automated backups

The kubegres controller not only manages the cluster state, it is also able to schedule recurring backups. Backups are not enabled by default, and require a PersistentVolumeClaim to store the backup files into. Start by creating the volume:

backups.yml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: demo-pg-backups
  namespace: default
spec:
  storageClassName: "standard"
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Mi

Then apply it to your kubernetes cluster:

kubectl apply -f backups.yml

Now that we have storage for backup files, edit the demo-pg kubegres to enable scheduled backups:

kubectl edit kubegres demo-pg

And add these lines to the spec field:

# ...
spec:# ...
  backup:
      schedule: "0 */1 * * *"
      pvcName: demo-pg-backups
      volumeMount: /var/lib/backup
# ...

This will create a recurring CronJob, running in the interval specified by schedule (refer to the cron spec for details on defining schedules):

kubectl get cronjobs

If everything worked properly, this should now show the backup job:

NAME            SCHEDULE     SUSPEND  ACTIVE  LAST SCHEDULE  AGE
backup-demo-pg  0 */1 * * *  False    0       <none>         5s

The backup job we created will run at every full hour and write a new backup file to the demo-pg-backups volume.

If you need to run a backup on-demand and cannot wait for the next scheduled one to complete, you can create a Job using the CronJob resource as a template and run that immediately:

kubectl create job --from=cronjob/backup-demo-pg my-immediate-backup-job

The output is available in the job logs:

kubectl logs job.batch/my-immediate-backup-job

Custom configurations

While the default settings may work for most users, you may come to a point where you need to set different configuration parameters. The kubegres resource provides the customConfig field, which can point to a ConfigMap containing one or more of the following config files:

  • primary_init_script.sh - This script is executed once when the first primary replica is created (i.e. when a cluster is freshly deployed, not on failover). This is a great place to set up secondary users accounts, databases and passwords, or import a database dump from external storage.
  • backup_database.sh - This script is executed every time a scheduled backup is performed by the builtin backup CronJob. The main use of this is to enhance the backup process, for example by running deduplication, managing backup retention or moving backups to external storage like S3.
  • postgres.conf - Overrides the main postgres configuration file.
  • pg-hba.conf - Overrides the authentication config file.

To see an example of this, let's set up a configmap that creates a user account with access to only a single database on startup and uses an increased shared_buffers size:

config.yml

apiVersion: v1
kind: ConfigMap
metadata:
 name: demo-pg-config
 namespace: default
data:
 primary_init_script.sh: |
   #!/bin/bash
   set -e
   psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
   CREATE USER 'sam' WITH PASSWORD 'sams_password';
   CREATE DATABASE 'sams_database';
   \connect 'sams_database';
   GRANT ALL ON SCHEMA public TO $customUserName;
   EOSQL
 postgres.conf: |
   listen_addresses = '*'
   max_connections = 100
   shared_buffers = 512MB

Note that every file not explicitly overridden in this config will remain in their default state. The adjusted config will change clusters that are deployed with it to automatically create the user sam with full access to the database sams_database, while also increasing the shared_buffers limit to 512mb.

Create the config in your cluster:

kubectl apply -f config.yml

Then the new demo-pg-config ConfigMap can be used to deploy a cluster with our customized configuration:

custom-kubegres.yml

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
 name: demo-pg-custom
 namespace: default
spec:
  replicas: 3
  image: postgres:16.1
  database:
     size: 200MicustomConfig: demo-pg-config
  env:
     - name: POSTGRES_PASSWORD
       valueFrom:
          secretKeyRef:
             name: demo-pg-passwords
             key: superUserPassword
     - name: POSTGRES_REPLICATION_PASSWORD
       valueFrom:
          secretKeyRef:
             name: demo-pg-passwords
             key: replicationUserPassword

The only difference to the cluster we deployed in the beginning is the field customConfig referencing the demo-pg-config to include the custom configuration files.

After creating the new cluster, the user sam and their database exists automatically, without any further changes required:

kubectl apply -f custom-kubegres.yml



With kubegres, kubernetes operators gain a flexible and lightweight tool to create and manage highly-available postgresql servers in their clusters, complete with automated backups and failover. The automations provided ensure that administrative overhead is low and operators can focus on managing the kubernetes environment rather than single deployed services. While setting up a cluster with kubegres is simple, remember that more steps are necessary for a full production environment, such as monitoring events, pod logs, alerting on backup failures and keeping track of slow SQL queries.

More articles

Writing kubernetes manifests by hand

Writing valid and secure object definitions

PHP output buffering

Controlling what gets sent and when

Automating local domains for minikube

Local ingress domains that just work

Checking disk health in Linux

Identify bad drives before they fail

Handling file downloads with PHP

Meeting user expectations with every download

Streamlined error handling in PHP

Handling all kinds of errors with a single function