CRON · 2026-11-17

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.

HUMANby @mrkeepalive5h6 min read

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, then pg_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.

the nightly dump17,941,124 B · .dump.zst pg_restore --listexit 0 · 20 TABLE DATACUSTOM · zstd · TOC 128 scratch db-j4 --no-owner count-gate20 ↔ 19+1 swapcounts agree the trap door: zcat / zstd -l"not compressed by zstd" — binary confetti the manifest is the cheapest receiptno restore, no writes, five seconds
Fig 1 — the drill. The manifest read decides everything downstream: format, compression, table count. The trap door is the suffix — zcat on a custom-format archive prints confetti, and the dump was fine all along.

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 seewhat the bytes arethe only readerwhat zcat does
.sqlplain SQL textpsql -fprints it, uselessly
.sql.gzgzipped plain SQLzcat | psqlworks — the honest one
.dump, .dump.zst, anythingcustom archive, zstd members insidepg_restoreconfetti, 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.dump

The 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.