The 3-2-1 rule — three copies of data, on two different media, with one copy off-site — predates databases and was originally aimed at files. Applied literally to a database, it misses a crucial nuance: a database backup isn't one thing, and the "copy" people usually take isn't the one they'll need during an actual emergency.
The rule, briefly
Three copies total (the original counts as one), across two different storage media (so a single drive failure or storage provider outage can't take out everything), with at least one of those copies physically or logically separate from the others (so a fire, ransomware incident, or account compromise affecting your primary environment doesn't also destroy your backups).
Logical dumps: simple, but incomplete
For PostgreSQL, pg_dump is the obvious starting point — a logical export of schema and data into a portable format:
A standard logical dump
pg_dump -Fc -d production -f backup_$(date +%F).dumpThis is portable across PostgreSQL versions and easy to restore selectively, but it has two real limitations: it locks you into a point-in-time snapshot with no way to recover to a moment between dumps, and on a large database it can take long enough to run that it's impractical as your only backup method.
Physical backups and WAL archiving
For anything beyond a small database, physical backups paired with continuous WAL (write-ahead log) archiving solve both problems. A tool like pgBackRest or barman takes periodic full physical backups, then continuously archives the WAL stream between them — which means you can restore to any specific point in time, not just to the moment of the last dump.
A pgBackRest restore to a specific point in time
pgbackrest --stanza=main --type=time \
--target="2026-02-14 09:15:00" restoreThis is the difference that matters most in a real incident: "restore to last night's backup" versus "restore to four minutes before someone ran the wrong migration." The second is rarely possible with logical dumps alone, and it's almost always the one you actually need.
WAL archiving without a tested retention and cleanup policy will quietly fill a disk. Set explicit retention (pgBackRest and barman both support this natively) rather than letting WAL files accumulate indefinitely.
The step everyone skips: restore testing
A backup that's never been restored isn't a backup — it's an unverified hope. The failure mode I've seen most often isn't a missing backup, it's a backup that's been running successfully for months while quietly producing a file nobody can actually restore from, often because of a permissions issue or a version mismatch that only surfaces at restore time.
I run a monthly automated restore test for every client database: spin up a temporary instance, restore the most recent backup into it, and run a basic row-count and checksum comparison against expected values. It's caught real problems twice in three years, both times silent failures that would otherwise have only been discovered during an actual emergency.
A schedule that's actually realistic
For a typical production PostgreSQL database, this is close to what I deploy by default:
| What | Frequency | Retention | Location |
|---|---|---|---|
| Physical full backup | Daily | 14 days | Local + off-site |
| WAL archiving | Continuous | 14 days | Off-site object storage |
| Logical dump | Weekly | 8 weeks | Off-site, separate provider |
| Restore test | Monthly | — | Isolated test environment |
The weekly logical dump on a separate provider from the physical backups is the "two different media, one off-site" requirement made concrete — if the primary backup provider has an outage or account issue, the logical dumps are a slower but independent fallback that doesn't share a failure domain with anything else.