Backup and disaster recovery

RPO and RTO: the two numbers a backup plan is built around

Recovery point objective (RPO) is how much data you can afford to lose, measured as a time window. If the last usable backup is from 02:00 and the database dies at 18:00, you have lost up to 16 hours of writes. Recovery time objective (RTO) is how long you can be down while you restore. Every backup and disaster-recovery decision trades money against one of these two numbers: a nightly dump is cheap and gives an RPO of a day; continuous archiving costs more and buys an RPO of minutes.

Wrong "We take consistent snapshots, so we do not lose data." A snapshot is consistent as of the moment it ran. Everything written after it is gone unless something else captured it. Consistency means you will not restore a half-written state. It says nothing about how recent that state is.

Logical vs physical backups

Two families, and interviewers want to know when each fits.

A logical backup exports the data as statements or a portable archive. In Postgres that is pg_dump for one database and pg_dumpall for cluster-wide objects like roles and tablespaces. It runs against a live server on an MVCC snapshot, so it does not block other clients, and its output can usually load into a newer major version, which makes it the tool for upgrades and moving data between machines.

pg_dump -Fc mydb > mydb.dump       # custom format, compressed
pg_restore -j 4 -d mydb mydb.dump  # restore with 4 parallel workers

A physical backup copies the actual data files (a base backup). It is tied to the exact server version and CPU architecture, and it is the basis for point-in-time recovery. Rule of thumb: logical for portability and selective restores, physical for fast full-cluster restores and PITR.

Wrong "pg_dump backs up the whole cluster including the users." pg_dump dumps one database and none of the global objects. Roles, tablespaces, and their grants come from pg_dumpall --globals-only. Restore a dump without them and the app has no login to connect with.

A backup you have never restored is not a backup

The dump job exiting 0 tells you the job ran, not that the file restores. Dumps complete and still turn out unrestorable: a table missing because of a permissions gap, a corrupt file, a dependency that fails to load. The only proof is a restore.

Put a restore drill on a schedule. Pull the latest backup into a throwaway environment, restore it, and check something real: row counts on the big tables, a few known records, that the application connects and reads its data. Time it, because that number is your actual RTO, and it is usually longer than anyone guessed.

Keep backups out of production's blast radius

If the credentials that run your app can also delete your backups, then one leaked key or one ransomware payload takes both, and modern ransomware hunts for the backups first. So the backup destination should use credentials separate from production and, ideally, live in a separate account. Cloud object stores add a write-once-read-many lock (S3 Object Lock and its equivalents) so a stored copy cannot be deleted or overwritten until its retention expires, even by an administrator. That immutable copy is the one you restore from when everything reachable with live credentials has been encrypted.

Point-in-time recovery: base backup plus replayed WAL

PITR is the physical-backup path to a chosen second. You keep a base backup (from pg_basebackup) and a continuous archive of WAL segments, configured with archive_mode = on and an archive_command or archive_library that copies each completed segment somewhere durable. To recover, you restore the base backup, tell Postgres how to fetch archived WAL with restore_command, set a target, and let it replay forward. This is why write-ahead logging is the prerequisite: the same log that makes a crash recoverable is what you replay to roll a restored snapshot up to 15:46, one second before a bad migration.

Targets can be a timestamp (recovery_target_time), an LSN (recovery_target_lsn), or a named restore point. Two facts trip people. The stop point must be after the base backup finished; you cannot recover into a moment while the backup was still running. And each recovery starts a new timeline, so you do not clobber the history you branched from. RPO here is bounded by how fresh the archive is: a segment ships when it fills (16 MB by default), so set archive_timeout to force a switch every N seconds when you need a tighter bound. Config files (postgresql.conf, pg_hba.conf) are not in the WAL, so back them up on their own.

Wrong "A logical pg_dump can be rolled forward to any timestamp with recovery_target_time." Only a physical base backup plus an archived WAL stream supports PITR. A dump is a single point; the recovery-target settings do nothing with it.

Verifying a restore, and why a green check is not enough

pg_verifybackup checks a base backup against its backup_manifest: the right files are present, their checksums match, and the WAL needed for recovery parses. Run it. But the documentation is explicit that this cannot include every check a running server performs, and that you should still perform test restores and confirm the databases contain the correct data. A server bug can emit WAL records with valid checksums that describe nonsensical actions; the checksum passes and the replay still breaks.

So verification has levels, and only the top one settles anything: files exist, checksums pass, a single table restores, a full restore starts and serves queries, and the application reads its own data back. Automate a restore into a scratch instance on a schedule and assert on real rows. The run gives you two things nothing else does: proof the backup restores, and a measured RTO instead of a guessed one.

Keeping backups immutable and out of production's blast radius

Treat the backup store as a separate security domain from production. If your app's IAM role can write the backups, it can usually delete them, so a leaked credential or ransomware that reaches that role destroys the recovery path along with the data. Two moves address this. Separate the credentials and the account so the backup destination is not reachable with production's keys. And make the stored copies immutable: S3 Object Lock and its equivalents enforce write-once-read-many, and in compliance mode no user, including the account root, can delete or overwrite an object before its retention expires. The only way past it is deleting the entire account.

This is the modern shape of the old 3-2-1 rule (three copies, two media types, one offsite), now often written 3-2-1-1-0: add one immutable or offline copy and require zero verification errors. The immutable copy is the one you restore from after an attacker has held your live credentials.

HA and DR are different problems

High availability keeps you serving through a component or instance failure inside one facility: a standby in the same region, automatic failover in seconds. Disaster recovery covers losing the facility or region, or losing the data itself to corruption or a bad deploy, and recovering somewhere geographically separate. A same-region replica is HA, not DR, for two reasons: it sits in the region's blast radius, and it faithfully replicates a DROP TABLE or a corrupt page to the standby the instant it happens. Replication and failover mechanics are their own topic; here the load-bearing point is only that neither a replica nor a failover is a backup.

AWS frames DR as four strategies, ordered by cost and by how fast they recover:

The catch AWS states plainly: even active/active does not give a zero RPO for a data disaster. Corruption, a bad migration, or a malicious delete replicates everywhere, so recovering from that class of event still means going back to a point-in-time backup taken before the damage. Every tier above still rests on tested, immutable backups.