Skip to content

Postgres Operations

Tomoda uses CloudNativePG (CNPG), a Kubernetes operator that manages PostgreSQL clusters, including provisioning, WAL archiving, base backups, PITR, monitoring, and failover.

Cluster topology

CNPG Operator (namespace: cnpg-system)
  |
  +-- postgres-dev  (namespace: data, image: tomoda-postgres)
  |     +-- postgres-dev-1     (primary, 3Gi local-path PVC, no backups)
  |
  +-- postgres-prod (namespace: data, image: tomoda-postgres)
        +-- postgres-prod-1    (primary, 150Gi local-path PVC)
        +-- WAL + ScheduledBackup --> r2:tomoda-db-backups/prod/

Dev and prod are separate clusters, same server

The postgres-dev and postgres-prod CNPG Cluster resources are independent: different PVCs, credentials and backup settings. Both live in the data namespace on the same k3s node and share its RAID1 NVMe, but destroying one does not affect the other.

Property postgres-dev postgres-prod
Image ghcr.io/tomoda-labs/tomoda-postgres:17-3.6-pgroonga4.0.1-pgvector0.8.0-h3-4.5.0 Same
Instances 1 1 (2 once a second server joins)
Storage 3Gi 150Gi
Memory (request / limit) 256Mi / 512Mi 8Gi / 16Gi
Database tomoda_dev tomoda_prod
Owner tomoda_dev_user tomoda_prod_user
Extensions PostGIS, PostGIS Topology, PGroonga, pgvector, unaccent, pg_trgm Same
shared_buffers / effective_cache_size 128MB / 384MB 4GB / 12GB
Base backup None Daily 03:00 UTC → R2
WAL archiving None Continuous → R2 (gzip)
Retention None 30d
Monitoring PodMonitor (Prometheus) Same

Service DNS

CNPG creates three services per cluster automatically:

Suffix Purpose
-rw Read-write (primary only)
-ro Read-only (replicas only; empty until you scale to 2+)
-r Any instance

The backend uses a stable ExternalName alias for backward compatibility:

Alias Resolves to Used by
postgres-postgresql.data.svc.cluster.local postgres-dev-rw.data.svc.cluster.local Backend (dev)
postgres-prod-postgresql.data.svc.cluster.local postgres-prod-rw.data.svc.cluster.local Backend (prod)

Configuration source files

File Purpose
k8s/envs/dev/postgres/manifests/cluster.yaml Dev Cluster CR + ExternalSecret + ExternalName
k8s/envs/prod/postgres/manifests/cluster.yaml Prod Cluster CR + ExternalSecret + ExternalName
k8s/envs/prod/postgres/manifests/backup.yaml ScheduledBackup CR
k8s/envs/platform/cnpg/application.yaml Argo CD app installing the CNPG operator via Helm
infrastructure/cloudflare/r2.tf tomoda-db-backups bucket + lifecycle

Tuning PostgreSQL parameters

Edit spec.postgresql.parameters in the cluster CR. Example from prod:

postgresql:
  parameters:
    max_connections: "200"
    shared_buffers: "4GB"
    effective_cache_size: "12GB"
    work_mem: "32MB"
    random_page_cost: "1.1"              # local NVMe
    log_min_duration_statement: "1000"   # log queries slower than 1s

Commit, push, Argo CD syncs. CNPG applies the change with an automatic rolling restart for params that require one.

Credentials

K8s Secrets:

  • postgres-dev-credentials in data
  • postgres-prod-credentials in data

Both are populated by an ExternalSecret that pulls tomoda-db-password from GCP Secret Manager (see Secrets Management). The same secret is consumed by the backend as DB_PASSWORD, so they always match.

Backups

How it works

CNPG backs up through the Barman Cloud plugin, which runs Barman's barman-cloud tools in a plugin-barman-cloud sidecar of each instance pod (config: the postgres-prod-backups ObjectStore, see Postgres (CloudNativePG)). Two mechanisms:

  1. WAL archiving (continuous): every WAL segment is shipped to R2 as it is written, with keys from r2-backup-credentials (ExternalSecret over tomoda-r2-cluster-credentials). Enables PITR to any second.
  2. Scheduled base backups (daily): physical backup at 03:00 UTC. Combined with WAL archives, this is the starting point for any restore.

Backup storage layout:

r2:tomoda-db-backups/
  prod/postgres-prod/
    base/     # base backups
    wals/     # WAL archive segments

The plugin prunes after 30 days (retentionPolicy on the ObjectStore); an R2 lifecycle rule deletes anything older than 45 days as a backstop.

Verify backups

# Backup CRs
kubectl get backup -n data

# Scheduled backups
kubectl get scheduledbackup -n data

# Last successful backup
kubectl get cluster postgres-prod -n data \
  -o jsonpath='{.status.lastSuccessfulBackup}'

# Per-cluster backup history
kubectl get backup -n data -l cnpg.io/cluster=postgres-prod

# Continuous archiving status
kubectl get cluster postgres-prod -n data \
  -o jsonpath='{.status.conditions}' | jq

# Recovery window the plugin sees (first recoverability point, last backup)
kubectl get objectstore postgres-prod-backups -n data -o jsonpath='{.status.serverRecoveryWindow}' | jq

# R2 contents (rclone remote "r2")
rclone lsd r2:tomoda-db-backups/prod/postgres-prod/base/
rclone ls r2:tomoda-db-backups/prod/postgres-prod/wals/ | head

Trigger a manual backup

kubectl apply -f - <<EOF
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: postgres-prod-manual-$(date +%Y%m%d%H%M)
  namespace: data
spec:
  method: plugin
  pluginConfiguration:
    name: barman-cloud.cloudnative-pg.io
  cluster:
    name: postgres-prod
EOF

kubectl get backup -n data -w

Restores

For full-cluster PITR and latest-state restores, use the wrapper script (Disaster Recovery).

Restore a single table (logical)

When you need to recover one table without rolling the whole DB back, restore a throwaway copy with the drill (prod keeps serving):

# 1. Restore to the timestamp into restore-drill and keep it up
task dr:drill -- --target-time "2026-04-27T09:55:00Z" --keep

# 2. Dump just the table from the drill cluster
kubectl exec postgres-prod-drill-1 -n restore-drill -c postgres -- \
  pg_dump -U postgres -d tomoda_prod \
  --table=users --data-only --column-inserts \
  > users-recovered.sql

# 3. Apply to the live cluster
cat users-recovered.sql | kubectl exec -i postgres-prod-1 -n data -- \
  psql -U tomoda_prod_user -d tomoda_prod

# 4. Delete the drill namespace
kubectl delete namespace restore-drill

Logical dumps over kubectl exec

kubectl exec buffers stdout. For large tables, prefer port-forwarding and running pg_dump from your laptop, or use a temporary debug pod with rclone to R2.

pg_dump / pg_restore between environments

For seeding dev from a redacted prod snapshot:

kubectl exec -it postgres-prod-1 -n data -- \
  pg_dump -U tomoda_prod_user -d tomoda_prod | gzip > prod-dump.sql.gz

# Redact before loading into dev. Then:
gunzip -c prod-dump.sql.gz | kubectl exec -i postgres-dev-1 -n data -- \
  psql -U tomoda_dev_user -d tomoda_dev

Scaling

Playbooks live in Scaling: CPU and memory, storage, read replica (instances: 2 plus DB_READ_HOST) and connection pooler (CNPG Pooler plus DB_POOLER=true).

Version upgrades

CNPG handles minor PostgreSQL upgrades (e.g., 17.1 -> 17.2) via image bump and rolling restart:

imageName: ghcr.io/cloudnative-pg/postgis:17.2-3.5

Commit, push. For major upgrades (17 -> 18), use CNPG's pg_upgrade workflow, which requires a planned maintenance window and the official CNPG upgrade docs. Do not attempt in-place major upgrades without testing in dev first.

Monitoring

CNPG exports Prometheus metrics via the built-in PodMonitor. Useful metrics in Grafana:

Metric Watch for
cnpg_collector_up Should be 1
cnpg_backends_total against cnpg_pg_settings_setting{name="max_connections"} Connection saturation
cnpg_backends_max_tx_duration_seconds, cnpg_backends_waiting_total Long transactions, lock waits
cnpg_pg_stat_archiver_seconds_since_last_archival, barman_cloud_cloudnative_pg_io_last_available_backup_timestamp WAL archiving and base backups
cnpg_pg_database_size_bytes, cnpg_collector_pg_wal Storage growth trend
cnpg_pg_database_xid_age Wraparound risk

Only cnpg_collector_up carries a cluster label; select the env with job="data/postgres-<env>" or pod=~"postgres-<env>-.*". Alerts: Data alert runbook.

The Postgres Grafana dashboard charts these per env; see Dashboards.

Debugging

# Cluster overview
kubectl get cluster -n data

# Detailed status
kubectl describe cluster postgres-prod -n data

# Status conditions
kubectl get cluster postgres-prod -n data \
  -o jsonpath='{.status.conditions}' | jq

# Current primary
kubectl get cluster postgres-prod -n data \
  -o jsonpath='{.status.currentPrimary}'

# Postgres pod logs
kubectl logs postgres-prod-1 -n data

# CNPG operator logs
kubectl logs -n cnpg-system -l app.kubernetes.io/name=cloudnative-pg

# Barman Cloud plugin: controller, and the backup sidecar in the instance pod
kubectl logs -n cnpg-system deploy/plugin-barman-cloud
kubectl logs postgres-prod-1 -n data -c plugin-barman-cloud

Cluster stuck in "Setting up primary"

kubectl logs -n cnpg-system -l app.kubernetes.io/name=cloudnative-pg --tail=50
kubectl describe pod postgres-prod-1 -n data

Most common cause: image pull failure (GHCR credentials in /etc/rancher/k3s/registries.yaml) or a full host disk.

Backup failing

kubectl get backup -n data -o wide
kubectl get externalsecret r2-backup-credentials -n data   # SecretSynced?
kubectl logs postgres-prod-1 -n data -c plugin-barman-cloud --tail=50
kubectl get pods -n cnpg-system -l app.kubernetes.io/name=plugin-barman-cloud   # plugin up?
# 403 / InvalidAccessKeyId: the R2 token in tomoda-r2-cluster-credentials is wrong or lacks write on tomoda-db-backups

PVC full

kubectl exec -it postgres-prod-1 -n data -- df -h /var/lib/postgresql/data
# local-path PVCs are directories on the host disk: the size field isn't enforced,
# so "full" means the server's NVMe is full. Free space (old images, Photon
# tarballs) or move workloads to a second server.