PostgreSQL Timelines and History File:Master Guide

2021-02-14 資料庫架構

 「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.Everytime 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 TABLEpostgres=INSERT 0 1postgres=CHECKPOINTpostgres=pg_switch_wal0/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 000000010000000000000008
postgres=INSERT 0 1postgres=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 -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'      -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 writingA 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 newtimeline
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
1<parentTLI>    0/70000D8<switchpoint>     after LSN 0/7000060<reason>parentTLI                 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)and another record at00000002000000000000000D (0/D000000)
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
timeline 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

相關焦點

  • 打開你的開源社區GitHub之路 GitHub Guide 中文翻譯全文
    Tip: Open this guide in a separate browser window (or tab) so you can see it while you complete the steps in the tutorial.不必要寫代碼要完成這個教程，你需要一個GitHub帳戶並聯網。
  • postgresql email列表對NVM WAL BUFFER的討論
    [1]https://www.postgresql.org/message-id/83eafbfd-d9c5-6623-2423-7cab1be3888c%40iki.fi[2]https://www.postgresql.org/message-id/2aec6e2a-6a32-0c39-e4e2-aad854543aa8%40iki.fi[3] https
  • 滲透中利用postgresql getshell及注入技巧
    3.B站在線學習連結由於分享的資源連結經常被和諧，所以必須遷移到B站上供大家學習B站up主官方帳號：VMYKnetwork團隊0x00 前言研究postgresql資料庫如何getshell是在滲透中遇到一個pgAdmin的web管理頁面可以直接操作postgresql且通過網上的文章沒有達到9.6版本getshell的效果所以便有了以下文章。
  • PostgreSQL類型bit和varbit報錯引起的日誌不可讀
    本文提到的是一個很罕見的問題，因為bit類型用的人少，並且跟另一個組件fi
  • PostgreSQL初體驗
    學習參考地址：菜鳥教程：https://www.runoob.com/postgresql/postgresql-tutorial.htmlW3C:https://www.w3cschool.cn/qysrc/qysrc-jwxg3758.html首先是配置環境，本人使用的是windows系統，接下來都是windows下的一些學習過程。
  • PostgreSQL函數
    /docs/12/xfunc-volatility.htmlhttps://github.com/digoal/blog/blob/master/201804/20180410_01.md?spm=a2c4e.10696291.0.0.362d19a4dclgcw&file=20180410_01.mdhttps://github.com/digoal/blog/blob/master/201212/20121226_01.md?
  • Teens get a history lesson from history makers
    Related stories:A school newspaper links Sioux Indian teens to their communityCollege guide aims to help students avoid a 'thin education'Tickets for forbidden history go online
  • 將函數索引從Oracle遷移到PostgreSQL
    參考文檔：函數穩定性講解- retalk PostgreSQL function's [volatile|stable|immutable ]https://github.com/digoal/blog/blob/master/201212/20121226_01.mdMigratingFunction
  • PostgreSQL簡介及安裝
    步驟：（1）訪問PostgreSQL官方主頁的下載區https://www.postgresql.org/download（2）點擊Binary packages->Linux->Red Hat family Linux (including CentOS/ Fedora/ Scientific/ Oracle variants
  • 雲計算核心技術Docker教程:Dockerfile文件ARG命令詳解
    Dockerfile 中的 ARG 指令是定義參數名稱，以及定義其默認值。該默認值可以在構建命令 docker build 中用 --build-arg 參數名=值 來覆蓋。
  • 去哪兒PostgreSQL指南
    主要內容命名規範Column設計Constraints 設計Index 設計關於NULL開發相關規範管理相關規範1 命名規範1.DB object: database, schema, table
  • 【PhD的自我修養】Job Market Guide
    JUNIOR ACADEMIC JOB MARKET 2018-2019 Editionhttps://www.aeaweb.org/content/file?id=869必讀經典A Guide for European Job Market Candidateshttps://www.eeassoc.org/doc/upload/JM_guide_11Set1820180911225514.pdf12. How to survive the Econ Job Market?
  • Master's stroke
    In 1931, master painter Qi Baishi received an artwork from Lin Fengmian, a fellow artist and then-principal of the Hangzhou Fine Arts School (now the China Academy of Art) in the country's east.
  • PostgreSQL 10 新增特性
    （點擊上方公眾號，可快速關注）來源：oschinawww.oschina.net/news/83681/new-features-coming-in-postgresql
  • BEC初級詞彙加油站20:rarely,master
    copy 原本，標準本；master credit 原始信用狀；master document 總單證；master list 總清單詳細含義分解：noun僕人 of servants1(old-fashioned
  • 天天英語|More than ever before in human history……
    51Talk一句話教你說早安▽More than ever before in human history, we share a common destiny.We can master it only if we face it together. And that is why we have the United Nations. 在人類歷史上，我們從未像現在這樣休戚與共。
  • This week we meet master coconut carver Mr Wu Mingju in Qi lou
    This week we meet with master coconut carver Mr Wu Mingju in Haikou
  • PostgreSQL連接Perl
    >host    all         all         127.0.0.1/32          md5 可以使用下面的命令啟動/重新啟動Postgres的伺服器的如果它沒有運行：[root@host]# service postgresql