10.3. timelineId and Timeline History File

PostgreSQL uses a timeline to distinguish between the original database cluster and recovered ones. It is a central concept of PITR. This section describes two items associated with the timeline: the timelineId and timeline history files.

Section Contents

10.3.1. timelineId

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

A unique timelineId belongs to each database cluster. The initdb utility creates the original database cluster with timelineId 1.

Whenever a database cluster recovers, the timelineId increases by 1. For example, in the previous section, the cluster recovered from the original one has timelineId 2.

Figure 10.4 illustrates the PITR process from the viewpoint of the timelineId.

Figure 10.4. Relation of timelineId between an original and a recovered database clusters.

  1. To return to the starting point of recovery, the current database cluster is removed and a base backup made in the past is restored. The red arrow curve in the figure represents this situation.

  2. Next, the PostgreSQL server starts and replays WAL data in the archive logs. This process begins from the REDO point created by pg_backup_start and continues until the recovery target by tracing the initial timeline (timelineId 1). The blue arrow line in the figure represents this situation.

  3. Then, a new timelineId 2 is assigned to the recovered database cluster, and PostgreSQL runs on the new timeline.

As mentioned in Section 9.2, the first 8 digits of a WAL segment filename equal the timelineId of the database cluster that created the segment. When the timelineId changes, the WAL segment filename also changes.

The recovery process can be described again by focusing on WAL segment files. Suppose a database cluster recovers using two archive logs ‘000000010000000000000009’ and ‘00000001000000000000000A’. The newly recovered database cluster is assigned timelineId 2, and PostgreSQL creates the WAL segment starting from ‘00000002000000000000000A’.

Figure 10.5 shows this situation.

Figure 10.5. Relation of WAL segment files between an original and a recovered database clusters.

10.3.2. Timeline History File

When a PITR process completes, a timeline history file (e.g., ‘00000002.history’) is created in the archive directory and the pg_wal subdirectory (or pg_xlog in versions 9.6 or earlier). This file records which timeline it branched from and when.

The naming rule for this file is shown below:

  • Timeline History File Pattern: [TimelineId].history

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

  • timelineId: The timelineId of the archive logs used for recovery.
  • LSN: The LSN location where the timeline switch occurred.
  • reason: A human-readable explanation of why the timeline changed.

A specific example is shown below:

$ cat /home/postgres/archivelogs/00000002.history
1	  0/A000198	before 2024-1-1 12:05:00.861324+00

The meaning is as follows:

The database cluster (timelineId 2) is based on the base backup from timelineId 1. It was recovered just before ‘2024-1-1 12:05:00.861324+00’ by replaying archive logs until LSN ‘0/A000198’.

In this way, each timeline history file provides a complete history of an individual recovered database cluster. Moreover, the PITR process itself uses this file. The next section explains the details.

Info

The timeline history file format changed in version 9.3.

The formats are shown below:

Later version 9.3:

timelineId	LSN	"reason"

Until version 9.2:

timelineId	WAL_segment	"reason"