Shulou(Shulou.com)05/31 Report--

This article mainly introduces "REDO point Analysis in PostgreSQL". In daily operation, I believe many people have doubts about REDO point analysis in PostgreSQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "REDO point Analysis in PostgreSQL". Next, please follow the editor to study!

1. Storage of REDO point

When the checkpointer process starts, the Redo point is obtained from the pg_control file and stored in memory. When the checkpoint is executed, the Redo point is updated to the location where the XLOG Record is about to be written. After the checkpoint execution is successful, the Redo point is updated to the pg_control file.

Similarly, when a database startup needs to perform a restore, get the REDO point from the pg_control file for recovery.

The pg_control file is located in the $PGDATA/global directory, and you can view the contents of the file through the command 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 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] $

Among them

Latest checkpoint's REDO location: 1/48447DF0

The recorded information is REDO point.

II. Changes in REDO point

1. View the current REDO point

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

The REDO point is 1pm 48448150.

two。 Perform DML operation

Insert 3 records

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

View the XLOG Record after 1 / 48448150

[xdb@localhost pg_wal] $pg_waldump-p. /-s 1/48448150rmgr: XLOG len (rec/tot): 106 / 106, tx: 0, lsn: 1Accord 48448150, prev 1/484480E0, desc: CHECKPOINT_ONLINE redo 1hand 48448150; tli 1; prev tli 1; fpw true; xid 0Rose 1982; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0ramp 0; oldest running xid 0 Onlinermgr: Heap len (rec/tot): 54 / 47474, tx: 1982, lsn: 1/484481C0, prev 1max 48448150, desc: INSERT off 11, blkref # 0: rel 1663xx 16402 blk 0 FPWrmgr: Transaction len (rec/tot): 34 / 34, tx: 1982, lsn: 1/484483A0, prev 1/484481C0, desc: COMMIT 2018-12-20 16 purse 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 pep 16402 rel 17046 blk 0rmgr: Transaction len (rec/tot): 34 / 34, tx: 1983, lsn: 1Unip 48448408, prev 1/484483C8, desc: COMMIT 2018-12-20 16 purve10 purve20.170594 CSTrmgr: Heap len (rec/tot): 59 / 59, tx: 1984, lsn: 1deband 48448430, prev 148448408, desc: 13 INSERT off Blkref # 0: rel 16402 rec/tot 17046 blk 0rmgr: Transaction len (rec/tot): 34 / 34, tx: 1984, lsn: 1mae 48448470, prev 1max 48448430, desc: COMMIT 2018-12-20 16 purse 10 Transaction len 22.268365 CSTpg_waldump: FATAL: error in WAL record at 1hand 48448470: invalid record length at 1 48448498: wanted 24, got 0

The position of the last XLOG Record record is 1max 48448470, plus the record size 34 (hexadecimal is 0x22), and the position is 1max 48448492. Theoretically, if checkpoint is executed now, the location is REDO point.

3. Execute checkpoint

Testdb=# checkpoint;CHECKPOINT

View the contents of the pg_control file

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

View the XLOG Record record again

[xdb@localhost pg_wal] $pg_waldump-p. /-s 1/48448150rmgr: XLOG len (rec/tot): 106 / 106, tx: 0, lsn: 1Accord 48448150, prev 1/484480E0, desc: CHECKPOINT_ONLINE redo 1hand 48448150; tli 1; prev tli 1; fpw true; xid 0Rose 1982; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0ramp 0; oldest running xid 0 Onlinermgr: Heap len (rec/tot): 54 / 47474, tx: 1982, lsn: 1/484481C0, prev 1max 48448150, desc: INSERT off 11, blkref # 0: rel 1663xx 16402 blk 0 FPWrmgr: Transaction len (rec/tot): 34 / 34, tx: 1982, lsn: 1/484483A0, prev 1/484481C0, desc: COMMIT 2018-12-20 16 purse 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 pep 16402 rel 17046 blk 0rmgr: Transaction len (rec/tot): 34 / 34, tx: 1983, lsn: 1Unip 48448408, prev 1/484483C8, desc: COMMIT 2018-12-20 16 purve10 purve20.170594 CSTrmgr: Heap len (rec/tot): 59 / 59, tx: 1984, lsn: 1deband 48448430, prev 148448408, desc: 13 INSERT off Blkref # 0: rel 16402 rec/tot 17046 blk 0rmgr: Transaction len (rec/tot): 34 / 34, tx: 1984, lsn: 1mae 48448470, prev 1max 48448430, desc: COMMIT 2018-12-20 16 purge 10 XLOG len 22.268365 CSTrmgr: XLOG len (rec/tot): 106 / 106, tx: 0, lsn: 1Accord 48448498, prev 148448470, desc: CHECKPOINT_ONLINE redo 1UX 48448498 Tli 1; prev tli 1; fpw true; xid 0VERV 1985; oid 25238; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0in DB 0; oldest running xid 0; onlinepg_waldump: FATAL: error in WAL record at 1 in DB 48448498: invalid record length at 1 in DB 48448508: wanted 24, got 0 [xdb@localhost pg_wal] $

You can see that 1am 48448498 is the starting write position of the last checkpoint record, and the REDO point is 1pm 48448498, which is 6 bytes more than expected (these 6 bytes are all 0x00, used to make up?).

4.dump WAL segment file

Use the hexdump tool to view the WAL segment file file again.

1max 48448470 start "COMMIT" record

[xdb@localhost pg_wal] $echo "obase=10;ibase=16 | 448470 "| bc4490352-- > File location offset [xdb@localhost pg_wal] $hexdump-C 0000000100000100000048-s 4490352-n 3400448470 22000000 c007 0000 30 84 44 48 01000000 |" .0.DH.... | 00448480 00010000 835e 7a d4 ff 08 cd 2f 6e 6e 20 |. Z..../Jnn | 00448490 02 00 |.. | 00448492 |

The header of XLOG Record is the XLogRecord structure, and the first field is the size of uint32's record, that is, 0x00000022, with a decimal size of 34 (the same size).

The next 6 bytes

All are 0x00.

[xdb@localhost pg_wal] $hexdump-C 0000000100000100000048-s 4490386-n 600448492 000000000 |. | 00448498

XLOG Record for checkpoint

[xdb@localhost pg_wal] $hexdump-C 0000000100000100000048-s 4490392-n 106 00448498 6a 000000000000 00 70 84 44 48 01000000 | j.p.DH. | 004484a8 10000000 ac d2 8b 95 ff 50 98 84 44 48 0100 | .P.DH.. | 004484b8 000001000000000000000000000000 |. | 004484c8 00 00 C1 07 00 00 96 62 00 01 00 00 00 | .b. | 004484d8 00 31 02 00 00 12 40 00 00 01 00 00 12 40 |. @. @ | 004484e8 00 00 ba 4e 1b 5c 00 00 00 |. N.\. | 004484f8 00 00 00 |. | 00448502

The size is 0x0000006A, that is 106B (head XLogRecord 24B + XLogRecordDataHeaderShort 2B + Checkpoint 80B).

FF 50 is the content of XLogRecordDataHeaderShort structure, 0xFF is the marker bit, and 0x50 is the size of Data (i.e. 80B).

For details of the checkpoint record, see the Checkpoint structure, whose first field field is 8-byte LSN-- > 0x00000001 48448498.

That is, REDO point:1/48448498.

At this point, the study of "REDO point Analysis in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.