K8S Series Part 06 - Databases Done Right: CloudNativePG

Most of my apps need a database, and almost all of them want PostgreSQL. The naive way to run Postgres in Kubernetes is a StatefulSet with a PVC - one pod, one disk. It works right up until it doesn't, and the ways it doesn't are exactly the ones that hurt: inconsistent backups, no failover, fiddly credential management.

CloudNativePG (CNPG) fixes all of that by being an operator. This article explains the operator pattern, then how CNPG specifically runs my application databases.

First: what's an "operator"?

A plain Kubernetes controller knows how to manage generic objects (Deployments, Pods). It has no idea what "a healthy PostgreSQL cluster" means, that a primary accepts writes, standbys stream replication, failover requires promoting a replica and repointing clients, backups need a checkpoint first, and so on.

An operator encodes that domain expertise. It:

1. Defines a Custom Resource (CRD) - a new object kind, here Cluster (a Postgres cluster), ScheduledBackup, Backup.
2. Runs a controller that watches those resources and does the expert work to make reality match them.

You describe what you want; the operator knows how. That's the whole pattern, and it's why my database YAML is short while the behaviour is sophisticated.

Why not a hand-rolled StatefulSet?

I actually started with a StatefulSet for my Authentik's deployment Postgres DB. Three problems pushed me to CNPG:

1. Crash-inconsistent backups. Velero snapshotted the PVC while Postgres was running. Postgres keeps dirty pages in shared_buffers that may not be on disk yet, so a restore from that snapshot could need crash recovery or be subtly inconsistent. A database backup that might restore is not a backup.
2. No failover. If the single pod died, Kubernetes restarted it, but in-flight work
   was lost and there was no hot standby - recovery time was "however long a restart and replay takes."
3. Manual credential management. The password lived in a SealedSecret and had to be kept in sync with whatever the app expected, by hand.

CNPG solves all three at once.

A CNPG cluster, declared

Here's the shape of what I commit per database (Grafana's, lightly trimmed):

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: grafana-cnpg
  namespace: monitoring
spec:
  instances: 2                       # ◄── primary + 1 hot standby

  bootstrap:
    initdb:
      database: grafana
      owner: grafana

  storage:
    storageClass: longhorn           # replicated block storage (article 05)
    size: 5Gi

  backup:
    volumeSnapshot:
      className: longhorn-velero      # snapshot via Longhorn CSI
      snapshotOwnerReference: backup

  affinity:
    topologyKey: kubernetes.io/hostname   # ◄── keep primary & standby on different nodes

  monitoring:
    enablePodMonitor: true            # expose metrics to Prometheus

That's it. From those ~25 lines CNPG runs a replicated, monitored, backup-capable Postgres cluster.

High availability: the primary/standby pair

instances: 2 means a primary (accepts reads and writes) and a standby that
streams replication from it (a hot, ready-to-promote copy).

The affinity.topologyKey: kubernetes.io/hostname line is doing important work: it tells CNPG to never place both instances on the same node. Otherwise a single machine failure could take out both copies, defeating the point. All four of my CNPG cluster (Authentik, Grafana, NetBox, AFFiNE) run this way. Failover drops recovery from "minutes of restore" to "seconds of promotion."

Not every database needs two instances - it costs a second PVC and some RAM. I run pairs where downtime is annoying (SSO, dashboards, IPAM). Apps with their own tuned Postgres (Immich) or trivial state (Vaultwarden on SQLite) don't use CNPG at all.

The three Services CNPG creates

For every cluster, CNPG maintains three Services and keeps them pointed at the right pods as the primary changes:

   <name>-rw   ──►  the current PRIMARY    (send writes here)
   <name>-ro   ──►  standbys only          (offload read-only queries)
   <name>-r    ──►  any instance           (round-robin reads)

Apps connect to <name>-rw and never have to know which pod is currently primary - after a failover, the -rw Service simply points at the new one. The app's connection string is stable across failovers.

Credentials, generated for you

CNPG auto-generates a Secret (<name>-app) with everything an app needs - username, password, host, uri, jdbc-uri, and more. The app references it directly:

env:
  - name: DB_PASSWORD
    valueFrom:
      secretKeyRef:
        name: grafana-cnpg-app
        key: password

No password in my repo, no manual syncing. CNPG owns the credential; the app reads it. This is also why these DBs don't even need a SealedSecret for their passwords.

Backups that are actually consistent

This was the original motivation, so it's worth being precise. A CNPG ScheduledBackupdoesn't just snapshot the disk and hope instead it coordinates with Postgres:

   ScheduledBackup fires (e.g. 02:00 daily)
     │
     ├─► CNPG tells Postgres: CHECKPOINT   (flush all dirty pages to disk)
     │
     ├─► Postgres confirms checkpoint complete
     │
     ├─► CNPG triggers a Longhorn VolumeSnapshot via CSI
     │
     └─► snapshot now represents a guaranteed-clean, consistent state

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: grafana-cnpg-backup
  namespace: monitoring
spec:
  schedule: "0 0 2 * * *"        # daily at 02:00 (note: CNPG uses 6-field cron!)
  backupOwnerReference: self
  cluster:
    name: grafana-cnpg
  method: volumeSnapshot

Because the checkpoint happens before the snapshot, a restore comes up clean with no crash recovery. This is the "Layer 2" of my backup strategy, application-consistent DB recovery points - and it sits between Longhorn snapshots (layer 1) and off-site Velero (layer 3). Full picture we will further discuss and explain article 09.

Operating CNPG

CNPG ships a kubectl plugin that's worth installing:

kubectl cnpg status grafana-cnpg -n monitoring     # health, primary, replication lag
kubectl get cluster -A                              # all CNPG clusters at a glance
kubectl get backup -A                               # backup history & outcomes

# connect to the primary for a manual query / dump
kubectl exec -it grafana-cnpg-1 -n monitoring -- psql -U postgres grafana

Replication lag is the metric I watch - a standby falling behind the primary means it can't take over cleanly. I have a Prometheus alert on it (article 10).

Takeaways

• An operator encodes domain expertise behind a custom resource - you declare a Cluster, CNPG handles primaries, standbys, failover, services, and backups.
• A hand-rolled Postgres pod gives you none of: consistent backups, failover, or managed credentials. CNPG gives all three from ~25 lines of YAML.
• instances: 2 + anti-affinity = a hot standby on a different node; failover in seconds. The -rw Service abstracts away "which pod is primary," so apps survive failovers without config changes.
• Consistent backups come from CHECKPOINT-then-snapshot, not snapshot-and-pray.

We've now covered the whole platform: Flux, ingress, TLS/secrets, storage, databases. Time to put it together and deploy an actual app, file by file.

Resources

This cluster

• k3s-safeqbit-local-hq repo - Cluster and ScheduledBackup CRs in each apps/.../<app>/ folder; Grafana's in infrastructure/.../configs/

Documentation

• CloudNativePG documentation - the operator, top to bottom
• CNPG: Backup and Recovery - VolumeSnapshot method, scheduling
• CNPG: Replication & failover - primary/standby behaviour
• CNPG: kubectl plugin - kubectl cnpg status
• PostgreSQL documentation - checkpoints, WAL, replication
• Kubernetes: Operator pattern and Custom Resources

Next lesson → In Deploying an app the GitOps way, the payoff: we'll put the whole platform together and stand up a real app from scratch, file by file, watching a single commit turn into a running service.