7.4 Write-Ahead Logging & Point-in-Time Recovery

7.4 Write-Ahead Logging & Point-in-Time Recovery

[!NOTE] Core Design Philosophy: In relational database theory, durability is enforced through the Write-Ahead Logging (WAL) protocol. The fundamental rule is simple: no data page modifications may be written to permanent disk storage until the log records describing those changes have been flushed to stable WAL storage. Write-Ahead Logging ensures that if the server abruptly crashes, power is lost, or the operating system panics, the database engine can recover by reading WAL records and replaying unwritten modifications.

While crash recovery happens automatically whenever PostgreSQL restarts after an unclean shutdown, Point-in-Time Recovery (PITR) is an administrative procedure. By combining a physical base backup with a continuous archive of WAL files, an administrator can roll a database forward and halt replay at a precise microsecond before a disaster occurred (such as an accidental DROP TABLE or flawed migration script).

---

Write-Ahead Logging (WAL) Architecture

In PostgreSQL, transactional modifications are recorded sequentially in the Write-Ahead Log before being applied to the data files in shared_buffers.

Physical Characteristics of WAL

• Location: WAL segments are stored in the pg_wal subdirectory inside PGDATA (prior to PostgreSQL 10, this directory was named pg_xlog).
• Fixed Segment Size: WAL is segmented into fixed-size files, by default 16MB in size (configurable at cluster initialization via initdb --wal-segsize).
• Segment Naming Convention: WAL files are named using a 24-character hexadecimal string representing three 8-character hex numbers:

  [ Timeline ID ] [ Logical Log File ] [ Segment Number ]
     00000001          0000000A               0000002F
  

• Log Sequence Number (LSN): Every byte offset in the WAL stream is uniquely identified by an LSN—a 64-bit integer displayed as two hexadecimal numbers separated by a slash (e.g., 16/B3748).

The Checkpoint Cycle and Crash Recovery

Periodically, the PostgreSQL checkpointer background process initiates a checkpoint:

1. It flushes all dirty 8KB shared buffer pages to disk.
2. It writes a checkpoint record to the WAL, noting the REDO start pointer (the LSN of the oldest WAL record needed to make data pages consistent).
3. Any WAL segments older than the REDO pointer that are no longer needed for crash recovery or replication can be recycled or removed.

When PostgreSQL crashes and restarts, it enters Redo Crash Recovery: it reads the checkpoint record, locates the REDO LSN, and replays all WAL records forward from that point to bring the database back into a fully consistent state.

---

Continuous Archiving Configuration

Crash recovery only protects against unexpected server crashes using local WAL in pg_wal. However, if the storage drive holding PGDATA experiences a catastrophic physical failure, local WAL is lost! Continuous archiving copies completed 16MB WAL segment files to secure, offsite secondary storage (such as NFS, SAN, or cloud object storage like AWS S3 or Google Cloud Storage).

+--------------------------------------------------------------------------+
|                       Continuous WAL Archiving Flow                      |
+--------------------------------------------------------------------------+
|  PostgreSQL Engine writes to pg_wal/000000010000000A0000002F             |
|         │                                                                |
|         ├──> Segment fills to 16MB                                       |
|         │                                                                |
|         └──> Archiver Process triggers archive_command                   |
|                   │                                                      |
|                   ▼                                                      |
|              cp %p /mnt/wal_archive/%f                                   |
|                   │                                                      |
|                   ▼                                                      |
|       /mnt/wal_archive/000000010000000A0000002F (Immutable Archive)      |
+--------------------------------------------------------------------------+

Core Archiving Parameters in postgresql.conf

1. wal_level: Determines how much detail is logged into the WAL. Must be set to replica (the modern default) or logical to support archiving and standby replication. Setting wal_level = minimal strips out necessary transaction detail and disables archiving.
2. archive_mode: Must be set to on to enable the archiver background process. (A special mode, archive_mode = always, allows standby replicas to archive WAL received via streaming replication).
3. archive_command: The shell command executed by PostgreSQL every time a 16MB WAL segment is closed. PostgreSQL supplies two expansion tokens:
   • %p: Replaced by the relative or absolute path of the completed WAL segment to be archived (e.g., pg_wal/00000001000000010000001A).
   • %f: Replaced by only the file name of the segment (e.g., 00000001000000010000001A).

# postgresql.conf archiving configuration
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 300

Critical Rules for archive_command

• Exit Code Semantics: The command must return an exit status of 0 on success. If it returns a non-zero exit code, PostgreSQL assumes the archive failed. It will retry indefinitely, preserving the WAL segment in pg_wal. If archiving remains broken, pg_wal will eventually consume all available disk space!
• Never Overwrite: The command must never overwrite an existing file in the archive. Using test ! -f destination && cp %p destination ensures idempotency.
• archive_timeout: Forces PostgreSQL to switch to a new WAL segment if the current segment has remained open for N seconds (e.g., 300 seconds = 5 minutes). This bounds the maximum window of data loss for low-traffic databases.
• Modern Alternative: archive_library (PG15+): PostgreSQL 15 introduced archive_library, allowing administrators to load shared libraries (like custom C modules) to stream WAL directly to cloud object stores without invoking external shell processes.

---

Point-in-Time Recovery (PITR) Mechanics

Point-in-Time Recovery operates on a simple principle:

1. Restore a past physical base backup (e.g., from yesterday at midnight).
2. Replay the sequence of archived WAL files forward.
3. Halt replay at the target transaction or timestamp immediately prior to the failure.

PostgreSQL 12+ Recovery Configuration Evolution

In PostgreSQL 11 and older, recovery was configured via an external file named recovery.conf. In PostgreSQL 12 and later, recovery.conf was completely removed! Recovery configuration now follows these rules:

• Recovery parameters are configured directly in postgresql.conf (or postgresql.auto.conf).
• An empty signal file named recovery.signal in the root of PGDATA instructs PostgreSQL to enter archive recovery mode.
• (For streaming replication standbys, the trigger file is standby.signal).

---

The Step-by-Step Point-in-Time Recovery Procedure

When a disaster strikes (e.g., an accidental table drop at 2026-09-06 14:32:15 UTC), execute the following 10-step PITR procedure:

Step 1: Stop the Server

Halt the active PostgreSQL instance immediately to prevent further writes:

pg_ctl stop -D /var/lib/postgresql/data -m fast

Step 2: Preserve Crash Data and Unarchived WAL

Never delete the current PGDATA without backing it up! Specifically, salvage the active pg_wal directory. The live database may contain WAL records of committed transactions that had not yet been copied to the archive by archive_command:

mkdir -p /var/backups/crashed_cluster
cp -r /var/lib/postgresql/data/pg_wal /var/backups/crashed_cluster/salvaged_wal

Step 3: Restore the Physical Base Backup

Clear the live PGDATA directory and unpack/restore a clean physical base backup taken prior to the disaster point:

rm -rf /var/lib/postgresql/data/*
tar -xf /var/backups/physical/base.tar -C /var/lib/postgresql/data/

Step 4: Clean Out Obsolete WAL Files

Remove any pre-existing WAL files that were copied over as part of the base backup:

rm -rf /var/lib/postgresql/data/pg_wal/*

Step 5: Copy Salvaged Unarchived WAL

Copy any salvaged WAL files saved in Step 2 into /var/lib/postgresql/data/pg_wal/. This ensures recovery can replay up to the final microsecond before the crash.

Step 6: Create the Recovery Signal File

Create an empty trigger file named recovery.signal in the root of PGDATA:

touch /var/lib/postgresql/data/recovery.signal

Step 7: Configure restore_command

In postgresql.conf (or postgresql.auto.conf), define how PostgreSQL should fetch missing WAL segments from the archive:

restore_command = 'cp /mnt/wal_archive/%f %p'

Step 8: Set the Recovery Target

Specify exactly where PostgreSQL should stop replaying WAL records. Target options include:

• recovery_target_time: Replay until a specific timestamp:

  recovery_target_time = '2026-09-06 14:32:00 UTC'
  

• recovery_target_xid: Replay until a specific transaction ID.
• recovery_target_name: Replay to a named restore point created earlier via SQL:

  SELECT pg_create_restore_point('before_payroll_run');
  

• recovery_target_inclusive: Controls whether the target transaction is included (true, default) or stopped immediately before it (false):

  recovery_target_inclusive = false
  

Step 9: Configure the Target Action

Define what happens once the recovery target is reached via recovery_target_action:

• pause (Default): Pauses recovery when the target is reached. The database enters read-only mode, allowing the DBA to connect, inspect tables, and verify data consistency. If satisfied, the DBA runs SELECT pg_wal_replay_resume(); to complete recovery.
• promote: Automatically finishes recovery and promotes the cluster to a read-write primary immediately upon reaching the target.
• shutdown: Automatically shuts down the server once the target is reached.

recovery_target_action = 'pause'

Step 10: Start the Server and Verify

Start the server. PostgreSQL detects recovery.signal, enters recovery, downloads WAL files using restore_command, replays transactions forward to the target, pauses (or promotes), removes recovery.signal, and opens for business!

---

Timelines and Timeline History Files (.history)

What happens when a database finishes recovery and begins accepting new writes? It branches into a new timeline.

Timeline 1:  ───[Checkpoint]───[LSN 100]───[LSN 200 (Drop Table)]───[LSN 300]──► (Dead End)
                                   │
                                   └──► PITR Replays to LSN 100
                                           │
Timeline 2:                                └───[LSN 101 (New Writes)]───[LSN 201]──►

Why Timelines Matter

If PostgreSQL stayed on Timeline 1 after recovery, newly generated WAL files would have the same names and sequence numbers as WAL files generated before the disaster, causing data corruption and overwriting the archive. Instead:

1. PostgreSQL switches from Timeline 1 to Timeline 2.
2. Future WAL files begin with 00000002... instead of 00000001....
3. PostgreSQL generates a timeline history file named 00000002.history.

Inside the .history File

The history file documents exactly where the new timeline branched off from its parent:

1       0/16B3748       before drop table disaster at 2026-09-06 14:32:00 UTC

This records that Timeline 2 branched from Timeline 1 at LSN 0/16B3748. This history file is archived alongside WAL segments. If an administrator ever needs to perform another recovery, PostgreSQL reads the history file to traverse across timeline branches seamlessly using recovery_target_timeline = 'latest'.

---

Exam Tips and Common Pitfalls

• Exam Trap: recovery.conf Deprecation: PostgreSQL 12 completely removed recovery.conf. Any exam question referencing recovery.conf describes pre-v12 behavior. In modern PostgreSQL, use recovery.signal and configure parameters in postgresql.conf.
• Exam Trap: Tokens %p and %f: Remember which token is which in archive_command and restore_command:
  • In archive_command: %p is the source path (in pg_wal), %f is the destination file name.
  • In restore_command: %f is the source file name (in the archive), %p is the destination path (in pg_wal).
• Exam Trap: Salvaging Active WAL: Never format pg_wal without first saving active unarchived segments! Unarchived segments contain transactions committed right up to the second of the crash.
• Exam Trap: recovery_target_action Defaults: The default action when a target is reached is pause, not promote. This safety feature lets DBAs inspect the database before making recovery permanent.