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.

Figure 10.3 illustrates the PITR process from the viewpoint of the timelineId. First, we remove our current database cluster and restore the base backup made in the past, in order to go back to the starting point of recovery, and such situation is represented in the red arrow curve in the figure. Next, we start the PostgreSQL server which replays WAL data in the archive logs from the REDO point created by the pg_start_backup until the recovery target by tracing along the initial timeline (timelineId 1), and such situation is represented in the blue arrow line in the figure. Then, a new timelineId 2 is assigned to recovered database cluster and PostgreSQL runs on the new timeline.

%FIG Figure 10.3: Relation of timelineId between an original and a recovered database clusters %IMG

As briefly mentioned in Chapter 9, the first 8-digit of WAL segment filename is equal to the timelineId of the database cluster created for each segment. When the timelineId is changed, WAL segment filename will also be changed.

Focusing on WAL segment files, the recovery process will be described again. Suppose that we recover the database cluster using two archive logs "000000010000000000000009" and "00000001000000000000000A". The newly recovered database cluster is assigned the timelineId 2, and PostgreSQL creates the WAL segment from "00000002000000000000000A". Figure 10.4 shows this situation.

%FIG Figure 10.4: Relation of WAL segment files between an original and a recovered database clusters %IMG

When a PITR process completes, a timeline history file with names like “00000002.history” is created under the archival directory and the pg_xlog subdirectory. This file records which timeline it branched off from and when.

The naming rule of this file is shown in the following:

"8-digit new timelineId".history

A timeline history file contains at least one line, and each line is composed of the following three items:

• timelineId – timelineId of the archive logs used to recover.
• LSN – LSN location where the WAL segment switches happened.
• reason – human-readable explanation of why the timeline was changed.

A specific example is shown below:

postgres> cat /home/postgres/archivelogs/00000002.history
1	  0/A000198	before 2016-1-26 12:05:00.861324+00

Meaning as follows:

and is recovered in the time just before “2016-1-26 12:05:00.861324+00” by replaying the archive logs until the 0/A000198.

In this way, each timeline history file tells us a complete history of the individual recovered database cluster. Moreover, it is also used to PITR process itself. The detail is explained in the next section.