pal-e-docs

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

review-1065-2026-04-21-r2 Review

review approved
Project

Verdict: APPROVED

Round 2 scope: verify the five [BODY] edits from review-1065-2026-04-21 closed their respective gaps, and confirm no regressions were introduced. All five gaps closed cleanly. No regressions. Ticket is ready to advance todo → next_up.

Round 1 Gap Closure (primary focus)

# Round 1 Gap Landed In Status
1 Pre-flight checklist in Environment New <strong>Pre-flight</strong> subsection under Environment: 5 gated checks covering <code>cnpg-s3-creds</code> readability, prod Pg image tag capture, CNPG operator version capture, scratch-ns absence, ≥1 completed backup. Explicitly gates the 4h timer with fail-→file-ticket-and-abort clause. [x] CLOSED
2 Scratch-namespace YAML delta block New <strong>Scratch Namespace Setup</strong> section. Explicit deltas for <code>metadata.namespace</code>, <code>metadata.name</code>, <code>spec.imageName</code> (from pre-flight #2), and critical guardrail that <code>externalClusters[0].serverName</code> must remain <code>pal-e-postgres</code> per SOP Gotcha #2. <code>cnpg-s3-creds</code> secret copy command included verbatim as a one-liner sed pipeline. [x] CLOSED
3 Sample-row verification mechanism (baseline-first, agent reads restored only) New <strong>Baseline Capture</strong> section. Lucas runs <code>SELECT COUNT(*), MAX(updated_at)</code> on prod ONCE before dispatch, output pasted into validation note. Agent runs identical queries against RESTORED cluster only. Explicit prohibition: "No psql connections from the agent to prod." Success criterion and AC both updated to tie to Lucas's baseline. [x] CLOSED
4 PITR target timestamp rule (30 min, not 5 min) Step 7 of <strong>What to Explore</strong>: "restore to a timestamp 30 minutes in the past" with <code>$(date -u -d '30 minutes ago' +%FT%TZ)</code>, inline rationale (safe within last-archived-WAL window AND safe after most recent base backup), and explicit prohibition on 5-minute target with the SOP failure-mode string quoted verbatim ("recovery ended before configured recovery target was reached"). Success Criteria and AC both propagated to 30-minute-ago timestamp. [x] CLOSED
5 Explicit "no prod writes" clause New <strong>No Prod Writes (hard constraint)</strong> subsection under Environment enumerating four forbidden actions: <code>pg_switch_wal()</code> call (SOP Gotcha #3), any DDL/DML/function call on prod, CRD mutation in <code>postgres</code> ns, psql session to <code>pal-e-postgres-rw</code>. Environment line 1 reads "100% READ-ONLY on prod." AC adds "Zero writes to prod <code>pal-e-postgres</code> cluster across the entire drill." [x] CLOSED

Template Completeness (Spike)

  • [x] Type — Spike
  • [x] Lineage — ties to #297 hard gate
  • [x] Repo — forgejo_admin/pal-e-platform
  • [x] Question — unchanged, still sharp
  • [x] What to Explore — 8 steps; step 7 updated to 30-min PITR target
  • [x] Success Criteria — 6 bullets; sample-row and PITR bullets both updated to match new mechanism
  • [x] Time-box — 4h, now gated on pre-flight pass
  • [x] Environment — expanded with Pre-flight, No Prod Writes, Scratch Namespace Setup, Baseline Capture subsections
  • [x] Acceptance Criteria — 10 bullets (was 7); new bullets cover pre-flight pass, baseline captured before drill, PITR-30-min, zero-writes audit, quarterly re-run ticket
  • [x] Out of Scope — 5 exclusions; off-cluster backup now points at plan-pal-e-backup Phase 2 with #299 closure note; arch-cnpg deferral added
  • [x] Related — 6 links; adds plan-pal-e-backup Phase 2 and feedback_never_write_prod_db.md
  • [x] Refinement footer documenting round-1 review pointer and the 5 [BODY] edits

Traceability

  • [x] story:superuser-recover — unchanged. Verified in round 1 (row 3 of project-pal-e-platform user-stories).
  • [x] arch:cnpg — unchanged. Round-1 [SCOPE] recommendation to create a standalone arch-cnpg note is now explicitly deferred in the issue's Out of Scope section as a downstream ticket. Acceptable.
  • [x] Forgejo issue — forgejo_admin/pal-e-platform#298, state: open, body refined 2026-04-21.
  • [x] Blocks #297 — relationship preserved.
  • [x] Companion #299 — verified closed via Forgejo API. Scope routed to plan-pal-e-backup Phase 2 (verified: phase anchor phase-2-database-backups-not-started exists, phase title "Database Backups"). Correct canonical landing spot.

File Targets

Spike tickets explore rather than edit files. The artifacts referenced are pal-e-docs notes, not repo files:
  • [x] sop-postgres-restore — unchanged since round 1; still the procedure being validated.
  • [x] plan-pal-e-backup Phase 2 — newly referenced in Out of Scope; verified exists.
  • [x] validation-postgres-restore-2026-04-XX — deliverable to be created during the drill. Naming convention matches other validation notes.
No broken references introduced in round 2.

Repo Placement

OK. No change from round 1. Issue correctly filed on forgejo_admin/pal-e-platform. Refinements did not introduce any cross-repo dependencies.

Dependencies

  • Blocks: #297 (P0 tf-state drift, board item 1064, in next_up). Unchanged.
  • Runtime prereqs: now encoded in the Pre-flight subsection — live CNPG backup pipeline (≥1 phase: completed), MinIO availability (implicit via backup listing), cnpg-s3-creds secret, prod Pg image tag, CNPG operator version, scratch ns absence. Round-1 gap (runtime prereqs were listed in SOP but not enforced in ticket) is closed.
  • Baseline capture prereq: Lucas runs read-only queries on prod once, pastes to validation note BEFORE agent dispatch. New dependency, explicitly sequenced. No ambiguity.
  • Closed sibling: #299 (off-cluster backup destination) — closed 2026-04-21, work routed to plan-pal-e-backup Phase 2. Removes one dependency-direction question that lingered in round 1.

Acceptance Criteria

Round 1 flagged two verification-mechanism gaps (sample-row + PITR). Both fully closed:
  • [x] Sample-row verification now specifies Lucas-captures-baseline-first + agent-reads-restored-only. Testable: compare restored COUNT/MAX to pasted baseline.
  • [x] PITR target timestamp now specifies the 30-minute rule with shell command and safe-window rationale. Testable: restored cluster reaches ready state with recoveryTarget applied.
  • [x] Pre-flight pass is an AC line item, so gate enforcement is observable.
  • [x] Zero-writes AC ("confirmed via absence of psql sessions in agent logs") is testable via the agent's own transcript.
  • [x] Cleanup AC ("scratch namespace cleaned up, no orphaned PVCs") testable via kubectl get pvc -A.
All 10 ACs are agent-verifiable.

Blast Radius

Round 1's largest concern was blast radius (scratch-ns footgun, Pg version pre-flight, pg_switch_wal on prod, Barman deprecation). Each concern now has an explicit mitigation in the body:
  • [x] Scratch-ns footgun (HIGH): mitigated by the Scratch Namespace Setup section (explicit ns, name, imageName, serverName deltas plus secret-copy command).
  • [x] Pg version pre-flight (MED): mitigated by Pre-flight #2 capturing exact imageName and making it a gate.
  • [x] Barman deprecation awareness (MED): mitigated by Pre-flight #3 capturing CNPG operator version into the validation note.
  • [x] pg_switch_wal on prod (LOW): mitigated by the No Prod Writes subsection explicitly forbidding the call.
No new blast-radius risks introduced by the refinements. Drill remains scoped to a scratch namespace; prod surface is read-only.

Decomposition Assessment

No decomposition needed. Round-2 body additions clarify rather than expand scope. Pre-flight + baseline capture happen before the 4h timer; scratch-ns setup + restore + PITR + teardown fit the original window. If anything, the refinements REDUCE execution risk (fewer stall modes on missing secrets, wrong image tag, wrong namespace), making the 4h budget more realistic than it was in round 1.

Regression Check

  • [x] All round-1 strengths preserved (Type, Lineage, Repo, Question, Time-box, SOP reference, #297 blocking gate).
  • [x] No content loss. Additions only.
  • [x] AC coherent with new body mechanics (baseline, PITR-30, zero-writes all reflected).
  • [x] Out of Scope correctly updated for #299 closure + arch-cnpg deferral.
  • [x] Refinement footer provides audit trail.
  • [x] No scope creep from #299 or plan-pal-e-backup Phase 2 bleeding in.
  • [x] Labels unchanged (type:spike, scope:unplanned, severity:p1, story:superuser-recover, arch:cnpg) — still apposite.

Nits (non-blocking)

  • Environment says "Postgres version: prod runs Pg 17.x" — round-1 review referenced SOP Gotcha #1 specifying Pg 17.4. Not a regression because Pre-flight #2 captures the exact imageName at drill time, which is the right mechanism. The "17.x" phrasing is appropriately loose.
  • AC bullet "Calendar reminder to re-run drill quarterly (file as separate ticket if no recurring schedule mechanism exists)" is good defensive scoping but could graduate to a dedicated follow-up during /update-docs. Noting for Epilogue capture, not refinement.

Recommendation

APPROVED. All five round-1 gaps closed with edits that exactly match the recommendations. No regressions. No new gaps. Ready to advance todo → next_up.
  • No [BODY] edits required.
  • No [LABEL] changes required.
  • [SCOPE] (downstream, not blocking): create arch-cnpg note or codify "arch:X may resolve to a project-page section" convention. Already tracked as an Out-of-Scope follow-up in the issue body. File as its own board item when convenient.
  • No [DECOMPOSE].
  • review-1065-2026-04-21 — round 1 review (NEEDS_REFINEMENT, five [BODY] gaps)
  • forgejo_admin/pal-e-platform#298 — the ticket, body refined 2026-04-21
  • forgejo_admin/pal-e-platform#299 — closed 2026-04-21, off-cluster scope routed to plan-pal-e-backup Phase 2
  • forgejo_admin/pal-e-platform#297 — the blocked P0 this drill clears
  • plan-pal-e-backup — Phase 2 canonical for off-cluster backup destination
  • sop-postgres-restore — the SOP being validated
  • skill-review-ticket — procedure followed
  • template-review — template this note conforms to