首先我们看一下pg_waldump的帮助信息。
# 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 设置读取的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; 指定资源管理器生成的记录。list用来展示所有资源管理器名称。
use --rmgr=list to list valid resource manager names
-s, --start=RECPTR start reading at WAL location RECPTR 设置读取的WAL开始点。
-t, --timeline=TLI timeline from which to read log records 设置从那个时间开始读取日志。默认为STARTSEG。
(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 显示事务ID
-z, --stats[=record] show statistics instead of records 显示概括统计信息。而不是每个记录。可以选择针对每个记录生成统计信息,而不是针对每个 资源管理器生成。
(optionally, show per-record statistics)
-?, --help show this help, then exit
看一下资源管理器列表
# 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
看一下执行的命令
# pg_waldump --start=1/304CEEA8 --end=1/304E1E28
rmgr: Heap2 len (rec/tot): 58/ 58, tx: 0, lsn: 1/304CEEA8, prev 1/304CEE70, desc: CLEAN remxid 10102, blkref #0: rel 1663/16385/1247 blk 8
rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 1/304CEEE8, prev 1/304CEEA8, desc: CREATE base/16385/64511
rmgr: Heap len (rec/tot): 203/ 203, tx: 10103, lsn: 1/304CEF18, prev 1/304CEEE8, desc: INSERT off 31, blkref #0: rel 1663/16385/1247 blk 8
rmgr: Btree len (rec/tot): 64/ 64, tx: 10103, lsn: 1/304CEFE8, prev 1/304CEF18, desc: INSERT_LEAF off 38, blkref #0: rel 1663/16385/2703 blk 2
rmgr: Btree len (rec/tot): 72/ 72, tx: 10103, lsn: 1/304CF028, prev 1/304CEFE8, desc: INSERT_LEAF off 33, blkref #0: rel 1663/16385/2704 blk 2
rmgr : 资源名称
lsn: 1/304CEEA8 日志编号
prev 1/304CEE70
desc : 对日志详细信息的描述
xid 事务id
增删改的日志
DELETE:
rmgr: Heap len (rec/tot): 59/ 227, tx: 10109, lsn: 1/304EE1A0, prev 1/304EE168, desc: DELETE off 1 KEYS_UPDATED , blkref #0: rel 1663/16385/64501 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 10109, lsn: 1/304EE288, prev 1/304EE1A0, desc: DELETE off 2 KEYS_UPDATED , blkref #0: rel 1663/16385/64501 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 10109, lsn: 1/304EE2C0, prev 1/304EE288, desc: DELETE off 3 KEYS_UPDATED , blkref #0: rel 1663/16385/64501 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 10109, lsn: 1/304EE2F8, prev 1/304EE2C0, desc: DELETE off 4 KEYS_UPDATED , blkref #0: rel 1663/16385/64501 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 10109, lsn: 1/304EE330, prev 1/304EE2F8, desc: COMMIT 2019-01-15 04:06:31.239516 EST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/304EE358, prev 1/304EE330, desc: RUNNING_XACTS nextXid 10110 latestCompletedXid 10109 oldestRunningXid 10110
INSERT:
rmgr: Heap len (rec/tot): 59/ 59, tx: 10110, lsn: 1/304EE390, prev 1/304EE358, desc: INSERT off 5, blkref #0: rel 1663/16385/64501 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 10110, lsn: 1/304EE3D0, prev 1/304EE390, desc: COMMIT 2019-01-15 04:07:05.287538 EST
rmgr: Heap len (rec/tot): 59/ 59, tx: 10111, lsn: 1/304EE3F8, prev 1/304EE3D0, desc: INSERT off 6, blkref #0: rel 1663/16385/64501 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 10111, lsn: 1/304EE438, prev 1/304EE3F8, desc: COMMIT 2019-01-15 04:07:09.351770 EST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/304EE460, prev 1/304EE438, desc: RUNNING_XACTS nextXid 10112 latestCompletedXid 10111 oldestRunningXid 10112
UPDATE:
rmgr: Heap len (rec/tot): 69/ 69, tx: 10112, lsn: 1/304EE498, prev 1/304EE460, desc: HOT_UPDATE off 5 xmax 10112 ; new off 7 xmax 0, blkref #0: rel 1663/16385/64501 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 10112, lsn: 1/304EE4E0, prev 1/304EE498, desc: COMMIT 2019-01-15 04:07:36.153162 EST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/304EE508, prev 1/304EE4E0, desc: RUNNING_XACTS nextXid 10113 latestCompletedXid 10112 oldestRunningXid 10113