postgresql 的WAL日志解析工具 pg_waldump
·
基本使用
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# 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
-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)
-q, --quiet do not print any output, except for errors
-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
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
中文翻译
STARTSEG 从指定的日志段文件开始读取。这也隐含地决定了要搜索文件的路径以及 要使用的时间线。
ENDSEG 在读取指定的日志段文件后停止。
Options:
-b, --bkp-details 输出有关备份块的细节。
-e, --end=RECPTR 在指定的日志位置停止读取，而不是一直读取到日志流的末尾。
-f, --follow 在到达可用 WAL 的末尾之后，保持每秒轮询一次是否有新的 WAL 出现。
-n, --limit=N 显示指定数量的记录，然后停止。
-p, --path=PATH 要在哪个目录中寻找日志段文件。默认是在当前目录的pg_xlog 子目录中搜索。
-r, --rmgr=RMGR 只显示由指定资源管理器生成的记录。如果把list作为资源管理器名称 传递给这个选项，则打印出可用资源管理器名称的列表然后退出。
-s, --start=RECPTR 要从哪个日志位置开始读取。默认是从找到的最早的文件的第一个可用日志记录开始。
-t, --timeline=TLI 要从哪个时间线读取日志记录。默认是使用startseg（如果指定） 中的值，否则默认为 1
-V, --version 打印pg_xlogdump版本并且退出。
-x, --xid=XID 只显示用给定事务 ID 标记的记录。
-z, --stats[=record] 显示概括统计信息（记录的数量和尺寸以及全页镜像）而不是显示 每个记录。可以选择针对每个记录生成统计信息，而不是针对每个 资源管理器生成。
-?, --help show this help, then exit
实践
展示 wal 中所有的资源管理名称
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# pg_waldump --rmgr=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 的事务日志
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# ls
000000010000000000000013 000000010000000000000014 000000010000000000000015 000000010000000000000016 000000010000000000000017 archive_status
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# pg_waldump 000000010000000000000013 000000010000000000000017
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000028, prev 0/12011720, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000060, prev 0/13000028, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/13000098, prev 0/13000060, desc: CHECKPOINT_ONLINE redo 0/13000060; tli 1; prev tli 1; fpw true; xid 0:748; oid 24582; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 748; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000110, prev 0/13000098, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: Heap len (rec/tot): 54/ 150, tx: 748, lsn: 0/13000148, prev 0/13000110, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/16393/16536 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 748, lsn: 0/130001E0, prev 0/13000148, desc: COMMIT 2021-12-09 08:52:27.138352 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000208, prev 0/130001E0, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000240, prev 0/13000208, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/13000278, prev 0/13000240, desc: CHECKPOINT_ONLINE redo 0/13000240; tli 1; prev tli 1; fpw true; xid 0:749; oid 24582; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 749; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/130002F0, prev 0/13000278, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: Heap len (rec/tot): 54/ 186, tx: 749, lsn: 0/13000328, prev 0/130002F0, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/16393/16536 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 749, lsn: 0/130003E8, prev 0/13000328, desc: COMMIT 2021-12-09 08:56:54.988836 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000410, prev 0/130003E8, desc: RUNNING_XACTS nextXid 750 latestCompletedXid 749 oldestRunningXid 750
rmgr: Heap len (rec/tot): 54/ 54, tx: 750, lsn: 0/13000448, prev 0/13000410, desc: DELETE off 1 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16393/16536 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 750, lsn: 0/13000480, prev 0/13000448, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16393/16536 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 750, lsn: 0/130004B8, prev 0/13000480, desc: COMMIT 2021-12-09 08:58:08.529192 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/130004E0, prev 0/130004B8, desc: RUNNING_XACTS nextXid 751 latestCompletedXid 750 oldestRunningXid 751
rmgr: Heap len (rec/tot): 70/ 70, tx: 751, lsn: 0/13000518, prev 0/130004E0, desc: HOT_UPDATE off 3 xmax 751 flags 0x00 ; new off 4 xmax 0, blkref #0: rel 1663/16393/16536 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 751, lsn: 0/13000560, prev 0/13000518, desc: COMMIT 2021-12-09 09:00:55.140021 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000588, prev 0/13000560, desc: RUNNING_XACTS nextXid 752 latestCompletedXid 751 oldestRunningXid 752
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/130005C0, prev 0/13000588, desc: RUNNING_XACTS nextXid 752 latestCompletedXid 751 oldestRunningXid 752
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/130005F8, prev 0/130005C0, desc: CHECKPOINT_ONLINE redo 0/130005C0; tli 1; prev tli 1; fpw true; xid 0:752; oid 24582; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 752; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000670, prev 0/130005F8, desc: RUNNING_XACTS nextXid 752 latestCompletedXid 751 oldestRunningXid 752
pg_waldump: fatal: error in WAL record at 0/13000670: invalid record length at 0/130006A8: wanted 24, got 0
日志参数介绍
rmgr : 资源名称
lsn: 0/0162D3F0 日志编号
prev 0/0162D3B8
desc ： 对日志详细信息的描述
xid 事务id
postgresql之根据lsn 获取 wal文件名
select pg_current_wal_lsn(),
pg_walfile_name(pg_current_wal_lsn()),
pg_walfile_name_offset(pg_current_wal_lsn());
插入，删除，修改数据操作的日志
wal 日志会先进入资源等待 rmgr: Standby ，分配一个事务id 748,进入rmgr: Heap,把插入事务写入到指定日志偏移位置 desc: INSERT off 2,分配下一事务id
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# pg_waldump 000000010000000000000013
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000028, prev 0/12011720, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000060, prev 0/13000028, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/13000098, prev 0/13000060, desc: CHECKPOINT_ONLINE redo 0/13000060; tli 1; prev tli 1; fpw true; xid 0:748; oid 24582; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 748; online
## 插入数据
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000110, prev 0/13000098, desc: RUNNING_XACTS nextXid 748 latestCompletedXid 747 oldestRunningXid 748
rmgr: Heap len (rec/tot): 54/ 150, tx: 748, lsn: 0/13000148, prev 0/13000110, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/16393/16536 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 748, lsn: 0/130001E0, prev 0/13000148, desc: COMMIT 2021-12-09 08:52:27.138352 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000208, prev 0/130001E0, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000240, prev 0/13000208, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/13000278, prev 0/13000240, desc: CHECKPOINT_ONLINE redo 0/13000240; tli 1; prev tli 1; fpw true; xid 0:749; oid 24582; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 749; online
## 插入数据
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/130002F0, prev 0/13000278, desc: RUNNING_XACTS nextXid 749 latestCompletedXid 748 oldestRunningXid 749
rmgr: Heap len (rec/tot): 54/ 186, tx: 749, lsn: 0/13000328, prev 0/130002F0, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/16393/16536 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 749, lsn: 0/130003E8, prev 0/13000328, desc: COMMIT 2021-12-09 08:56:54.988836 UTC
## 删除数据（此处删除了两条）
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000410, prev 0/130003E8, desc: RUNNING_XACTS nextXid 750 latestCompletedXid 749 oldestRunningXid 750
rmgr: Heap len (rec/tot): 54/ 54, tx: 750, lsn: 0/13000448, prev 0/13000410, desc: DELETE off 1 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16393/16536 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 750, lsn: 0/13000480, prev 0/13000448, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16393/16536 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 750, lsn: 0/130004B8, prev 0/13000480, desc: COMMIT 2021-12-09 08:58:08.529192 UTC
## 更新数据
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/130004E0, prev 0/130004B8, desc: RUNNING_XACTS nextXid 751 latestCompletedXid 750 oldestRunningXid 751
rmgr: Heap len (rec/tot): 70/ 70, tx: 751, lsn: 0/13000518, prev 0/130004E0, desc: HOT_UPDATE off 3 xmax 751 flags 0x00 ; new off 4 xmax 0, blkref #0: rel 1663/16393/16536 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 751, lsn: 0/13000560, prev 0/13000518, desc: COMMIT 2021-12-09 09:00:55.140021 UTC
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/13000588, prev 0/13000560, desc: RUNNING_XACTS nextXid 752 latestCompletedXid 751 oldestRunningXid 752
pg_waldump: fatal: error in WAL record at 0/13000588: invalid record length at 0/130005C0: wanted 24, got 0
查看事务日志
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# pg_waldump -b -f -r Transaction 000000010000000000000013
rmgr: Transaction len (rec/tot): 34/ 34, tx: 748, lsn: 0/130001E0, prev 0/13000148, desc: COMMIT 2021-12-09 08:52:27.138352 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 749, lsn: 0/130003E8, prev 0/13000328, desc: COMMIT 2021-12-09 08:56:54.988836 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 750, lsn: 0/130004B8, prev 0/13000480, desc: COMMIT 2021-12-09 08:58:08.529192 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 751, lsn: 0/13000560, prev 0/13000518, desc: COMMIT 2021-12-09 09:00:55.140021 UTC
^C
root@fe82b689f575:/var/lib/postgresql/data/pg_wal# pg_waldump -b -f -r Transaction 000000010000000000000013 000000010000000000000017
rmgr: Transaction len (rec/tot): 34/ 34, tx: 748, lsn: 0/130001E0, prev 0/13000148, desc: COMMIT 2021-12-09 08:52:27.138352 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 749, lsn: 0/130003E8, prev 0/13000328, desc: COMMIT 2021-12-09 08:56:54.988836 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 750, lsn: 0/130004B8, prev 0/13000480, desc: COMMIT 2021-12-09 08:58:08.529192 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 751, lsn: 0/13000560, prev 0/13000518, desc: COMMIT 2021-12-09 09:00:55.140021 UTC
^C
更多推荐
0
0- 0
已为社区贡献12条内容
所有评论(0)