Review: P1: validate sop-postgres-restore via dry-run drill (blocks #297)

Verdict: NEEDS_REFINEMENT

Template Completeness (Spike)

• [x] Type: Spike
• [x] Lineage — ties to #297 hard gate
• [x] Repo — forgejo_admin/pal-e-platform
• [x] Question — "Does sop-postgres-restore actually restore end-to-end?"
• [x] What to Explore — 8 concrete steps
• [x] Success Criteria — 6 bullets
• [x] Time-box — 4 hours, with fail-stop clause
• [x] Environment — cluster, backup source, backup list, WAL, retention, SOP slug
• [x] Acceptance Criteria — 7 bullets (extends template, acceptable for spike-with-deliverable)
• [x] Out of Scope — 4 exclusions, each with a follow-up note
• [x] Related — 5 links including #297 and feedback notes

Traceability

• [x] story:superuser-recover — label present, story verified in project-pal-e-platform user-stories section (row 3: "I can recover from failures using documented SOPs. Every failure mode has a runbook." Metric: all failure modes covered by recovery SOPs.)
• [x] arch:cnpg — label present, CNPG is documented in project-pal-e-platform Architecture → Deployment mermaid diagram (cnpg-system namespace, pal-e-postgres cluster).
• [ ] arch note MISSING — no dedicated arch-cnpg note exists in pal-e-docs (searched). The project-page diagram covers CNPG at a deployment level but there's no standalone architecture note that the arch:cnpg label points at. [SCOPE] Create arch-cnpg architecture note (or document the convention that arch:X labels may resolve to a project-page section instead of a standalone note when the component is covered there). This is not a blocker for this specific ticket but is accumulating debt across CNPG-labeled work.
• [x] Forgejo issue — forgejo_admin/pal-e-platform#298, state: open
• [x] Blocks #297 — confirmed in #297 AC: "If any step would cause restart, pause and confirm backup restorability via sop-postgres-restore dry-run BEFORE proceeding. This is a hard gate."

File Targets

• [x] sop-postgres-restore — note exists, has Prerequisites, Steps 1-5, Gotchas, See also sections. Pg 17 image version gotcha documented. Last updated 2026-03-08.

Repo Placement

Dependencies

• Blocks: #297 (P0 tf-state drift, board item 1064, currently in next_up column). #297's hard gate requires this drill to PASS. Correctly documented in both directions.
• Depends on: live CNPG backup pipeline (verified healthy — 8 daily backups 2026-04-14 through 2026-04-21, WAL archive active). Depends on MinIO availability and cnpg-s3-creds secret. These runtime prereqs are listed in the SOP but not verified in the ticket's pre-flight. See [BODY] recommendation 1.
• Sibling out-of-scope tickets filed correctly: automated restore testing, off-cluster backup destination, HA replicas.

Acceptance Criteria

• "Sample row queries against restored DBs match prod" — does not specify WHO runs the prod query and HOW. Per feedback_never_write_prod_db.md, read-only prod queries are allowed, but the safer, more auditable pattern is: Lucas runs SELECT COUNT(*) + timestamp-of-latest-row on prod once, captures to chat or the validation note as a checksum, then the agent runs the same queries on the RESTORED cluster only. This eliminates any need for the agent to ever connect a psql session to the prod pal-e-postgres-rw service. See [BODY] recommendation 3.
• Point-in-time recovery AC — says "restore to 5 minutes ago using WAL." Target timestamp selection needs a rule: must be AT LEAST (last full backup time) AND AT MOST (now - 1 minute), otherwise the SOP's documented failure mode applies ("recovery ended before configured recovery target was reached" if WAL not yet archived past target). Recommend: capture a known-good write into a scratch table on prod, note timestamp, target PITR to that timestamp + 1s. But that violates read-only prod. Alternate: pick a timestamp 30 min in the past and verify the COUNT matches what prod was at that time (which requires pre-flight baseline capture anyway). See [BODY] recommendation 4.

Blast Radius

• [HIGH] Scratch namespace vs SOP default. The SOP's Step 2 YAML uses name: pal-e-postgres-restore in namespace: postgres — the SAME namespace as prod. The ticket correctly specifies scratch ns postgres-restore-test but does NOT show the adjusted YAML, does NOT document that cnpg-s3-creds secret (SOP prerequisite #3) lives in postgres ns and must be copied to the scratch ns, and does NOT document that the serverName in externalClusters must remain pal-e-postgres even in the scratch cluster (per SOP Gotcha #2 — matches MinIO path). Risk: an agent that copy-pastes SOP YAML verbatim restores into prod namespace alongside the live cluster. Or adjusts ns but forgets the secret, causing a 30-min head-scratching debug before the drill has even started. See [BODY] recommendation 2 — add explicit scratch-ns YAML delta + secret copy command.
• [MED] Pg version pre-flight. SOP Gotcha #1 is the #1 known failure mode: "imageName must match source Postgres version. Source is Pg 17.4. Use :17 tag. Default CNPG image is Pg 18 and CANNOT read Pg 17 data." Ticket says "verify in scratch cluster matches prod" but does not require this check as a pre-flight gate BEFORE starting the 4h timer. Recommend adding to Environment: "Pre-flight: kubectl get cluster -n postgres pal-e-postgres -o jsonpath='{.spec.imageName}' → use that exact value in scratch cluster." See [BODY] recommendation 1.
• [MED] Barman Cloud Plugin deprecation. SOP Gotcha #4 flags that native barman-cloud is deprecated in CNPG 1.28 and removed in 1.29 (phase-postgres-4a-barman-plugin-migration). If scratch ns spins up against current CNPG operator version and the operator has already crossed the deprecation threshold, the SOP's bootstrap.recovery.source syntax may behave differently than on prod's 49d-old cluster. Worth a pre-flight: kubectl get deployment -n cnpg-system -o jsonpath='{.items[*].spec.template.spec.containers[*].image}' and note version in validation note.
• [LOW] Force WAL switch before restore. SOP Gotcha #3 says "Force WAL switch before restore if you need the very latest data: kubectl exec -n postgres pal-e-postgres-1 -c postgres -- psql -U postgres -c 'SELECT pg_switch_wal();'" — this is a WRITE to prod (function call, technically not DML, but triggers a WAL segment archive). Per feedback_never_write_prod_db.md, this needs Lucas approval. For a validation drill, it's not required (we can restore to last archived WAL segment, not "now"). Recommend: ticket explicitly SKIP the pg_switch_wal() step to keep the drill 100% read-only on prod. See [BODY] recommendation 5.

Decomposition Assessment

Recommendation

• [BODY] 1. Add pre-flight checklist to Environment section. Before starting the 4h timer, verify: (a) cnpg-s3-creds secret readable in postgres ns, (b) Pg image tag on prod cluster (kubectl get cluster -n postgres pal-e-postgres -o jsonpath='{.spec.imageName}') — this exact value must be used in scratch cluster, (c) CNPG operator version (kubectl get deployment -n cnpg-system cnpg-controller-manager -o jsonpath='{.spec.template.spec.containers[*].image}'), (d) scratch namespace postgres-restore-test does not already exist, (e) at least one daily backup listed in kubectl get backup -n postgres has phase: completed.
• [BODY] 2. Add scratch-namespace YAML delta block. Explicit guidance: namespace becomes postgres-restore-test, cluster name becomes pal-e-postgres-restore-test, externalClusters.serverName MUST remain pal-e-postgres (matches MinIO path per SOP Gotcha #2), and the cnpg-s3-creds secret must be copied into the scratch ns first: kubectl get secret cnpg-s3-creds -n postgres -o yaml | sed 's/namespace: postgres/namespace: postgres-restore-test/' | kubectl apply -f -. This single block prevents the most likely failure mode (accidental prod-namespace restore) AND the most likely stall mode (missing secret).
• [BODY] 3. Specify sample-row verification mechanism. Lucas captures a baseline BEFORE the drill: SELECT COUNT(*), MAX(updated_at) FROM notes; on paledocs, equivalent on twitch2kwager and basketball_test. Baseline pasted into validation note. Agent runs same queries on RESTORED cluster only. Agent NEVER connects psql to prod pal-e-postgres-rw. Comparison = baseline vs restored.
• [BODY] 4. Specify PITR target timestamp rule. "Target a timestamp 30 minutes in the past ($(date -u -d '30 minutes ago' +%FT%TZ)). This is safely within the last-archived-WAL window and safely after the last base backup. Verify PITR success by confirming COUNT(*) at that timestamp matches Lucas's 30-min-old baseline if captured, or by confirming the restored cluster reaches ready state with the recoveryTarget applied."
• [BODY] 5. Explicit "no prod writes" clause. Add to Environment: "This drill is 100% read-only on prod. Do NOT run pg_switch_wal() (SOP Gotcha #3) — the drill uses last archived WAL, not 'now'. No DDL, no DML, no function calls, no CRD mutations on prod pal-e-postgres cluster. All mutation is in postgres-restore-test ns."
• [SCOPE] Downstream: File a follow-up ticket to create arch-cnpg architecture note (or codify convention that arch:X labels can resolve to project-page sections). Not blocking this ticket.