Backup

An exported snapshot includes customer 1 and order 10. A later committed related pair changes the source count to 2 and 2, while the dump restores 1 and 1 with no orphan order.

pg_dump captures a consistent snapshot

pg_dump exports one PostgreSQL database using a consistent view of transactional data, even while other sessions commit changes. This prevents the ordinary table-by-table copy problem in which related tables reflect different committed states. The snapshot is only part of the backup contract: the archive must also be complete for its intended scope, readable by compatible tools, and successfully restored into a usable database. ...

A physical base leads through retained good changes to a named stop point before bad changes. A second, broken chain shows a missing segment preventing arrival at the target.

Postgres WAL archiving reaches point-in-time recovery

Point-in-time recovery starts with a valid physical base backup and replays the WAL required to reach a chosen recovery target. The target must fall within the recoverable history of that backup and its available timelines. Archiving does not create the ability to recover to arbitrary earlier dates, nor does it repair a gap between the backup and the desired endpoint. ...

The same checksummed archive first fails in a fresh cluster because a role is missing, then restores after that role is supplied. The recovered two orders total 6075 cents.

Backups are untested hope without proof

A completed backup job establishes that the backup process produced an artifact. A restore rehearsal establishes whether that artifact, the available tooling, and the required environment can produce a usable system. These are different claims. Recovery evidence should identify the backup used, the state recovered, the dependencies supplied, and the time needed to make the result useful to the application. ...

Completed WAL segments reach the archive only when preservation succeeds. A nonzero archive result causes retry and local retention, while identical and conflicting repeats require different outcomes.

WAL archiving preserves a recoverable history

Continuous WAL archiving preserves completed log files outside PostgreSQL’s active WAL directory. Together with a suitable base backup and an uninterrupted recovery chain, those files let recovery replay logged database changes to a covered target. Archiving is separate from ordinary commit acknowledgment: a transaction can commit while its WAL segment is still open or while the archive destination is unavailable. ...

A full baseline, increment 1, and increment 2 form a required chain leading to a restored database. Keep every required input.

Full versus incremental backups

A full backup provides a baseline without depending on an earlier backup from the same method. An incremental backup stores changes relative to a specified reference and depends on the material needed to reconstruct the omitted content. That distinction describes recovery dependencies, not a universal promise about speed or bytes transferred. A large changed working set can make an incremental backup substantial too. ...

A 3-2-1 design schematic puts working copy 1 and tape backup copy 2 at site A, and disk backup copy 3 at site B. Two media and one off-site location are illustrated, not measured.

Backups follow the 3-2-1 rule

The classic 3-2-1 backup rule calls for three copies of data, on two storage media, with one copy off site. The working data counts as one copy, leaving two backup copies. The rule is a starting point for separating failure exposure. It does not promise that every set of three filenames survives hardware failure, accidental deletion, credential compromise, or a mistake propagated to all copies. ...