Restoredrill: Verifying Your PostgreSQL Recovery Pipeline

"Untested backups aren't backups."

restoredrill is a specialized utility designed to provide empirical proof that your PostgreSQL backups are actually recoverable before a crisis occurs.

Instead of crossing your fingers, it automates the process of fetching the most recent backup, deploying it into a transient Postgres container, executing a suite of user-defined validations, and generating a detailed JSON report including the total restoration time.

The Problem: The "Silent Failure" Trap

Many organizations hope claim their backups work, but few actually verify them because:

1. There is no "safe" environment to perform a restore.
2. Engineering time is scarce.

When teams do automate this, they often rely on hand-rolled bash scripts and cron jobs. These typically fail silently—the script crashes, or it begins restoring an ancient, stale file—and the team remains oblivious for weeks.

restoredrill transforms this process into a one-command habit, ensuring that when a check is skipped, it is loud and visible.

Compliance and Evidence

Unlike continuous monitoring, restoredrill runs on the specific schedule defined by your recovery policy. This is critical for GRC (Governance, Risk, and Compliance) because continuous claims can create audit gaps.

If you are pursuing any of the following, restoredrill provides the exact evidence required:

• SOC 2
• ISO 27001
• AWS Foundational Technical Review (FTR)

---

Tooling Comparison

Depending on your needs, you might choose different tools. Here is how restoredrill fits into the ecosystem:

---

How it Works: The Workflow

The following diagram illustrates the restoredrill execution pipeline:

---

Quickstart Guide

You can prove your restore capability in about ten minutes without needing production access.

• Step 1: Create a local dump of your database (Supabase, RDS, or local). pg_dump -Fc -d "$DATABASE_URL" -f backup.dump
• Step 2: Place examples/quickstart.yml in the same directory (or update backup.source).
• Step 3: Execute the drill:

  $ restoredrill --config quickstart.yml --trigger manual
  

Expected Output: restoredrill: PASS, restore took 4.2s, 1/1 checks passed, report: restoredrill-report.json

---

Deep Dive: Validation Layers

restoredrill employs a fail-closed philosophy: if a check cannot be executed, it is marked as a failure, not a skip.

1. Pre-checks (The RPO Layer)

Before the restore begins, the tool verifies:

• Is the backup file size reasonable?
• Is the archive header readable?
• Is the backup recent enough to meet your Recovery Point Objective (RPO)?

$\text{RPO Met} = (\text{Current Time} - \text{Backup Timestamp}) \le \text{RPO Target}$

2. Structural Checks

Once restored, it verifies the integrity of the database:

• Did the restore process exit successfully?
• Are the expected tables present?
• Are sequences synchronized with their tables? (Crucial, as sequence lag only appears during the first INSERT after a disaster).

3. Read-path Checks

A restore can report success while the data is corrupted. restoredrill prevents this via:

• Row count verification.
• Data freshness checks.
• Custom SQL assertions defined by the user.

4. Performance & Environment

• RTO Evidence: Measures the Recovery Time Objective (RTO). $\text{RTO} = \text{Restore Completed At} - \text{Restore Initiated At}$
• Sanity Check: Ensures the container can actually accept connections, proving the recovery environment has sufficient resources.

---

Auditor-Ready Reporting

The JSON report schema is the result of months of iteration with professional auditors. To prevent spreadsheet errors, every field is always present, regardless of whether it applies to that specific run.

Key Report Fields

• triggered_by / pipeline_job_id: Identifies if a human or a CI pipeline started the drill.
• backup_resolved_key: The specific file used. If an S3 prefix is used, restoredrill identifies the newest valid backup, ignoring newer non-backup files (like checksums).
• backup_candidates_considered: A detailed list of every object inspected and why others were rejected.
• rpo_met & rto_met: Boolean indicators of whether the recovery targets were achieved.
• validation_errors: A granular breakdown of exactly what failed and why.

{
  "status": "FAIL",
  "rto_met": true,
  "rpo_met": false,
  "validation_errors": {
    "freshness_check": "Backup is 48 hours old; target is 24 hours."
  }
}