PostgreSQL Backup + Restore Drill (DEVOPS-OPS-029-01)

Audience: DevOps and QA operators rehearsing disaster recovery for a Stella Ops install. Owner: DevOps + QA roles. Sprint reference: docs/implplan/SPRINT_20260518_077_DevOps_destructive_drills_and_drift_readiness.md.

Purpose

Prove that the Stella Ops postgres state volume can be backed up, the state can be wiped, and the backup can be restored cleanly back into the empty cluster without losing data. This is the disaster-recovery counterpart to OPS-028 (auto-migration from a wiped volume) and exercises the full forward path documented in ADR-004: forward-only migrations.

When to run

• During an approved destructive reset window — same posture as wipe-postgres.sh. The drill itself wipes the postgres volume in the middle of the procedure; everything downstream of postgres will see the database disappear and reappear.
• Quarterly, at minimum, to keep recovery muscle memory current.
• Before any major release that touches schema layout outside the normal forward-only migration window.

Scripts

• devops/compose/scripts/drill-backup-restore.sh — bash variant for Linux/WSL operator workstations.
• devops/compose/scripts/drill-backup-restore.ps1 — PowerShell variant for Windows operator workstations.

Both variants ship with --dry-run (bash) / -DryRun (PowerShell) as the default mode. They print the actions the drill would perform but do NOT touch the live stack. Pass --confirm / -Confirm to actually run the destructive sequence.

Procedure (what the script does)

The script executes the following sequence:

1. pg_dump the live cluster to a timestamped artifact under artifacts/qa/backup-restore-drill/pg-dump-<ts>.sql. The dump uses --no-owner --no-privileges so the file can be replayed against the recreated cluster regardless of the bootstrap role layout.
2. Sanity-check the dump file — non-empty, parses as plain SQL. Empty or unreadable artifacts fail the drill with exit code 2 before any destructive step runs.
3. Capture pre-restore row counts for a representative table set: authority.users, authority.clients, authority.permissions, shared.tenants. Written to artifacts/qa/backup-restore-drill/row-counts-pre-<ts>.txt.
4. Wipe the postgres volume by delegating to wipe-postgres.{sh,ps1} with the --yes / -Yes flag. The wipe script is the canonical “fresh DB” step and explicitly stops the container before removing the volume so the rm cannot be soft- skipped (Sprint 20260504_007 VOL-RACE-003 contract).
5. Bring postgres back up via docker compose -f devops/compose/docker-compose.stella-ops.yml up -d postgres. Wait up to READY_TIMEOUT seconds (default 120) for pg_isready inside the container.
6. Restore the dump by piping the SQL file into psql -U <user> -d <db> inside the container. Non-zero exit fails the drill with exit code 5.
7. Capture post-restore row counts into artifacts/qa/backup-restore-drill/row-counts-post-<ts>.txt.
8. Diff pre vs. post row counts. Any divergence fails the drill with exit code 6. A passing drill writes a structured JSON result to artifacts/qa/backup-restore-drill/result-<ts>.json and exits 0.

Exit codes

Exit code 10 is deliberately distinct from 0 so callers can tell a real success from a dry-run preview at a glance.

Usage

Dry-run (default)

The dry-run mode prints what the drill would do without invoking any destructive command. Use this first to confirm the script can locate the container, volume, and compose file in your environment.

Linux / WSL:

./devops/compose/scripts/drill-backup-restore.sh

Windows / PowerShell:

.\devops\compose\scripts\drill-backup-restore.ps1

Confirmed destructive run

Only run with explicit operator approval inside a recorded reset window. The procedure stops postgres mid-stream and the rest of the stack will report database-unavailable errors until the restore completes.

Linux / WSL:

./devops/compose/scripts/drill-backup-restore.sh --confirm

Windows / PowerShell:

.\devops\compose\scripts\drill-backup-restore.ps1 -Confirm

Custom artifact directory

./devops/compose/scripts/drill-backup-restore.sh --confirm \
  --out-dir /var/lib/stellaops/drills/2026Q2

.\devops\compose\scripts\drill-backup-restore.ps1 -Confirm `
  -OutDir 'D:\stellaops\drills\2026Q2'

Recovery path if the drill fails

If the drill fails between the wipe step and a successful restore, the stack is in the same state as a fresh-volume bootstrap (the normal OPS-028 path). Recover with:

1. Confirm postgres is up: docker ps --filter name=stellaops-postgres.
2. Wait for the postgres-init scripts to finish: docker logs stellaops-postgres 2>&1 | grep 'running /docker-entrypoint-initdb.d/'.
3. Restart all services so they re-apply auto-migrations: docker compose -f devops/compose/docker-compose.stella-ops.yml restart.
4. Verify the admin login works via https://stella-ops.local.

If you have a known-good dump that the restore phase rejected (exit code 5), capture the psql failure log alongside the dump file in artifacts/qa/backup-restore-drill/ and open a follow-up sprint task referencing the failing exit code.

Cross-references

• docs/runbooks/migration-recovery.md — for the forward-only migration recovery path that postgres restore relies on.
• docs/architecture/decisions/ADR-004-forward-only-migrations.md — rationale for snapshot-based recovery over down-migrations.
• devops/compose/scripts/wipe-postgres.{sh,ps1} — the canonical fresh DB step the drill delegates to for the wipe.
• Sprint docs/implplan/SPRINT_20260518_077_DevOps_destructive_drills_and_drift_readiness.md task DEVOPS-OPS-029-01.

Operator checklist

Before invoking --confirm / -Confirm:

• [ ] Operator has explicit reset-window approval recorded.
• [ ] No other team is mid-deploy or mid-QA on the same compose stack.
• [ ] Sufficient disk under the configured OutDir for the pg_dump artifact (rule of thumb: 2x the current PGDATA size).
• [ ] docker ps shows stellaops-postgres healthy.
• [ ] The previous successful drill result JSON is preserved before starting the new run (move or rename so the timestamped artifacts do not collide on identical-second runs).

After the drill:

• [ ] result-<ts>.json shows "status": "passed".
• [ ] Admin login through https://stella-ops.local succeeds.
• [ ] Spot-check at least one tenant-scoped table beyond the four the drill counts (e.g., concelier.advisories, signals.*) by comparing row counts against a recent backup.