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.

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.

Summary

• Core Idea: A backup is a captured, self-consistent representation of cluster state that a restore process can turn back into a running PostgreSQL instance.
• Why It Matters: Every later recovery objective, from a dropped table to a lost region, is bounded by what your backups actually captured and how quickly they can be replayed.
• Key Concepts: logical backup, physical backup, consistency point, write-ahead log (WAL), restore, backup catalog.
• When to Use: Every production cluster needs a backup strategy in place before the first schema migration and before the first byte of customer data lands.
• Limitations / Trade-offs: A backup that has never been restored is only a hypothesis, and the method you choose trades restore speed against restore granularity.
• Related Topics: point-in-time recovery, disaster recovery tiers, WAL archiving, streaming replication.

Foundations

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.

Mechanics & Interactions

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 replay

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

Advanced Considerations & Applications

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.

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.

Common Misconceptions

• "A replica is a backup." A streaming replica protects against hardware and node failure, but it faithfully replicates deletes, corruption, and bad migrations, so it cannot substitute for an independently retained backup.
• "If the backup job succeeded, the backup works." A backup job exiting with status zero only proves the copy completed, not that the data inside it is complete, uncorrupted, or restorable under time pressure.
• "Logical and physical backups are interchangeable." They protect against different failure modes and support different recovery operations, so most production systems need both rather than picking one.
• "More frequent backups always mean better protection." Backup frequency only bounds your recovery point for that backup type, and without WAL archiving in between, frequency alone cannot close small recovery gaps.
• "Restore speed doesn't matter until an incident happens." Restore speed is a function of data volume, hardware, and tooling that should be measured ahead of time, because discovering it during an outage is the worst possible moment to learn it.

FAQs

Related

• Backup Basics - the concrete logical-vs-physical recipe this page's mental model supports
• pg_dump & pg_restore - format and parallelism detail for logical backups
• Backup Verification - proving the restore path actually works
• PITR Basics - how a physical backup plus WAL becomes a rewindable timeline
• DR Basics - how backup strategy feeds RPO and RTO targets

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, and PostGIS 3.5+.