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

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

According to the introduction of the WAL file structure in the previous sections, we can write a Mini Program that parses the transaction log to view the contents of the log file, but PG has helped us take into account that PG provides a tool for dump transaction log: pg_waldump.

Note: pg_waldump for PG 10.x, if it is PG 9.x or below, use pg_xlogdump.

A brief introduction to pg_waldump

Execute under Linux and use-- help to view help.

[xdb@localhost pg_wal] $pg_waldump-- helppg_waldump decodes and displays PostgreSQL write-ahead logs for debugging.Usage: pg_waldump [OPTION]. [STARTSEG [ENDSEG]] Options:-b,-- bkp-details output detailed information about backup blocks-e,-- end=RECPTR stop reading at WAL location RECPTR-f,-- follow keep retrying after reaching end of WAL-n,-- limit=N number of records to display-p -- path=PATH directory in which to find log segment files or a directory with a. / pg_wal that contains such files (default: current directory,. / pg_wal, $PGDATA/pg_wal)-r,-- rmgr=RMGR only show records generated by resource manager RMGR Use-- rmgr=list to list valid resource manager names-s,-- start=RECPTR start reading at WAL location RECPTR-t,-- timeline=TLI timeline from which to read log records (default: 1 or the value used in STARTSEG)-V,-- version output version information, then exit-x,-- xid=XID only show records with transaction ID XID-z -- stats [= record] show statistics instead of records (optionally, show per-record statistics)-help show this help, then exit [xdb@localhost pg_wal] $

-b,-- bkp-details output detailed information about backup blocks

Output the details of backup blocks, that is, full-write-page.

-e-- end=RECPTR stop reading at WAL location RECPTR

The search ends at this LSN offset

-f,-- follow keep retrying after reaching end of WAL

Continue to try when you reach the end of WAL

-n,-- limit=N number of records to display

Number of outputs of XLOG Record

-p,-- path=PATH directory in which to find log segment files or a

Directory with a. / pg_wal that contains such files

(default: current directory,. / pg_wal, $PGDATA/pg_wal)

In which directory do you look for WAL segment files

-r,-- rmgr=RMGR only show records generated by resource manager RMGR

Use-rmgr=list to list valid resource manager names

Displays only the XLOG Record of the specified RMGR

-s-- start=RECPTR start reading at WAL location RECPTR

Start the search at this LSN offset

-t,-- timeline=TLI timeline from which to read log records

(default: 1 or the value used in STARTSEG)

Specified timeline timeline

-V,-version output version information, then exit

Output version information, and then exit

-x,-- xid=XID only show records with transaction ID XID

Output only the XLOG Record of the specified transaction ID

-z,-- stats [= record] show statistics instead of records

(optionally, show per-record statistics)

Output statistics

-help show this help, then exit

Output help information

II. Use of pg_waldump

Here are the files in the pg_wal directory on the test machine

[xdb@localhost pg_wal] $lltotal 98332 RWMI. 1 xdb xdb 16777216 Dec 20 12:02 0000000100000100000048Murray. 1 xdb xdb 16777216 Dec 19 16:47 0000000100000100000049Murray. 1 xdb xdb 16777216 Dec 19 16:47 000000010000010000004A Murrw. 1 xdb xdb 16777216 Dec 19 16:47 000000010000010000004B Murrw. 1 xdb xdb 16777216 Dec 19 16:47 000000010000010000004C Murray. 1 xdb xdb 16777216 Dec 19 16:47 000000010000010000004Ddrwxmuri. 2 xdb xdb 6 Nov 16 15:48 archive_status

Output file 000000010000000100000048 first 4 XLOG Record

Command: pg_waldump-p. /-s 1max 48000000-n 4

[xdb@localhost pg_wal] $pg_waldump-p. /-s 1 4rmgr 48000000-n 4rmgr: Heap len (rec/tot): 77 / 77, tx: 1964, lsn: 1 rel 48000070, prev 1/47FFFFF8, desc: INSERT off 117028 blk 1110rmgr: Heap len (rec/tot): 77 / 77, tx: 1964, lsn: 1/480000C0, prev 1 Chart 48000070, desc: INSERT off 7 Blkref # 0: rel 1663 prev 1/480000C0 16402 blkref 17031 blk 1111rmgr: Heap len (rec/tot): 77 / 77, tx: 1964, lsn: 1 Acme 48000110, prev 1/480000C0, desc: INSERT off 8, blkref # 0: rel 16402 prev 1/480000C0 17031 blk 1111rmgr: Heap len (rec/tot): 77 / 77, tx: 1964, lsn: 148000160, prev 1 Chart 48000110, desc: INSERT off 9, blkref # 0: rel 166316402 blk 1111rmgr 17031 blk 1111

Note the first record, the previous LSN is 1/47FFFFF8 (prev 1/47FFFFF8), indicating that the last XLOG Record of the previous page is stored in the XLogLongPageHeaderData of this page. The size of the storage space can be calculated from the LSN of the XLOG Record (1pm 48000070) and the size of the XLogLongPageHeaderData (40B). Interested students can calculate by themselves.

Note: for the calculation of LSN, please refer to PostgreSQL DBA (15)-WAL file structure.

View XLOG Record after Redo point

First use the pg_controldata command to view Redo point-- > 1/484336A0

[xdb@localhost pg_wal] $pg_controldatapg_control version number: 1100Catalog version number: 201809051Database system identifier: 6624362124887945794Database cluster state: in productionpg_control last modified: Thu 20 Dec 2018 12:17:39 PM CSTLatest checkpoint location: 1/484336D8Latest checkpoint's REDO location: 1/484336A0Latest checkpoint's REDO WAL file: 000000010000000100000048Latest checkpoint's TimeLineID: 1.

Then use pg_waldump to view

Command: pg_waldump-p. /-s 1/484336A0

[xdb@localhost pg_wal] $pg_waldump-p. /-s 1/484336A0rmgr: Standby len (rec/tot): 50 / 50, tx: 0, lsn: 1/484336A0, prev 1 oldestRunningXid 1971rmgr 48433668, desc: RUNNING_XACTS nextXid 1971 latestCompletedXid 1970 oldestRunningXid 1971rmgr: XLOG len (rec/tot): 106 / 106, tx: 0, lsn: 1/484336D8, prev 1/484336A0, desc: CHECKPOINT_ONLINE redo 1 hand 484336A0; tli 1; prev tli 1; fpw true Xid 0multi 1971; oid 17046; multi 1; offset 0; oldest xid 561 in DB 16402; oldest multi 1 in DB 16402; oldest/newest commit timestamp xid: 0apace 0; oldest running xid 1971 Onlinermgr: Standby len (rec/tot): 50 / 50, tx: 0, lsn: 1oldestRunningXid 1971pg_waldump 48433748, prev 1/484336D8, desc: RUNNING_XACTS nextXid 1971 latestCompletedXid 1970 oldestRunningXid 1971pg_waldump: FATAL: error in WAL record at 1 oldestRunningXid 1971pg_waldump 48433748: invalid record length at 1 Universe 48433780: wanted 24, got 0 [xdb@localhost pg_wal] $, the study on "how to use the pg_waldump tool in PostgreSQL" is over. I hope you can 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.