PostgreSQL Backups and PITR: WAL Archiving, Native Incremental Backups, pgBackRest and Restore Drills

A practical guide to the system that only matters on the day everything goes wrong: why RPO and RTO decide your architecture before any tool does, what pg_dump does and does not do (and the three omissions that ruin a restore: roles, extensions, and the cost of rebuilding indexes), continuous WAL archiving and the merciless contract of archive_command — the command that returns 0 without copying, and the one that fails until pg_wal fills the disk and takes production down — real monitoring with pg_stat_archiver, base backups with pg_basebackup and why --wal-method=stream makes the copy self-contained, the low-level API renamed in PostgreSQL 15 (pg_backup_start/pg_backup_stop, exclusive mode gone), verification with pg_verifybackup and its tar support in PostgreSQL 18, native incremental backups since PostgreSQL 17 with summarize_wal, wal_summary_keep_time and pg_combinebackup, including the indivisible-chain trap that turns six incrementals into unreadable garbage if you delete the full backup they depend on, a complete step-by-step PITR with recovery.signal, recovery_target_time and why recovery_target_action = pause keeps you from cementing the mistake, timelines and the .history files almost nobody archives, pgBackRest 2.59 with an encrypted S3 repository, block-level incremental backup, delta restore and the check command, the 3-2-1-1-0 rule with Object Lock and an IAM policy that stops production from deleting its own backups, and an automated restore drill in Python that checks content — not just that the instance starts — and exports your real RTO as a Prometheus metric. With eight recurring mistakes, a production checklist and an FAQ. Extended edition: server-side versus client-side compression with parallel zstd and measuring real throughput with --target=blackhole, parallel dump and restore and the single-large-table limit, the backup that faithfully copies your corrupted data (checksums on by default in PostgreSQL 18, pg_checksums, online enabling in PostgreSQL 19 and pg_amcheck against the restored copy), finding the exact instant with pg_waldump and recovering by XID or LSN instead of guessing a timestamp, surgical single-table restore with a throwaway instance and explicit SQL reconciliation, the replication slots that fill pg_wal (max_slot_wal_keep_size and PostgreSQL 18's idle_replication_slot_timeout), pg_rewind for bringing the old primary back without copying three terabytes and why it is not a backup, the arithmetic of retention and the repo1-retention-archive trap, repository encryption and the break-glass procedure that breaks the key's circular dependency, managed services and the cross-region or cross-account copies that lose PITR, a full minute-by-minute case study with a real 54-minute RTO, and a glossary.