WAL and PITR: When a pg_dump Is Not Enough

pg_dump is a photograph. WAL is the film of every change after the photograph. If your RPO is a day, a nightly dump is enough. If a bad migration at 15:12 should be undone to 15:11, you need point-in-time recovery. PITR is a base backup plus a continuous archive of WAL segments plus a restore that stops at a timestamp. It is more moving parts than pg_dump. It is the difference between losing a day and losing a minute.

PostgreSQL 16 on Ubuntu already writes WAL into pg_wal inside the data directory. That is crash recovery for the same disk. PITR needs those files copied somewhere that survives the data disk.

Turn on archiving before you need it

wal_level must be replica or higher. archive_mode must be on. archive_command must copy a completed segment to a directory that is not the data volume. Test the command by itself. A command that returns non-zero stalls archiving and eventually fills pg_wal.

# /etc/postgresql/16/main/conf.d/wal.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/pgwal_archive/%f && cp %p /var/lib/pgwal_archive/%f'
archive_timeout = 60s
max_wal_senders = 3

%p is the pathname of the file to archive. %f is the name. The test ! -f guard avoids overwriting. archive_timeout of 60 seconds ships a partial segment so a quiet database still has a recent archive. Restart after archive_mode changes.

sudo mkdir -p /var/lib/pgwal_archive
sudo chown postgres:postgres /var/lib/pgwal_archive
sudo systemctl restart postgresql@16-main
sudo -u postgres psql -c "SHOW archive_mode;"
sudo -u postgres psql -c "SELECT pg_switch_wal();"
ls -l /var/lib/pgwal_archive | tail

If the archive directory stays empty, archive_command failed. Read the PostgreSQL log. Permission denied is the usual cause. Do not point archive_command at /var/lib/postgresql/16/main. That is the disk you are trying to survive.

Ship the archive off the VPS the same as dumps. A second High-Speed SSD volume on the same machine survives an OS-disk fill; it does not survive a VPS you deleted. Copy the archive directory off-box.

Take a base backup, not only WAL

WAL without a starting point cannot restore. pg_basebackup copies the data directory while the cluster runs and includes the WAL needed to make that copy consistent.

sudo -u postgres pg_basebackup -D /var/backups/pgbase/$(date +%F)   -Ft -z -P -X none --checkpoint=fast
# keep /var/lib/pgwal_archive growing after this

-Ft -z writes a tar plus gzip. -X none because you already archive WAL yourself. Store the base backup next to dumps, off the data disk. Take a new base backup daily or weekly. Restore time is base plus every WAL segment after it, so an old base plus a month of WAL is a long replay.

Restore to a timestamp

The drill is: stop the target, replace its data directory with the base backup, configure restore_command to fetch WAL from the archive, set recovery_target_time, create recovery.signal, start.

sudo systemctl stop postgresql@16-main
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken
sudo mkdir /var/lib/postgresql/16/main
sudo tar -xzf /var/backups/pgbase/2026-06-07/base.tar.gz -C /var/lib/postgresql/16/main
# also extract pg_wal from the backup if present
echo "restore_command = 'cp /var/lib/pgwal_archive/%f %p'" | sudo tee /var/lib/postgresql/16/main/postgresql.auto.conf
echo "recovery_target_time = '2026-06-07 15:11:00+05:30'" | sudo tee -a /var/lib/postgresql/16/main/postgresql.auto.conf
echo "recovery_target_action = 'promote'" | sudo tee -a /var/lib/postgresql/16/main/postgresql.auto.conf
sudo touch /var/lib/postgresql/16/main/recovery.signal
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo systemctl start postgresql@16-main
sudo journalctl -u postgresql@16-main -e

recovery_target_time is timezone-aware. Use the same zone the cluster logs in. When replay hits the target, promote makes the cluster writable. Query the data. If you overshot, you need a fresh copy of the base; you cannot rewind a promoted cluster.

Do this on a spare VPS, not on the only copy of production. PITR consume the base backup files; keep an extra copy.

pg_dump still earns its keep. Logical dumps are easier to restore onto a new major version. PITR is for the window between dumps and for the moment someone ran DELETE without WHERE. Run both. Dump nightly. Archive WAL continuously. Drill PITR on a spare box until the commands are boring.

Know when dump is enough. A blog, a staging app, or a dataset you can rebuild from an upstream has a 24-hour RPO and a restore that is one pg_restore. A payments ledger, an order table, or anything you cannot reconstruct from another system has an RPO measured in minutes. That is WAL. The archive must leave the VPS. A second High-Speed SSD volume on the same machine survives an OS-disk fill; it does not survive a deleted instance. Copy /var/lib/pgwal_archive off-box the same way you copy dumps, and check that new files appear after pg_switch_wal. If they do not, you do not have PITR, you have a config line.

Watch pg_wal on the data volume. Failed archive_command leaves segments behind, the directory grows, and PostgreSQL will eventually stop writes. df -h on the data mount belongs in the same morning check as the dump audit. archive_timeout of 60 seconds is how a quiet database still ships a recent partial segment instead of waiting for 16 MB of traffic that may not come until the next morning.

Takeaway

When losing a day of writes is unacceptable, add WAL archiving and a base backup, then practice restore to a timestamp. A Lucknow DC01 Ubuntu VPS on Netbay comes up in under 60 seconds and is the right place to rehearse PITR before you need it — netbayhosts.in.