TopolearnPITR is how you recover from 'someone ran DELETE without WHERE at 14:03' with minutes of data loss.
Every change is written to the write-ahead log (WAL) before data pages, in 16 MB segment files. Checkpoints periodically flush dirty pages so older WAL can be recycled. That same log enables point-in-time recovery (PITR):
1. Take a base backup, a physical copy of the data directory made while the server runs: pg_basebackup -D /backups/base -Ft -z -X stream. PostgreSQL 17 adds incremental base backups (--incremental, combined later with pg_combinebackup). 2. Archive WAL continuously: set wal_level = replica, archive_mode = on and an archive_command (or archive_library) that copies each finished segment to safe storage. 3. To recover, restore the base backup, set restore_command to fetch archived segments, set recovery_target_time = '2026-05-03 14:02:00+00', create recovery.signal, and start the server. It replays WAL up to the target and stops, then you promote it.
Tools such as pgBackRest, Barman and WAL-G handle the details: parallel and incremental backups, compression, encryption, retention, and verification. Use one rather than hand-written scripts.
Key settings: archive_timeout bounds how much WAL (and so data) sits un-archived on a quiet server. Monitor pg_stat_archiver for failures — a failing archive_command makes WAL pile up until the disk fills.
Physical backups are tied to the major version and the whole cluster; keep logical dumps too for portability.
Example
# postgresql.conf
# wal_level = replica
# archive_mode = on
# archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# archive_timeout = 60
pg_basebackup -h db1 -U replicator -D /backups/base_2026_05_03 -Ft -z -X stream -P
# --- disaster: bad DELETE at 14:03, restore to 14:02 on a new server ---
tar -xzf /backups/base_2026_05_03/base.tar.gz -C /var/lib/postgresql/18/main
cat >> /var/lib/postgresql/18/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-05-03 14:02:00+00'
recovery_target_action = 'pause'
EOF
touch /var/lib/postgresql/18/main/recovery.signal
pg_ctl -D /var/lib/postgresql/18/main start
# inspect the data, then: SELECT pg_wal_replay_resume(); to finish and promoteRemember
- Base backup + archived WAL = restore to any point in time
- Use pgBackRest, Barman or WAL-G rather than custom scripts
- Monitor pg_stat_archiver; a broken archive_command fills the disk
Watch out for
- Archiving WAL without ever doing a test recovery to a target time.