Recovery numbers you can measure

You met RPO and RTO in Backups and disaster recovery. As the DBA, your job is to turn those targets into numbers you have measured. For Mangrove's order database the business has agreed on targets for a bad change (someone deletes or corrupts data): lose at most 5 minutes of orders, and be back within 2 hours.

Your measured RPO is the age of the newest file in the WAL archive. The order database is about 800 GB and changes all day, so a 16 MB WAL segment fills in seconds at the evening peak. At 4 a.m. it fills slowly, so archive_timeout = '60s' forces a switch at least once a minute. If archiving breaks, the RPO grows every minute until someone notices. That is why archive age is an alert, as you will see below.

Your measured RTO is the sum of every step, end to end. A restore is a chain of slow steps that cannot overlap much:

• Notice and decide. Someone has to see the problem, find the time of the mistake and choose to restore. Often 15 to 30 minutes.
• Fetch the base backup. 800 GB from object storage at roughly 250 MB/s is about 55 minutes. Parallel transfer and compression help a lot. Network speed sets the limit.
• Replay the WAL. Every WAL file since the base backup is replayed, one change after another, by a single recovery process. The order database grows only about 1.5 GB a day, but it writes far more WAL than that, roughly 30 GB on a normal day, because every update, every index change and the first change to each page after a checkpoint are all logged. Six days since the last full backup means about 180 GB of WAL to replay.
• Check the data and switch the app. Row counts, the newest order time, a smoke test, then pointing the API at the new server.

Before the drill, everyone guessed "about an hour". The guess left out WAL replay, which depends on how old the newest base backup is. So the backup schedule sets your RTO as much as the restore tools do.

pg_dump done properly: what a dump holds and what it leaves out

A logical dump is your second, independent kind of backup: it can restore one table, move data to another major version, and does not depend on the WAL chain. To make one that restores completely on an empty server, you need to know four things.

Pick the right format. The custom format (-Fc) is one compressed file. The directory format (-Fd) writes one file per table into a folder. Only the directory format lets pg_dump itself run in parallel with -j. Both formats let pg_restore -j restore in parallel, and both let you list the contents with pg_restore -l and restore one table with -t. A plain .sql file can do none of that.

Know what lives outside the database. pg_dump saves one database. Roles, their passwords and tablespaces belong to the whole server, so they are not in it. Dump them separately with pg_dumpall --globals-only and restore that file first, or every GRANT in the dump fails with "role does not exist". Settings made with ALTER DATABASE … SET and grants on the database itself are only included when you dump with --create. The configuration files (postgresql.conf, pg_hba.conf) are in no dump at all. Neither are extension binaries: the restore server needs the same extensions installed.

A dump holds a snapshot open. pg_dump reads everything inside one transaction, so the dump is consistent. For the 2 or 3 hours a full dump of the order database takes, that open snapshot stops VACUUM from removing dead rows anywhere in the database (Running Postgres in production explains why). Run big dumps from a replica when you can, or at quiet hours.

A dump takes locks. It holds a shared lock on every table it dumps until it finishes. Reads and writes carry on, but any ALTER TABLE must wait, and every query that arrives after the waiting ALTER queues behind it (Changing the schema without downtime). Do not schedule dumps and migrations at the same time. --lock-wait-timeout makes the dump give up instead of waiting forever for its locks at the start.

Base backups and the archive contract

For point-in-time recovery you need a physical base backup plus every WAL file after it. pg_basebackup copies the data directory while the server runs. By default (-X stream) it also streams the WAL written during the copy, so the backup can be made consistent. Since PG 13 it writes a backup manifest, and pg_verifybackup checks the files against it (which backup formats it can check is version dependent). PG 17 added incremental base backups: pg_basebackup --incremental copies only blocks changed since an earlier backup, needs summarize_wal = on, and pg_combinebackup rebuilds a full copy before a restore.

The WAL side is a contract between PostgreSQL and your archive_command (or an archive_library module, PG 15 and later). When a segment is full, PostgreSQL writes a small .ready marker for it. The archiver process runs your command for that file. Your command must return exit code 0 only when the file is safely and durably stored, and it must never overwrite a different file with the same name. On exit 0, PostgreSQL marks the segment .done, and a later checkpoint may recycle it. On any other exit code, PostgreSQL waits and tries the same file again, forever. It never skips a file, because one gap would break every restore after it.

This is why a backup problem can take production down. If the archive bucket's credentials expire on Friday evening, nothing breaks at first. WAL piles up in pg_wal, roughly 30 GB a day on the order database, until the disk is full and PostgreSQL stops accepting writes. Two good habits follow. Never "fix" a stuck archive by deleting files from pg_wal; repair the command and let it catch up. And never use a command that returns 0 without really storing the file, such as archive_command = '/bin/true', on a server whose backups you rely on: every restore will hit a gap.

Point-in-time recovery: picking the backup, the WAL and the target

A PITR restore needs three choices, made before you touch any server.

The base backup. Take the newest successful base backup that finished before your target time. A backup that started before the target but finished after it cannot be used: PostgreSQL cannot stop recovery at a moment when that backup was still being copied, because the copy is not consistent until its end point.

The WAL range. You need every segment from the one where the backup started to the one that contains the target time, with no gaps. Check this before you start. Discovering a missing file two hours into WAL replay costs you the whole restore. If a file is missing, look for it in the second repository or in pg_wal on the old primary before you begin.

The target. recovery_target_time is the usual choice. You can also stop at a transaction ID (recovery_target_xid), a WAL position (recovery_target_lsn) or a named restore point (recovery_target_name). Only one target may be set. Restore points are a cheap habit before risky changes: run SELECT pg_create_restore_point('before_0042') before migration 0042, and if it goes wrong you can stop recovery exactly there. recovery_target_inclusive (on by default) decides whether a transaction that commits exactly at the target is kept. recovery_target_action decides what happens at the target: pause (the default) lets you check the data read-only first, promote ends recovery and opens for writes, shutdown stops the server.

When recovery ends, the server starts a new timeline and writes a small history file to the archive. If your first restore stopped too late and you restore again from the same archive, the timelines keep the two histories apart. Since PG 12 the default recovery_target_timeline = 'latest' follows the newest branch. Leave the default unless you know you want an older branch.

Tools, monitoring and the monthly drill

Hand-written cp archiving is fine for learning. In production a backup tool does the hard parts. pgBackRest, WAL-G and Barman all take base backups (full plus differential or incremental), archive WAL in parallel, compress it, encrypt it, and copy it to object storage or a backup server. Each also enforces retention: when an old full backup expires, the tool also deletes the WAL that only that backup needed, and keeps everything a newer backup still depends on. Each can also verify what it stored: pgBackRest has check and verify commands, and Barman has check. The choice matters less than running one of them, configured, monitored and restored from.

Backups fail quietly, so alert on what you can measure. Four signals cover most failures:

• Archive age: now minus last_archived_time from pg_stat_archiver. Alert when it passes your RPO.
• Archiving failing now: last_failed_time newer than last_archived_time. A non-zero failed_count alone may be an old, recovered failure.
• Backlog: the number of .ready files waiting, from pg_ls_archive_statusdir() (PG 12 and later), and the size of pg_wal.
• Base backup age: the newest successful base backup should be under about 26 hours old with nightly backups. Alert on silence, not only on errors: a cron job that never starts prints no error.

Then comes the part most teams skip: the restore drill. Once a month, restore the order database to a chosen minute from yesterday onto a scratch server, with a clock running. Check row counts and the newest order time against production. Write down the backup used, the time of each step, the total, and what surprised you. Compare the total with the 2-hour target, fix the biggest gap, and repeat next month. The written record is your evidence: it is what you show an auditor, and what the next on-call engineer reads at 3 a.m.

On a managed service (RDS, Cloud SQL, Azure Database for PostgreSQL) the provider takes base backups, archives WAL and offers PITR to any moment in a retention window that you set, up to a provider maximum. A PITR restore there creates a new instance with a new address. Several jobs stay yours: choosing the retention, turning on a copy in another region or account if you need one, keeping logical dumps for long-term or exit needs, and timing restores, because the provider does not promise how long a restore of 800 GB takes.