PostgreSQL Point-in-Time Recovery with WAL Archiving

PostgreSQL point-in-time recovery (PITR) lets you restore a cluster to an exact moment — for example 14:32:07, one second before a bad DELETE. It works by combining a base backup with a continuous stream of archived write-ahead log (WAL) segments, so you can replay changes up to a chosen target instead of losing everything since the last dump.

This guide enables WAL archiving, takes a base backup with pg_basebackup, and restores the cluster to a recovery target. Commands target PostgreSQL 12 and newer on Ubuntu 24.04 or Debian 12, but the same settings apply on AlmaLinux and Rocky Linux.

Before you start

You need:

• A Linux VPS with PostgreSQL installed and running, and sudo access.
• Free disk space for the WAL archive. WAL volume scales with write traffic, so size it generously and monitor it.
• The PostgreSQL data directory path. Find it with:

sudo -u postgres psql -c "SHOW data_directory;"

• A second server or scratch directory to test the restore. Never practise a restore on the production cluster first.

Step 1 — Enable WAL archiving

Open the configuration file reported by SHOW config_file;. On Debian and Ubuntu this is usually /etc/postgresql/16/main/postgresql.conf.

Set these values:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

%p is the path of the WAL file being archived and %f is its file name. The test ! -f guard stops PostgreSQL from overwriting an existing segment. archive_timeout forces a segment switch after a period of low activity, which limits how much data a crash can cost you.

Create the archive directory with the right ownership:

sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/wal_archive
sudo systemctl restart postgresql

Restarting is required because archive_mode is not a reload-only setting. Then confirm archiving works:

sudo -u postgres psql -c "SELECT pg_switch_wal();"
sudo -u postgres psql -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"

failed_count should stay at zero and last_archived_wal should update. If archive_command exits non-zero, PostgreSQL keeps the segment in pg_wal and retries, so the directory grows until you fix it.

Step 2 — Take a base backup

A base backup is the starting point for every restore. Take one with pg_basebackup while the server is running:

sudo install -d -o postgres -g postgres /var/lib/postgresql/backups
sudo -u postgres pg_basebackup \
  -D /var/lib/postgresql/backups/base-$(date +%F) \
  -Ft -z -P -X stream

• -Ft -z writes a compressed tar archive.
• -X stream includes the WAL generated during the backup, so the archive is self-consistent.
• -P prints progress.

Record the time the backup finished and the current WAL position, so you know the earliest point you can restore to:

sudo -u postgres psql -c "SELECT pg_current_wal_lsn(), now();"

Step 3 — Reproduce the incident

To prove the setup works, create a table, note the time, then destroy it.

sudo -u postgres psql -d appdb

CREATE TABLE pitr_test (id int, note text);
INSERT INTO pitr_test SELECT g, 'row ' || g FROM generate_series(1, 100) g;
SELECT now();

Wait a few seconds, then drop the table and force a WAL switch so the change is archived:

DROP TABLE pitr_test;
SELECT pg_switch_wal();

Note the timestamp printed by SELECT now; — that is your recovery target.

Step 4 — Restore to the target time

Stop the service and move the current data directory aside:

sudo systemctl stop postgresql
sudo -u postgres mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.old
sudo -u postgres mkdir -p /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /var/lib/postgresql/backups/base-2026-01-01/base.tar.gz \
  -C /var/lib/postgresql/16/main

If the backup contains a separate pg_wal archive, extract it into the data directory too.

Create the recovery signal file. PostgreSQL 12 and newer use recovery.signal instead of the old recovery.conf:

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

Append the recovery settings to postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-01-01 14:32:00 UTC'
recovery_target_action = 'promote'

Set the time zone explicitly in the target so the server's local time zone cannot shift it. recovery_target_action = 'promote' makes the server accept writes once the target is reached, instead of pausing in recovery.

Start the server and watch the log:

sudo systemctl start postgresql
sudo journalctl -u postgresql -f

You should see lines about restoring from the archive, then a message that recovery is complete and the server has promoted.

Check it works

Confirm the cluster is no longer in recovery and that the table is back:

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -d appdb -c "SELECT count(*) FROM pitr_test;"

pg_is_in_recovery should return f, and the count should match the rows you inserted. Check the timeline advanced, which proves a promotion happened:

sudo -u postgres psql -c "SELECT timeline_id FROM pg_control_checkpoint();"

Finally, confirm archiving resumed on the new timeline:

sudo -u postgres psql -c "SELECT archived_count, failed_count FROM pg_stat_archiver;"

Once you are satisfied, remove the old data directory to free disk space.

Troubleshooting

• could not find WAL segment during recovery — the archive is missing a segment. Check failed_count in pg_stat_archiver, verify the restore_command path, and confirm the archive directory is readable by the postgres user.
• Server never promotes — recovery.signal is missing, recovery_target_action is not set, or the target time is later than the last archived WAL. Check the log for the last replayed LSN and compare it with the target.
• pg_wal grows without bound — archive_command is failing. Run it manually as the postgres user to see the error, then fix permissions or disk space.
• database files are incompatible with server — you restored a backup from a different PostgreSQL major version. Restore with the same major version, or run pg_upgrade.
• Permission denied writing to the archive — the directory is not owned by postgres. Fix with sudo chown postgres:postgres /var/lib/postgresql/wal_archive.

Next steps

• Back up and restore PostgreSQL with pg_dump and pg_restore for logical backups alongside PITR.
• Install the latest PostgreSQL from the official PGDG repository to run a current major version.
• Set the time zone and keep the clock in sync with NTP so recovery targets are accurate.
• Create PostgreSQL users, roles and databases before you restore into a fresh cluster.