Show HN: Restoredrill – proves your Postgres backups restore

Untested backups aren't backups. restoredrill proves your PostgreSQL backups really restore. It fetches the latest backup, restores it into a throwaway Postgres container, runs checks you define, and writes a JSON study pinch the reconstruct time.

Status: v0.1.0, early days. Postgres only. Things whitethorn still change.

Everyone knows they should trial restores. Almost cipher does, because there's obscurity safe to reconstruct to and ne'er capable time. Teams that automate it usually hand-roll a cron occupation and a script, and those neglect softly successful their ain way. The existent threat isn't a bad backup. It's the drill silently not running, aliases silently restoring a old file, and cipher noticing for a month.

restoredrill makes the drill a one-command habit, and makes skipping it loud. It runs connected immoderate schedule your betterment argumentation sets. It doesn't presume you request to trial perpetually (a batch of GRC proposal really warns against that, since immoderate spread successful a "continuous" declare becomes an audit finding). It conscionable proves you did what you said you'd do, connected schedule.

A argumentation doc is easy to fake, connected intent aliases by accident. "We trial quarterly" could person been written past week pinch thing really tally successful a year. A timestamped, machine-generated study is harder to fake.

If you're doing SOC 2, ISO 27001, aliases an AWS Foundational Technical Review, this is the style of grounds they inquire for: existent logs from existent restores, tied to what ran and when.

There are different devices successful this space. Worth naming plainly alternatively of pretending they don't exist.

Databasus is simply a coagulated self-hosted backup level for Postgres, MySQL, MariaDB, and MongoDB, pinch a afloat web UI and a restore-verification characteristic built in. If you want 1 dashboard to negociate backups crossed respective database engines, look location first. BackupDrill does thing adjacent to this for Supabase specifically, including Storage files.

restoredrill isn't trying to beryllium either of those. It's a single-purpose, CI-native cheque that produces a study shaped for an auditor, not a dashboard: fail-closed connected everything, an RPO freshness precheck, your ain SQL assertions, RTO tracked against a target, and each section ever coming truthful it copies cleanly into a SOC 2, ISO 27001, aliases AWS FTR grounds packet. If you already person a backup instrumentality and conscionable request impervious it restores, connected a schedule, successful a shape an auditor accepts, this is built for precisely that gap.

The accustomed excuse for not testing restores is "there's obscurity safe to do it." There is: a throwaway instrumentality connected your ain laptop.

1. Dump immoderate Postgres you have. Supabase, RDS, your section dev box, doesn't matter:

   pg_dump -Fc -d "$DATABASE_URL" -f backup.dump

2. Copy examples/quickstart.yml adjacent to it (or constituent backup.source astatine wherever you saved it).

3. Run it:

   $ restoredrill --config quickstart.yml --trigger manual restoredrill: PASS, reconstruct took 4.2s, 1/1 checks passed, report: restoredrill-report.json

That's it. No S3, nary CI, nary accumulation credentials. You now person a JSON record proving a existent reconstruct happened, timestamped, connected your laptop, successful astir 10 minutes. Once that works, adhd existent checks (row counts, freshness, your ain SQL assertions, spot examples/restoredrill.yml) and constituent it astatine your existent backup.

Checks tally successful tiers. Every cheque is fail-closed: if a cheque can't run, that counts arsenic a failure, not a skip.

1. Prechecks, earlier the reconstruct moreover starts: is the backup record large enough, is its archive header readable, and is it really caller (the RPO check). This catches a backup cron that died softly and near the aforesaid old record successful place.
2. Structural: did the reconstruct finish, are capable tables there, and are sequences successful sync pinch their tables. A series that lags its column's max worth only shows up connected the first INSERT aft a existent disaster. restoredrill catches it now instead.
3. Read-path: statement counts, information freshness, and immoderate SQL assertions you write. A reconstruct tin exit 0 and still beryllium lying until personification really sounds the data. One existent incident: a reconstruct process exited cleanable while the database down it was silently corrupted. That's what these checks are for.
4. RTO evidence: really agelong the reconstruct really took, checked against a target if you group one.
5. Environment sanity: the instrumentality has to travel up and judge connections astatine all, which besides proves the betterment situation has capable room to work.

The study is the existent merchandise here. Automating the reconstruct is the easy part. Getting a study format an auditor accepts connected the first walk takes existent iteration. This schema comes from personification who paid that costs directly: 3 rewrites and months of backmost and distant pinch a existent auditor.

Every section is ever present, ne'er missing conscionable because it doesn't apply. Auditors often transcript these into a spreadsheet, and a section that sometimes exists and sometimes doesn't breaks that. Key fields:

• triggered_by / triggered_by_user / pipeline_job_id: aforesaid schema whether a scheduler ran this aliases a personification pushed the fastener (--trigger manual --triggered-by [email protected]). Manual runs transportation the aforesaid accountability arsenic scheduled ones.
• backup_resolved_key: the existent record aliases entity drilled, not conscionable the configured source. If your root is an S3 prefix, restoredrill picks the newest object, but only aft checking it really looks for illustration the correct backup format. A checksum record aliases different sidecar uploaded aft the existent backup can't triumph conscionable by being newer.
• backup_candidates_considered: each S3 prefix entity restoredrill looked at, successful order, and why immoderate sewage skipped. Empty for non-prefix sources.
• backup_timestamp / backup_age_seconds / rpo_target_seconds / rpo_met: the freshness and RPO grounds described above.
• restore_initiated_at / restore_completed_at / restore_duration_seconds / rto_target_seconds / rto_met: RTO evidence, measured against a target if you group one.
• validation_errors: each grounded check, arsenic its ain field, pinch what grounded and why. If a tally fails, you want to cognize what broke, not conscionable that thing did.
• notify_errors: a surgery Slack aliases webhook URL is simply a finding, not a silent no-op. If a notify descend fails to deliver, it shows up here, and the process exits non-zero, moreover if the drill itself passed.
• All timestamps are a literal "YYYY-MM-DD HH:MM:SS UTC" string, not epoch aliases RFC3339. Most auditor workflows extremity successful copy-pasting into a spreadsheet, and this format survives that.

The JSON study stays inspectable down to what really ran. No polished PDF summary asking you, aliases your auditor, to conscionable spot it.

Whether your reconstruct is getting slower complete clip isn't thing 1 study tin show connected its own. That's a occupation for immoderate you provender these reports into: a log aggregator, a dashboard, moreover a spreadsheet. Every study includes restore_duration_seconds truthful that's easy to chart.

• Docker
• A PostgreSQL backup: pg_dump -Fc archive aliases plain SQL dump, section aliases successful S3 (aws CLI required for S3 sources)

restoredrill sends results into what you already watch, alternatively of asking you to cheque location new:

• Prometheus: output.prometheus_textfile writes node_exporter textfile metrics. Alert connected the property of restoredrill_last_run_timestamp_seconds. That's your "verified wrong N hours" signal, and it catches a drill that softly stopped running.
• Slack: notify.slack_webhook_url gets a one-line PASS/FAIL summary pinch the grounded checks listed.
• Anything else: notify.webhook_url gets the afloat JSON study via POST.

A grounded transportation to immoderate of these shows up successful notify_errors and makes the process exit non-zero. Losing your only notification transmission silently is precisely the benignant of quiet nonaccomplishment this instrumentality exists to catch.

The exit codification makes this a earthy scheduled CI job. See .github/workflows/restoredrill.yml for a moving example, aliases usage action.yml straight successful your ain workflow. Pass --trigger manual (and --triggered-by) erstwhile a personification runs it by manus instead. The grounds output looks the aforesaid either way.

• The ephemeral-container exemplary assumes your database fits comfortably successful a instrumentality connected the runner. Multi-terabyte estates request a different attack (restore to dedicated infra). restoredrill isn't that today.
• pg_dump-level verification doesn't workout PITR aliases WAL replay. pgBackRest support, which does, is connected the roadmap.
• The archive integrity precheck only useful for pg_dump_custom. Plain SQL dumps person nary table-of-contents header to check, truthful there's nary balanced precheck for that format. That's a existent gap, not an oversight: pg_dump_sql corruption gets caught later, and much expensively, astatine reconstruct clip instead.
• The aforesaid spread applies to picking the correct record from an S3 prefix. restoredrill tin cheque a candidate's contented for pg_dump_custom (it looks for the PGDMP header), but plain SQL has nary specified signature. backup.s3_object_pattern is required erstwhile you harvester a prefix root pinch pg_dump_sql. restoredrill fails astatine config load alternatively of guessing which record is the existent backup.
• We don't mention circumstantial SOC 2 aliases ISO 27001 clause numbers here. The study is built astir what the underlying power really asks for (documented, provable betterment testing connected immoderate cadence your argumentation sets), but getting a compliance citation incorrect is worse than not citing one. Check your ain power connection and auditor alternatively of trusting a clause number from us.

• pgBackRest repositories (PITR way verification)
• GCS backup sources
• MySQL, past restic
• Differential checks (restored vs. prod complete the pre-backup window)
• Scheduled mode

The sequence-integrity cheque has a Docker-backed integration trial (it starts a existent Postgres container). It skips cleanly if Docker isn't available.

MIT