How to set up PostgreSQL point-in-time recovery with WAL archiving

When a nightly pg_dump is not enough

A pg_dump taken at 02:30 restores the database as it was at 02:30. Everything written after that is gone. PostgreSQL also records every change in its write-ahead log (WAL), in 16 MB segment files under pg_wal/. Keep a copy of the data directory plus every segment written since, and you can replay the log to any moment after the copy.

Most servers want both: dumps for single databases and upgrades, PITR for undoing the last hour. WAL replays only onto a physical copy, never onto a pg_dump.

Write an archive_command that cannot lose WAL

PostgreSQL runs archive_command once per completed segment, with %p as its path and %f as its file name. Exit 0 tells PostgreSQL the file is safe to recycle, so the command must exit non-zero on any failure and never overwrite a different file of the same name (two servers sharing one archive). After a crash PostgreSQL may re-send a segment it already archived, so an identical existing file must count as success. The documentation's one-liner, test ! -f /archive/%f && cp %p /archive/%f, fails that last case and never flushes the copy to disk. Create the directories, then this script:

sudo install -d -o postgres -g postgres -m 700 /var/backups/pg /var/backups/pg/wal /var/backups/pg/base

#!/usr/bin/env bash
# Called by PostgreSQL as: pg-archive-wal %p %f
set -euo pipefail

ARCHIVE="/var/backups/pg/wal"
src="$1"
dest="$ARCHIVE/$2.gz"

# Already archived (PostgreSQL can retry after a crash): succeed only if identical.
if [ -e "$dest" ]; then
  if gzip -dc "$dest" | cmp -s - "$src"; then
    exit 0
  fi
  echo "pg-archive-wal: $dest exists with different contents" >&2
  exit 1
fi

# Compress to a temporary name, flush it to disk, then rename into place.
gzip -c "$src" > "$dest.tmp"
sync "$dest.tmp"
mv "$dest.tmp" "$dest"
sync "$ARCHIVE"

sudo chmod 755 /usr/local/bin/pg-archive-wal

A segment closed early is still 16 MB; gzip shrank ours to 16–20 KB, and a busy one to 1.1 MB. Since PostgreSQL 15, archive_library can load a C module instead of a shell command (setting both is an error). Open-source tools such as pgBackRest plug into archive_command and also manage base backups and retention.

Turn on archiving

On Debian and Ubuntu, postgresql.conf includes every .conf file in conf.d, so the settings can live in their own file. Paths here are for PostgreSQL 16; change 16 to your version. On other systems, add the lines to postgresql.conf.

wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/pg-archive-wal %p %f'
archive_timeout = 60

• wal_level = replica is the default. minimal does not log enough to archive, and the server refuses archive_mode with it.
• archive_mode = on starts the archiver. It needs a restart; archive_command only needs a reload.
• archive_timeout = 60 switches segments after 60 seconds if anything was written, so recent transactions reach the archive within a minute. That can mean 1,440 full-size files a day, over 22 GB uncompressed, which is why the script compresses.

sudo systemctl restart postgresql@16-main

Force a switch and check that a segment arrived:

sudo -u postgres psql -c "SELECT pg_switch_wal()"

sudo -u postgres psql -x -c "SELECT * FROM pg_stat_archiver"

-[ RECORD 1 ]------+------------------------------
archived_count     | 1
last_archived_wal  | 000000010000000000000001
last_archived_time | 2026-10-03 20:17:40.136289+00
failed_count       | 0
last_failed_wal    | 
last_failed_time   | 
stats_reset        | 2026-10-03 20:16:38.954237+00

Take a base backup

Archive first, then back up: replay needs every segment from the moment the base backup starts. pg_basebackup copies the running cluster over a replication connection, which Ubuntu's default pg_hba.conf allows locally.

sudo -u postgres pg_basebackup -D /var/backups/pg/base/2026-10-03_2017 -Ft -z -X stream -P -c fast

• -D is the target directory. It must be empty or not exist.
• -Ft writes tar files instead of a copy of the directory tree; -z gzips them.
• -X stream (the default) also saves the WAL written during the backup, in pg_wal.tar.gz, so the backup can start even without the archive.
• -P prints progress. -c fast checkpoints at once instead of waiting for the next scheduled checkpoint.

Our 35 MB test cluster became a 4.8 MB base.tar.gz, a 17 KB pg_wal.tar.gz and a backup_manifest. The archive also gets a backup history file, named after the first segment the backup needs:

sudo -u postgres gzip -dc /var/backups/pg/wal/000000010000000000000003.00000028.backup.gz

START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
STOP WAL LOCATION: 0/3000100 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-10-03 20:17:52 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1
STOP TIME: 2026-10-03 20:17:53 UTC
STOP TIMELINE: 1

From PostgreSQL 18, pg_verifybackup checks tar backups against the manifest; -n skips WAL parsing, which tar backups do not support. Version 18 verified our backup of a 16 server, where version 16 refused the tar files. On Debian and Ubuntu, call it by its full path:

sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup -n /var/backups/pg/base/2026-10-03_2017

backup successfully verified

Find the moment to stop at

Before a risky migration or cleanup, name the current position. Recovery can stop exactly there later:

sudo -u postgres psql -c "SELECT pg_create_restore_point('before_cleanup')"

 pg_create_restore_point 
-------------------------
 0/4029C20
(1 row)

Without a restore point, use the time just before the mistake. To find it exactly, decompress the segment written around then and list its commits with pg_waldump, using the binary that matches the server's major version:

sudo -u postgres sh -c 'mkdir -p /tmp/walcheck && gunzip -c /var/backups/pg/wal/000000010000000000000004.gz > /tmp/walcheck/000000010000000000000004'

sudo -u postgres /usr/lib/postgresql/16/bin/pg_waldump -r Transaction /tmp/walcheck/000000010000000000000004

rmgr: Transaction len (rec/tot):     34/    34, tx:        733, lsn: 0/04029B90, prev 0/04029B50, desc: COMMIT 2026-10-03 20:18:10.660446 UTC
rmgr: Transaction len (rec/tot):     34/    34, tx:        734, lsn: 0/04627848, prev 0/04627810, desc: COMMIT 2026-10-03 20:18:13.854830 UTC

-x 734 shows what transaction 734 did: DELETE records on rel 1663/16384/16386, meaning tablespace, database OID and file node. In that database, SELECT pg_filenode_relation(0, 16386) returned orders. Any target between the two commits, such as 20:18:13, keeps everything except the DELETE.

Restore to a point in time

Stop the server and move the data directory aside rather than deleting it: its pg_wal may hold segments that never reached the archive.

sudo systemctl stop postgresql@16-main

sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken

sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/16/main

sudo -u postgres tar -xzf /var/backups/pg/base/2026-10-03_2017/base.tar.gz -C /var/lib/postgresql/16/main

Leave pg_wal.tar.gz out; the archive has those segments. Then tell PostgreSQL where the archive is and where to stop:

restore_command = 'gunzip -c /var/backups/pg/wal/%f.gz > %p'
recovery_target_time = '2026-10-03 20:18:13+00'
recovery_target_action = 'pause'

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

sudo systemctl start postgresql@16-main

sudo tail -n 12 /var/log/postgresql/postgresql-16-main.log

gzip: /var/backups/pg/wal/00000002.history.gz: No such file or directory
2026-10-03 20:19:11.250 UTC [1558613] LOG:  starting point-in-time recovery to 2026-10-03 20:18:13+00
2026-10-03 20:19:11.251 UTC [1558613] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000060, on timeline ID 1
2026-10-03 20:19:11.347 UTC [1558613] LOG:  restored log file "000000010000000000000003" from archive
2026-10-03 20:19:11.389 UTC [1558613] LOG:  redo starts at 0/3000028
2026-10-03 20:19:11.502 UTC [1558613] LOG:  restored log file "000000010000000000000004" from archive
2026-10-03 20:19:11.539 UTC [1558613] LOG:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000100
2026-10-03 20:19:11.539 UTC [1558613] LOG:  consistent recovery state reached at 0/3000100
2026-10-03 20:19:11.539 UTC [1558609] LOG:  database system is ready to accept read-only connections
2026-10-03 20:19:11.589 UTC [1558613] LOG:  recovery stopping before commit of transaction 734, time 2026-10-03 20:18:13.85483+00
2026-10-03 20:19:11.589 UTC [1558613] LOG:  pausing at the end of recovery
2026-10-03 20:19:11.589 UTC [1558613] HINT:  Execute pg_wal_replay_resume() to promote.

The gzip line is normal: PostgreSQL asks for history files that may not exist. While paused, the server is read-only, so check the data. Ours had all 51,000 rows, including 1,000 written after the base backup:

sudo -u postgres psql -d shop -c "SELECT count(*) FROM orders"

If the data is right, end recovery; the log says selected new timeline ID: 2 and archive recovery complete. For a later moment instead, stop the server, move the target later and start again. For an earlier one, start over from the base backup.

sudo -u postgres psql -c "SELECT pg_wal_replay_resume()"

sudo rm /etc/postgresql/16/main/conf.d/recovery.conf

PostgreSQL deletes recovery.signal itself. The new timeline's WAL is archived under new names (00000002…), so the old history stays intact. Take a fresh base backup once you are happy.

Set at most one target. recovery_target_action is pause (default), promote or shutdown.

Keep base backups and WAL in step

WAL is only useful from the start of the oldest base backup you keep. This script takes a backup, checks the tar file reads to the end, keeps the newest two, and deletes archived segments older than the oldest one left:

#!/usr/bin/env bash
# Take a base backup, keep the newest few, and delete WAL that no kept backup needs.
set -euo pipefail

BASE_DIR="/var/backups/pg/base"
WAL_DIR="/var/backups/pg/wal"
KEEP=2

DEST="$BASE_DIR/$(date +%Y-%m-%d_%H%M)"
trap 'rm -rf "$DEST.partial"' EXIT

pg_basebackup -D "$DEST.partial" -Ft -z -X stream -c fast
tar -tzf "$DEST.partial/base.tar.gz" > /dev/null
mv "$DEST.partial" "$DEST"

# Delete all but the newest $KEEP finished backups.
find "$BASE_DIR" -mindepth 1 -maxdepth 1 -type d -name '????-??-??_????' | sort | head -n -"$KEEP" | xargs -r rm -rf

# Delete archived WAL older than the oldest backup that is left.
OLDEST=$(find "$BASE_DIR" -mindepth 1 -maxdepth 1 -type d -name '????-??-??_????' | sort | head -n 1)
FIRST_WAL=$(tar -xzOf "$OLDEST/base.tar.gz" backup_label | sed -n 's/^START WAL LOCATION: .*(file \(.*\))$/\1/p')
pg_archivecleanup -x .gz "$WAL_DIR" "$FIRST_WAL"

15 3 * * 0 postgres /usr/local/bin/pg-base-backup >> /var/backups/pg/base-backup.log 2>&1

backup_label inside base.tar.gz names the backup's first segment. Weekly backups with KEEP=2 give a recovery window of 7 to 14 days. Storage peaks just after a new backup, before the oldest is deleted: three base backups plus 14 days of WAL. With 6 GB backups and 2 GB of compressed WAL a day, that is 3 × 6 + 14 × 2 = 46 GB. Replaying a week of WAL takes time too; measure it with a test restore.

pg_archivecleanup's last argument is the oldest file to keep; a .backup file name works. -n lists what it would delete:

sudo -u postgres pg_archivecleanup -n -x .gz /var/backups/pg/wal 000000010000000000000003.00000028.backup

/var/backups/pg/wal/000000010000000000000001.gz
/var/backups/pg/wal/000000010000000000000002.gz

Ship the archive off the server

An archive on the database's own disk dies with it. Copy /var/backups/pg to object storage every few minutes with rclone or restic, into a bucket on Amazon S3, Backblaze B2 or Cloudflare R2. Your worst-case loss is then archive_timeout plus the copy interval. The archive holds every row ever written, so encrypt it before it leaves the server.

Watch the archiver

A failing archive_command does not stop PostgreSQL. It retries, and WAL piles up in pg_wal until the disk fills and the server shuts down. Alert when failed_count rises:

sudo -u postgres psql -c "SELECT archived_count, failed_count, last_failed_wal, now() - last_archived_time AS since_last_archive FROM pg_stat_archiver"

We planted a different file under the next segment's name. The script refused to overwrite it and the log showed why:

pg-archive-wal: /var/backups/pg/wal/000000010000000000000007.gz exists with different contents
2026-10-03 20:20:27.864 UTC [1557319] LOG:  archive command failed with exit code 1
2026-10-03 20:20:27.864 UTC [1557319] DETAIL:  The failed archive command was: /usr/local/bin/pg-archive-wal pg_wal/000000010000000000000007 000000010000000000000007
2026-10-03 20:20:27.864 UTC [1557319] WARNING:  archiving write-ahead log file "000000010000000000000007" failed too many times, will try again later

Once we removed the file, the segment archived on the next retry. On an idle server last_archived_time stops moving, which is normal. A command killed by a signal, or one that is not found, is not counted in failed_count, so watch the log too.

Incremental backups (PostgreSQL 17 and newer)

Version 17 added incremental base backups, which copy only blocks changed since an earlier backup. Our test server runs 16, so these commands are checked against the PostgreSQL 18 documentation, not run. First turn on WAL summaries; a reload is enough:

summarize_wal = on

Then take a full backup in the default plain format, and later an incremental one that names the full backup's manifest:

sudo -u postgres pg_basebackup -D /var/backups/pg/full -c fast

sudo -u postgres pg_basebackup -D /var/backups/pg/incr-1 --incremental=/var/backups/pg/full/backup_manifest -c fast

sudo -u postgres /usr/lib/postgresql/18/bin/pg_combinebackup /var/backups/pg/full /var/backups/pg/incr-1 -o /var/backups/pg/combined

The incremental backup fails unless WAL was summarized since the earlier backup started, and summaries are deleted after wal_summary_keep_time (10 days by default). To restore, pg_combinebackup rebuilds a full backup from the chain, oldest first; recovery then works as above. PostgreSQL does not track which backups a chain needs, so never delete one a later incremental depends on.

Common errors

Frequently asked questions

Can I restore a single table with point-in-time recovery?

Not directly; it restores the whole cluster. Restore to a separate server or directory, then copy the table out with pg_dump and load it into production.

How much data can I lose with WAL archiving?

Up to archive_timeout, plus however often you copy the archive off the server. With archive_timeout = 60 and a five-minute copy, about six minutes.

Does point-in-time recovery replace pg_dump?

No. Physical backups only restore to the same major version and only as a whole cluster. Keep dumps for upgrades and single databases.