10.4. Point-in-Time Recovery with Timeline History File

The timeline history file is essential for performing second and subsequent Point-in-Time Recovery (PITR) operations. The following example demonstrates its utility during a second recovery attempt.

Consider a scenario where an error occurs at ‘12:15:00’ in a recovered database cluster with timeline ID 2. To recover the database to this point, the postgresql.conf file (or recovery.conf for versions 11 or earlier) should be configured as follows:

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = "2024-1-1 12:15:00 GMT"
recovery_target_timeline = 2

The recovery_target_time parameter specifies the desired recovery time. The recovery_target_timeline parameter is set to 2 to recover along that specific timeline.

Restarting the PostgreSQL server enters PITR mode and recovers the database to the target time along timeline ID 2 (see Figure 10.6).

Figure 10.6. Recover the database at 12:15:00 along the timelineId 2.

During recovery, PostgreSQL performs the following steps:

  1. PostgreSQL reads the ‘CHECKPOINT LOCATION’ value from the backup_label file.

  2. PostgreSQL reads parameter values from postgresql.conf (or recovery.conf in versions 11 or earlier); in this example, restore_command, recovery_target_time, and recovery_target_timeline.

  3. PostgreSQL reads the timeline history file “00000002.history” corresponding to the recovery_target_timeline value.

  4. PostgreSQL replays WAL data using these steps:

    1. From the REDO point to LSN ‘0/A000198’ (specified in the 00000002.history file), PostgreSQL reads and replays WAL data from archive logs with timeline ID 1.
    2. From the point after LSN ‘0/A000198’ to the point before timestamp ‘2024-1-1 12:15:00’, PostgreSQL reads and replays WAL data from archive logs with timeline ID 2.

  5. When the recovery process completes, the current timeline ID advances to 3. PostgreSQL creates a new timeline history file “00000003.history” in the pg_wal subdirectory (pg_xlog in versions 9.6 or earlier) and the archival directory.

$ cat /home/postgres/archivelogs/00000003.history
1         0/A000198     before 2024-1-1 12:05:00.861324+00

2         0/B000078     before 2024-1-1 12:15:00.927133+00

For multiple PITR operations, the appropriate timeline ID must be explicitly set to ensure the use of the correct timeline history file.

Timeline history files serve not only as logs of the database cluster’s history but also as recovery instructions for the PITR process.