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 how it is utilized during a second recovery attempt.

Consider a scenario where an error occurs at ‘12:15:00’ in the recovered database cluster, which has a timelineId of 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, while the recovery_target_timeline parameter is set to 2 to recover along its timeline.

Restart the PostgreSQL server to enter PITR mode and recover the database at 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 value of ‘CHECKPOINT LOCATION’ from the backup_label file.

  2. Some values of parameters are read from the postgresql.conf (versions 11 or earlier, recovery.conf); in this example, restore_command, recovery_target_time and recovery_target_timeline.

  3. PostgreSQL reads the timeline history file ‘00000002.history’ that is corresponding to the value of the parameter recovery_target_timeline.

  4. PostgreSQL does replaying WAL data by the following steps:

    1. From the REDO point to the LSN ‘0/A000198’, which is written in the ‘00000002.history’ file, PostgreSQL reads and replays WAL data of appropriate archive logs whose timelineId is 1.
    2. From the one after LSN ‘0/A000198’ to the one before the timestamp ‘2024-1-1 12:15:00’, PostgreSQL reads and replays WAL data (of appropriate archive logs) whose timelineId is 2.

  5. When the recovery process completes, the current timelineId will advance to 3, and a new timeline history file named ‘00000003.history’ is created in the pg_wal subdirectory (pg_xlog if 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 timelineId should be explicitly set to ensure the correct timeline history file is used.

In this way, timeline history files serve not only as logs of the database cluster’s timeline history but also as recovery instruction documents for the PITR process.