Logical Backups — pg_dump and pg_restore

pg_dump writes one database out as SQL statements or as an archive file, and what lands in that file is the database exactly as it existed the moment the dump started. It runs while the application keeps working, blocking neither readers nor writers, and the result is portable in a way nothing else in this chapter is: across major versions, across architectures, onto a laptop.

It is also the reason the answer at 14:12 was "restore last night and lose the day". Nadia's nightly dumps were doing precisely what they are designed to do, which is capture one instant per run. The strategy built on top of that was the failure, and the properties that disqualify a dump for Cartwheel are structural rather than fixable. This topic covers what a dump is genuinely good for, which is more than the incident makes it sound.

What a Dump Produces

The output format matters more than it looks, and there are four. Plain SQL (-Fp) is the default and is a text file of statements that psql replays. The custom archive (-Fc) is compressed and allows selective restore from a single file. The directory format (-Fd) writes one file per table plus a table of contents, and it is the only format that supports a parallel dump with -j, because it is the only one where several processes can write at once. Tar (-Ft) exists and is rarely the right answer. Compression is chosen separately with -Z, which accepts gzip, lz4, zstd or none.

The consistency guarantee is worth stating precisely, because it is stronger than people expect and it has a price attached. The dump is internally consistent, a snapshot of the database as of when pg_dump began, and it does not block other operations while it runs. The mechanism is a single transaction holding a single snapshot for the entire duration, which means that while a four-hour dump runs, the oldest snapshot the cluster must preserve is four hours old. Dead row versions anywhere in the cluster stay uncollectable for that whole window, on every table, including the ones the dump has already finished reading.

What a Dump Does Not Contain

pg_dump dumps one database, and roles, tablespaces and cluster-wide grants do not live inside any database. They come from pg_dumpall, whose --globals-only flag writes exactly those objects and no table data at all. Skip it and the restore produces a cluster where nothing has an owner and nobody can log in: cartwheel_app, cartwheel_analytics, cartwheel_migrator and cartwheel_admin do not exist, so every GRANT in the dump fails and the application cannot connect to the database that was just successfully restored.

pg_dump -Fd -j 4 -Z zstd \
        -f /backup/cartwheel-2026-08-13 cartwheel

pg_dumpall --globals-only \
        -f /backup/globals-2026-08-13.sql     -- roles and tablespaces

# restoring the pair, globals first:
psql -X --set ON_ERROR_STOP=on -f /backup/globals-2026-08-13.sql postgres
pg_restore -j 4 -d cartwheel /backup/cartwheel-2026-08-13

The first command dumps four tables at a time into a directory; the second writes the roles and tablespaces as plain SQL, which is the only thing pg_dumpall produces. Order matters on the way back: the globals must exist before anything tries to grant privileges to them, and restoring them needs superuser access, since recreating roles and tablespaces is a superuser operation by definition. Store the two files together with the same date in the name; a globals file from three months ago restores a permission model from three months ago, and every grant in it will apply cleanly.

Restoring

pg_restore reads the non-plain formats: custom, directory and tar. A plain dump is not its business at all; that one goes through psql, which by default keeps going after an SQL error and finishes reporting success, so --set ON_ERROR_STOP=on is the difference between a restore and a story about a restore. pg_restore's default is the same shape: without -e it continues past errors and prints a count at the end, on a terminal that has just scrolled several thousand lines.

Most of the value on the way back sits in three flags. -j runs the expensive steps concurrently: loading the data, building the indexes, validating the constraints. It works with the custom and directory formats, though not together with --single-transaction. -1 wraps the whole restore in one transaction so a failure leaves nothing behind, and it implies exit-on-error. --section splits the work into pre-data, data and post-data, which is how you load rows into a schema that already exists. Behind all three sits the number that decides whether this is a backup strategy at all: restoring is dramatically slower than dumping, because the dump copies rows while the restore rebuilds every index and revalidates every constraint from scratch.

Version Portability

This is the capability physical backups do not have, and the reason a dump belongs in the toolbox permanently. The output of pg_dump can be loaded into servers newer than the pg_dump that produced it, and pg_dump can read servers older than itself, back to 9.2. What it will not do is dump from a server newer than its own major version; it refuses outright rather than risk producing an invalid dump. The rule that falls out is one line long and it is the one people get backwards: use the newer side's binaries. Pointing a laptop's 17 pg_dump at Cartwheel's 18 cluster fails immediately; pointing 18's pg_dump at a 17 cluster is exactly the supported path, and it is how the upgrade in Chapter 14 moves data when it has to.

Selective Work

A dump can be narrowed: -n analytics for one schema, -t orders for one table, --schema-only or --data-only to take half of it. This is the everyday use that keeps pg_dump in daily rotation even on a cluster whose real backups are physical. Staging gets refreshed with the schema plus the small tables (products, product_categories, order_statuses, couriers) and none of the 40 million orders. An analyst gets one table on a laptop instead of a read-only role on the replica. And when 90,000 rows have to come back into a live production table, the mechanism that carries them is a single-table dump taken from a scratch cluster, which is exactly where topic 66 ends up.

Where It Stops as a Backup Strategy

What disqualifies it as Cartwheel's only backup is three properties, all of them structural. A dump captures exactly one moment, the instant it began, so no accident can ever be answered with anything other than "restore the most recent dump", regardless of when the accident happened. Restore time is dominated by index rebuilds rather than by data volume, which on a several-hundred-gigabyte database is hours against a recovery objective measured in tens of minutes. And every run holds a snapshot open on the source for its full duration.

The second and third are mitigable. Take the dump from pg-replica-a and the primary stops paying for the held snapshot, since a dump is a read like any other. Use the directory format with -j on both ends and the wall-clock numbers improve considerably. The first one is not mitigable by any flag, because the information needed to reach 14:11 was never written into the file. Topic 65 is where that information comes from. Cartwheel keeps the nightly dump anyway, for the portability, the staging refreshes and the single-table extractions that a physical backup cannot do at all.