firedrill

You don't have backups. You have hopes.

firedrill takes a Postgres backup and actually restores it into a disposable, version-matched container, then reports whether it worked and how long each stage took. It is built to fail your build the day a backup stops being restorable — not the day you need it.

A verification that could not run never reports as passing.

https://maximo000.github.io/firedrill/ — what it checks, and the measurements behind each check.

That rule is the whole design. No Docker, an unreadable archive, a container that never came up — every one of those produces COULD NOT VERIFY and a non-zero exit, never a green tick. A backup tool that says "OK" because it silently skipped the restore is worse than no tool, because it manufactures confidence.

Status

Fetch (local, presigned URL, or S3, checksum-verified when you supply a digest — and reported as unverified when you do not), restore into an ephemeral version-matched container, then the ladder: structure, volume, semantics, integrity. Three tiers, JUnit and JSON output, a history file that turns RTO into a measured trend, a GitHub Action, and point-in-time recovery.

What works today:

$ firedrill run backups/production-2026-08-23.dump

firedrill  backups/production-2026-08-23.dump
  archive   custom v1.15.0  13.3KB  from 'postgres'
  source    PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2)  -> restored into postgres:16

  [ok  ] inspect     0.00s  16.15 (Debian 16.15-1.pgdg13+2) (custom)
  [ok  ] target      0.83s  postgres:16
  [ok  ] restore     0.09s  exit 0
  [ok  ] smoke       0.15s  1 user table(s)

  total     1.42s

  PASS -- restored and answered queries.

And when it isn't fine:

  [ok  ] inspect     0.00s  16.15 (Debian 16.15-1.pgdg13+2) (custom)
  [ok  ] target      0.83s  postgres:16
  [FAIL] restore     0.09s  exit 1
  [ok  ] smoke       0.16s  1 user table(s)

  1 finding(s):
    CRITICAL ARCHIVE_TRUNCATED    could not read from input file: end of file
             The archive is incomplete. The backup job most likely ran out of
             disk or was killed. Check the writer's exit status and free space
             at write time -- pg_dump can exit 0 having written a short file.

  FAIL -- the restore ran and produced findings above.

Note the schema restored and the table exists. Only the rows are missing. That is what a truncated backup looks like from the outside.

Install

pip install firedrill

Python 3.10+, and a working Docker. No Postgres client is required on the host — the archive header is parsed in pure Python and every database operation happens inside the target container, which is also what makes the version matching real.

Use

firedrill run path/to/dump.dump            # restore and report
firedrill run s3://backups/pg/daily/       # the newest object under a prefix
firedrill run --live DATABASE_URL --keep backups/   # dump a running database now, prove it restores
firedrill run dump.dump --image 'pgvector/pgvector:pg{major}'  # extensions the stock image lacks
firedrill physical pgbackrest --repo DIR --target "..."      # pgBackRest / WAL-G, with PITR -- see below
firedrill run dump.dump --json report.json # machine-readable
firedrill run dump.dump --rto 45m          # exceeding the budget is a finding
firedrill run dump.dump --tier fast        # schema only, for every commit
firedrill clean                            # remove containers left by a crash
firedrill doctor                           # check Docker, disk and config before trusting a run

Exit code is 0 only when the restore genuinely ran and produced no finding at or above --fail-on (default high).

Where the backup lives

A path works, and so does the place the backup actually sits:

version: 1
source:
  type: s3
  bucket: acme-backups
  prefix: postgres/daily/
  select: newest          # or an explicit `key:`
  sha256: "007168050a7570c6a9c93230992de425f81316bf688ceb277546e45265aae9d5"

type: local (a path), type: https (a presigned URL — S3, GCS and Azure all issue them), and type: s3 (pip install firedrill[s3]). Give a sha256: or a size: and the artefact is checked against it before anything tries to restore it: a dump that arrives truncated but plausible never reaches a container, and the run reports rather than passes. Quote the digest — YAML reads a bare all-digit value as a number.

Or skip the config: firedrill run s3://bucket/key, and a trailing slash (s3://bucket/prefix/) takes the newest object under it.

No backup yet? Take one and prove it in the same command

$ DATABASE_URL=postgresql://app@db.internal/shop firedrill run --live DATABASE_URL --keep backups/
  [ok  ] fetch      26.59s  4,601,447 bytes from live  fresh pg_dump
  [ok  ] target      8.48s  postgres:18
  [ok  ] restore     5.94s  exit 0
  [ok  ] smoke       0.55s  70 user table(s)

(Real output, against pagila on PostgreSQL 18.) --live asks the server its version, runs pg_dump -Fc from a container of that same major -- nothing but Docker on the host -- and drills the result; --keep DIR leaves the verified dump there as <db>-<UTC timestamp>.dump. The argument is the name of the variable holding the URL, never the URL, for the same reason there is no --password; the password is registered for redaction and passed to the container by name. A localhost database is reached as the Docker host. pg_dump only reads, so this is read-only too. In firedrill.yml: source: {type: live, url_env: DATABASE_URL, keep: backups/}.

Three properties worth stating plainly:

• Read-only by construction. sources.py contains no verb that writes, deletes, copies or tags anything at the origin. "Never writes to the source" is a property of the file, not a promise in a README.
• Credentials from the environment only. There is no --access-key and no place for one in firedrill.yml, because /proc/*/cmdline is world-readable and CI logs echo command lines. boto3's default chain already does the right thing.
• A presigned URL's signature is a credential, so it is stripped from every report, log line and finding, and plain http to a non-local host is refused outright rather than putting a working one on the wire.

Which dump formats

--jobs N (or jobs: N in firedrill.yml) passes -j N to pg_restore, which is what makes the directory format's parallel restore actually parallel. Custom and tar archives accept it too, restoring serially where pg_restore cannot split the work. Default is 1.

Custom, directory and tar all carry a PGDMP header — directory and tar keep theirs in a toc.dat member — so the major version is read out of the artefact itself, with no PostgreSQL client on the host. That is what makes the version-matching real.

Plain SQL has no binary header, but pg_dump starts every plain dump with -- Dumped from database version 16.15 (...), and that's where the version comes from (read straight out of a .gz too). It's a comment, so it can be stripped; when it's gone, firedrill refuses with VERSION_UNKNOWN and asks for --postgres MAJOR rather than guessing.

Plain dumps need one check the others don't, measured on PostgreSQL 16: psql restores a plain dump that was cut off mid-table with exit 0 and no error at all — it takes end-of-file as the end of the data, and the table comes back with 9,710 of its 20,000 rows. Nothing in the restore reports it. The only witness is the file: pg_dump always ends a plain dump with -- PostgreSQL database dump complete, so a dump without that line is reported ARCHIVE_TRUNCATED (critical) before anything is restored. psql also exits 0 when a statement fails (a missing role, measured), so its stderr is classified line by line with the same rules as pg_restore's, and the exit code firedrill reports comes from those findings.

A plain dump's header is only a comment, and people edit these files. pagila's published pagila-schema.sql says "Dumped from database version 12.11" and uses uuidv7(), which arrived in 18. Restored into 12 as the header says, 140 statements fail -- and psql, reading from a pipe, prints them without its usual psql:file:line: prefix. 0.3.0 matched only the prefixed form and called that restore clean; 0.4.0 reads both and runs psql with -f - so the prefix is there anyway. --postgres 18 then restores all 70 tables, and the one remaining failure is real: pagila's film_embedding needs pgvector, which --image 'pgvector/pgvector:pg{major}' supplies.

A plain dump can only be restored whole, so the fast and sample tiers refuse it (TIER_UNSUPPORTED) instead of quietly running a full restore.

MySQL and MariaDB

firedrill run backup.sql recognises a mysqldump or mariadb-dump file (plain or .gz) by its own header and takes the same path Postgres does: the server version comes from the dump, the restore runs in mysql:<version> or mariadb:<version>, and then it runs smoke, your semantics: checks, and CHECK TABLE on every restored table.

• Truncation. Both tools end a finished dump with -- Dump completed on .... The mysql client restores a dump cut at a statement boundary with exit 0, so a missing trailer is ARCHIVE_TRUNCATED (critical) before anything is restored.
• Errors. The client stops at the first failing statement; its ERROR nnnn (state) at line N becomes a finding naming the line, since everything after it was never restored.
• MariaDB 10.5.25+/11.x dumps open with a /*M!999999\- enable the sandbox mode */ line before the header -- the reason they won't load into MySQL. firedrill reads past it and restores them into MariaDB.
• The password reaches the client through MYSQL_PWD inside the container, never argv.

Tested on datacharmer/test_db's employees database (300,024 employees, 2,844,047 salary rows), dumped by each engine's own tool:

Which PostgreSQL versions

Tested end to end against 13, 14, 15, 16, 17 and 18 — dump on any of them, restored into a container of that same major, read out of the archive header. Archive formats 1.14 (13–15), 1.15 (16) and 1.16 (17–18) each have a committed header fixture, so the parser stays covered even where containers cannot run.

One honest limit: datcollversion arrived in PostgreSQL 15, so on 13 and 14 no collation version exists to compare. firedrill reports COLLATION_UNVERIFIABLE there rather than pretending — nothing, not this tool and not PostgreSQL, can tell you whether text indexes on those servers sort like production's.

A structure reference is portable across all six: taken on 14, it is clean against a restore on 18. Upgrading Postgres must not turn every check red, because that is how a team learns to ignore the tool.

Tiers: how much to restore

A 2 TB restore cannot run on every commit.

version: 1
tier: sample
sample:
  tables: [orders, customer]

The report always says which tier ran, in capitals when it is not full, and a partial pass gets its own sentence — PASS (fast tier) — the schema restored. Whether the DATA is there was not checked. A pass from a schema-only run must never look like a pass from a full one.

The rungs a tier cannot honour report NOT RUN, never a tick, and the config refuses combinations that would produce a misleading finding rather than running them: tier: sample with a volume rule on an unsampled table, or with semantics at all — a smoke query is arbitrary SQL, so there is no knowing whether it reads a table whose rows came back.

The ladder, and firedrill.yml

A bare firedrill run proves the backup restores and answers queries. To prove it restored the right data, put a firedrill.yml next to it — it is picked up automatically, or named with --config.

version: 1

rto_budget: 45m

structure:
  reference: schema/production.txt   # committed, reviewable, diffable

volume:
  tables:
    orders: {min_rows: 1}

semantics:
  - name: recent orders exist
    sql: SELECT count(*) FROM orders WHERE created_at > now() - interval '7 days'
    expect: "> 0"

ignore:
  - check: COLLATION_UNVERIFIABLE
    reason: "restoring on alpine in CI; tracked in DR-114"

Generate the structure reference once and commit it:

firedrill run dump.dump --write-reference schema/production.txt

It is one line per catalog object, sorted, so a schema change shows up as a readable diff in review rather than a wall of pg_dump output.

Three things the loader does that are worth knowing, because each one is a failure it refuses to let pass quietly:

• An unknown key is an error, not a warning. A typo'd tolerence: that loaded silently would mean a check you believe is running is not running, and the run would still go green.
• Every ignore needs a written reason. An unexplained suppression is a config error. It is the only process this tool imposes, and it is what keeps a green run meaningful.
• expect must be a comparison against a number. There is deliberately no way to write a check whose result is printed, so a smoke query returns a shape and never a row.

Rungs that nothing configured report n/a — not configured — rather than a tick. "Nothing asked for this" and "this passed" are different facts.

What it checks today

Each of these is proved against a deliberately broken backup that pg_restore itself is perfectly happy with — that is the point of the whole ladder — and each is also asserted not to fire on a healthy one. A false positive costs exactly what a false negative costs: a DR tool that cries wolf gets muted, and a muted DR tool is worse than none because it still looks like coverage.

Why the exit code is not enough

A missing role or extension is often described as a pg_restore warning with a zero exit code. Measured on PostgreSQL 16 and 18 with custom-format archives, that is not what happens: the exit code was 1 in every broken case, accompanied by warning: errors ignored on restore: N.

The exit code is still not sufficient: it is one bit, so it says something broke but never what, and "role absent" and "archive truncated" need different findings and different fixes. firedrill therefore uses both signals and reports which one fired — with EXIT_CODE_LIED kept as a guard, because the tool must not depend on that measurement staying true in a future release.

In CI, in ten lines

- uses: MaXiMo000/firedrill@v0
  with:
    config: firedrill.yml
    rto: 45m
    history: firedrill-history.json

The action fails the build when the drill fails and when it could not run at all — "we did not verify" must never be quieter than "we verified and it was fine". It publishes the report before failing, so a red build is an actionable one, and exposes ok, verified, findings, seconds and a summary like restored in 4m12s, 0 findings.

Copy-paste templates live in examples/: a nightly full drill with read-only AWS credentials via OIDC, and a PR comment that runs the fast tier and edits its own comment instead of posting a new one each push.

--junit report.xml writes JUnit XML so CI shows each rung separately. A rung that could not run is <skipped>, never a silent pass, because most dashboards colour those differently — which is exactly the distinction worth preserving.

Trends: RTO you have measured, not claimed

firedrill run dump.dump --history firedrill-history.json

Each run appends its durations, row counts and versions, and is measured against the last known-good run of the same tier. The report then says 19% slower than the last good full run (2.3s on 2026-08-24T…).

That history is also what makes volume.tolerance meaningful — a tolerance needs something to be tolerant of:

version: 1
volume:
  tolerance: 10%
  tables:
    audit_log: {tolerance: 50%}   # append-only, grows fast

Only a drop past the tolerance is a finding. Tables grow; a rule that fired on growth would go off every week until somebody muted it, taking the real findings with it.

The history file holds counts, durations and versions — aggregates and catalog facts. It has no field that could hold a row, which is pinned by a test, because it is the artefact of this tool most likely to be committed to a repo by accident.

pgBackRest and WAL-G

Most production Postgres isn't backed up with pg_dump at all. It's backed up with pgBackRest or WAL-G: a base backup plus a WAL archive. firedrill restores those with the tool itself, replays the WAL, and puts the recovered server through the same checks:

$ firedrill physical pgbackrest --repo /backups/pgbackrest --target "2026-09-26 20:28:59+00" --config boundary.yml
  backup    pgBackRest 20260926-202837F  stanza demo  finished 2026-09-26T20:28:56+00:00
  source    PostgreSQL 16  -> recovered to 2026-09-26 20:28:59+00 on postgres:16

  [ok  ] recover    23.81s  20260926-202837F -> 2026-09-26 20:28:59+00
  [ok  ] smoke       0.84s  shop: 2 table(s)
  [ok  ] semantics   0.72s  2 check(s) in shop
  [ok  ] integrity   2.39s  1 sequence(s)

  PASS -- recovered to the target, and the boundary is where it should be.

firedrill physical pgbackrest --repo DIR                 # newest backup, all archived WAL
firedrill physical walg --repo DIR --target "2026-09-26 20:30:23+00"
firedrill physical pgbackrest --stanza main --postgres 16  # a remote (S3/GCS/Azure) repository
firedrill physical walg --repo DIR --max-age 26h          # and the newest backup must be recent

• The major version comes from the repository (backup.info, the WAL-G stop sentinel), and the restore runs in postgres:<major> with the tool added: pgBackRest from the image's own PGDG repository, WAL-G's release binary pinned by sha256. --image takes a {major} template for extension images.
• A remote repository is configured the way the tools are always configured -- PGBACKREST_REPO1_TYPE=s3, WALG_S3_PREFIX, AWS_*, and so on. Those variables are passed to the container by name, never value.
• It never archives into the repository it checks. A restored data directory carries production's own archive_command; started as it is, the drill would push WAL into production's archive. The server is started with -c archive_mode=off (which outranks every file), and then asked: if it reports anything but off, the drill stops with ARCHIVING_ENABLED. Tested with a backup that carries archive_mode = 'on' from ALTER SYSTEM: the repository held the same WAL files before and after. A local repository is also mounted read-only.

Measured against repositories made by pgBackRest 2.59.1 and WAL-G v3.0.9 themselves (built by tests/make_physical.py, in CI):

A restore that ran and broke says FAIL: the backup was tested, and it is broken. COULD NOT VERIFY stays reserved for a check that could not happen.

Alerts, and running it on a schedule

export BACKUP_ALERTS=https://hooks.slack.com/services/...
firedrill physical pgbackrest --repo /backups --max-age 26h --notify BACKUP_ALERTS

--notify ENV_VAR POSTs one JSON message when a drill fails or could not run. It carries text (Slack, Mattermost) and content (Discord) with a one-line verdict, plus ok, verified, worst and the finding rules for anything else. --notify-on always reports passes too. The argument is the variable's name: a webhook URL is a credential. A webhook that refuses the message fails the run rather than being skipped quietly, and its URL never appears in the error.

Without GitHub Actions, a systemd timer on any host with Docker does it:

# /etc/systemd/system/firedrill.service
[Service]
Type=oneshot
EnvironmentFile=/etc/firedrill.env          # BACKUP_ALERTS=..., PGBACKREST_REPO1_* ...
ExecStart=/usr/local/bin/firedrill physical pgbackrest --stanza main --postgres 16 --max-age 26h --notify BACKUP_ALERTS

# /etc/systemd/system/firedrill.timer
[Timer]
OnCalendar=*-*-* 04:17:00
Persistent=true
[Install]
WantedBy=timers.target

Point-in-time recovery

A dump proves you can get a database back. PITR proves you can get it back to a chosen moment — which is what you need after a DELETE without a WHERE at 14:02. Almost nobody tests it, because testing it means performing it.

firedrill pitr \
  --base /backups/base --wal /backups/wal \
  --target '2026-08-25 14:01:00' --config firedrill.yml

The assertion has two halves, and it needs both:

version: 1
semantics:
  - name: the row written before the target survived
    sql: select count(*) from events where label = 'before'
    expect: "== 1"
  - name: the row written after the target did not
    sql: select count(*) from events where label = 'after'
    expect: "== 0"

Restoring everything satisfies the first. Restoring nothing satisfies the second. Only together do they say recovery stopped where it was told. Configure neither and firedrill reports PITR_UNASSERTED rather than calling it a pass — a server that came up proves a server came up.

Two things measured on PostgreSQL 16 that shaped this:

• An unreachable target does not silently promote. Recovery exits with FATAL: recovery ended before configured recovery target was reached, so "did it get there" needs no heuristic.
• recovery_target_time counts as reached only when a commit with a later timestamp exists in the WAL. A target after the final commit is unreached, not "satisfied at end of WAL".

Prior art, honestly

firedrill never takes backups. A tool that both takes and verifies its own backups is grading its own homework.

Safety

This tool restores databases, so the interlocks are the price of admission:

• It only ever uses a target it created itself. There is no code path that connects to a user-supplied DSN.
• The backup is bind-mounted read-only.
• No --dsn and no --password flags. The container password is generated per run, passed to Docker by variable name so it never enters argv, and never written to disk.
• No port is published; the target is unreachable from the host.
• Teardown runs in a finally, and firedrill clean removes anything a crash orphaned.

See SECURITY.md.

Development

python tests/make_corpus.py                       # build the broken-backup corpus
python tests/test_firedrill.py --require-integration

The corpus is generated from real containers, never hand-edited. Findings are asserted in both directions — a false positive fails the build exactly as hard as a false negative, because a DR tool that cries wolf gets muted, and a muted DR tool still looks like coverage.

--require-integration makes a skipped container test a build failure. Without it (Windows, where Linux containers cannot run) those tests skip by name and the count is printed, so "it passed" and "it did not run" never look alike.

python tests/field_test.py restores pagila, chinook and northwind — real databases this project did not write. Worth running after changing any check: the corpus is synthetic, so it can only find the cases its author thought of.

Cutting a release

# pyproject.toml is the single source of truth; release.yml fails if the
# tag and the declared version disagree.
python tests/test_firedrill.py --require-integration
git commit -am "Release vX.Y.Z" && git tag -a vX.Y.Z -m "firedrill vX.Y.Z"
git push origin main --follow-tags
git tag -f v0 vX.Y.Z && git push -f origin v0      # the moving major tag

The tag runs verify → github-release → image (GHCR, with provenance) → pypi (Trusted Publishing over OIDC — there is no API token in this repo and there must never be one). The pypi job waits for a reviewer, and a published version number can never be reused, so let the gate do its job.

Licence

MIT.