How WAL Archiving and Point-in-Time Recovery Work in Postgres

Point-in-time recovery in Postgres rebuilds a cluster from a base backup and then replays archived write-ahead log segments up to a moment you choose. Archiving turns on with wal_level at replica, archive_mode on, and an archive_command that copies each finished 16 MB segment somewhere safe.

In the current PostgreSQL release, as of November 2026, the restore side is a restore_command, a recovery target, and an empty recovery.signal file.

What does the write-ahead log actually record?

Every change to a data file, logged before the file itself changes. The docs define it in one line, “Write-Ahead Logging (WAL) is a standard method for ensuring data integrity,” and the rule is that data files are written only after the change is logged. A commit therefore flushes only the log, and one fsync covers many transactions.

The log is a byte stream cut into segment files, each record at a log sequence number that increases with every record. A checkpoint runs every checkpoint_timeout or when max_wal_size is near, with defaults of 5 minutes and 1 GB, and old segments are recycled after it unless archiving still holds them.

How does WAL archiving work?

The archiver hands every finished segment to a command you write, which must fail loudly and never overwrite. Set wal_level to replica, archive_mode to on, and an archive_command that copies the file at %p to a destination named by %f. The first two need a restart, the command only a reload.

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = 60

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = 60

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = 60

The docs say “It is important that the archive command return zero exit status if and only if it succeeds,” because a false zero lets the server recycle a segment that was never saved. The test ! -f guard matters because GNU cp -i returns zero when the target already exists, and an archive_command of /bin/true breaks the chain while looking healthy.

The archiver only runs on completed segments, so a quiet database holds an unarchived half-full segment for hours. archive_timeout forces a switch, and the docs call a minute or so reasonable, since forced segments are still full size. Anything faster is a job for pg_receivewal, which does not wait for segments to complete. Watch pg_stat_archiver, whose failed_count column is the first sign of a broken chain.

Why a base backup is the other half of a restore

WAL only describes changes, so recovery needs a file copy of the cluster to apply them to. pg_basebackup takes that copy online over the replication protocol and, by default, streams the WAL written during the backup into the output. Since PostgreSQL 13 it writes a JSON manifest with a CRC32C checksum per file for pg_verifybackup to check.

pg_basebackup -h db.shop.internal -U replicator -D /backups/base -Ft -z -P

pg_basebackup -h db.shop.internal -U replicator -D /backups/base -Ft -z -P

pg_basebackup -h db.shop.internal -U replicator -D /backups/base -Ft -z -P

A .backup history file lands in the archive beside the segment the backup started in, and every segment numerically below it is no longer needed. PostgreSQL 17 added pg_basebackup --incremental with pg_combinebackup to rebuild a full copy from a chain. A pg_dump is no substitute, since dumps cannot be used as part of a continuous-archiving solution.

How does point-in-time recovery replay the archive?

Recovery is a restore_command, one target, and a signal file, and the server pauses just after the target by default. Clear the data directory, unpack the base backup, copy any never-archived segments from the old pg_wal back in, set the parameters, create an empty recovery.signal, and start. The docs are blunt, “The one thing that you absolutely must specify is the restore_command,” which fetches each segment on request.

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-11-04 09:30:00+00'
recovery_target_action = 'pause'

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-11-04 09:30:00+00'
recovery_target_action = 'pause'

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-11-04 09:30:00+00'
recovery_target_action = 'pause'

The command is asked for files that do not exist, .history files included, and must return nonzero without treating that as an error. Only one target is allowed, and with none the server recovers to the end of the WAL. Pause leaves the cluster readable for checking, then pg_wal_replay_resume() finishes the job and the log prints archive recovery complete from xlog.c.

A finished recovery starts a new timeline. In the docs’ words, “Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery.” Its ID becomes the leading digits of every later segment, so the old branch survives for a second attempt. recovery_target_timeline defaults to latest, and no recovery reaches a branch that forked before the base backup.

How much data can you lose, and how long does a restore take?

The loss window is the gap since the last archived segment, and the restore time is the age of the base backup. The docs say the window is bounded by archive_timeout and that streaming is the answer below a minute, which is how Barman reaches a recovery point objective of zero. Replay time grows with every segment, so the backup interval is a trade between archive storage and recovery time.

Where WAL archiving and point-in-time recovery go wrong

Most failed restores trace back to an archive that broke weeks earlier without anyone noticing.

The archive command fails and pg_wal fills the disk

The server retries forever while pg_wal grows past max_wal_size, and when the file system fills PostgreSQL does a PANIC shutdown with no committed data lost. The log line is archive command failed with exit code, from shell_archive.c. Fix the command or destination, then confirm last_archived_wal is moving again.

SELECT archived_count, last_archived_wal, failed_count, last_failed_wal
FROM

SELECT archived_count, last_archived_wal, failed_count, last_failed_wal
FROM

SELECT archived_count, last_archived_wal, failed_count, last_failed_wal
FROM

Recovery ends before the target is reached

The server stops with recovery ended before configured recovery target was reached, which the docs describe as a fatal error. Either the target is earlier than the end of the base backup, which is not allowed, or a segment is missing from the archive. Pick a later target, or start from an older backup with a complete chain.

The signal file is missing

Starting a restored backup without recovery.signal fails with could not locate required checkpoint record, and the hint in xlogrecovery.c says to touch recovery.signal or standby.signal. Ignore the other half of that hint, since deleting backup_label during a restore corrupts the cluster.

Which backup tools manage archiving and restores?

The built-in tools cover the mechanics and nothing else, so most teams put a backup manager between Postgres and the archive. Each wraps archive_command and restore_command and adds retention, with the license read from each project’s LICENSE file.

What managed Postgres services offer for point-in-time recovery

Every major managed service runs this machinery for you and exposes a retention window instead of archive_command. None of the eight documents a way to point the archive at your own bucket, and most restore only into a new instance.

Related guides

The logical replication guide covers the other consumer of the same log, and the zero-downtime migration walkthrough shows where a base backup fits in a cutover. Checkpointing and integrity verification carry the same resume-from-a-known-position idea into pipelines, and the database replication guide maps all of it.

Frequently asked questions

What is point-in-time recovery in PostgreSQL?

Point-in-time recovery rebuilds a cluster from a file-level base backup and then replays archived write-ahead log segments up to a moment you choose, such as the second before a bad DELETE. It needs WAL archiving to have been running before the incident, and it restores the whole cluster rather than one database or table.

Which parameters enable WAL archiving and point-in-time recovery?

Archiving needs wal_level at replica or higher, archive_mode on, and an archive_command or archive_library, the first two set before a restart. Recovery needs restore_command, at most one recovery target such as recovery_target_time, an optional recovery_target_action, and an empty recovery.signal file in the data directory when the server starts.

Can point-in-time recovery restore a single dropped table?

Not directly. Recovery always rebuilds an entire cluster, so the usual pattern is to restore into a second server with a target just before the drop and copy the table back with pg_dump. Restoring the production cluster in place would also roll back every other change made since that moment.

Does point-in-time recovery replace pg_dump backups?

No, they answer different questions. A dump is a logical snapshot from the moment pg_dump began and cannot be rolled forward, but it restores one database or table and survives a major version change. A base backup plus archived WAL restores a whole cluster to any second, on the same major version.

What is the difference between RPO and RTO for a Postgres restore?

RPO is how much data you accept losing, and with file-based archiving it equals the gap since the last archived segment, which archive_timeout bounds to about a minute. RTO is how long the restore takes, which grows with the age of the base backup because every segment since it has to be replayed.

How often should you take a base backup for point-in-time recovery?

Often enough that replaying the WAL since the last one fits your recovery time target. Crunchy Bridge takes one every day, and the Postgres docs frame the interval as a trade between archive storage and the time you are prepared to spend recovering. Incremental backups in PostgreSQL 17 make frequent ones cheaper.

Can you run point-in-time recovery without stopping the primary server?

Yes, if you restore into a separate data directory or machine. The base backup is taken online over the replication protocol and the archive is a copy, so the primary keeps serving while a second server replays to the target. Only an in-place restore of the production cluster needs the primary stopped first.

Should you restore in place or into a new server?

Into a new server, almost always. A fresh data directory lets you pause at the target, check the data read-only, and promote only when it is right, while the damaged cluster stays intact as evidence. An in-place restore wipes the data directory first and leaves no second chance if the target was wrong.