Skip to content

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 chart cloudnative-pg/cloudnative-pg, version 0.29.1 (CNPG 1.30.1), installed into cnpg-system. Pod monitor enabled so Prometheus scrapes the controller.
  • Barman Cloud plugin: Argo CD app cnpg-barman-cloud in the same file. Helm chart cloudnative-pg/plugin-barman-cloud, version 0.8.1 (plugin v0.15.1), also in cnpg-system (the plugin must share the operator's namespace). It owns the ObjectStore CRD and injects a plugin-barman-cloud sidecar into every instance that uses it. Operator and plugin talk gRPC over mutual TLS with certificates from cert-manager (a chart-made self-signed Issuer). 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 the data namespace.

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" (operator config.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 logs Upgrading instance manager and the Cluster shows the online-upgrade phase until it is done; the postgres container log then has Starting CloudNativePG Instance Manager with 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 clusters and poolers CRDs exceed the 256 KB client-side apply annotation limit, hence ServerSideApply=true and the ServerSideDiff compare option.
  • Primary lease. The operator coordinates promotion through a Lease named after the cluster in data (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 while instances: 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-tree barmanObjectStore (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 Gi PVC (room for the location catalog + its search indexes).
  • DB tomoda_dev, owner tomoda_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_buffers and effective_cache_size are 25% and 75% of the limit, the same ratios as prod.

Prod (postgres-prod)

  • 1 instance, 150 Gi PVC.
  • DB tomoda_prod, owner tomoda_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: 2 with a synchronous standby.
  • Hostname anti-affinity so the two instances never co-locate.
  • A PodDisruptionBudget so 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>-ca in data. A per-app-namespace postgres-ca ExternalSecret mirrors ca.crt into tomoda-<env> via ESO's Kubernetes provider, reading it with the postgres-ca-reader ServiceAccount (granted get on that secret by the cnpg-ca-reader-{dev,prod} Role in data). Backend and seed-load pods mount it at /etc/postgres-ca/ca.crt (DB_SSLROOTCERT).
  • Cert SANs. The app connects through the postgres-<env>-postgresql ExternalName alias, not covered by CNPG's default SANs, so each cluster adds it to spec.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 serverName defaults to the cluster name, so prod writes prod/postgres-prod/. Leave serverName empty in the ObjectStore; set it, when needed, as a plugin parameter on the Cluster.
  • Retention. retentionPolicy lives on the ObjectStore. The sidecar on the primary prunes backups older than the 30-day recovery window for its own serverName only, and removes the matching Backup CRs.
  • Sidecar. barman-cloud-wal-archive and barman-cloud-backup run in the plugin-barman-cloud sidecar, not the postgres container; instanceSidecarConfiguration.resources sizes it. A change there rolls the instance.
  • Credentials. r2-backup-credentials is an ExternalSecret over tomoda-r2-cluster-credentials in the same manifest. The plugin creates a Role in data so the instance pod can read the ObjectStore and 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 with schedule: "0 0 3 * * *". CNPG schedules use a 6-field cron with seconds first (sec min hour dom mon dow), so a 5-field expression like 0 3 * * * runs hourly. policy/cnpg_scheduled_backup.rego rejects 5-field schedules in CI.
  • Sync order. The ObjectStore carries sync wave -1, and postgres-prod retries a failed sync for up to about an hour, so the Cluster only syncs once the ObjectStore CRD from cnpg-barman-cloud exists.
  • Metrics. The plugin exports barman_cloud_cloudnative_pg_io_last_available_backup_timestamp, ..._last_failed_backup_timestamp and ..._first_recoverability_point through 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 Backup CR with method: plugin pointing at the cluster; the sidecar runs it immediately and uploads to the ObjectStore's destinationPath.
  • Storage: local-path doesn'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 a Cluster.spec.bootstrap.recovery flow against a backup.
  • Connectivity check: kubectl -n data exec -it postgres-<env>-1 -- psql for ad-hoc shells; for UI access use pgAdmin.
  • Monitoring: monitoring.enablePodMonitor: true on both clusters means Prometheus (in the monitoring namespace) scrapes per-instance metrics; the alerts in k8s/envs/platform/manifests/postgres-alerts.yaml select on the scrape job data/postgres-<env> (Data alert runbook).