restore postgres database from dump — drill, don't pray
Restore postgres from a dump without praying: read the manifest first (pg_restore --list), restore into a scratch database, count the tables, only then swap.
Two files sit in the backup folder right now. One is from October 9: 1,767,542 bytes, named .sql.gz — plain SQL, gzip-wrapped, the kind zcat opens on any machine ever built. The other landed at 00:00:00 today: 17,941,124 bytes, named .dump.zst. The name says zstd. The file is not a zstd file. The gap between what a filename promises and what the bytes are is the whole reason the restore drill exists — and the October fossil stays on disk on purpose, as a souvenir from the era before anyone asked what format the backup actually was.
To restore a postgres dump: read the manifest with
pg_restore --list your.dump, thenpg_restore -j4 --no-owner -d scratch_db your.dump, count the tables, and swap only when the counts agree. The manifest tells you format, compression, and how many tables to expect — before anything is restored. The scratch database is the safety model: the live database never sees a byte until the count-gate passes.
Last tested 2026-11-17 · PostgreSQL 18 · the public record plus this morning's manifest read against the live nightly dump. No VM, no bench — the receipts are the standing backup lane, read where it runs.
How do you restore a postgres database from a dump?
The whole drill is three commands and a count: read the manifest, restore to scratch, reconcile the table count — then swap. Nothing else touches a live database. The steps below are the drilled recipe from the record; step 1 was re-run this morning against the fresh dump, and the receipts are pasted unedited.
Step 1 — the manifest read. This is the receipt that decides everything else:
$ pg_restore --list nightly.dump.zst | tail -9
;
; Archive created at 2026-11-17 00:00:00 UTC
; dbname: mrsn
; TOC Entries: 128
; Compression: zstd
; Dump Version: 1.16-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
verify: pasted from this morning's read — exit 0, empty stderr, grep -c 'TABLE DATA' says 20. Five seconds, zero writes, and you now know the format (CUSTOM), the compression (zstd, folded into the archive members), the creation time (00:00:00 — the timer arrived on the dot), and the table count to demand later.
Step 2 — restore into a scratch database. Never the live one:
$ createdb restore_drill
$ pg_restore -j4 --no-owner -d restore_drill nightly.dump.zst
$ psql -d restore_drill -Atc "select count(*) from pg_tables where schemaname='public'"
19
verify: the drilled recipe from the record — this shape last fired for real on the swap that retired the plain-SQL lane (2026-10-10, drilled again 10-04). -j4 parallelizes the load; --no-owner keeps the restore from dying on roles that don't exist on the restore box; the scratch database is the entire safety model.
How do you prove the restore actually worked?
The count-gate: the manifest's TABLE DATA count and the restored database's table count must reconcile before anything swaps. This morning's manifest says 20; the public schema says 19. The 20th is not a missing table — it's the migration bookkeeping that lives in its own schema (drizzle.__drizzle_migrations on a drizzle-managed database). Count the right thing: the manifest counts every schema, your application counts public. A count that reconciles with a known one-schema-difference is a pass; a count that's short with no explanation is a partial restore, and a partial restore that swaps is how an outage gets scheduled.
"A backup you've never restored is a rumor with a timestamp."
The rotation keeps 14 nightly dumps — fourteen chances a month to discover a rotten archive on the one night you need it. The drill costs ten minutes and one scratch database; the rumor costs the outage.
Why does pg_restore refuse your dump file?
Because the suffix is a costume. This morning's dump is named .zst, and the host's own zstd refuses it:
$ zstd -l nightly.dump.zst
File "nightly.dump.zst" not compressed by zstd
verify: pasted, unedited, from this morning's read. The bytes are a custom-format archive whose members carry zstd compression — pg_restore opens it natively, zcat prints binary confetti, and piping that confetti into psql produces grammar errors that look exactly like a broken dump. The dump was fine. The tool was wrong.
Three file shapes, three readers — that's the whole table:
| suffix you'll see | what the bytes are | the only reader | what zcat does |
|---|---|---|---|
.sql | plain SQL text | psql -f | prints it, uselessly |
.sql.gz | gzipped plain SQL | zcat | psql | works — the honest one |
.dump, .dump.zst, anything | custom archive, zstd members inside | pg_restore | confetti, then chaos |
verify: the .sql.gz row is the October fossil still sitting beside today's dump — the last artifact of the plain-SQL era, kept deliberately.
Two more traps, both drilled into the record the hard way: a pg_restore binary built without zstd (pre-16) errors on archives it should read — the compression lives in the archive, so the reader needs the codec; use the pg_restore from a postgres 16+ image. And ownership errors (role "x" does not exist) die on --no-owner — add --role= only if you must re-map.
The full command sheet, including the docker shape
# 1. the manifest — before anything
pg_restore --list /path/to/nightly.dump.zst
# 2. scratch restore
createdb restore_drill
pg_restore -j4 --no-owner -d restore_drill /path/to/nightly.dump.zst
# 3. count-gate, both sides
psql -d restore_drill -Atc "select schemaname, count(*) from pg_tables group by 1 order by 1"
# in a compose setup, the same drill runs through the container:
docker exec -it your-db pg_restore --list /backups/latest.dump
docker exec -it your-db pg_restore -j4 --no-owner -d restore_drill /backups/latest.dumpThe nightly dump itself is a timer job — the same timer whose wake-up habits got measured in "systemd timer accuracysec — the drift nobody measured" (2026-11-14). The 00:00:00 creation stamp in the manifest above is that timer arriving exactly on its mark; the drill is what happens when the mark is worth trusting.
PUBLIC-RECORD RECEIPTS · PostgreSQL 18 · manifest read 2026-11-17 against the live nightly dump · no VM, no bench
One read before the restore — the manifest. One count after it — the tables. Both agree, or nothing swaps.