Backups and Point-in-Time Recovery in PostgreSQL

Replication protects you from a server dying. It does not protect you from a bad DELETE, a botched migration, an application bug that corrupts data, or ransomware, because replication faithfully copies all of those to your replicas within milliseconds. For those, you need backups, and specifically backups good enough to restore to the moment just before the damage. Point-in-time recovery is what makes that possible, and knowing how it works is the difference between losing an hour of data and losing everything.

This is part 36 of the PostgreSQL Masterclass, following high availability and failover.

Why replication is not a backup

This deserves stating plainly because the confusion causes real data loss. A replica mirrors the primary continuously. If you run DELETE FROM orders without a WHERE clause on the primary, that delete replicates to every replica in under a second. Your replicas are now just as empty as your primary. Replication is for availability, not for recovering from mistakes. You need backups that capture a past state you can return to.

Logical backups: pg_dump

The simplest backup is a logical dump: pg_dump writes out the SQL (or a custom archive) needed to recreate a database. It's straightforward and portable across Postgres versions:

pg_dump -Fc -d appdb -f appdb_2024_04_16.dump   -- custom-format archive
pg_restore -d appdb_restored appdb_2024_04_16.dump

Logical dumps are great for small-to-medium databases, for copying a database between environments, and for version upgrades. Their limits show at scale: a dump of a large database is slow, takes a consistent snapshot only as of when it started, and you can only restore to the moment the dump was taken. If your nightly dump runs at 2am and disaster strikes at 4pm, you've lost 14 hours. For anything sizable or important, dumps alone aren't enough.

Physical backups plus WAL: the basis of PITR

Point-in-time recovery combines two things: a physical base backup (a copy of the actual data files) and the continuous archive of WAL, the write-ahead log that records every change. With a base backup from Sunday and every WAL segment since, you can restore Sunday's files and then replay the WAL forward to any exact moment you choose, say 3:59pm, one minute before the bad delete.

The mechanism:

1. Take periodic base backups with pg_basebackup or a tool built on it.
2. Continuously archive WAL as it's generated, by setting archive_mode and an archive_command (or using a backup tool that does it).
3. To recover, restore a base backup and configure a recovery target, then Postgres replays WAL up to that point and stops.

# recovery target: replay up to just before the incident
recovery_target_time = '2024-04-16 15:59:00'

That ability to name a target time and stop there is the whole value. You lose only what happened after your chosen point, which you set to just before the damage.

Use a backup tool, not hand-rolled scripts

Configuring base backups, WAL archiving, retention, and recovery correctly by hand is fiddly and easy to get subtly wrong, and a backup you can't restore is worse than no backup because it gives false confidence. Purpose-built tools handle it:

• pgBackRest and Barman are the leading open-source backup managers. They handle base backups, WAL archiving, retention policies, compression, encryption, and (critically) tested restores, and they support PITR out of the box.
• Managed services (RDS, Aurora, Cloud SQL) do continuous backup and PITR automatically. You pick a retention window and can restore to any second within it with a few clicks.

Whether you use a tool or a managed service, don't assemble PITR from raw shell scripts unless you have a strong reason. The tools exist precisely because the naive version has too many failure modes.

The rule everyone ignores: test your restores

A backup you have never restored is a hypothesis, not a backup. The single most common backup failure is discovering during a real incident that the backups were incomplete, corrupted, missing WAL, or that nobody knows the restore procedure. The fix is boring and essential: restore your backups regularly, to a scratch server, and verify the data is intact. Automate it if you can. A restore drill once a quarter turns "we think we have backups" into "we know we can recover in 40 minutes," and that knowledge is the actual product of a backup strategy.

Retention, offsite, and encryption

A few more essentials, briefly. Keep multiple backups over time, not just the latest, so you can recover from damage that went unnoticed for days. Store backups somewhere separate from the database, ideally a different region or provider, so a single failure or account compromise can't take both. And encrypt backups, since they contain all your data, they deserve the same protection as the live database.

Backups are the safety net under everything else in this series. Replication and HA keep you running through hardware failure; backups and PITR bring you back from human error, bugs, and corruption. Take physical backups with continuous WAL archiving through a real tool or managed service, set retention consciously, store copies offsite and encrypted, and (the part that actually matters) test restores until you trust them. That's what lets you recover to the minute before disaster instead of hoping.

Next, we return to day-to-day operations and tune the autovacuum system we've referenced throughout, so it keeps up with a busy production database.