pg_waldump工具

2020-09-15 15:23 abce 阅读(1089) 评论(0) 编辑收藏举报

pg_waldump显示WAL，将WAL以人可以读的格式输出。主要用于调试或教育目的。这个实用程序只能由安装的用户运行，因为它需要对数据目录进行只读访问。

语法

1	`pg_waldump [option...] [startseg [endseg] ]`

参数

$ pg_waldump --help
pg_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 
                         #输出backup blocks的详细信息，即full-write-page信息
  -e, --end=RECPTR       stop reading at WAL location RECPTR
                         #停止读取WAL的位置
  -f, --follow           keep retrying after reaching end of WAL
                         #在到达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

查看WAL的资源管理器列表：

$ pg_waldump -r list
XLOG
Transaction
Storage
CLOG
Database
Tablespace
MultiXact
RelMap
Standby
Heap2
Heap
Btree
Hash
Gin
Gist
Sequence
SPGist
BRIN
CommitTs
ReplicationOrigin
Generic
LogicalMessage

输出STARTSEG到ENDSEG的事务日志

$ ll
-rw------- 1 postgres postgres 16777216 Sep 15 11:55 0000015C0000006A00000036
-rw------- 1 postgres postgres 16777216 Sep 15 14:32 0000015C0000006A00000037
-rw------- 1 postgres postgres 16777216 Sep 15 14:58 0000015C0000006A00000038
-rw------- 1 postgres postgres 16777216 Sep 14 23:55 0000015C0000006A00000039
-rw------- 1 postgres postgres 16777216 Sep 15 03:55 0000015C0000006A0000003A
-rw------- 1 postgres postgres 16777216 Sep 15 05:25 0000015C0000006A0000003B
-rw------- 1 postgres postgres     4457 Aug 16 15:21 0000015C.history
drwx------ 2 postgres postgres     4096 Sep 15 14:33 archive_status
 
$ pg_waldump 0000015C0000006A00000037 0000015C0000006A0000003A |more
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913618, lsn: 6A/37000560, prev 6A/36FFFD68, desc: INSERT_LEAF off 77, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913618, lsn: 6A/370005A0, prev 6A/37000560, desc: COMMIT 2020-09-15 11:55:51.480718 CST
rmgr: Heap        len (rec/tot):    904/  5456, tx:   16913619, lsn: 6A/370005D0, prev 6A/370005A0, desc: HOT_UPDATE off 6 xmax 16913619 ; new off 1 xmax 0, blkref #0: rel 1663/36838/407654 blk 23 FPW
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913619, lsn: 6A/37001B20, prev 6A/370005D0, desc: COMMIT 2020-09-15 11:55:51.483057 CST
rmgr: Heap        len (rec/tot):   1867/  1867, tx:   16913621, lsn: 6A/37001B50, prev 6A/37001B20, desc: INSERT off 4, blkref #0: rel 1663/36838/1294702 blk 94267
rmgr: Heap        len (rec/tot):   1875/  1875, tx:   16913622, lsn: 6A/370022B8, prev 6A/37001B50, desc: INSERT+INIT off 1, blkref #0: rel 1663/36838/1294702 blk 94269
rmgr: Heap        len (rec/tot):   1858/  1858, tx:   16913620, lsn: 6A/37002A10, prev 6A/370022B8, desc: INSERT+INIT off 1, blkref #0: rel 1663/36838/1294702 blk 94270
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913621, lsn: 6A/37003158, prev 6A/37002A10, desc: INSERT_LEAF off 78, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913622, lsn: 6A/37003198, prev 6A/37003158, desc: INSERT_LEAF off 79, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913620, lsn: 6A/370031D8, prev 6A/37003198, desc: INSERT_LEAF off 78, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913621, lsn: 6A/37003218, prev 6A/370031D8, desc: COMMIT 2020-09-15 11:55:51.492875 CST
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913622, lsn: 6A/37003248, prev 6A/37003218, desc: COMMIT 2020-09-15 11:55:51.492878 CST
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913620, lsn: 6A/37003278, prev 6A/37003248, desc: COMMIT 2020-09-15 11:55:51.492882 CST
rmgr: Heap        len (rec/tot):    364/  2512, tx:   16913623, lsn: 6A/370032A8, prev 6A/37003278, desc: HOT_UPDATE off 6 xmax 16913623 ; new off 11 xmax 0, blkref #0: rel 1663/36838/407654 blk 76 FPW
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913623, lsn: 6A/37003C78, prev 6A/370032A8, desc: COMMIT 2020-09-15 11:55:51.494092 CST
rmgr: Heap        len (rec/tot):    351/   351, tx:   16913624, lsn: 6A/37003CA8, prev 6A/37003C78, desc: HOT_UPDATE off 8 xmax 16913624 ; new off 3 xmax 0, blkref #0: rel 1663/36838/407654 blk 100
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913624, lsn: 6A/37003E08, prev 6A/37003CA8, desc: COMMIT 2020-09-15 11:55:51.494374 CST
rmgr: Heap        len (rec/tot):    367/   367, tx:   16913625, lsn: 6A/37003E38, prev 6A/37003E08, desc: HOT_UPDATE off 14 xmax 16913625 ; new off 19 xmax 0, blkref #0: rel 1663/36838/407654 blk 14
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913625, lsn: 6A/37003FA8, prev 6A/37003E38, desc: COMMIT 2020-09-15 11:55:51.494512 CST
rmgr: Heap        len (rec/tot):   1876/  1876, tx:   16913626, lsn: 6A/37003FD8, prev 6A/37003FA8, desc: INSERT off 3, blkref #0: rel 1663/36838/1294702 blk 94268
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913626, lsn: 6A/37004748, prev 6A/37003FD8, desc: INSERT_LEAF off 81, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913626, lsn: 6A/37004788, prev 6A/37004748, desc: COMMIT 2020-09-15 11:55:51.495797 CST
rmgr: Heap        len (rec/tot):    367/   367, tx:   16913627, lsn: 6A/370047B8, prev 6A/37004788, desc: HOT_UPDATE off 7 xmax 16913627 ; new off 3 xmax 0, blkref #0: rel 1663/36838/407654 blk 23
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913627, lsn: 6A/37004928, prev 6A/370047B8, desc: COMMIT 2020-09-15 11:55:51.497043 CST
rmgr: Heap        len (rec/tot):   1861/  1861, tx:   16913628, lsn: 6A/37004958, prev 6A/37004928, desc: INSERT+INIT off 1, blkref #0: rel 1663/36838/1294702 blk 94271
rmgr: Heap        len (rec/tot):   1869/  1869, tx:   16913629, lsn: 6A/370050A0, prev 6A/37004958, desc: INSERT off 4, blkref #0: rel 1663/36838/1294702 blk 94268
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913628, lsn: 6A/370057F0, prev 6A/370050A0, desc: INSERT_LEAF off 82, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Btree       len (rec/tot):     64/    64, tx:   16913629, lsn: 6A/37005830, prev 6A/370057F0, desc: INSERT_LEAF off 83, blkref #0: rel 1663/36838/1294709 blk 1123
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913628, lsn: 6A/37005870, prev 6A/37005830, desc: COMMIT 2020-09-15 11:55:51.522523 CST
rmgr: Transaction len (rec/tot):     46/    46, tx:   16913629, lsn: 6A/370058A0, prev 6A/37005870, desc: COMMIT 2020-09-15 11:55:51.522530 CST