Skip to content

Postgres (CloudNativePG)

Tomoda's primary datastore. Postgres runs in-cluster as a CloudNativePG (CNPG) Cluster per environment, not on Cloud SQL or any other managed service. Both environments share one GKE cluster and one data namespace, with separate Cluster CRs (postgres-dev, postgres-prod) backed by separate PVCs, secrets, and backup paths.

Not Cloud SQL

The only Postgres in Tomoda's application path is the CNPG cluster described here. The Photon Indexer design references a possible warm Nominatim Postgres on Cloud SQL (see Photon indexing), unrelated to application data.

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.25.0, installed into cnpg-system. Pod monitor enabled so Prometheus scrapes the controller.
  • 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.

Node placement

Both clusters pin to the on-demand pool=data pool via spec.affinity: nodeSelector: {pool: data} plus a toleration for the workload=stateful:NoSchedule taint. The taint keeps everything except CNPG + Redis off the pool, so a database is never spot-preempted (a preempted DB is an outage). See GKE node pools.

Cluster CR

Both environments run the same custom operand image, built from k8s/envs/platform/postgres-image and tracked by :latest in each repo:

imageName: us-central1-docker.pkg.dev/development-485000/tomoda-<env>-repo/tomoda-postgres:latest

Postgres 17 with PostGIS 3.5, plus PGroonga (multilingual full-text search, including CJK) and pgvector (semantic search) for the location service. The bootstrap step runs CREATE EXTENSION for postgis, postgis_topology, pgroonga, vector, unaccent, and pg_trgm after initdb, so apps get spatial types and search on a fresh cluster with no extra step.

A Cloud Build trigger (postgres-image-push-trigger) rebuilds the image on every merge to main touching k8s/envs/platform/postgres-image/, pushing :<version> and :latest to both repos. A fresh cluster pulls the newest :latest; an existing cluster rolls only on a deliberate pod restart (no hot-swap on tag re-push). Bump _VERSION in cloudbuild-postgres.yaml when extension versions change (:<version> is the rollback handle). Cold start: run task postgres-image:push once 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, maintenance_work_mem=256MB, log_statement=all (verbose for debugging).
  • Resources: 50m–250m CPU, 128Mi–256Mi memory.

Prod (postgres-prod)

  • 1 instance (lean), 20 Gi PVC.
  • DB tomoda_prod, owner tomoda_prod_user.
  • shared_buffers=256MB, effective_cache_size=512MB, work_mem=4MB, maintenance_work_mem=256MB, log_statement=none, log_min_duration_statement=1000 (only log slow queries).
  • Resources: 100m–1000m CPU, 256Mi–1024Mi memory.

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 / GKE upgrade can't take both down.
  • Raise the pool=data ceiling to 2–3 nodes so the standby lands on a distinct node across zones (see GKE).

Backups (below) are unchanged either way.

Both clusters set pg_hba: hostssl all all 10.1.0.0/16 scram-sha-256 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 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 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 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

Each Cluster ships its WALs and base backups to GCS via Barman:

backup:
  barmanObjectStore:
    destinationPath: gs://tomoda-db-backups-development-485000/<env>/
    googleCredentials:
      gkeEnvironment: true
  retentionPolicy: "30d"

gkeEnvironment: true tells Barman to use the pod's ambient Workload Identity credentials. The Cluster CR's serviceAccountTemplate runs every Postgres pod as a K8s ServiceAccount annotated to impersonate cnpg-backup-sa@development-485000.iam.gserviceaccount.com, which has write access to the backup bucket — see Backup infra.

A ScheduledBackup (manifests/backup.yaml) triggers a full base backup daily at 03:00 UTC via the same barmanObjectStore. Retention 30 days.

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 pointing at the cluster; CNPG runs it immediately and uploads to the configured destinationPath.
  • Scaling storage — increase spec.storage.size on the Cluster CR. CNPG resizes the PVC; the StorageClass (standard-rwo) supports online expansion on GKE.
  • Version upgrade — bump imageName (e.g. 17-3.5 → 18-3.5). 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; alerts on replication lag, WAL archive failure, and disk usage are wired into the platform alerting bundle.