ops: preflight now VALIDATES the rollback dump instead of suggesting it (TK-11364)

82fadf193cd65fa064f5db58d18fca3f581d3fe7 · 2026-09-10 08:46:57 -0700 · Steve

Checklist item 2 has always said the dump must be validated with pg_restore
--list, but that was a note a human had to remember at 3am. Nobody ever ran
it, so O3's entire rollback rested on an unverified 12.8GB file.

Preflight now does it automatically, cheaply:
  - pg_restore --list, and assert SourceEvidence/StagedRecord/ValidationEvent/
    InventoryHome are actually present in the TOC
  - read the LAST data block in the archive

The tail read is the one that matters here. A pg_dump killed by a full disk
produces a VALID TOC with a truncated tail, and the TOC sits at the FRONT of a
custom-format archive — so reading it proves nothing about the tail. That is a
live risk on this box, which is at 94% and already skipping other backups (the
BACKUP-SKIPPED flag printed right below). Reading the last block forces a seek
to near EOF and does catch it. Both checks measured ~0.04s on the real dump.

Also prints the full-archive proof command for pre-window use rather than
running ~6 min of CPU on every preflight.

Verified live against today's dump: TOC OK (30 tables), all four critical
tables present, tail read OK. Separately ran the full proof out-of-band —
pg_restore of the entire SourceEvidence data block exited 0 after 2m11s
(13GB read), so today's dump is a genuinely valid rollback.

Read-only against prod; no mutations.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

Files touched

M ops/phase2-drafts/preflight.sh

Diff

commit 82fadf193cd65fa064f5db58d18fca3f581d3fe7
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 08:46:57 2026 -0700

    ops: preflight now VALIDATES the rollback dump instead of suggesting it (TK-11364)
    
    Checklist item 2 has always said the dump must be validated with pg_restore
    --list, but that was a note a human had to remember at 3am. Nobody ever ran
    it, so O3's entire rollback rested on an unverified 12.8GB file.
    
    Preflight now does it automatically, cheaply:
      - pg_restore --list, and assert SourceEvidence/StagedRecord/ValidationEvent/
        InventoryHome are actually present in the TOC
      - read the LAST data block in the archive
    
    The tail read is the one that matters here. A pg_dump killed by a full disk
    produces a VALID TOC with a truncated tail, and the TOC sits at the FRONT of a
    custom-format archive — so reading it proves nothing about the tail. That is a
    live risk on this box, which is at 94% and already skipping other backups (the
    BACKUP-SKIPPED flag printed right below). Reading the last block forces a seek
    to near EOF and does catch it. Both checks measured ~0.04s on the real dump.
    
    Also prints the full-archive proof command for pre-window use rather than
    running ~6 min of CPU on every preflight.
    
    Verified live against today's dump: TOC OK (30 tables), all four critical
    tables present, tail read OK. Separately ran the full proof out-of-band —
    pg_restore of the entire SourceEvidence data block exited 0 after 2m11s
    (13GB read), so today's dump is a genuinely valid rollback.
    
    Read-only against prod; no mutations.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
---
 ops/phase2-drafts/preflight.sh | 29 ++++++++++++++++++++++++++++-
 1 file changed, 28 insertions(+), 1 deletion(-)

diff --git a/ops/phase2-drafts/preflight.sh b/ops/phase2-drafts/preflight.sh
index c4fec0a1..a7c0b319 100644
--- a/ops/phase2-drafts/preflight.sh
+++ b/ops/phase2-drafts/preflight.sh
@@ -71,7 +71,34 @@ if [ -n "${DUMP:-}" ]; then
   AGE_H=$(( ( $(date +%s) - $(stat -c %Y "$DUMP") ) / 3600 ))
   echo "     age: ${AGE_H}h"
   [ "$AGE_H" -gt 26 ] && echo "     >> WARNING: older than a nightly cycle — is the dump job still running?"
-  echo "     >> checklist item 2 also requires VALIDATING it: pg_restore --list \"$DUMP\" >/dev/null"
+  # Checklist item 2 says "validate it" — so validate it here instead of leaving a note
+  # a tired human skips at 3am. Both checks below measured ~0.04s on the real 12.8 GB dump.
+  if pg_restore --list "$DUMP" > /tmp/_pf_toc.txt 2>/tmp/_pf_toc.err; then
+    echo "     TOC: OK ($(grep -c 'TABLE DATA' /tmp/_pf_toc.txt) tables)"
+    for t in SourceEvidence StagedRecord ValidationEvent InventoryHome; do
+      grep -q "TABLE DATA public $t " /tmp/_pf_toc.txt \
+        && echo "     TOC contains $t: yes" \
+        || echo "     >> MISSING FROM DUMP: $t — this dump is NOT a valid rollback."
+    done
+    # A pg_dump killed by a full disk yields a VALID TOC with a truncated tail (very live
+    # risk on this box — see the BACKUP-SKIPPED flag below). The TOC sits at the FRONT of a
+    # custom-format archive, so reading it proves nothing about the tail. Reading the LAST
+    # data block forces a seek to near EOF and does catch truncation, cheaply.
+    LAST=$(awk '/TABLE DATA/{n=$0} END{print n}' /tmp/_pf_toc.txt | sed 's/.*TABLE DATA public \([^ ]*\) .*/\1/')
+    if [ -n "${LAST:-}" ]; then
+      if pg_restore --data-only -t "$LAST" -f /dev/null "$DUMP" 2>/tmp/_pf_tail.err; then
+        echo "     tail read ($LAST, last data block): OK — archive is not truncated"
+      else
+        echo "     >> TAIL READ FAILED on $LAST — DUMP IS TRUNCATED/CORRUPT. NOT a rollback:"
+        sed 's/^/        /' /tmp/_pf_tail.err | head -3
+      fi
+    fi
+  else
+    echo "     >> pg_restore --list FAILED — dump unreadable, there is NO rollback:"
+    sed 's/^/        /' /tmp/_pf_toc.err | head -3
+  fi
+  echo "     (full-archive proof, ~6 min CPU, run once before the window if you want it:"
+  echo "      pg_restore --data-only -t SourceEvidence -f /dev/null \"$DUMP\")"
 else
   echo "   !! NO homesonspec dump found in /root/backups/db, /var/backups, /var/lib/postgresql/backups."
   echo "   !! There is no rollback. Do NOT convert until a fresh verified dump exists."

← 616032a6 ops: fix preflight rollback check reporting a false "no back · back to Homesonspec · O3: prove SourceEvidence partitioning is feasible with no re d6400d4d →