PostgreSQL Timelines and History File:Master Guide-技术圈

“TIMELINESARE THE DIVERGENT POINTS”

Let’s assume you did a point in timerecovery after a wrong transaction, PostgreSQL branches to a new timeline andcontinue with the operations.But what happens after you perform a pointin time recovery, realized you made a mistake again?That’s where recovery_target_timeline comesinto picture.In this post we are going to understandeverything about recovery_target_timeline and timelines in PostgreSQL ingeneral.PostgreSQLTimelineEverytime you do a transaction inPostgreSQL the information is recorded in a wal file under $DATADIR/wallocation.The first file that is created is 000000010000000000000001 and when it is filled the next wal will becreated with the name 000000010000000000000002 and so on.(Itis a HEX notation and more information is beyond the scope of this post)Here, the first eight digits representPostgreSQL timeline.In our example, the database cluster is intimeline 1.After every point in time recovery, thetimeline id will be increased by 1 and a new file called NewTimelineID.historyis created.recovery_target_timeline is a parameterwhich helps us to take our cluster to any timeline in the history provided avalid base backup and all the archivelogs in place.Lets consider below example.I have initialized and started a newcluster with the below command

-bash-4.1$ initdb -D basebackup1-bash-4.1$ pg_ctl start -D /u02/basebackup1

Then I created a table and inserted arecord into it

postgres=# create table timeline(tid int,remarks varchar(1000));CREATE TABLEpostgres=# insert into timelinevalues('1','This is timeline id 1');INSERT 0 1postgres=# checkpoint;CHECKPOINTpostgres=# select pg_switch_wal();pg_switch_wal---------------0/15D4B70(1 row)

My record is somewhere in my wal000000010000000000000001After few switches, I have taken a fullbackup when my wal at 000000010000000000000005

-bash-4.1$ ls -rlttotal 147460-rw------- 1 postgres postgres 16777216 Nov22 13:03 000000010000000000000001-rw------- 1 postgres postgres 16777216 Nov22 13:03 000000010000000000000002-rw------- 1 postgres postgres 16777216 Nov22 13:03 000000010000000000000003-rw------- 1 postgres postgres 16777216 Nov22 13:05 000000010000000000000004-rw------- 1 postgres postgres 16777216 Nov22 13:05 000000010000000000000005-rw------- 1 postgrespostgres      337 Nov 22 13:05000000010000000000000005.00000028.backup-rw------- 1 postgres postgres 16777216 Nov22 13:06 000000010000000000000006-rw------- 1 postgres postgres 16777216 Nov22 13:06 000000010000000000000007

Then I made a few switches and when my walis at 000000010000000000000008I inserted a new record.

postgres=# insert into timelinevalues('1','This is timeline id 1 after basebackup');INSERT 0 1postgres=# checkpoint;CHECKPOINT-bash-4.1$ pg_waldump000000010000000000000008 | grep INSERTrmgr:Heap        len(rec/tot):     54/   214,tx:        487, lsn: 0/08000110, prev 0/080000D8,desc: off 2 flags 0x00, blkref #0: rel 1663/13530/16384 blk 0 FPW-bash-4.1$

Then I made few switches and my currentarchived wal location is something like this

-bash-4.1$ ls -rlttotal 311308-rw------- 1  16777216 Nov 2213:03 000000010000000000000001-rw------- 1  16777216 Nov 2213:03 000000010000000000000002-rw------- 1  16777216 Nov 2213:03 000000010000000000000003-rw------- 1  16777216 Nov 2213:05 000000010000000000000004-rw------- 1  16777216 Nov 2213:05 000000010000000000000005-rw-------1       337 Nov 22 13:05000000010000000000000005.00000028.backup-rw------- 1  16777216 Nov 2213:06 000000010000000000000006-rw------- 1  16777216 Nov 2213:06 000000010000000000000007-rw------- 1  16777216 Nov 2213:07 000000010000000000000008-rw------- 1  16777216 Nov 2213:07 000000010000000000000009-rw------- 1  16777216 Nov 2213:09 00000001000000000000000A

At this point of time, I have done a pointin time recovery to some point in 000000010000000000000007 wal, before secondinsert.so I gave recovery target lsn as‘0/07000060’ in postgresql.conf file.

-bash-4.1$ cat postgresql.conf | grep lsnrecovery_target_lsn ='0/07000060'      # the WAL LSN up to whichrecovery will proceed-bash-4.1$

After successful point in time recovery,the PostgreSQL branched to a new timeline.At the end of recovery the following things will happenEnd of recovery means the point where thethe database opens up for writingNew timeline is chosenA timeline history file is writtenThe partial last WAL file on the previoustimeline is copied with the new timeline’s IDA checkpoint record is written on the newtimelinealert log says,

LOG:  starting point-in-timerecovery to WAL location (LSN) "0/7000060"LOG:  restored log file"000000010000000000000005" from archiveLOG:  redo starts at 0/5000028LOG:  consistent recovery statereached at 0/5000138LOG:  database system is ready toaccept read only connectionsLOG:  restored log file"000000010000000000000006" from archiveLOG:  restored log file"000000010000000000000007" from archiveLOG:  recovery stopping after WALlocation (LSN) "0/7000060"LOG:  pausing at the end ofrecoveryHINT:  Executepg_wal_replay_resume() to promote.

after executing pg_wal_replay_resume(), thepostgresql changed to timeline 2 and the same information is recorded inarchivelog location.

-rw------- 1 postgres postgres 16777216 Nov22 13:05 000000010000000000000005-rw------- 1 postgrespostgres      337 Nov 22 13:05000000010000000000000005.00000028.backup-rw------- 1 postgres postgres 16777216 Nov22 13:06 000000010000000000000006-rw------- 1 postgres postgres 16777216 Nov22 13:06 000000010000000000000007-rw------- 1 postgres postgres 16777216 Nov22 13:07 000000010000000000000008-rw------- 1 postgres postgres 16777216 Nov22 13:07 000000010000000000000009-rw------- 1 postgres postgres 16777216 Nov22 13:09 00000001000000000000000A-rw-r----- 1 postgrespostgres       33 Nov 22 13:12 00000002.history-rw-r----- 1 postgres postgres 16777216 Nov22 13:13 000000020000000000000007-rw------- 1 postgres postgres 16777216 Nov22 13:13 000000020000000000000008-rw------- 1 postgres postgres 16777216 Nov22 13:14 000000020000000000000009-rw-r----- 1 postgres postgres 16777216 Nov22 13:15 00000002000000000000000A-bash-4.1$

Here, the PostgreSQL has branched to a newtimeline at walfile 7 and started creating new walfiles with timeline id 2.The 00000002.history fileconfirms that the PostgreSQL has branched to new timeline.The history file is a small text file thatread

-bash-4.1$ cat 00000002.history1 0/70000D8 after LSN 0/7000060

Here

1    0/70000D8     after LSN 0/7000060parentTLI                 ID of the parent timelineswitchpoint                XLogRecPtrof the WAL location where the switch happenedreason                      human-readable explanation of why the timeline was changed

Now, I inserted one recordat 00000002000000000000000A (0/A000060)

postgres=# insert into timelinevalues('2','This is timeline id 2 correct');INSERT 0 1

and another record at00000002000000000000000D (0/D000000)

postgres=# insert into timelinevalues('2','This is timeline id 2 wrong at 0/D000000');INSERT 0 1

At this point of time I realized that I dida mistake at 00000002000000000000000D and has to rollback to 00000002000000000000000C of timeline 2.This can be achieved by setting belowparameters in postgresql.conf filerecovery_target_timeline = '2'recovery_target_lsn = '0/0C000060'After setting up above parameters, Istarted the cluster and the alert log says

LOG:  database system wasinterrupted; last known up at 2020-11-22 13:05:01 ISTLOG:  restored log file "00000002.history"from archivecp: cannot stat`/u02/archivelogs/00000003.history': No such file or directoryLOG:  starting point-in-timerecovery to WAL location (LSN) "0/C000060"LOG:  restored log file"00000002.history" from archiveLOG:  restored log file "000000010000000000000005"from archiveLOG:  redo starts at 0/5000028LOG:  consistent recovery statereached at 0/5000138LOG:  database system is ready toaccept read only connectionsLOG:  restored log file"000000010000000000000006" from archiveLOG:  restored log file"000000020000000000000007" from archiveLOG:  restored log file"000000020000000000000008" from archiveLOG:  restored log file"000000020000000000000009" from archiveLOG:  restored log file"00000002000000000000000A" from archiveLOG:  restored log file"00000002000000000000000B" from archiveLOG:  restored log file "00000002000000000000000C"from archiveLOG:  recovery stopping after WALlocation (LSN) "0/C000060"LOG:  pausing at the end ofrecoveryHINT:  Executepg_wal_replay_resume() to promote...LOG:  redo done at 0/C000060LOG:  last completed transactionwas at log time 2020-11-22 13:15:29.696929+05:30LOG:  selected new timeline ID: 3

When I selected the table,

postgres=# select * from timeline; tid|            remarks-----+-------------------------------   1 | This is timeline id 1   2 | This is timeline id 2correct(2 rows)

The history file will is recorded withbelow details

-bash-4.1$ cat 00000003.history1      0/70000D8       after LSN 0/7000060  2      0/C0000D8       after LSN 0/C000060

Heretimeline 1 branched at 0/70000D8timeline 2 branched at 0/C0000D8and current timeline is going to be 3.

-rw-r----- 1 postgres postgres 16777216 Nov22 13:16 00000002000000000000000F-rw-r----- 1 postgrespostgres       67 Nov 22 15:59 00000003.history-rw-r----- 1 postgres postgres 16777216 Nov22 16:05 00000003000000000000000C