PostgreSQL Backups That Restore: Base Backups, WAL, and Point-in-Time Recovery
Date Published
Design from recovery objectives
Backup frequency is not the starting point. Define how much data the business can lose and how long recovery may take. The recovery point objective drives WAL archival and backup frequency. The recovery time objective drives backup size, storage throughput, automation, and whether a warm standby or cross-region copy is needed.
Combine base backups with WAL
A physical base backup provides a consistent starting point. Archived WAL records every subsequent change and enables recovery to a selected time, transaction, or restore point. Monitor archive_command success, failed_count, last archived WAL, and archive delay. A missing WAL segment can break the recovery chain even when base backups are intact.
Apply the 3-2-1 rule thoughtfully
Keep at least three copies of important data, on two different storage systems, with one copy isolated from the primary environment. Isolation matters more than geography when ransomware or operator error can delete both production and backups with the same credentials. Encrypt backup data, restrict deletion, and separate backup administration from database administration where practical.
Back up the surrounding system
Database files alone do not recreate the service. Preserve roles and grants, extensions, PostgreSQL and Patroni configuration, TLS material, encryption-key procedures, backup-tool configuration, monitoring definitions, and infrastructure code. Store TDE keys separately from encrypted backups, with an escrow and rotation process that is itself tested.
Restore on a schedule
Automate an isolated restore of the newest backup, replay WAL to a defined target, start PostgreSQL, and run consistency and application checks. Record elapsed time and investigate drift from the objective. Periodically perform a full disaster-recovery exercise in which operators follow the written runbook without relying on undocumented knowledge.