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

The timeline history file plays an important role in the second and subsequent PITR processes. By trying a second time recovery, we will explore how it is used.

Again, suppose that you made a mistake at ‘12:15:00’ in the recovered database cluster whose timelineId is 2. In this case, to recover the database cluster, you should create a new recovery.conf file as shown below:

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

The parameter ‘recovery_target_time’ sets the time you made the new mistake, and the parameter ‘recovery_target_timeline’ is set at 2 in order to recover along its timeline.

Restart the PostgreSQL server and enter PITR mode to recover the database at the target time along the timelineId 2. See Fig. 10.5.

Fig. 10.5. Recover the database at 12:15:00 along the timelineId 2.

  • (1) PostgreSQL reads the value of ‘CHECKPOINT LOCATION’ from the backup_label file.

  • (2) Some values of parameters are read from the 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.

postgres> 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

When you do PITR more than once, you should explicitly set a timelineId for using the appropriate timeline history file.

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