PostgreSQL Point-in-Time Recovery with WAL Archiving

Set up WAL archiving and restore a PostgreSQL cluster to an exact second with point-in-time recovery, using pg_basebackup, archive_command and a recovery target.

  • Advanced
  • Linux VPS
  • 6 min read

By RunRacks Team · Published

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:
Terminal
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:

INI
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:

Terminal
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:

Terminal
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:

Terminal
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:

Terminal
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.

Terminal
sudo -u postgres psql -d appdb
SQL
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:

SQL
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:

Terminal
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:

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

Append the recovery settings to postgresql.conf:

INI
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:

Terminal
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:

Terminal
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:

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

Finally, confirm archiving resumed on the new timeline:

Terminal
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

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read