This post is a part of my document.

In this post, I will explore the start-up sequence of the streaming replication to understand how those processes are started and how the connection between them is established.

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.

Timeline in PostgreSQL is used to distinguish between the original database cluster and the recovered ones, and is central concept of PITR. In this section, two things associated with the timeline are described: timelineId and timeline history files.

Each timeline is given a corresponding timelineId, a 4-byte unsigned integer starting at 1.

An individual timelineId is assigned to each database cluster. The timelineId of original database cluster created by the initdb utility is 1. Whenever database cluster recovers, timelineId will be increased by 1. For example, in the example of the previous section, the timelineId of the cluster recovered from the original one is 2.

This post is the first two sections of my document.

In PostgreSQL, online physical full backup has been available since version 8.0, and a snapshot of a running whole database cluster (i.e. physical backup data) is known as a base backup.

Point-in-Time Recovery (PITR), which has also been available since version 8.0, is the feature to restore a database cluster to any point in time using a base backup and archive logs created by continuous archiving feature.