Postgres on CloudNativePG

CloudNativePG (CNPG) is a Postgres operator that handles HA replicas, PITR, and Barman-managed S3 backups inside Kubernetes. The OrbitalReg chart no longer ships its own Postgres StatefulSet for production — point at a CNPG cluster instead.

This page is the externally-rendered companion to docs/operations/postgres-migration-cnpg.md.

Why CNPG

• Continuous WAL archiving — RPO of seconds, not the daily-snapshot RPO of a stand-alone install
• Point-in-time recovery — restore to any second within the retention window
• In-cluster failover — primary loss promotes a replica in under 30 seconds, no operator action
• Backup verification — the Backup verification job restores into an ephemeral CNPG cluster, which is only easy because CNPG's bootstrap-from-backup flow is first-class

Install CNPG

The CloudNativePG operator itself is installed once per cluster:

kubectl apply -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/releases/cnpg-1.24.0.yaml

Provision an OrbitalReg cluster

The OrbitalReg chart's values.postgres.cnpg.enabled=true mode templates a Cluster resource:

postgres:
  cnpg:
    enabled: true
    instances: 3
    storage:
      size: 100Gi
      storageClass: fast
    backup:
      enabled: true
      s3:
        endpoint: s3.example.com
        bucket: orbitalreg-postgres-backup
        existingSecret: orbitalreg-cnpg-s3
      retentionPolicy: "30d"
      schedule: "0 2 * * *"

The chart also creates a pg-superuser Secret and a read-write Service that OrbitalReg's API connects to via DATABASE_URL=postgres://…@<release>-postgres-rw:5432/orbitalreg.

Migrate from stand-alone Postgres

The full migration playbook lives at docs/operations/postgres-migration-cnpg.md. The shape:

1. Drain writes — set the API to read-only mode under Admin → Maintenance (no new uploads, but downloads keep working).
2. Take a pg_dump of the existing database.
3. Provision the CNPG cluster with the chart values above.
4. Restore the dump into the new cluster's database.
5. Update DATABASE_URL to point at the new RW Service.
6. Roll out and re-enable writes.

End-to-end downtime for a 50-GB database is typically 10–20 minutes on a warm cluster; the dump-and-restore is the long pole.

Day-2 operations

Task	Command
Trigger an on-demand backup	kubectl cnpg backup <cluster> -n <ns>
List backups	kubectl get backup -n <ns>
Promote a replica	kubectl cnpg promote <cluster> <pod>
Inspect WAL lag	kubectl cnpg status <cluster>
Run the verify-restore drill	./scripts/orbital-restore.sh --scenario verify --target-time "now"

Capacity sizing

A reasonable starting shape:

Workload size	CNPG instances	CPU per pod	Mem per pod	Storage
Small (≤ 10 GB)	2	500m	1 Gi	50 Gi
Medium (≤ 100 GB)	3	1	2 Gi	200 Gi
Large (≤ 1 TB)	3	2	4 Gi	2 Ti

OrbitalReg's hottest tables — artifacts, scan_findings, artifact_pulls — are bounded by retention; the retention runner keeps the row counts stable rather than growing without bound.

Autovacuum tuning

audit_events and artifact_pulls are OrbitalReg's two large append-only tables, monthly RANGE-partitioned. Rows only leave them via the retention sweeper, in bursts, so a partition's dead-tuple count can jump well past what the cluster-wide autovacuum default considers "a lot" — Postgres only triggers autovacuum once dead tuples exceed autovacuum_vacuum_scale_factor (default 20%) of the table's live rows, which on a large single-month partition is a lot of bloat to carry before cleanup even starts.

The API tightens both scale factors per-partition when it creates or extends the monthly runway (internal/audit/partitions.go, ensureMonthlyPartition):

ALTER TABLE audit_events_2026_08 SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_insert_scale_factor = 0.02
);

This runs unconditionally on every partition in the runway (current month + 3 months ahead) each time EnsureFuturePartitions / EnsureFuturePullPartitions executes at boot, so partitions created before this tuning shipped are retrofitted automatically on the next rollout — no manual backfill needed. Watch postgres_table_bloat_ratio{table_family="audit_events"} / {table_family="artifact_pulls"} (the bloat dashboard) after a rollout to confirm the ratio trends down instead of climbing between retention sweeps.

Storage alerts

The bundled PrometheusRule ships a dedicated orbitalreg.postgres-storage group: OrbitalRegPostgresStorageHigh (warning, PVC over 80% used for 15m) and OrbitalRegPostgresStorageCritical (critical, over 92% for 5m) on the CNPG data volume, plus OrbitalRegPostgresBloatHigh (warning, postgres_table_bloat_ratio over 0.3 for 30m on either partitioned family) so a bloat run that's outpacing the autovacuum tuning above pages before the PVC itself fills. This is separate from the chart's generic highDiskUsage rule (charts/orbitalreg/templates/prometheusrule.yaml), which covers every OrbitalReg-owned PVC at info severity — the Postgres-specific rules here page at warning/critical because a full CNPG volume is a cluster-wide outage, not a single degraded workload.

Related docs

• Disaster recovery — restore runbook
• Backup verification — weekly automated restore-into-ephemeral-cluster
• docs/operations/postgres-migration-cnpg.md — full migration recipe