[Summary] Chapter#10 "The Internals of PostgreSQL" Base Backup and Point-in-Time Recovery

Database is classified into two categories:

• Logical Backup
• Physical Backup Both has there advantages and disadvantages and one of the disadvantage of logical Backup is it takes too much time for its performance, but physical Backup takes relative shorter time for to restore large databases. Point-in-time architecture is feature to restore cluster to any point in time using base backup and archive logs.

Base Backup
Following the standard procedure for base backup using low level commands:

• Issue the pg_backup_start Command.
• Take a snapshot of database clusters with the archiving command you want to use.
• Issue the pg_backup_stop command.

How Point-in-time recovery Works:
PostgreSQL in PITR-mode replays WAL data of the archive logs on the base backup, from the redo point created by the pg_backup_start up to the point you want to recover. In PostgreSQL point to be recovered is referred to as a recovery target.

TimelineId and timeline history file
To distinguish between original database clusters and recovered database cluster PostgreSQL use Timeline and it is central concept of PITR.
Two associated things are following:

• TimelineID
  Each timeline has its timelineId which is 4-byte unsigned Integer starts from 1.
  TimelineId for original database cluster is 1, and when database cluster recovers, timelineId will be increased by 1.

• Timeline History
  When PITR process completes a timeline history file names like "00000002.history" is created under the archival directory and pg_xlog subdirectory. This file records which timeline is branched off from and when.

Timeline history contain atleast one line and each line is composed of the following three lines:

• TimelineID: It is used to recover.
• LSN logs: LSN location where WAL segment switches happend.
• Reason: Human readable explanation of why the time was changed.

Point-in-time Recovery with Timeline History File
It is used when Second time recovery happen. Suppose you have made mistake at 12:15:00 in the recovered database cluster whose timelineId is 2, to recover database cluster, you should create a new recovery.conf.

Reference