A backup is a recoverable copy of PostgreSQL data, stored independently of the system it protects. It is insurance against server loss, corruption, accidental changes, and malicious damage—but only if it can be restored. RAID1 mirroring and streaming replication add redundancy, yet they can reproduce the same corruption or unwanted change. They are not substitutes for a backup. section 9.1 section 9.4
A common mistake is treating a disk mirror, standby, or filesystem snapshot as a database backup. RAID duplicates filesystem corruption. A standby can receive damaging changes. PostgreSQL also depends on synchronized WAL, buffers, transaction state, and segmented data files, making file-level copies unsafe unless the snapshot is atomic across the filesystem. section 9.1
A dump is recoverable, but not point-in-time recovery
pg_dump creates a consistent snapshot of one database without stopping operations, and that dump can be restored into an empty database. pg_dumpall includes cluster-wide global objects, but neither approach guarantees consistency between running interdependent databases. More importantly, a dump restores only to its backup time: a DROP TABLE after that backup cannot be undone by restoring the dump without also losing later valid transactions. Point-in-Time Recovery (PITR) with continuous WAL archiving is needed to recover to the point before damage. section 9.2 section 9.7
A durable backup practice avoids several operational traps:
- Use PostgreSQL-appropriate backup tools. pg_basebackup is identified for full backups; dedicated PostgreSQL-aware tools such as Barman or pgBackRest are preferred over homegrown scripts. section 9.7
- Keep copies away from the database hardware and filesystem. A backup under PGDATA on the same physical drive can disappear with the server; retain a redundant second copy, preferably in another location. section 9.3 section 9.7
- Automate regular, non-interactive backups. Manual work varies with people, schedules, absences, and undocumented knowledge; automation removes that dependency. section 9.3
- Pair base backups with WAL retention for PITR. Full backups alone leave exposure to every transaction committed after the latest backup. section 9.7
- Verify the entire recovery path by restoring into a testing environment. A successful backup command does not prove that its output, storage, or restore command will work during an incident. section 9.4 section 9.7
Testing is a recovery fire drill, not a cosmetic check. A backup can be unusable because its command was invoked incorrectly, its storage was corrupted, its destination lost the file, or the restore command fails. Restoring in a test environment validates both the backup and the recovery procedure before an emergency makes either failure catastrophic. section 9.4
Figure 9.2 A sample PostgreSQL backup setup with a dedicated Barman server and geographical redundancy showing the possible transfer paths
This layout illustrates a dedicated Barman backup server and geographically redundant transfer paths, reinforcing that backup copies should not share the database server’s failure domain. section 9.3
Backups belong in proactive operations. Establish and test the strategy before data loss, rather than improvising it after a failure. Automation does not remove accountability: verify each stage, including storage and a full restore. section 9.7 section 11.2
