{"id":816,"date":"2026-10-31T05:00:42","date_gmt":"2026-10-31T05:00:42","guid":{"rendered":"https:\/\/abrarqasim.com\/blog\/postgres-restore-drill-pg-dump-green-exit-code-proves-little\/"},"modified":"2026-10-31T05:00:42","modified_gmt":"2026-10-31T05:00:42","slug":"postgres-restore-drill-pg-dump-green-exit-code-proves-little","status":"publish","type":"post","link":"https:\/\/abrarqasim.com\/blog\/postgres-restore-drill-pg-dump-green-exit-code-proves-little\/","title":{"rendered":"Postgres Restore Drill: What a Green pg_dump Exit Code Proves"},"content":{"rendered":"

Short version for the impatient: a pg_dump<\/code> that exits with code 0 tells you the dump finished. It doesn’t tell you the dump restores, and it says nothing about whether the restored database has your data in it. The only way to know is to restore it somewhere disposable and look.<\/p>\n

I’ve been thinking about this since two Postgres announcements landed on postgresql.org in the same week. BackupDrill<\/a> is an open-source CLI for Supabase projects that restores each backup into a throwaway container. SafeGrd<\/a> does a similar thing as a service. Two separate projects shipping the same idea in a week tells me a lot of people are tired of finding out about broken backups on the worst possible day. I haven’t used either one yet. What follows is the DIY version I’d build first, so you can judge what the tools add.<\/p>\n

What a green pg_dump actually proves<\/h2>\n

Not much. When pg_dump<\/code> exits cleanly, you know it connected, read the catalogs, streamed table data, and wrote an archive to disk. The pg_dump documentation<\/a> is clear that it takes a consistent snapshot, which is great, but consistent isn’t the same as restorable.<\/p>\n

Here are the ways a successful dump still fails you. The archive can be truncated if the disk filled up and your script ignored the error from a pipe. The dump can come from a role that couldn’t see some schemas, so those schemas are silently missing. A dump from a newer major version can’t be restored by an older pg_restore<\/code>, so a staging box on an old version can tell you nothing about a production archive. And if your cron job redirects errors to a log nobody reads, a run that dumped zero rows looks identical to a good one.<\/p>\n

Notice the pattern. Every one of those failures happens at dump time and surfaces at restore time, which is months later. That gap is the whole problem.<\/p>\n

Here’s what BackupDrill says its drill checks, according to the announcement: the archive checksum, that pg_restore<\/code> completed, that post-data objects like indexes, constraints and triggers were created, that every table is present, and that tables which held data aren’t empty. It also records how long the restore took. I like that list because it’s specific. It goes past “the command returned 0” and asks what a restored database should look like.<\/p>\n

A restore drill in about twenty lines<\/h2>\n

You don’t need a product for the first version. You need Docker, a dump file in custom format, and a script that fails loudly. Custom format (pg_dump -Fc<\/code>) matters here, because pg_restore<\/code> can list and selectively restore from it, and plain SQL dumps can’t do that.<\/p>\n

#!\/usr\/bin\/env bash\n# restore-drill.sh \/path\/to\/backup.dump\nset -euo pipefail\n\nDUMP="${1:?path to a pg_dump -Fc file}"\nNAME="drill-$$"\n\ndocker run -d --rm --name "$NAME" -e POSTGRES_PASSWORD=drill postgres:17 >\/dev\/null\ntrap 'docker stop "$NAME" >\/dev\/null' EXIT\n\nuntil docker exec "$NAME" pg_isready -U postgres >\/dev\/null 2>&1; do sleep 1; done\nsleep 3   # the image runs a temporary server first; see the gotcha below\n\ndocker exec "$NAME" createdb -U postgres drill\n\nstart=$(date +%s)\ndocker exec -i "$NAME" pg_restore -U postgres -d drill \\\n  --exit-on-error --no-owner < "$DUMP"\necho "restore took $(( $(date +%s) - start ))s"\n\ndocker exec "$NAME" psql -U postgres -d drill -At -f - <<'SQL'\nselect 'tables', count(*) from pg_tables where schemaname = 'public';\nselect 'indexes', count(*) from pg_indexes where schemaname = 'public';\nselect 'triggers', count(*) from pg_trigger where not tgisinternal;\nSQL\n<\/code><\/pre>\n
A few choices in there deserve a defense. --exit-on-error<\/code> makes pg_restore<\/code> stop at the first failure, instead of its default of printing errors and carrying on, which is how a half-restored database ends up looking fine. --no-owner<\/code> avoids a flood of errors about roles that exist in production and not in the throwaway container. You lose the ownership check, and I think that’s an acceptable trade for a drill. The pg_restore reference<\/a> lists both flags if you want to read the details.<\/p>\n
Now the gotcha I promised. The official postgres<\/code> Docker image starts a temporary server to run its init scripts, shuts it down, then starts the real one. pg_isready<\/code> can report success against the temporary server, and your next command then lands in the gap between the two. The sleep 3<\/code> is ugly. A cleaner fix is to retry createdb<\/code> in a loop, and I’d do that in anything that runs unattended.<\/p>\n
Match the major version of the container to the server you dump from. Restoring a dump from Postgres 17 into a 15 container is the kind of mismatch a drill should catch, and that’s the point, but then the failure tells you about your drill and not about your backup.<\/p>\n
Counts are not enough<\/h2>\n
The script above prints table, index and trigger counts, and those numbers only help if you know what they should be. So record the expected values at dump time and compare. The cheapest version is to write a small manifest next to every dump:<\/p>\n
psql "$DATABASE_URL" -At -c "\n  select 'orders', count(*) from orders\n  union all select 'customers', count(*) from customers\n  union all select 'invoices', count(*) from invoices\n" > backup-2026-10-29.manifest\n<\/code><\/pre>\n
After the drill restores, run the same query against the container and diff<\/code> the two files. Use exact count(*)<\/code> for the tables that matter and skip pg_stat_user_tables.n_live_tup<\/code>, which is an estimate and will drift from the real number. The numbers won’t match if rows were written between the dump and the manifest, so generate both back to back on a quiet system, or accept a tolerance and say so in the script.<\/p>\n
Table counts catch missing data. They don’t catch wrong data. For the one or two tables a business can’t lose, I’d add a single spot check that mirrors something a human cares about, like the sum of invoice totals for last month. If that sum is off by a cent after restore, you want to know before a client asks.<\/p>\n
Also compare sequences. pg_dump<\/code> records sequence values, so last_value<\/code> should match between source and restore. If it doesn’t, you have a partial restore that still passes every row count and then fails on the first insert with a duplicate key error. It’s a small query, and I’d rather run it in a drill than discover it in production.<\/p>\n
Where the tools fit<\/h2>\n
Once you have the script above, the interesting question is what BackupDrill and SafeGrd would add, and the answer is mostly scheduling, storage and alerting. BackupDrill’s announcement describes backups streamed to any S3-compatible bucket (S3, R2, Backblaze B2, Wasabi) with a checksummed manifest, plus a hosted service that runs drills on a schedule and sends email alerts when one fails. SafeGrd describes age encryption on your host before upload and Object Lock on the bucket, which protects backups from deletion, and that’s a real concern the script above ignores.<\/p>\n
I’d think hard about scope before adopting either. BackupDrill covers Supabase-hosted Postgres only for now, per its own announcement, and dumps the public<\/code> schema by default. If you run Postgres on a VPS, that rules it out today. SafeGrd supports Postgres 13 and later and lists Supabase, Neon and RDS from a connection string, which is broader. In both cases you’re trusting a third party with the one job where being wrong is expensive, so read what leaves your host and who holds the keys. SafeGrd says the backup data doesn’t go through its servers, and that’s the right claim to want. I’d still test it with a database I can afford to lose.<\/p>\n
If you already run your own backups on a small server, I wrote up retention and restore testing in my restic and Postgres dump post<\/a>. The drill in this post slots in after that pipeline, as the step that proves the files are usable.<\/p>\n
Making it a habit<\/h2>\n
A drill that runs once teaches you very little, so schedule it. On a small box a systemd timer or a cron entry is plenty. I do this kind of operational hardening for clients, and you can see the sort of work on my portfolio<\/a>. The pattern is always the same: the backup job and the drill are separate jobs, and the drill alerts you when it fails, not when it passes. A passing drill that emails you every night trains you to ignore it.<\/p>\n
Two decisions to make up front. How old a backup can be before you consider it a failure? And how long a restore can take before you consider it a failure? The second one is the number people skip, and it’s the one that matters during an outage. If your restore takes 40 minutes, your recovery objective is at least 40 minutes, whatever the plan on paper says.<\/p>\n
This week, take your most recent dump and run the script above against it on your laptop. Time the restore, write the number in your runbook, and note the first thing that goes wrong. Something will.<\/p>\n","protected":false},"excerpt":{"rendered":"
BackupDrill and SafeGrd both restore backups on a schedule. Here is the DIY restore drill I would build first with pg_dump, pg_restore and Docker, and what to check.<\/p>\n","protected":false},"author":2,"featured_media":815,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"BackupDrill and SafeGrd both restore backups on a schedule. Here is the DIY restore drill I would build first with pg_dump, pg_restore and Docker, and what to check.","rank_math_focus_keyword":"pg_dump","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[237],"tags":[887,80,888,238,8],"class_list":["post-816","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","tag-backups","tag-devops","tag-pg-dump","tag-postgresql","tag-self-hosting"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=816"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/816\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/815"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}