PostgreSQL Feature Introduction: Timeline Resolution - Alibaba Cloud Developer Forums: Cloud Discussion Forums

  • UID623
  • Fans4
  • Follows1
  • Posts72

[PostgreSQL Development]PostgreSQL Feature Introduction: Timeline Resolution

More Posted time:Jul 20, 2016 13:59 PM
PostgreSQL Feature Introduction: Timeline Resolution
A distinctive PG concept, the parameter "timeline" is occasionally used in backup recovery documentation. The concept is rarely explained and not easy to understand. This document provides a detailed explanation on the concept.

Introduction of Timeline

To better understand the background for timeline introduction, let's first see what happens in the absence of timeline. Recovering a database to a certain time point is used as an example. Assume that DBA deletes a key table from a running database at 12:00 AM on Wednesday but detects the deletion until Friday noon. Then DBA uses the original database backup and the logs saved in the archiving directory to recover the database to the time point of 11:00 AM on Wednesday. The recovered database can work normally. However, DBA later realizes that the database shall be recovered to the time point of 8:00 AM on Thursday, but fails to do so. The reason is that, files with the same name as old WAL files are generated during database running and will overwrite the original logs when they are saved to the archiving directory, resulting in loss of WAL files required for database recovery. To avoid this problem, you need to distinguish WAL files generated by the original database from the new WAL files generated during running of the recovered database. You can rename the new WAL files.

Scenarios in Which a New Timeline Is Generated

In which scenario will a new timeline be generated?
1. The recovery.conf file for Point-in-time Recovery (PITR) configuration: restore_command = 'cp /mnt/server/archivedir/%f %p' //Recover logs from the archiving directory
recovery_target_time = '2015-7-16 12:00:00 ' //Specify the archiving time point. If the parameter is not specified, the last transaction finished before the fault is recovered
recovery_target_timeline = 'latest' //Specify the archiving timeline. 'latest' indicates the latest timeline branch. If the parameter is not specified, the database is recovered to the timeline in pg_control before the fault
 standby_mode = ‘off’ //The database enters standby mode instead of PITR mode after it is started
After the recovery.conf file is configured and the database is started, a new timeline and a new history file are generated. By default, the database is recovered based on the timeline the same as that of the current base backup. If you want to recover the database to certain timelines, you need to specify the target timeline recovery_target_timeline for recovery.conf. However, the database cannot be recovered to a time point earlier than that of the base backup branch.2. Use "standby promote" to create a PG master-backup, disable the master database, and run the command below on the backup database server: $ pg_ctl promote –D $PGDATA
The backup database is promoted as the master database, and a new timeline and a new history file are generated.

History File

Each time a new timeline is created, PostgreSQL creates a "timeline history" file named ".history". The timeline history file consists of contents in the original timeline history file and a switching record of the current timeline. Assume that the recovered database is started and switched to the new timeline ID=5. Then the timeline history file is named 00000005.history. The file records the reason, timeline, and time for file branch-off. The file may contain multiple rows of records. Each record contains the following formatted contents: * <parentTLI> <switchpoint> <reason>**      parentTLI       ID of the parent timeline*      switchpoint     XLogRecPtr of the WAL position where the switch happened*      reason          human-readable explanation of why the timeline was changed
For example,$ cat 00000004.history1   0/140000C8  no recovery target specified2   0/19000060  no recovery target specified3   0/1F000090  no recovery target specified
When the database is recovered from an archive containing multiple timelines, the history files allow the system to select the correct WAL files. The history files can also be archived to the WAL archiving directory like WAL files. The history files are very small text files and thus require little storage space. If you want to recover the database by specifying the target timeline tli in recovery.conf, the program finds the .history file first, then finds log files corresponding to all timelines from startTLI to tli in pg_control based on the relationship between timeline branches recorded in the .history file, and recovers the database.

You can use the timeline mechanism in PG to conveniently recover a database to any time point, which is significant to database backup. We can properly back up and archive data during database use, and use the timeline mechanism to recover the database and get required data in case of data loss or damage.