Logical and physical backups compared

The copy that started and returned 45000 rows in the previous lesson proved only that crash recovery does its job well; it established nothing about whether the files described a coherent instant. The same capture produced the other outcome moments later, when the identical tree with pg_wal emptied ended in PANIC: could not locate a valid checkpoint record at 0/2F20158 and never reached a prompt. Both results point at one conclusion from opposite ends: a database backup is a backup only when the database took part in producing it. That leaves the question of how it takes part, and across every engine worth the name there are two answers with very different consequences at restore time.

Two families, separated by what they hand back

A logical backup asks the database to describe its own contents. What comes out is not the files but a rendering of what those files mean — CREATE TABLE and COPY statements in a script, or the same information inside a tool-specific archive that a companion utility replays later. The database serves the request the way it serves any other read: through the executor, inside a transaction, honouring the visibility rules a reporting query obeys. The PostgreSQL documentation describes pg_dump as writing either a plain SQL script or one of the archive formats, and notes that the directory format is the only one supporting parallel dumps.

A physical backup asks the database for permission to copy its files, and then copies them. The on-disk structures travel exactly as they are: the pages, the free-space and visibility maps, the control file, the write-ahead log. The database contributes coordination rather than description. It says when the copy may begin, it records the point from which the copied files have to be recovered, and it defines the range of write-ahead log that must accompany them for the copy to be usable at all. pg_basebackup performs that handshake as one command; a storage snapshot bracketed by the server start and stop calls performs the same handshake by hand.

One family carries meaning, the other carries bytes. Nearly every practical difference between them is a consequence of that single sentence, and the consequences run in opposite directions — which is why neither family is the better one, and why an estate that holds only one of them usually finds out which recoveries it gave up during an incident rather than during design.

Selective recovery is a property of the format

The most common database emergency is not a lost server. It is one table truncated by a migration, one schema dropped by a script that ran against the wrong connection string, one tenant deleted by a bug. The question that decides which backup can help is not how recent the copy is; it is whether the copy has any addressable interior.

A logical dump does. Its unit is an object: a schema, a table, a set of rows in a defined order. The pg_dump documentation describes selecting objects at dump time, and it describes the archive formats as the ones pg_restore can be selective about, which extends the same selectivity to restore time. Recovering one table therefore means restoring one table, into a database that stays online while the other four hundred are untouched.

A physical backup does not. Its unit is the cluster, and PostgreSQL’s documentation states the restriction outright: a file-system-level backup restores the entire cluster, never individual tables or databases. The bytes are pages, and a page carries no usable identity outside the running server that maps it to a relation through the system catalogues — catalogues that are themselves stored in those same pages. Nothing sensible extracts one table from a file-level copy. The physical route to one table is to restore the whole cluster somewhere else, start it, and then copy the table across with a logical operation, which is a real technique and also an honest admission that the selectivity came from the logical step and not from the physical backup.

Two consequences follow that people underestimate. The first is scope: a base backup is a copy of everything, so restoring it in place replaces every database in the cluster, including the ones that were fine. The second is blast radius during an incident, when someone is under pressure and the physical restore is the only artefact available. The temptation is to restore over the running cluster because that is the fast path. Doing so destroys the evidence of what went wrong and the fallback position at the same time, and it may replace good data in other databases with older good data — the sort of second incident that turns a one-table problem into a full outage. Restore beside production, then move the object across.

pg_basebackup and the base that recovery builds on

The physical example from the capture behind this part is one command with a data directory, a WAL method and a checkpoint mode.

$ pg_basebackup -D /work/base -X stream -c fast

  >>> exit code: 0
rows contained in the base backup: 45000

Nothing about that output is remarkable, and that is the point worth making about the family. The copy contains 45000 rows because the cluster held 45000 rows, and it is a copy of the files rather than a description of them. What makes it valuable is not the copy itself but what can be attached to it. The same capture continued past the backup: five thousand more orders arrived, then an unqualified DELETE took the table to zero rows, and the archive held five WAL segments with pg_stat_archiver reporting archived=6 failed=0. Replaying the base backup forward against that archive logged starting point-in-time recovery to 2026-08-28 13:34:40.077562+00, then recovery stopping before commit of transaction 836, and the result was checked rather than admired: rows recovered : 50000 (expected 50000) and sum(amount) : 825025000 (expected 825025000).

That is the capability the logical family cannot offer at all. A dump is a photograph of one instant, chosen when the dump ran. A base backup plus a continuous log archive is a photograph plus every subsequent change in order, which is what makes an arbitrary instant addressable. The recovery point of a nightly dump is last night. The recovery point of a base backup with a healthy archive is any moment the archive still covers — subject entirely to the archive being continuous and verified, which is the subject of the next lesson and the place where this architecture usually fails.

The restore nobody timed

This is where the two families stop being a matter of taste. A logical dump is compact, portable and reassuringly quick to produce, especially with the parallelism the directory format allows. None of those properties says anything about the restore, because the restore is a different operation doing different work — and the estate that owns only dumps has an RTO that nobody has ever measured.

The throughput capture recorded for this course made the same point with a different tool and a deliberately extreme setup, which is why it is useful as a shape rather than as a number. Measured on restic 0.18.0 inside a container on tmpfs, on a 12-core machine, on one date: a first backup of 400 MiB took 1.49s, a second backup of the same data with nothing changed took .73s, and a restore of that 400 MiB took 1.07s. The capture states the conclusion plainly, that the nightly job reports the middle number while the incident needs the last one, and that backup duration is not a predictor of restore duration because backup is incremental and restore never is. Those figures were measured on RAM-backed storage on one machine and must not be reused as planning figures for anything.

For a logical database backup the same asymmetry exists for a structural reason: the two operations are not even the same kind of work. The dump reads rows through a query and writes them out. The restore parses, inserts, validates and builds. Nothing in the dump duration constrains the restore duration, and the gap widens with index count, constraint complexity and row count rather than with the byte size of the file everyone looks at.

The decision rule, stated honestly

Physical for recovery of the whole service, and for meeting a short RTO. When the requirement is that the cluster exists again quickly, the family that writes pages beats the family that rebuilds them, and the gap should widen with data volume. Physical is also the only family that can serve as the base for a point-in-time recovery, so any requirement expressed as a recovery point tighter than the backup interval implies a physical base plus a continuous log archive.

Logical for selective recovery, for moving between versions, and as a second format when the physical chain is the only copy. Recovering one table without disturbing the rest of the cluster is a logical operation. So is a major-version upgrade, and so is a move onto different hardware or a managed platform where the on-disk format is not yours to control. The third reason is the one most often skipped and the one this course keeps returning to: a physical chain is a base plus an ordered log, and a defect anywhere in that structure — a corrupt segment, a retention rule that removed a member, a bug in the tool that wrote it — can invalidate the recoveries that depend on it. A dump is produced by a different code path, stored as a different artefact, and read by a different utility, so it fails independently. That independence is the entire value, and it evaporates if the dump lands on the same storage as the base backups.

Written out, the pairing for a service that matters is unremarkable:

BASE=/srv/backup/base/$(date +%F)
pg_basebackup -D "$BASE" -X stream -c fast

alongside a periodic dump of each database, kept on separate storage:

DB=orders
OUT=/srv/backup/logical
STAMP=$(date +%F)
pg_dump --format=custom --dbname="$DB" --file="$OUT/$DB-$STAMP.dump"

The -c fast in the first command asks for an immediate checkpoint rather than a spread one, which gets the backup started sooner at the cost of a burst of I/O on the source — a trade to make deliberately rather than by habit. The custom format in the second is what makes the dump readable selectively later; a plain SQL script is a single ordered stream and gives that up.

Production discipline

1. Classify every database backup you own by what the database was asked to produce. A description of the data restores selectively and crosses versions; a copy of the files restores the whole cluster into the same major version. A schedule that names files rather than families cannot answer which recoveries it supports.
2. Hold both families for anything whose loss matters. Physical for whole- service recovery and as the base for point-in-time recovery; logical for the single-table incident, the version move, and as an artefact that fails independently of the base-plus-log structure. Put them on separate storage or the independence is decorative.
3. Measure the logical restore before you promise a recovery time. Restore a real dump into an isolated instance, time it end to end including index builds and validation, and record the date and the row count beside the figure. The backup duration tells you nothing: the measured capture showed .73s to back up 400 MiB and 1.07s to restore it, on RAM-backed storage on one machine.
4. Treat the major version as part of the physical restore target. A base backup is readable by a server sharing its on-disk format. Record the version and the build alongside the backup, and confirm that whatever you would restore onto during an incident matches it.
5. Restore beside production, never over it. The physical family replaces the entire cluster, so an in-place restore during a one-table incident destroys both the evidence and the fallback. Bring the copy up separately, verify it against a property recorded before the incident, then move the object across.

Cross-course references

• PostgreSQL for Production Sysadmins — Part XIII (Backup, Archiving and Point-in-Time Recovery) develops the engine-specific mechanics this lesson treats generically: the archive command, the recovery target settings, and the base-backup options. Read it as the depth behind the physical family described here, once the choice between the two families has already been made.
• Kubernetes for Production Sysadmins — Part LXIX (etcd Restore) is the same distinction with the logical family absent. An etcd snapshot is a physical artefact restored as a whole data directory, so there is no selective path to one key, which is exactly why this lesson treats all-or-nothing scope as a property of the format rather than a limitation of one tool.
• Observability for Production Sysadmins — Part XXII (SLO-Based Alerting) builds alerting from a stated objective and measured performance against it. That is the missing half of a logical-only estate: with the restore duration never measured, no objective can be stated, and nothing can fire when the dataset grows past the recovery time somebody once assumed.

Quiz