这篇文章主要介绍“PostgreSQL中REDO point分析”，在日常操作中，相信很多人在PostgreSQL中REDO point分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中REDO point分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

checkpointer进程启动时,从pg_control文件中获取Redo point并存储在内存中,在执行checkpoint时更新Redo point为当前即将写XLOG Record的位置,checkpoint执行成功后把Redo point更新到pg_control文件中.
类似的,数据库启动需要执行恢复时,从pg_control文件中获取REDO point进行恢复.
pg_control文件位于$PGDATA/global目录中,可通过命令pg_controldata查看文件中的内容.

[xdb@localhostpg111db]$find./-namepg_control./global/pg_control[xdb@localhostpg111db]$pg_controldatapg_controlversionnumber:1100Catalogversionnumber:201809051Databasesystemidentifier:6624362124887945794Databaseclusterstate:inproductionpg_controllastmodified:Thu20Dec201803:34:05PMCSTLatestcheckpointlocation:1/48447DF0Latestcheckpoint'sREDOlocation:1/48447DF0Latestcheckpoint'sREDOWALfile:000000010000000100000048Latestcheckpoint'sTimeLineID:1Latestcheckpoint'sPrevTimeLineID:1Latestcheckpoint'sfull_page_writes:onLatestcheckpoint'sNextXID:0:1979Latestcheckpoint'sNextOID:25238Latestcheckpoint'sNextMultiXactId:1Latestcheckpoint'sNextMultiOffset:0Latestcheckpoint'soldestXID:561Latestcheckpoint'soldestXID'sDB:16402Latestcheckpoint'soldestActiveXID:0Latestcheckpoint'soldestMultiXid:1Latestcheckpoint'soldestMulti'sDB:16402Latestcheckpoint'soldestCommitTsXid:0Latestcheckpoint'snewestCommitTsXid:0Timeoflatestcheckpoint:Thu20Dec201803:34:05PMCSTFakeLSNcounterforunloggedrels:0/1Minimumrecoveryendinglocation:0/0Minrecoveryendingloc'stimeline:0Backupstartlocation:0/0Backupendlocation:0/0End-of-backuprecordrequired:nowal_levelsetting:minimalwal_log_hintssetting:offmax_connectionssetting:100max_worker_processessetting:8max_prepared_xactssetting:0max_locks_per_xactsetting:64track_commit_timestampsetting:offMaximumdataalignment:8Databaseblocksize:8192Blockspersegmentoflargerelation:131072WALblocksize:8192BytesperWALsegment:16777216Maximumlengthofidentifiers:64Maximumcolumnsinanindex:32MaximumsizeofaTOASTchunk:1996Sizeofalarge-objectchunk:2048Date/timetypestorage:64-bitintegersFloat4argumentpassing:byvalueFloat8argumentpassing:byvalueDatapagechecksumversion:0Mockauthenticationnonce:90bf37566703859a557b7f20688eb944b6335b5c3d36f5530941ebf1dfa777c1[xdb@localhostpg111db]$

其中

Latestcheckpoint'sREDOlocation:1/48447DF0

记录的信息即为REDO point.

1.查看当前的REDO point

[xdb@localhostpg111db]$pg_controldata|grep'REDOlocation'Latestcheckpoint'sREDOlocation:1/48448150

REDO point为1/48448150.

2.执行DML操作
插入3条记录

testdb=#insertintocpvalues(7);INSERT01testdb=#insertintocpvalues(8);INSERT01testdb=#insertintocpvalues(9);INSERT01

查看1/48448150后的XLOG Record

[xdb@localhostpg_wal]$pg_waldump-p./-s1/48448150rmgr:XLOGlen(rec/tot):106/106,tx:0,lsn:1/48448150,prev1/484480E0,desc:CHECKPOINT_ONLINEredo1/48448150;tli1;prevtli1;fpwtrue;xid0:1982;oid25238;multi1;offset0;oldestxid561inDB16402;oldestmulti1inDB16402;oldest/newestcommittimestampxid:0/0;oldestrunningxid0;onlinermgr:Heaplen(rec/tot):54/474,tx:1982,lsn:1/484481C0,prev1/48448150,desc:INSERToff11,blkref#0:rel1663/16402/17046blk0FPWrmgr:Transactionlen(rec/tot):34/34,tx:1982,lsn:1/484483A0,prev1/484481C0,desc:COMMIT2018-12-2016:10:17.471639CSTrmgr:Heaplen(rec/tot):59/59,tx:1983,lsn:1/484483C8,prev1/484483A0,desc:INSERToff12,blkref#0:rel1663/16402/17046blk0rmgr:Transactionlen(rec/tot):34/34,tx:1983,lsn:1/48448408,prev1/484483C8,desc:COMMIT2018-12-2016:10:20.170594CSTrmgr:Heaplen(rec/tot):59/59,tx:1984,lsn:1/48448430,prev1/48448408,desc:INSERToff13,blkref#0:rel1663/16402/17046blk0rmgr:Transactionlen(rec/tot):34/34,tx:1984,lsn:1/48448470,prev1/48448430,desc:COMMIT2018-12-2016:10:22.268365CSTpg_waldump:FATAL:errorinWALrecordat1/48448470:invalidrecordlengthat1/48448498:wanted24,got0

最后一个XLOG Record记录的位置为1/48448470,加上记录大小34(十六进制为0x22),位置为1/48448492,按理论上来说,如果现在执行checkpoint,该位置为REDO point.

3.执行checkpoint

testdb=#checkpoint;CHECKPOINT

查看pg_control文件内容

[xdb@localhostpg111db]$pg_controldata|grep'REDOlocation'Latestcheckpoint'sREDOlocation:1/48448498

再次查看XLOG Record记录

[xdb@localhostpg_wal]$pg_waldump-p./-s1/48448150rmgr:XLOGlen(rec/tot):106/106,tx:0,lsn:1/48448150,prev1/484480E0,desc:CHECKPOINT_ONLINEredo1/48448150;tli1;prevtli1;fpwtrue;xid0:1982;oid25238;multi1;offset0;oldestxid561inDB16402;oldestmulti1inDB16402;oldest/newestcommittimestampxid:0/0;oldestrunningxid0;onlinermgr:Heaplen(rec/tot):54/474,tx:1982,lsn:1/484481C0,prev1/48448150,desc:INSERToff11,blkref#0:rel1663/16402/17046blk0FPWrmgr:Transactionlen(rec/tot):34/34,tx:1982,lsn:1/484483A0,prev1/484481C0,desc:COMMIT2018-12-2016:10:17.471639CSTrmgr:Heaplen(rec/tot):59/59,tx:1983,lsn:1/484483C8,prev1/484483A0,desc:INSERToff12,blkref#0:rel1663/16402/17046blk0rmgr:Transactionlen(rec/tot):34/34,tx:1983,lsn:1/48448408,prev1/484483C8,desc:COMMIT2018-12-2016:10:20.170594CSTrmgr:Heaplen(rec/tot):59/59,tx:1984,lsn:1/48448430,prev1/48448408,desc:INSERToff13,blkref#0:rel1663/16402/17046blk0rmgr:Transactionlen(rec/tot):34/34,tx:1984,lsn:1/48448470,prev1/48448430,desc:COMMIT2018-12-2016:10:22.268365CSTrmgr:XLOGlen(rec/tot):106/106,tx:0,lsn:1/48448498,prev1/48448470,desc:CHECKPOINT_ONLINEredo1/48448498;tli1;prevtli1;fpwtrue;xid0:1985;oid25238;multi1;offset0;oldestxid561inDB16402;oldestmulti1inDB16402;oldest/newestcommittimestampxid:0/0;oldestrunningxid0;onlinepg_waldump:FATAL:errorinWALrecordat1/48448498:invalidrecordlengthat1/48448508:wanted24,got0[xdb@localhostpg_wal]$

可以看到1/48448498是最后一条checkpoint记录的起始写入位置,REDO point为1/48448498,比预想的要多出6个字节(这6个字节都是0x00,用于补齐?).

4.dump WAL segment file
再次使用hexdump工具查看WAL segment file文件.

1/48448470开始的"COMMIT"记录

[xdb@localhostpg_wal]$echo"obase=10;ibase=16;448470"|bc4490352-->文件位置偏移[xdb@localhostpg_wal]$hexdump-C000000010000000100000048-s4490352-n340044847022000000c00700003084444801000000|".......0.DH....|0044848000010000835e7ad4ff08cd2f4a6e6e20|.....^z..../Jnn|004484900200|..|00448492

XLOG Record的头部首先是XLogRecord结构体,第一个域是uint32的record的大小,即0x00000022,十进制为34(大小无异).

后续的6个字节
均为0x00

[xdb@localhostpg_wal]$hexdump-C000000010000000100000048-s4490386-n600448492000000000000|......|00448498

XLOG Record for checkpoint

[xdb@localhostpg_wal]$hexdump-C000000010000000100000048-s4490392-n106004484986a000000000000007084444801000000|j.......p.DH....|004484a810000000acd28b95ff50988444480100|.........P..DH..|004484b800000100000001000000010000000000|................|004484c80000c107000096620000010000000000|.......b........|004484d800003102000012400000010000001240|..1....@.......@|004484e80000ba4e1b5c00000000000000000000|...N.\..........|004484f800000000000000000000|..........|00448502

大小为0x0000006A,即106B(头部XLogRecord 24B + XLogRecordDataHeaderShort 2B + Checkpoint 80B ).
从FF 50为XLogRecordDataHeaderShort结构体的内容,0xFF为标志位,0x50为Data的大小(即80B).
checkpoint记录的内容详见Checkpoint结构体,该结构体第一个域字段为8个字节的LSN-->0x00000001 48448498,
即REDO point:1/48448498.

到此，关于“PostgreSQL中REDO point分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！