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-credentialsindatapostgres-prod-credentialsindata
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:
- WAL archiving (continuous): every WAL segment is shipped to R2 as it is written, with keys from
r2-backup-credentials(ExternalSecret overtomoda-r2-cluster-credentials). Enables PITR to any second. - 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.