PostgreSQL中REDO point分析

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

一、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@localhost pg111db]$ find ./ -name pg_control./global/pg_control[xdb@localhost pg111db]$ pg_controldatapg_control version number:            1100Catalog version number:               201809051Database system identifier:           6624362124887945794Database cluster state:               in productionpg_control last modified:             Thu 20 Dec 2018 03:34:05 PM CSTLatest checkpoint location:           1/48447DF0Latest checkpoint's REDO location:    1/48447DF0Latest checkpoint's REDO WAL file:    000000010000000100000048Latest checkpoint's TimeLineID:       1Latest checkpoint's PrevTimeLineID:   1Latest checkpoint's full_page_writes: onLatest checkpoint's NextXID:          0:1979Latest checkpoint's NextOID:          25238Latest checkpoint's NextMultiXactId:  1Latest checkpoint's NextMultiOffset:  0Latest checkpoint's oldestXID:        561Latest checkpoint's oldestXID's DB:   16402Latest checkpoint's oldestActiveXID:  0Latest checkpoint's oldestMultiXid:   1Latest checkpoint's oldestMulti's DB: 16402Latest checkpoint's oldestCommitTsXid:0Latest checkpoint's newestCommitTsXid:0Time of latest checkpoint:            Thu 20 Dec 2018 03:34:05 PM CSTFake LSN counter for unlogged rels:   0/1Minimum recovery ending location:     0/0Min recovery ending loc's timeline:   0Backup start location:                0/0Backup end location:                  0/0End-of-backup record required:        nowal_level setting:                    minimalwal_log_hints setting:                offmax_connections setting:              100max_worker_processes setting:         8max_prepared_xacts setting:           0max_locks_per_xact setting:           64track_commit_timestamp setting:       offMaximum data alignment:               8Database block size:                  8192Blocks per segment of large relation: 131072WAL block size:                       8192Bytes per WAL segment:                16777216Maximum length of identifiers:        64Maximum columns in an index:          32Maximum size of a TOAST chunk:        1996Size of a large-object chunk:         2048Date/time type storage:               64-bit integersFloat4 argument passing:              by valueFloat8 argument passing:              by valueData page checksum version:           0Mock authentication nonce:            90bf37566703859a557b7f20688eb944b6335b5c3d36f5530941ebf1dfa777c1[xdb@localhost pg111db]$

其中

Latest checkpoint's REDO location:    1/48447DF0

记录的信息即为REDO point.

二、REDO point的变更

1.查看当前的REDO point

[xdb@localhost pg111db]$ pg_controldata|grep 'REDO location'Latest checkpoint's REDO location:    1/48448150

REDO point为1/48448150.

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

testdb=# insert into cp values(7);INSERT 0 1testdb=# insert into cp values(8);INSERT 0 1testdb=# insert into cp values(9);INSERT 0 1

查看1/48448150后的XLOG Record

[xdb@localhost pg_wal]$ pg_waldump -p ./ -s 1/48448150rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 1/48448150, prev 1/484480E0, desc: CHECKPOINT_ONLINE redo 1/48448150; tli 1; prev tli 1; fpw true; xid 0:1982; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; onlinermgr: Heap        len (rec/tot):     54/   474, tx:       1982, lsn: 1/484481C0, prev 1/48448150, desc: INSERT off 11, blkref #0: rel 1663/16402/17046 blk 0 FPWrmgr: Transaction len (rec/tot):     34/    34, tx:       1982, lsn: 1/484483A0, prev 1/484481C0, desc: COMMIT 2018-12-20 16:10:17.471639 CSTrmgr: Heap        len (rec/tot):     59/    59, tx:       1983, lsn: 1/484483C8, prev 1/484483A0, desc: INSERT off 12, blkref #0: rel 1663/16402/17046 blk 0rmgr: Transaction len (rec/tot):     34/    34, tx:       1983, lsn: 1/48448408, prev 1/484483C8, desc: COMMIT 2018-12-20 16:10:20.170594 CSTrmgr: Heap        len (rec/tot):     59/    59, tx:       1984, lsn: 1/48448430, prev 1/48448408, desc: INSERT off 13, blkref #0: rel 1663/16402/17046 blk 0rmgr: Transaction len (rec/tot):     34/    34, tx:       1984, lsn: 1/48448470, prev 1/48448430, desc: COMMIT 2018-12-20 16:10:22.268365 CSTpg_waldump: FATAL:  error in WAL record at 1/48448470: invalid record length at 1/48448498: wanted 24, got 0

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

3.执行checkpoint

testdb=# checkpoint;CHECKPOINT

查看pg_control文件内容

[xdb@localhost pg111db]$ pg_controldata|grep 'REDO location'Latest checkpoint's REDO location:    1/48448498

再次查看XLOG Record记录

[xdb@localhost pg_wal]$ pg_waldump -p ./ -s 1/48448150rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 1/48448150, prev 1/484480E0, desc: CHECKPOINT_ONLINE redo 1/48448150; tli 1; prev tli 1; fpw true; xid 0:1982; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; onlinermgr: Heap        len (rec/tot):     54/   474, tx:       1982, lsn: 1/484481C0, prev 1/48448150, desc: INSERT off 11, blkref #0: rel 1663/16402/17046 blk 0 FPWrmgr: Transaction len (rec/tot):     34/    34, tx:       1982, lsn: 1/484483A0, prev 1/484481C0, desc: COMMIT 2018-12-20 16:10:17.471639 CSTrmgr: Heap        len (rec/tot):     59/    59, tx:       1983, lsn: 1/484483C8, prev 1/484483A0, desc: INSERT off 12, blkref #0: rel 1663/16402/17046 blk 0rmgr: Transaction len (rec/tot):     34/    34, tx:       1983, lsn: 1/48448408, prev 1/484483C8, desc: COMMIT 2018-12-20 16:10:20.170594 CSTrmgr: Heap        len (rec/tot):     59/    59, tx:       1984, lsn: 1/48448430, prev 1/48448408, desc: INSERT off 13, blkref #0: rel 1663/16402/17046 blk 0rmgr: Transaction len (rec/tot):     34/    34, tx:       1984, lsn: 1/48448470, prev 1/48448430, desc: COMMIT 2018-12-20 16:10:22.268365 CSTrmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 1/48448498, prev 1/48448470, desc: CHECKPOINT_ONLINE redo 1/48448498; tli 1; prev tli 1; fpw true; xid 0:1985; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; onlinepg_waldump: FATAL:  error in WAL record at 1/48448498: invalid record length at 1/48448508: wanted 24, got 0[xdb@localhost pg_wal]$

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

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

1/48448470开始的"COMMIT"记录

[xdb@localhost pg_wal]$ echo "obase=10;ibase=16;448470"|bc4490352 --> 文件位置偏移[xdb@localhost pg_wal]$ hexdump -C 000000010000000100000048 -s 4490352 -n 34 00448470  22 00 00 00 c0 07 00 00  30 84 44 48 01 00 00 00  |".......0.DH....|00448480  00 01 00 00 83 5e 7a d4  ff 08 cd 2f 4a 6e 6e 20  |.....^z..../Jnn |00448490  02 00                                             |..|00448492

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

后续的6个字节
均为0x00

[xdb@localhost pg_wal]$ hexdump -C 000000010000000100000048 -s 4490386 -n 6 00448492  00 00 00 00 00 00                                 |......|00448498

XLOG Record for checkpoint

[xdb@localhost pg_wal]$ hexdump -C 000000010000000100000048 -s 4490392 -n 106 00448498  6a 00 00 00 00 00 00 00  70 84 44 48 01 00 00 00  |j.......p.DH....|004484a8  10 00 00 00 ac d2 8b 95  ff 50 98 84 44 48 01 00  |.........P..DH..|004484b8  00 00 01 00 00 00 01 00  00 00 01 00 00 00 00 00  |................|004484c8  00 00 c1 07 00 00 96 62  00 00 01 00 00 00 00 00  |.......b........|004484d8  00 00 31 02 00 00 12 40  00 00 01 00 00 00 12 40  |..1....@.......@|004484e8  00 00 ba 4e 1b 5c 00 00  00 00 00 00 00 00 00 00  |...N.\..........|004484f8  00 00 00 00 00 00 00 00  00 00                    |..........|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分析"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！