InterDB Blog Written by Hironobu Suzuki

PITR with Timeline history file

The timeline history file plays an important role in the second and subsequent PITR processes. By trying the 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 shown below:

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

The parameter recovery_target_time sets the time you made new mistake, and the 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 Figure 10.5.

%FIG Figure 10.5: Recover the database at 12:15:00 along the timelineId 2 %IMG

  1. PostgreSQL reads the value of “CHECKPOINT LOCATION” from the backup_label file.
  2. Some values of parameters are read from the recovery.conf;
  3. PostgreSQL reads the timeline history file “00000002.history” which is corresponding to the value of the parameter recovery_target_timeline.
  4. PostgreSQL does replaying WAL data by the following steps:
  5. 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.
  6. From the one after LSN “0/A000198” to the one before the timestamp “2016-1-26 12:15:00” PostgreSQL reads and replays WAL data (of appropriate archive logs) whose timelineId is 2.
  7. When the recovery process completes, the current timelineId will advance to 3, and new timeline history file named 00000003.history is created in the pg_xlog subdirectory and the archival directory.
postgres> cat /home/postgres/archivelogs/00000003.history
1         0/A000198     before 2016-1-26 12:05:00.861324+00

2         0/B000078     before 2016-1-26 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.