pg_waldump工具
2020-09-15 15:23 abce 阅读(1061) 评论(0) 编辑 收藏 举报pg_waldump显示WAL,将WAL以人可以读的格式输出。主要用于调试或教育目的。这个实用程序只能由安装的用户运行,因为它需要对数据目录进行只读访问。
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