Draft DR Drills and Backup-Validation Checklists

Tier: Foundation · Workflow area: Reliability & Incident Ops · ~30 minutes

The DR gap in most database teams is not tooling — pgBackRest, native backup and cloud snapshots all work. The gap is the paperwork nobody writes: which failures we drill, what "recovered" provably means, and the evidence that restores actually run. AI is good at exactly this drafting work, because the inputs are your own topology and policies, and every output is reviewed by the engineer who will execute the drill. Execution itself — failing over, restoring, deciding on data loss — stays entirely human.

When to use

• The estate has backups but no drill calendarrestores have "always worked" but nobody has run one under a timer this year.
• Before an audit or a compliance cycleISO 27001, SOC 2 and RBI-style regimes all ask for tested recovery evidence, not backup configs.
• After topology changea new replica tier, a move to managed cloud, a new region: every change invalidates part of the old drill set.

When NOT to use

• To automate actual failoverAI drafts plans and checklists; it does not get a lever. DR execution is a human decision with a named owner, every time.
• When RPO/RTO targets do not existdrills validate targets. If the business has never stated them, run that conversation first; AI can draft the straw-man matrix to argue about, but the sign-off is the business's.
• As a substitute for the first real restoreif you have literally never restored this database, do that today, by hand, before designing a drill program around assumptions.

The recipe

1. 1.

   Feed the model your real topology and policy, not a generic description. The draft is only as specific as the input:

   Copy

   # pgBackRest: current backup reality
   pgbackrest --stanza=main info
   
   # What exists, how old, how big
   pgbackrest --stanza=main info --output=json > backup-state.json
   

   Collect alongside it: the instance list with sizes and roles, the stated (or guessed) RPO/RTO per service tier, retention settings, and where backups physically live.

2. 2.

   Ask for the four artifacts in one structured pass.

   Copy

   Context: PostgreSQL 15, 3 clusters (sizes below), pgBackRest to S3,
   WAL archiving on, stated targets: tier-1 RPO 5 min / RTO 1 h,
   tier-2 RPO 24 h / RTO 8 h. Topology and backup info attached.
   
   Draft, as four separate artifacts:
   
   1. RPO/RTO MATRIX — per cluster: stated target, what the current
      backup config can THEORETICALLY achieve, and the gap. Show your
      arithmetic (WAL archive interval, full/diff cadence, restore
      throughput assumptions — state assumptions explicitly).
   2. FAILURE-INJECTION LIST — 8-10 drill scenarios ordered by
      likelihood x impact: single-table logical corruption, dropped
      table, full instance loss, region loss, backup-repo loss,
      ransomware-encrypted primary, silent WAL archiving failure,
      restore-target-version mismatch. For each: what to inject in a
      drill environment, and what the drill proves.
   3. RESTORE-VALIDATION QUERIES — SQL that proves a restore is
      complete and consistent, not merely started.
   4. QUARTERLY BACKUP-VALIDATION CHECKLIST — the boring recurring
      list: archive continuity, last-restore date, size trend,
      retention vs policy, encryption, off-account copy.
   
   Do not invent numbers I did not give you; mark unknowns [ASSUMED].
   

   The [ASSUMED] markers become your review agenda — especially restore throughput, which the model cannot know and which dominates real RTO.

3. 3.

   Correct the matrix with one measured restore. Run a timed restore of the smallest production-representative cluster and replace the model's assumed throughput with your number:

   Copy

   time pgbackrest --stanza=main --delta restore
   # then WAL replay to a target
   

   If measured restore throughput says tier-1 RTO is 3 h against a 1 h target, the matrix's gap row is now a finding for management, with your timing as evidence. This one measurement is the difference between a paper matrix and a real one.

4. 4.

   Make the validation queries prove consistency, not existence. Tighten the AI draft to check three layers:

   Copy

   -- 1. Recovery reached the intended point
   SELECT pg_last_wal_replay_lsn(), pg_is_in_recovery();
   
   -- 2. Row counts on sentinel tables vs recorded pre-drill counts
   SELECT 'orders' t, COUNT(*) FROM orders
   UNION ALL SELECT 'ledger', COUNT(*) FROM ledger;
   
   -- 3. Application-level invariants still hold
   SELECT COUNT(*) FROM ledger l
   LEFT JOIN orders o ON o.id = l.order_id WHERE o.id IS NULL;
   

   Record expected values for the sentinels before the drill; "the database started" is not a pass condition.

5. 5.

   Run the first drill from the checklist, cold. Pick the top scenario (dropped table via point-in-time restore is a good first), schedule two hours, and have an engineer follow the drill doc literally while a scribe notes every improvisation. Improvised steps are checklist defects; timing overruns are matrix corrections.

6. 6.

   Install the calendar and the evidence habit. Quarterly checklist runs and at least two drills a year land in the team calendar with owners. Each run produces a dated one-page record — scenario, timings, pass/fail per validation query, defects found — filed where an auditor can be pointed at it. The stack of these records is your tested-recovery evidence.

Make it yours

• SQL Serverthe same pass over native backups or Ola Hallengren jobs; validation adds RESTORE VERIFYONLY (necessary, not sufficient) plus DBCC CHECKDB on the restored copy; drills include an AG failover with a timed application reconnect.
• MySQLbuild around XtraBackup or clone plugin plus binlog point-in-time recovery; have the model flag the GTID continuity checks, then verify them against your replication setup.
• Managed cloud (RDS/Cloud SQL)snapshots shift the checklist toward cross-region and cross-account copy verification, restore-to-new-instance timing (often the RTO surprise) and the provider's PITR window vs your stated RPO.

Quality check

• Every matrix number is measured or marked assumedan RTO nobody timed is a wish. The drill program's first job is replacing assumptions with timings.
• Backup-repo loss and ransomware are on the injection listthe failure that takes primary and backups together is the one the checklist most needs and drafts most often omit.
• Validation queries have recorded expected valuesa query without a pre-drill baseline can only prove the database is up, not that it is right.
• Drills have dates, owners and filed recordsan undrilled plan and no plan are audit-equivalent.
• A human owns the go/no-gothe drill doc names who decides to fail over in a real event and who accepts data loss; no step delegates that to tooling or AI.