WAL Archiving and Point-in-Time Recovery (PITR)

Why Backups Are Not Enough

A normal backup (like pg_basebackup) gives you the database at one fixed moment in time.
But if you need to recover to a specific second before a mistake, you need more — that’s where WAL Archiving and PITR come in.

How WAL Archiving Works

When WAL archiving is enabled, PostgreSQL copies each completed WAL file to another location (such as a backup folder or remote server).

In postgresql.conf:

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

This command saves every WAL segment into the wal_archive folder, creating a full timeline of all changes since the last base backup.

How Point-in-Time Recovery (PITR) Works

To restore your database to an exact time (for example, right before a DELETE or DROP mistake):

1. Take a base backup of your database (e.g., at midnight).
2. Keep archiving WAL files during the day.
3. If an error happens at 3 PM, you can restore to 2:59:59 PM.

Steps:

• Stop PostgreSQL.
• Restore your base backup to the data directory.
• Place all archived WAL files where PostgreSQL can find them.
• Add a file recovery.signal to enable recovery mode.
• Configure in postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-10-05 14:59:59'

• Start PostgreSQL.
  It will replay all WAL logs and stop exactly at your target time.

This lets you “travel back in time” to the exact moment before the problem occurred.

Common PITR Scenarios

Scenario	Solution
Accidental DELETE or DROP	Restore to just before the command
Data corruption	Restore last valid backup + WAL replay
Testing old states	Rebuild snapshot from specific date

Monitoring WAL Activity and Replication Progress

PostgreSQL provides several built-in views and functions to help monitor how WAL behaves, both for local performance and replication.

Check WAL and Checkpoint Statistics

SELECT
    checkpoint_timed,
    wal_written,
    wal_archived
FROM pg_stat_bgwriter;

This shows important background information:

Column	Meaning
checkpoint_timed	Time of the last automatic checkpoint. It tells you when PostgreSQL last flushed all dirty pages to disk.
wal_written	The total amount of WAL (in bytes) written since the server started. A fast increase means a lot of write activity.
wal_archived	Number of WAL segments successfully archived. If this number increases steadily, archiving is working correctly.

If wal_written grows but wal_archived does not, it may mean your archive command is failing.

Check Current WAL Position (Primary)

SELECT pg_current_wal_lsn();

This shows the current WAL location (LSN) on the primary server — the exact point where new transactions are being written.

• LSN = Log Sequence Number
• Each new WAL record increases this number
• You can compare it to a replica’s position to measure replication lag

Check WAL Replay Position (Replica)

SELECT pg_last_wal_replay_lsn();

This shows the last WAL location that has been replayed on a standby (replica). If you compare this value to the primary’s pg_current_wal_lsn(), the difference shows how far the replica is behind (replication lag).

You can even calculate it directly:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn());

This returns the lag in bytes.

Example Interpretation

Metric	Location	Example Value	Meaning
pg_current_wal_lsn()	Primary	0/70016A0	WAL position currently being written
pg_last_wal_replay_lsn()	Replica	0/7001628	WAL position already replayed
Difference	—	~200 bytes	Replica is slightly behind primary

In short:

• If both values are close: replication is healthy
• If difference is largeL check network or disk I/O on replica

Analogy

Think of WAL as a journal:

• The primary keeps writing new pages (WAL segments).
• The replica keeps reading and copying those pages.
• If the replica’s bookmark (replay LSN) is close to the primary’s, replication is catching up.
• If the gap grows, it’s lagging behind.