Postgres (CloudNativePG)¶
Tomoda's primary datastore. Postgres runs in-cluster as a CloudNativePG (CNPG) Cluster per environment, not on a managed service. Both environments share the k3s cluster and one data namespace, with separate Cluster CRs (postgres-dev, postgres-prod) backed by separate PVCs, secrets, and backup paths.
Two layers¶
CNPG ships as two things: a cluster-scoped operator (the controller that owns the Cluster CRD) and the instances it manages. They are deployed by separate Argo CD Applications:
- Operator:
k8s/envs/platform/cnpg/application.yaml. Helm chartcloudnative-pg/cloudnative-pg, version0.29.1(CNPG 1.30.1), installed intocnpg-system. Pod monitor enabled so Prometheus scrapes the controller. - Barman Cloud plugin: Argo CD app
cnpg-barman-cloudin the same file. Helm chartcloudnative-pg/plugin-barman-cloud, version0.8.1(plugin v0.15.1), also incnpg-system(the plugin must share the operator's namespace). It owns theObjectStoreCRD and injects aplugin-barman-cloudsidecar into every instance that uses it. Operator and plugin talk gRPC over mutual TLS with certificates from cert-manager (a chart-made self-signedIssuer). Controller: 10m CPU / 64Mi requested, 256Mi limit. - Instances:
k8s/envs/<env>/postgres/manifests/cluster.yaml, applied by a per-env Argo CD Application that targets thedatanamespace.
A single operator install reconciles Cluster CRs across all namespaces; there is no separate prod operator.
Operator upgrades¶
- In place.
ENABLE_INSTANCE_MANAGER_INPLACE_UPDATES: "true"(operatorconfig.data): after an operator upgrade the new instance manager binary replaces the running one and adopts the running postmaster, so Postgres is not restarted. The operator logsUpgrading instance managerand the Cluster shows the online-upgrade phase until it is done; the postgres container log then hasStarting CloudNativePG Instance Managerwith the new version. The pod's bootstrap init container keeps the old operator image tag, so the pod definition does not show the operator version. - Still a restart: a change to the pod spec itself (operand image, resources, sidecars,
.spec.plugins) recreates the instance, with no switchover since there is one instance. - CRDs ship as chart templates, so Argo CD upgrades them with the chart. The
clustersandpoolersCRDs exceed the 256 KB client-side apply annotation limit, henceServerSideApply=trueand theServerSideDiffcompare option. - Primary lease. The operator coordinates promotion through a
Leasenamed after the cluster indata(kubectl get lease -n data). The liveness isolation check (primary shut down when it can reach neither the API server nor a peer) is skipped whileinstances: 1; it applies once prod runs two instances. - Deprecated, still in use:
monitoring.enablePodMonitor(the operator-managed PodMonitor the Postgres alerts scrape through) and the in-treebarmanObjectStore(below). The webhook warns on both.
Node placement¶
No node selectors or taints: on one server both clusters share the RAID1 NVMe through local-path PVCs. When a second server joins, raise prod to instances: 2 with pod anti-affinity so the standby lands on the other server.
Cluster CR¶
Both environments run the same custom operand image, built from
k8s/envs/platform/postgres-image and pinned to its version tag (CNPG rejects :latest):
imageName: ghcr.io/tomoda-labs/tomoda-postgres:17-3.6-pgroonga4.0.1-pgvector0.8.0-h3-4.5.0
Postgres 17 with PostGIS 3.6, plus PGroonga (multilingual full-text search,
including CJK), pgvector (semantic search) and h3-pg (hexagonal cell grid) for
the location service. The bootstrap step runs CREATE EXTENSION for postgis,
postgis_topology, pgroonga, vector, unaccent, pg_trgm, h3 and
h3_postgis after initdb, so apps get spatial types, search and cells on a
fresh cluster with no extra step. On an existing cluster, a new extension is a
one-off CREATE EXTENSION IF NOT EXISTS ... as the superuser after the pods
roll to the new image.
.github/workflows/images.yml builds the image on merges to main touching
k8s/envs/platform/postgres-image/, tagged from that directory's VERSION
(existing tags are never overwritten). When extension versions change, bump
VERSION and imageName in both cluster manifests together; CNPG rolls the pods. Cold start:
task images:build -- tomoda-postgres before the cluster syncs. Build details
live in k8s/envs/platform/postgres-image/.
Dev (postgres-dev)¶
- 1 instance,
3 GiPVC (room for the location catalog + its search indexes). - DB
tomoda_dev, ownertomoda_dev_user. max_connections=100,shared_buffers=128MB,effective_cache_size=384MB,maintenance_work_mem=256MB,log_min_duration_statement=1000.- Resources: 50m CPU request (no limit), 256Mi memory request, 512Mi limit.
shared_buffersandeffective_cache_sizeare 25% and 75% of the limit, the same ratios as prod.
Prod (postgres-prod)¶
- 1 instance,
150 GiPVC. - DB
tomoda_prod, ownertomoda_prod_user. max_connections=200,shared_buffers=4GB,effective_cache_size=12GB,work_mem=32MB,maintenance_work_mem=1GB,random_page_cost=1.1(local NVMe),log_min_duration_statement=1000(only log slow queries).- Resources: 2 CPU / 8Gi requested, 16Gi memory limit, no CPU limit.
HA switch (resilient flavour)¶
The HA upgrade from instances: 1 is a single coordinated switch on the prod Cluster:
instances: 2with a synchronous standby.- Hostname anti-affinity so the two instances never co-locate.
- A
PodDisruptionBudgetso a node drain can't take both down. - A second server in the cluster so the standby lands on distinct hardware (see Scaling).
Backups (below) are unchanged either way.
Both clusters set pg_hba: hostssl all all 10.42.0.0/16 scram-sha-256 (the k3s pod CIDR) with password_encryption=scram-sha-256, so application password logins require TLS + SCRAM and can only originate from the in-cluster pod range. The postgres-{dev,prod}-policy NetworkPolicy further restricts reach (see Network Policies); no external Service is exposed.
Client TLS (verify-full)¶
The backend connects with DB_SSLMODE=verify-full, verifying the server certificate chain and hostname. Two pieces make that work:
- CA distribution. CNPG generates the server CA as
postgres-<env>-caindata. A per-app-namespacepostgres-caExternalSecret mirrorsca.crtintotomoda-<env>via ESO's Kubernetes provider, reading it with thepostgres-ca-readerServiceAccount (grantedgeton that secret by thecnpg-ca-reader-{dev,prod}Role indata). Backend and seed-load pods mount it at/etc/postgres-ca/ca.crt(DB_SSLROOTCERT). - Cert SANs. The app connects through the
postgres-<env>-postgresqlExternalName alias, not covered by CNPG's default SANs, so each cluster adds it tospec.certificates.serverAltDNSNames.
Credentials¶
Bootstrap credentials are populated by ExternalSecrets pulling tomoda-db-password-<env> from the gsm-tomoda ClusterSecretStore (GCP Secret Manager). The ESO template formats them as a kubernetes.io/basic-auth Secret named postgres-<env>-credentials (username = env owner, password from GSM). CNPG reads it during initdb.
The backend's backend-secrets-<env> ExternalSecret pulls the same tomoda-db-password-<env> key and exposes it as DB_PASSWORD, so both sides stay in sync from one GSM value.
DSNs (application-facing)¶
CNPG creates postgres-<env>-rw, postgres-<env>-ro, and postgres-<env>-r Services automatically. To keep the backend's DB_HOST stable, each Cluster manifest declares an ExternalName Service aliasing the stable hostname to the CNPG -rw Service:
| Env | Application connects to | Aliases to |
|---|---|---|
| dev | postgres-postgresql.data.svc.cluster.local |
postgres-dev-rw.data.svc.cluster.local |
| prod | postgres-prod-postgresql.data.svc.cluster.local |
postgres-prod-rw.data.svc.cluster.local |
These exact hostnames are baked into the Tomoda backend-config-<env> ConfigMap (see Tomoda).
Backups¶
Prod ships its WALs and base backups to R2 through the Barman Cloud plugin (dev has no backups). CNPG's in-tree spec.backup.barmanObjectStore is deprecated since CNPG 1.26, with removal scheduled for 1.31; policy/cnpg_barman_plugin.rego rejects it, and method: barmanObjectStore, in CI.
The postgres-prod Cluster names the plugin as its WAL archiver and points it at an ObjectStore in the same manifest:
# Cluster
plugins:
- name: barman-cloud.cloudnative-pg.io
isWALArchiver: true
parameters:
barmanObjectName: postgres-prod-backups
---
# ObjectStore postgres-prod-backups
spec:
configuration:
destinationPath: s3://tomoda-db-backups/prod/
endpointURL: https://<account>.r2.cloudflarestorage.com
s3Credentials:
accessKeyId: { name: r2-backup-credentials, key: access_key_id }
secretAccessKey: { name: r2-backup-credentials, key: secret_access_key }
region: { name: r2-backup-credentials, key: region }
wal: { compression: gzip }
data: { compression: gzip }
retentionPolicy: "30d"
instanceSidecarConfiguration:
resources: { requests: { cpu: 50m, memory: 128Mi }, limits: { memory: 1Gi } }
- Path. The plugin's
serverNamedefaults to the cluster name, so prod writesprod/postgres-prod/. LeaveserverNameempty in theObjectStore; set it, when needed, as a plugin parameter on the Cluster. - Retention.
retentionPolicylives on theObjectStore. The sidecar on the primary prunes backups older than the 30-day recovery window for its ownserverNameonly, and removes the matchingBackupCRs. - Sidecar.
barman-cloud-wal-archiveandbarman-cloud-backuprun in theplugin-barman-cloudsidecar, not thepostgrescontainer;instanceSidecarConfiguration.resourcessizes it. A change there rolls the instance. - Credentials.
r2-backup-credentialsis an ExternalSecret overtomoda-r2-cluster-credentialsin the same manifest. The plugin creates a Role indataso the instance pod can read theObjectStoreand that Secret. - Schedule. A
ScheduledBackup(manifests/backup.yaml,method: plugin,pluginConfiguration.name: barman-cloud.cloudnative-pg.io) takes a base backup daily at 03:00 UTC withschedule: "0 0 3 * * *". CNPG schedules use a 6-field cron with seconds first (sec min hour dom mon dow), so a 5-field expression like0 3 * * *runs hourly.policy/cnpg_scheduled_backup.regorejects 5-field schedules in CI. - Sync order. The
ObjectStorecarries sync wave-1, andpostgres-prodretries a failed sync for up to about an hour, so the Cluster only syncs once theObjectStoreCRD fromcnpg-barman-cloudexists. - Metrics. The plugin exports
barman_cloud_cloudnative_pg_io_last_available_backup_timestamp,..._last_failed_backup_timestampand..._first_recoverability_pointthrough the instance's metrics port; the backup alerts and dashboards use them. - Customer-key encryption (SSE-C) is not in a plugin release: plugin-barman-cloud#646, open PR #1017.
Operations¶
Runbook-level steps (base backup on demand, point-in-time recovery, storage expansion, major-version upgrades, switchover) are in the Postgres operations runbook. A quick orientation:
- Manual base backup: apply a
BackupCR withmethod: pluginpointing at the cluster; the sidecar runs it immediately and uploads to theObjectStore'sdestinationPath. - Storage:
local-pathdoesn't enforce or expand sizes; the database can use whatever the server's NVMe has free. Watch host disk instead (see the disk budget). - Version upgrade: bump
imageName. CNPG performs an in-place minor upgrade per pod; majors require aCluster.spec.bootstrap.recoveryflow against a backup. - Connectivity check:
kubectl -n data exec -it postgres-<env>-1 -- psqlfor ad-hoc shells; for UI access use pgAdmin. - Monitoring:
monitoring.enablePodMonitor: trueon both clusters means Prometheus (in themonitoringnamespace) scrapes per-instance metrics; the alerts ink8s/envs/platform/manifests/postgres-alerts.yamlselect on the scrape jobdata/postgres-<env>(Data alert runbook).