PostgreSQL PITR: Validating WAL Archiving and Recovery Paths
Configure WAL archiving, manage pgBackRest retention, and validate point-in-time recovery paths for PostgreSQL. A practical DBA guide based on official...
29 Sept 2026 · 4 min read
Configure WAL archiving, manage pgBackRest retention, and validate point-in-time recovery paths for PostgreSQL DBAs.
WAL Archiving Configuration Requirements
Point-in-time recovery (PITR) requires a continuous sequence of archived Write-Ahead Log (WAL) files. PostgreSQL stores the WAL in the pg_wal/ subdirectory and records every change to database data files. The system must capture each WAL segment file once it is full and save that data before recycling the segment for reuse.
Set specific parameters in postgresql.conf to enable this mechanism. The wal_level parameter must be replica or higher, and archive_mode must be on. Specify the shell command in the archive_command parameter or the library in the archive_library parameter. The archive_command supports flexible storage destinations, such as an NFS-mounted directory or a tape drive. Use %p for the file path and %f for the file name.
Set wal_level to replica or higher to enable WAL archiving capabilities.
Set archive_mode to on to activate the archiving process.
Define archive_command with %p for the file path and %f for the file name.
Ensure the archive destination is accessible and distinct from the primary data directory.
pgBackRest Backup Types and Retention Logic
pgBackRest manages PostgreSQL backups using full, differential, and incremental types. A full backup copies the entire database cluster and is the only type that restores independently. The first backup of a cluster in pgBackRest is always a full backup.
Differential backups copy only files changed since the last full backup. They use less disk space than full backups but require the corresponding full backup to remain valid for restoration. Incremental backups copy only files changed since the last backup of any type. These are generally much smaller but depend on the entire chain of prior backups back to the most recent full backup. If any backup in the chain is invalid, the incremental backup cannot restore.
Full backups are self-contained and do not depend on external files for consistency.
Differential backups depend on the validity of the preceding full backup.
Incremental backups depend on all prior backups back to the last full backup.
Retention policies must account for these dependencies to ensure restorability.
Recovery Mechanics and Consistency
Recovering from a continuous archive backup involves restoring the file system backup and replaying the backed-up WAL files to reach a current state. This approach does not require a perfectly consistent file system backup as the starting point. Log replay corrects any internal inconsistency in the backup, similar to crash recovery.
This method allows you to stop WAL replay at any specific point in time. The database administrator can restore the database to its state at any time since the base backup, provided the necessary WAL segments are available. This capability supports precise point-in-time recovery, which is critical for mitigating logical errors or data corruption that occur after a transaction has been committed.
Log replay corrects internal inconsistencies in the base backup.
Replay can be halted at any specific timestamp to achieve a consistent snapshot.
PITR requires a continuous sequence of WAL files extending back to the base backup start time.
Version Compatibility and Protocol Integrity
Version consistency between local and remote components is critical for operational stability when using pgBackRest. The local and remote pgBackRest versions must match exactly. WAL archiving and backups will not function until the versions are aligned if there is a mismatch.
A specific protocol error reports a mismatch, indicating that the expected version value does not match the received value. This error prevents the system from proceeding with archiving or backup operations, ensuring that data integrity is not compromised by incompatible protocol versions. Upgrade local and remote binaries together to maintain this integrity.
Local and remote pgBackRest versions must match exactly.
Version mismatches result in a ProtocolError and halt archiving operations.
Upgrade local and remote binaries simultaneously to avoid protocol errors.
Operational Caveats and Storage Considerations
Continuous archiving requires significant archival storage. The base backup may be bulky, and a busy system will generate many megabytes of WAL traffic that must be archived. This method is preferred in situations where high reliability is needed, but it demands careful capacity planning.
pg_dump and pg_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution. These tools create logical dumps that do not contain enough information to be used by WAL replay. Continuous archiving supports the restoration of an entire database cluster, not a subset of databases. Plan for the storage overhead associated with maintaining a continuous sequence of WAL segments.
Continuous archiving requires substantial storage for base backups and WAL segments.
Logical dumps from pg_dump cannot be used for WAL replay in PITR.
PITR restores the entire database cluster, not individual databases.
Talk to Worlber
Planning a PostgreSQL migration, PGEE deployment, or production database platform? Speak with Worlber Database Services.
Sources
PostgreSQL: Documentation: 18: Chapter 25. Backup and Restore