SOP: Postgres Restore (CNPG + MinIO)

sop-postgres-restore Sop

sop active

Project

SOP: Postgres Restore from CNPG Backup

Replaces: sop-litestream-restore (archived)

Last tested: 2026-04-21 — full drill + PITR PASS (see validation-postgres-restore-2026-04-21)

Expected timing: cluster-apply → first-query-success is ~55 seconds for current paledocs+twitch2kwager-scale data (~200MB base backup) on k3s+local-path storage. PITR adds ~5s for WAL replay. Scales roughly linearly with base-backup size.

Prerequisites

CNPG operator running (namespace cnpg-system, deployment cnpg-cloudnative-pg)
MinIO accessible at http://minio.minio.svc.cluster.local:9000
cnpg-s3-creds secret in postgres namespace (must be copied to the restore namespace — see Step 1.5)
WAL + base backups in postgres-wal MinIO bucket
Image tag for restore cluster: ghcr.io/cloudnative-pg/postgresql:17 — NOT :17.4-1. See Gotcha #1.
NetworkPolicy whitelist: default-deny-ingress in minio ns only allows 7 namespaces (tailscale, postgres, woodpecker, monitoring, tofu-state, pal-e-mail, westside-contracts). If restoring into a scratch ns (e.g. postgres-restore-test), patch the netpol before the cluster bootstraps; revert after cleanup. One-liner at bottom of this SOP.

Step 0: Pre-flight Checks

Gate the drill on all five. Any failure → abort and file a separate ticket.

Step 1: Verify Backups Exist

Step 1.5: Create Scratch NS, Copy Creds, Patch NetPol

Step 2: Create Recovery Cluster

WARNING: Never restore into the same namespace as the production cluster. Always use a dedicated scratch ns (e.g. postgres-restore-test).

Full restore (latest available point — replays ALL archived WAL):

Point-in-time restore (specific timestamp):

Add under bootstrap.recovery:

WARNING: Target time must not exceed the last archived WAL transaction. If it does, Postgres fails with recovery ended before configured recovery target was reached. Verify target is within mc ls local/postgres-wal/pal-e-postgres/wals/ range first. When in doubt, omit recoveryTarget for full restore.

Step 3: Wait for Recovery

Expected duration: For paledocs+twitch2kwager-scale data (~200MB base backup), ready-to-serve takes 45-60 seconds on current k3s+local-path storage. PITR adds ~5 seconds for WAL replay. Larger DBs scale roughly linearly with base-backup size.

Step 4: Verification Queries

Don't just check COUNT(*) FROM notes — verify against a captured baseline per-DB, per-table.

Step 5a: Cleanup (Dry-Run Drill)

Step 5b: Swap (Real DR — Replacing Production)

Scale down the app: kubectl scale deploy -n pal-e-docs pal-e-docs --replicas=0
Delete the old cluster: kubectl delete cluster -n postgres pal-e-postgres
Rename the recovery cluster (or update the app's connection string)
Scale up the app

Note: Renaming a CNPG Cluster is not directly supported. Easier to update the app's DATABASE_URL to point to the new cluster's service name, or recreate with the original name.

Swap hardening (separate ticket): App PDBs, ArgoCD sync lock, service-name collisions — these footguns need a dedicated runbook. Filed as pal-e-platform improvement ticket.

Gotchas

#1 imageName for RESTORE must be :17, not the prod tag. Prod cluster currently uses ghcr.io/cloudnative-pg/postgresql:17.4-1. RESTORE cluster MUST use :17 (plain major tag). CNPG 1.28.1's barman-cloud-restore invocation passes 4 positional args; the barman-cloud 3.13.0 bundled in :17.4-1 expects 3 and errors with unrecognized arguments: /var/lib/postgresql/data/pgdata. The :17 tag ships barman-cloud 3.17.0 (Pg 17.9) which works. Verified 2026-04-21 drill. When upgrading prod, test restore-compat in scratch ns FIRST.
#2 serverName is required in externalClusters. Must be pal-e-postgres (matches backup path in MinIO), regardless of the cluster's metadata.name.
#3 pg_switch_wal() — DANGER in dry-run. Useful during real DR to flush the current WAL segment to S3 before recovery. DO NOT run against prod during dry-run drills — it mutates prod state. Dry-run drills use the latest archived WAL as-is.
#4 Barman Cloud Plugin migration pending. Native barman-cloud is deprecated in CNPG 1.28, removed in 1.29. See phase-postgres-4a-barman-plugin-migration. This SOP uses the legacy path; expect to rewrite Step 2 once migration lands.
#5 NetworkPolicy footgun. default-deny-ingress on minio ns only whitelists 7 namespaces. Any new restore ns must be added via kubectl patch before the recovery job starts, or the bootstrap pod fails at barman-cloud-backup-list with a cryptic endpoint-URL connect error.