Backup & Restore Foundations
Backup and restore is the foundational discipline underneath every other PostgreSQL recovery capability, from undoing a single bad DELETE to standing a cluster back up in another region.
//
Search across all documentation pages
Backup and restore is the foundational discipline underneath every other PostgreSQL recovery capability, from undoing a single bad DELETE to standing a cluster back up in another region.
This page builds the mental model of what a backup actually captures, how a restore turns that capture back into a running instance, and why the method you pick sets a hard ceiling on what later recovery work can achieve.
At its simplest, a backup is a copy of data taken at a known moment that a restore can use to recreate that moment on a different or later instance.
PostgreSQL supports two fundamentally different ways of making that copy, and the distinction between them shapes almost every decision in this section of the docs.
A logical backup, produced by pg_dump or pg_dumpall, asks the running database to describe its objects and data as SQL statements or an archive of that intent, so restore replays those statements against a fresh, possibly different, PostgreSQL instance.
A physical backup, produced by pg_basebackup or a tool like pgBackRest, instead copies the actual data files on disk, so restore places those files back and lets PostgreSQL's own crash-recovery machinery bring them to a consistent state.
Because a physical backup is just a copy of files that are actively being written to, it needs a way to guarantee the copy is internally coherent even though different files were copied at slightly different instants.
That guarantee comes from the write-ahead log, PostgreSQL's append-only record of every change, which the restored instance replays forward from the moment the backup started until the copy reaches a consistent state.
A logical backup sidesteps that problem entirely by asking the database to produce a self-consistent snapshot at the SQL level, using the same MVCC machinery that gives any long-running query a stable view of data.
The consistency point is the moment a backup becomes trustworthy: for a logical dump it is the instant the snapshot was taken, and for a physical backup it is the point after WAL replay catches the file copy up to a coherent state.
Restore, in both cases, is not a single action but a pipeline of locating the backup, verifying it, loading or copying it, and then bringing PostgreSQL through whatever recovery steps return it to a serving state.
A backup catalog is simply the record of what backups exist, when they were taken, and how to retrieve them, and without one a valid backup sitting in storage is indistinguishable from a corrupt one.
Physical backup tools do not actually need to freeze the filesystem or stop writes, because PostgreSQL exposes a low-level API that brackets the copy operation and lets WAL fill the gap.
-- Conceptual shape of what pg_basebackup and pgBackRest do under the hood
SELECT pg_backup_start('nightly-base', fast => true);
-- ... external tool copies data files while writes continue ...
SELECT pg_backup_stop(); -- returns the WAL end position needed to replayCalling pg_backup_start does not pause the cluster; it marks the WAL position where the backup begins so recovery knows where replay must start, and pg_backup_stop records where replay must end for the copy to become consistent.
This is why a physical backup is inseparable from WAL: the files alone are an inconsistent jumble of different moments, and only replaying the WAL segments generated during the copy window turns them into a single coherent snapshot.
A logical backup has no equivalent gap to fill, because pg_dump opens one transaction with a repeatable-read snapshot and reads every object through that same frozen view, so nothing outside the dump itself needs replaying.
That difference cascades into restore behavior: restoring a physical backup always reconstructs the entire cluster as it existed at the consistency point, while restoring a logical backup can target a single table, a single schema, or the whole database, because it was never anything more than a sequence of independent SQL operations.
Backups also interact with replication in a way that surprises newcomers, because a streaming replica is not a backup at all, only a continuously moving copy that faithfully applies every mistake the primary makes, including a dropped table or a corrupted row.
A replica protects against hardware failure, while only an independent backup, taken and retained on its own schedule, protects against operator error or logical corruption, and conflating the two is one of the most common gaps in an otherwise well-run cluster.
The backup method you choose also determines what later recovery tooling can do with it, because point-in-time recovery requires a physical base backup plus a continuous WAL archive, and it cannot be built from a logical dump alone.
At scale, the choice of backup method becomes an economic and operational trade-off as much as a technical one, weighing restore precision against storage cost and operational complexity.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
Logical (pg_dump) | Selective, portable across versions and platforms | Restore is slow at multi-terabyte scale | Object-level recovery, cross-version migration |
| Physical base backup | Fast full-cluster restore, foundation for PITR | All-or-nothing, needs matching major version | Full cluster recovery, replica bootstrap |
| Continuous WAL archive | Enables recovery to any point in time | Useless without a base backup to anchor it | Undoing recent operator error precisely |
| Storage/volume snapshot | Very fast to take and restore | Crash-consistent only, not application-consistent | Fast recovery paired with WAL for correctness |
A cluster running pgvector 0.8+ or PostGIS 3.5+ extensions adds another wrinkle, because a physical restore brings the extension binaries and data together automatically, while a logical restore requires the target instance to already have matching extension versions installed before the dump will load cleanly.
Backup retention is itself an architectural decision, because keeping only the minimum required by policy leaves no margin for slow-discovered corruption, while keeping everything indefinitely turns storage cost into an unbounded liability.
Encryption and access control belong to the backup design from the start, not as an afterthought, because a backup is functionally a complete copy of production data sitting in a location that is often less scrutinized than the primary database itself.
The single most consequential decision in this whole space is treating backup and restore as one workflow rather than two, because an untested restore path is not a smaller risk than no backup at all, it is the same risk wearing a disguise.
A logical backup describes database objects and data as SQL-level operations that a target instance replays, while a physical backup copies the actual on-disk files that PostgreSQL reads directly.
Replaying the WAL segments generated between pg_backup_start and pg_backup_stop turns the raw file copy, taken at slightly different instants across files, into a single coherent snapshot.
No, pg_dump takes a repeatable-read snapshot and reads through that frozen view, so writers and other readers continue uninterrupted for the duration of the dump.
Generally yes for reasonably close versions, which is one of logical backup's main strengths, but always test the restore against the actual target version rather than assuming compatibility.
WAL retention determines how far back PITR can reach, while backup retention determines how many independent full recovery points exist, and conflating the two leaves gaps when either one is trimmed.
Only if it is paired with WAL replay to reach application consistency, because a snapshot alone is crash-consistent, meaning it looks like the instance was powered off rather than cleanly stopped.
Yes, it is the record of what backups exist, where they live, and how to retrieve them, and tools like pgBackRest maintain one automatically so operators are never guessing during an incident.
Physical backups carry extension data and binaries together automatically, while logical restores require the target instance to already have matching extension versions installed before the dump will load.
The failure modes that break a restore, such as a missing WAL segment or a corrupted archive, are silent until the moment you actually need the backup, at which point there is no time left to discover them.
It adds a decryption step before the copy or dump can be read, so restore tooling and key access must be planned and drilled just as carefully as the backup process itself.
Point-in-time recovery is built on top of a physical base backup plus continuous WAL archiving, and disaster recovery tiers describe how backup and replication choices combine to meet a business-defined RTO and RPO.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 19, 2026