PostgreSQL (CloudNativePG)

Overview

Product applications in the cluster get PostgreSQL from the CloudNativePG operator. Platform services keep the host’s PostgreSQL instead (Keycloak, Miniflux, Invidious), reference PostgreSQL.

Item Value
Operator chart cloudnative-pg 0.29.1 (operator 1.30.1)
Backup plugin chart plugin-barman-cloud 0.9.0 (plugin v0.16.0)
Wrapper chart cloudnative-pg/ in the charts repo
Application cloudnative-pg in the apps repo, namespace cnpg-system
Storage ceph-rbd, reference Storage
Backups Ceph RGW, http://10.100.0.1:7480

The Barman Cloud plugin runs in the operator’s namespace and gets its mTLS certificates from cert-manager. Backups go through the plugin’s ObjectStore resource; the in-tree barmanObjectStore field of a Cluster is deprecated upstream and not used.

flowchart LR
    app["App pod"] -->|"&lt;cluster&gt;-rw"| pg["Cluster<br/>(1 instance, ceph-rbd)"]
    pg -->|"WAL + daily base backup"| plugin["Barman Cloud plugin<br/>(cnpg-system)"]
    plugin --> rgw[("Ceph RGW<br/>s3://&lt;bucket&gt;/&lt;namespace&gt;")]
    bao["OpenBao"] -->|"ESO"| secrets["credentials · RGW keys"]
    secrets --> pg
    secrets --> app

Each app declares its own Cluster, ObjectStore and ScheduledBackup in its chart. Clusters run one instance, because the cluster has a single schedulable worker. Apps connect through the <cluster>-rw Service.

rgxr

The rgxr chart (rgxr/templates/database.yaml) runs the rgxr-pg Cluster in each of rgxr-test and rgxr-prod:

Item Value
Image ghcr.io/cloudnative-pg/postgresql:17.12-minimal-trixie
Database, owner rgxr, rgxr
Volume 5 Gi on ceph-rbd
Base backup daily at 03:00, retention 30 days
Backup prefix s3://rgxr-backups/rgxr-test, s3://rgxr-backups/rgxr-prod

Credentials — the External Secret rgxr-pg-credentials templates the owner’s username and password for CloudNativePG and the API’s DATABASE_URL from the single OpenBao entry rgxr/postgres (password), so the password and the URL cannot drift. The RGW keys come from rgxr/backup through the External Secret rgxr-backup.

Backup storage — create a dedicated RGW user and the bucket on the server, then store the user’s keys in OpenBao. Create the user and print its key pair:

sudo radosgw-admin user create --uid=rgxr-backup --display-name="rgxr backups"

Store the keys from that output (never paste them into git or chat):

bao kv put rgxr/backup access-key-id=<access_key> secret-access-key=<secret_key>

Create the bucket as that user:

AWS_ACCESS_KEY_ID=<access_key> AWS_SECRET_ACCESS_KEY=<secret_key> \
  nix run nixpkgs#awscli2 -- --endpoint-url http://10.100.0.1:7480 \
  s3api create-bucket --bucket rgxr-backups

Migration from the StatefulSet — both environments run on rgxr-pg (postgres.cloudnativepg: true). The chart can still bootstrap a new Cluster from a running rgxr-postgres StatefulSet: postgres.importLegacy: true imports its database (bootstrap.initdb.import, type microservice) and keeps the StatefulSet running as the source. Compare row counts of every table on both sides before turning the flag off; both commands connect over the pod’s local socket. The query:

Q="select table_name, (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, table_name), false, true, '')))[1]::text
   from information_schema.tables where table_schema = 'public' order by 1"

Run it against the old StatefulSet and the new Cluster; the outputs must match:

kubectl -n rgxr-test exec rgxr-postgres-0 -- psql -U rgxr -d rgxr -Atc "$Q"
kubectl -n rgxr-test exec rgxr-pg-1 -c postgres -- psql -d rgxr -Atc "$Q"

Turning the flag off removes the StatefulSet but not its local-path PVC data-rgxr-postgres-0. Those PVCs still exist in rgxr-test and rgxr-prod; delete them by hand once no rollback is wanted:

kubectl -n rgxr-test delete pvc data-rgxr-postgres-0
kubectl -n rgxr-prod delete pvc data-rgxr-postgres-0

Restore check — Backup resources with method: plugin take an on-demand base backup; rgxr-pg-verify in each namespace is the first one, taken after the migration.

Backup Coverage

CloudNativePG backups land in Ceph RGW on the server. The server’s Restic job to Backblaze B2 does not cover RGW buckets or the kubernetes pool, so these backups have no off-site copy. Reference Storage.