使用 pg_waldump 和 pg_walinspect 查看 WAL 文件的内容
2024-07-10 15:55 abce 阅读(17) 评论(0) 编辑 收藏 举报
pg_waldump 支持 12 及以上版本；pg_walinspect 支持 15 及以上版本。
pg_waldump
pg_waldump 可从 WAL 文件段中展示人类可读的信息。
pg_waldump 使用要求
·将 wal_level 设置为 "replica" 或更高级别；在 RDS 上，将 wal_level 设置为 "logical"
·安装 postgresql server 的用户或拥有数据目录读取权限的用户才能使用 pg_waldump
pg_waldump 的用法和演示
· pg_waldump -p [path to wal files] -s <start LSN> -e [end LSN] [walfile] · pg_waldump -p [path to wal files] <wal file> · pg_waldump <walfile> -q
这里 -q, -quiet : 表示不打印任何输出，错误除外
下面，将演示如何使用 WAL 函数 pg_current_wal_lsn（最后写入位置）来使用 pg_waldump。
1.捕获当前的 pg_lsn，以分析 WAL 的详细信息。
postgres=# SELECT pg_current_wal_lsn(), pg_walfile_name( pg_current_wal_lsn() ); pg_current_wal_lsn | pg_walfile_name --------------------+-------------------------- 0/31F4FD10 | 000000010000000000000031 (1 row) postgres=#
2.执行一些 SQL 操作来分析 WAL 文件中的内容
postgres=# create table region ( region_id smallint not null, region_description character varying(60) not null ); CREATE TABLE postgres=# insert into region values (1, 'Eastern'); insert into region values (2, 'Western'); insert into region values (3, 'Northern'); insert into region values (4, 'Southern'); INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 postgres=# alter table only region add constraint pk_region primary key (region_id); ALTER TABLE postgres=# update region set region_description = 'Southern Region' where region_id=4; UPDATE 1 postgres=# delete from region where region_id=3; DELETE 1 postgres=#
3.捕获当前的 pg_lsn，以分析 WAL 的详细信息
postgres=# SELECT pg_current_wal_lsn(), pg_walfile_name( pg_current_wal_lsn() ); pg_current_wal_lsn | pg_walfile_name --------------------+-------------------------- 0/31F71150 | 000000010000000000000031 (1 row) postgres=#
4.使用 pg_waldump 工具，从 WAL 文件中读取 SQL
/usr/lib/postgresql/16/bin/pg_waldump -s 0/31F4FD10 -e 0/31F71150 -p /var/lib/postgresql/16/main/pg_wal/ 000000010000000000000031
结果输出：
# /usr/lib/postgresql/16/bin/pg_waldump -s 0/31F4FD10 -e 0/31F71150 -p /var/lib/postgresql/16/main/pg_wal/ 000000010000000000000031 rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/31F4FD10, prev 0/31F4FCD8, desc: CREATE base/5/16405 rmgr: Heap len (rec/tot): 54/ 3838, tx: 766, lsn: 0/31F4FD40, prev 0/31F4FD10, desc: INSERT off: 20, flags: 0x00, blkref #0: rel 1663/5/1247 blk 14 FPW rmgr: Btree len (rec/tot): 53/ 5213, tx: 766, lsn: 0/31F50C58, prev 0/31F4FD40, desc: INSERT_LEAF off: 256, blkref #0: rel 1663/5/2703 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6525, tx: 766, lsn: 0/31F520D0, prev 0/31F50C58, desc: INSERT_LEAF off: 120, blkref #0: rel 1663/5/2704 blk 2 FPW rmgr: Heap2 len (rec/tot): 57/ 6321, tx: 766, lsn: 0/31F53A50, prev 0/31F520D0, desc: MULTI_INSERT ntuples: 1, flags: 0x02, blkref #0: rel 1663/5/2608 blk 12 FPW rmgr: Btree len (rec/tot): 53/ 7229, tx: 766, lsn: 0/31F55320, prev 0/31F53A50, desc: INSERT_LEAF off: 165, blkref #0: rel 1663/5/2673 blk 4 FPW rmgr: Btree len (rec/tot): 53/ 5077, tx: 766, lsn: 0/31F56F78, prev 0/31F55320, desc: INSERT_LEAF off: 113, blkref #0: rel 1663/5/2674 blk 7 FPW rmgr: Heap len (rec/tot): 211/ 211, tx: 766, lsn: 0/31F58368, prev 0/31F56F78, desc: INSERT off: 21, flags: 0x00, blkref #0: rel 1663/5/1247 blk 14 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F58440, prev 0/31F58368, desc: INSERT_LEAF off: 256, blkref #0: rel 1663/5/2703 blk 2 rmgr: Btree len (rec/tot): 53/ 7165, tx: 766, lsn: 0/31F58480, prev 0/31F58440, desc: INSERT_LEAF off: 18, blkref #0: rel 1663/5/2704 blk 4 FPW rmgr: Heap2 len (rec/tot): 85/ 85, tx: 766, lsn: 0/31F5A098, prev 0/31F58480, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [105], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F5A0F0, prev 0/31F5A098, desc: INSERT_LEAF off: 165, blkref #0: rel 1663/5/2673 blk 4 rmgr: Btree len (rec/tot): 53/ 5469, tx: 766, lsn: 0/31F5A138, prev 0/31F5A0F0, desc: INSERT_LEAF off: 108, blkref #0: rel 1663/5/2674 blk 5 FPW rmgr: Heap len (rec/tot): 54/ 1946, tx: 766, lsn: 0/31F5B698, prev 0/31F5A138, desc: INSERT off: 8, flags: 0x00, blkref #0: rel 1663/5/1259 blk 0 FPW rmgr: Btree len (rec/tot): 53/ 2553, tx: 766, lsn: 0/31F5BE38, prev 0/31F5B698, desc: INSERT_LEAF off: 123, blkref #0: rel 1663/5/2662 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 4809, tx: 766, lsn: 0/31F5C850, prev 0/31F5BE38, desc: INSERT_LEAF off: 96, blkref #0: rel 1663/5/2663 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6661, tx: 766, lsn: 0/31F5DB20, prev 0/31F5C850, desc: INSERT_LEAF off: 287, blkref #0: rel 1663/5/3455 blk 1 FPW rmgr: Heap2 len (rec/tot): 59/ 2303, tx: 766, lsn: 0/31F5F540, prev 0/31F5DB20, desc: MULTI_INSERT ntuples: 2, flags: 0x02, blkref #0: rel 1663/5/1249 blk 57 FPW rmgr: Btree len (rec/tot): 53/ 7661, tx: 766, lsn: 0/31F5FE40, prev 0/31F5F540, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 FPW rmgr: Btree len (rec/tot): 53/ 4213, tx: 766, lsn: 0/31F61C48, prev 0/31F5FE40, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 FPW rmgr: Btree len (rec/tot): 80/ 80, tx: 766, lsn: 0/31F62CD8, prev 0/31F61C48, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F62D28, prev 0/31F62CD8, desc: INSERT_LEAF off: 207, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap2 len (rec/tot): 806/ 806, tx: 766, lsn: 0/31F62D68, prev 0/31F62D28, desc: MULTI_INSERT ntuples: 6, flags: 0x02, offsets: [16, 17, 18, 19, 20, 21], blkref #0: rel 1663/5/1249 blk 57 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63090, prev 0/31F62D68, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F630D8, prev 0/31F63090, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63118, prev 0/31F630D8, desc: INSERT_LEAF off: 217, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63160, prev 0/31F63118, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F631A0, prev 0/31F63160, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F631E8, prev 0/31F631A0, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63228, prev 0/31F631E8, desc: INSERT_LEAF off: 218, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63270, prev 0/31F63228, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F632B0, prev 0/31F63270, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F632F8, prev 0/31F632B0, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63338, prev 0/31F632F8, desc: INSERT_LEAF off: 219, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63380, prev 0/31F63338, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap2 len (rec/tot): 85/ 85, tx: 766, lsn: 0/31F633C0, prev 0/31F63380, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [106], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 53/ 8121, tx: 766, lsn: 0/31F63418, prev 0/31F633C0, desc: INSERT_LEAF off: 286, blkref #0: rel 1663/5/2673 blk 8 FPW rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F653F0, prev 0/31F63418, desc: INSERT_LEAF off: 118, blkref #0: rel 1663/5/2674 blk 7 rmgr: Standby len (rec/tot): 42/ 42, tx: 766, lsn: 0/31F65438, prev 0/31F653F0, desc: LOCK xid 766 db 5 rel 16405 rmgr: Transaction len (rec/tot): 437/ 437, tx: 766, lsn: 0/31F65468, prev 0/31F65438, desc: COMMIT 2024-07-09 21:09:51.133656 CST; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16405 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/31F65620, prev 0/31F65468, desc: RUNNING_XACTS nextXid 767 latestCompletedXid 766 oldestRunningXid 767 rmgr: Heap len (rec/tot): 65/ 65, tx: 767, lsn: 0/31F65658, prev 0/31F65620, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 767, lsn: 0/31F656A0, prev 0/31F65658, desc: COMMIT 2024-07-09 21:10:14.008853 CST rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/31F656C8, prev 0/31F656A0, desc: RUNNING_XACTS nextXid 768 latestCompletedXid 766 oldestRunningXid 767; 1 xacts: 767 rmgr: Heap len (rec/tot): 65/ 65, tx: 768, lsn: 0/31F65700, prev 0/31F656C8, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 768, lsn: 0/31F65748, prev 0/31F65700, desc: COMMIT 2024-07-09 21:10:14.012714 CST rmgr: Heap len (rec/tot): 66/ 66, tx: 769, lsn: 0/31F65770, prev 0/31F65748, desc: INSERT off: 3, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 769, lsn: 0/31F657B8, prev 0/31F65770, desc: COMMIT 2024-07-09 21:10:14.016042 CST rmgr: Heap len (rec/tot): 66/ 66, tx: 770, lsn: 0/31F657E0, prev 0/31F657B8, desc: INSERT off: 4, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 770, lsn: 0/31F65828, prev 0/31F657E0, desc: COMMIT 2024-07-09 21:10:14.019345 CST rmgr: Standby len (rec/tot): 42/ 42, tx: 771, lsn: 0/31F65850, prev 0/31F65828, desc: LOCK xid 771 db 5 rel 16405 rmgr: Storage len (rec/tot): 42/ 42, tx: 771, lsn: 0/31F65880, prev 0/31F65850, desc: CREATE base/5/16408 rmgr: Standby len (rec/tot): 42/ 42, tx: 771, lsn: 0/31F658B0, prev 0/31F65880, desc: LOCK xid 771 db 5 rel 16408 rmgr: Heap len (rec/tot): 203/ 203, tx: 771, lsn: 0/31F658E0, prev 0/31F658B0, desc: INSERT off: 9, flags: 0x00, blkref #0: rel 1663/5/1259 blk 0 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F659B0, prev 0/31F658E0, desc: INSERT_LEAF off: 124, blkref #0: rel 1663/5/2662 blk 2 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F659F0, prev 0/31F659B0, desc: INSERT_LEAF off: 92, blkref #0: rel 1663/5/2663 blk 2 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F65A38, prev 0/31F659F0, desc: INSERT_LEAF off: 288, blkref #0: rel 1663/5/3455 blk 1 rmgr: Heap2 len (rec/tot): 176/ 176, tx: 771, lsn: 0/31F65A78, prev 0/31F65A38, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [22], blkref #0: rel 1663/5/1249 blk 57 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F65B28, prev 0/31F65A78, desc: INSERT_LEAF off: 222, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F65B70, prev 0/31F65B28, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap len (rec/tot): 54/ 8046, tx: 771, lsn: 0/31F65BB0, prev 0/31F65B70, desc: INSERT off: 2, flags: 0x01, blkref #0: rel 1663/5/2610 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 3117, tx: 771, lsn: 0/31F67B38, prev 0/31F65BB0, desc: INSERT_LEAF off: 112, blkref #0: rel 1663/5/2678 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 3433, tx: 771, lsn: 0/31F68780, prev 0/31F67B38, desc: INSERT_LEAF off: 167, blkref #0: rel 1663/5/2679 blk 1 FPW rmgr: Heap len (rec/tot): 54/ 3990, tx: 771, lsn: 0/31F694F0, prev 0/31F68780, desc: INSERT off: 16, flags: 0x00, blkref #0: rel 1663/5/2606 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6A4A0, prev 0/31F694F0, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2579 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 5413, tx: 771, lsn: 0/31F6ADE8, prev 0/31F6A4A0, desc: INSERT_LEAF off: 113, blkref #0: rel 1663/5/2664 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 5861, tx: 771, lsn: 0/31F6C328, prev 0/31F6ADE8, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2665 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6DA10, prev 0/31F6C328, desc: INSERT_LEAF off: 112, blkref #0: rel 1663/5/2666 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6E370, prev 0/31F6DA10, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2667 blk 1 FPW rmgr: Heap2 len (rec/tot): 85/ 85, tx: 771, lsn: 0/31F6ECB8, prev 0/31F6E370, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [107], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 53/ 7609, tx: 771, lsn: 0/31F6ED10, prev 0/31F6ECB8, desc: INSERT_LEAF off: 5, blkref #0: rel 1663/5/2673 blk 5 FPW rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70AE8, prev 0/31F6ED10, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2674 blk 7 rmgr: Heap2 len (rec/tot): 85/ 85, tx: 771, lsn: 0/31F70B30, prev 0/31F70AE8, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [108], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70B88, prev 0/31F70B30, desc: INSERT_LEAF off: 287, blkref #0: rel 1663/5/2673 blk 8 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70BD0, prev 0/31F70B88, desc: INSERT_LEAF off: 116, blkref #0: rel 1663/5/2674 blk 7 rmgr: XLOG len (rec/tot): 49/ 169, tx: 771, lsn: 0/31F70C18, prev 0/31F70BD0, desc: FPI , blkref #0: rel 1663/5/16408 blk 1 FPW rmgr: XLOG len (rec/tot): 49/ 137, tx: 771, lsn: 0/31F70CC8, prev 0/31F70C18, desc: FPI , blkref #0: rel 1663/5/16408 blk 0 FPW rmgr: Heap len (rec/tot): 188/ 188, tx: 771, lsn: 0/31F70D58, prev 0/31F70CC8, desc: INPLACE off: 8, blkref #0: rel 1663/5/1259 blk 0 rmgr: Heap len (rec/tot): 188/ 188, tx: 771, lsn: 0/31F70E18, prev 0/31F70D58, desc: INPLACE off: 9, blkref #0: rel 1663/5/1259 blk 0 rmgr: Transaction len (rec/tot): 293/ 293, tx: 771, lsn: 0/31F70ED8, prev 0/31F70E18, desc: COMMIT 2024-07-09 21:10:29.438970 CST; inval msgs: catcache 55 catcache 54 catcache 7 catcache 6 catcache 32 catcache 19 catcache 55 catcache 54 catcache 55 catcache 54 relcache 16408 relcache 16405 snapshot 2608 relcache 16405 relcache 16408 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/31F71000, prev 0/31F70ED8, desc: RUNNING_XACTS nextXid 772 latestCompletedXid 771 oldestRunningXid 772 rmgr: Heap len (rec/tot): 84/ 84, tx: 772, lsn: 0/31F71038, prev 0/31F71000, desc: HOT_UPDATE old_xmax: 772, old_off: 4, old_infobits: [], flags: 0x00, new_xmax: 0, new_off: 5, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 772, lsn: 0/31F71090, prev 0/31F71038, desc: COMMIT 2024-07-09 21:10:37.981162 CST rmgr: Heap len (rec/tot): 54/ 54, tx: 773, lsn: 0/31F710B8, prev 0/31F71090, desc: DELETE xmax: 773, off: 3, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 773, lsn: 0/31F710F0, prev 0/31F710B8, desc: COMMIT 2024-07-09 21:10:46.696138 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/31F71118, prev 0/31F710F0, desc: RUNNING_XACTS nextXid 774 latestCompletedXid 773 oldestRunningXid 774
根据操作顺序， Waldump 中，可以看到 CREATE，通过它在 CREATE base/5/16405中创建了对象，然后执行了 COMMIT 事务：
postgres=# select oid,relname,relkind from pg_class where oid='16405'; oid | relname | relkind -------+---------+--------- 16405 | region | r (1 row) postgres=#
对应的wal内容：
rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/31F4FD10, prev 0/31F4FCD8, desc: CREATE base/5/16405 rmgr: Heap len (rec/tot): 54/ 3838, tx: 766, lsn: 0/31F4FD40, prev 0/31F4FD10, desc: INSERT off: 20, flags: 0x00, blkref #0: rel 1663/5/1247 blk 14 FPW rmgr: Btree len (rec/tot): 53/ 5213, tx: 766, lsn: 0/31F50C58, prev 0/31F4FD40, desc: INSERT_LEAF off: 256, blkref #0: rel 1663/5/2703 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6525, tx: 766, lsn: 0/31F520D0, prev 0/31F50C58, desc: INSERT_LEAF off: 120, blkref #0: rel 1663/5/2704 blk 2 FPW rmgr: Heap2 len (rec/tot): 57/ 6321, tx: 766, lsn: 0/31F53A50, prev 0/31F520D0, desc: MULTI_INSERT ntuples: 1, flags: 0x02, blkref #0: rel 1663/5/2608 blk 12 FPW rmgr: Btree len (rec/tot): 53/ 7229, tx: 766, lsn: 0/31F55320, prev 0/31F53A50, desc: INSERT_LEAF off: 165, blkref #0: rel 1663/5/2673 blk 4 FPW rmgr: Btree len (rec/tot): 53/ 5077, tx: 766, lsn: 0/31F56F78, prev 0/31F55320, desc: INSERT_LEAF off: 113, blkref #0: rel 1663/5/2674 blk 7 FPW rmgr: Heap len (rec/tot): 211/ 211, tx: 766, lsn: 0/31F58368, prev 0/31F56F78, desc: INSERT off: 21, flags: 0x00, blkref #0: rel 1663/5/1247 blk 14 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F58440, prev 0/31F58368, desc: INSERT_LEAF off: 256, blkref #0: rel 1663/5/2703 blk 2 rmgr: Btree len (rec/tot): 53/ 7165, tx: 766, lsn: 0/31F58480, prev 0/31F58440, desc: INSERT_LEAF off: 18, blkref #0: rel 1663/5/2704 blk 4 FPW rmgr: Heap2 len (rec/tot): 85/ 85, tx: 766, lsn: 0/31F5A098, prev 0/31F58480, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [105], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F5A0F0, prev 0/31F5A098, desc: INSERT_LEAF off: 165, blkref #0: rel 1663/5/2673 blk 4 rmgr: Btree len (rec/tot): 53/ 5469, tx: 766, lsn: 0/31F5A138, prev 0/31F5A0F0, desc: INSERT_LEAF off: 108, blkref #0: rel 1663/5/2674 blk 5 FPW rmgr: Heap len (rec/tot): 54/ 1946, tx: 766, lsn: 0/31F5B698, prev 0/31F5A138, desc: INSERT off: 8, flags: 0x00, blkref #0: rel 1663/5/1259 blk 0 FPW rmgr: Btree len (rec/tot): 53/ 2553, tx: 766, lsn: 0/31F5BE38, prev 0/31F5B698, desc: INSERT_LEAF off: 123, blkref #0: rel 1663/5/2662 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 4809, tx: 766, lsn: 0/31F5C850, prev 0/31F5BE38, desc: INSERT_LEAF off: 96, blkref #0: rel 1663/5/2663 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6661, tx: 766, lsn: 0/31F5DB20, prev 0/31F5C850, desc: INSERT_LEAF off: 287, blkref #0: rel 1663/5/3455 blk 1 FPW rmgr: Heap2 len (rec/tot): 59/ 2303, tx: 766, lsn: 0/31F5F540, prev 0/31F5DB20, desc: MULTI_INSERT ntuples: 2, flags: 0x02, blkref #0: rel 1663/5/1249 blk 57 FPW rmgr: Btree len (rec/tot): 53/ 7661, tx: 766, lsn: 0/31F5FE40, prev 0/31F5F540, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 FPW rmgr: Btree len (rec/tot): 53/ 4213, tx: 766, lsn: 0/31F61C48, prev 0/31F5FE40, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 FPW rmgr: Btree len (rec/tot): 80/ 80, tx: 766, lsn: 0/31F62CD8, prev 0/31F61C48, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F62D28, prev 0/31F62CD8, desc: INSERT_LEAF off: 207, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap2 len (rec/tot): 806/ 806, tx: 766, lsn: 0/31F62D68, prev 0/31F62D28, desc: MULTI_INSERT ntuples: 6, flags: 0x02, offsets: [16, 17, 18, 19, 20, 21], blkref #0: rel 1663/5/1249 blk 57 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63090, prev 0/31F62D68, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F630D8, prev 0/31F63090, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63118, prev 0/31F630D8, desc: INSERT_LEAF off: 217, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63160, prev 0/31F63118, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F631A0, prev 0/31F63160, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F631E8, prev 0/31F631A0, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63228, prev 0/31F631E8, desc: INSERT_LEAF off: 218, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63270, prev 0/31F63228, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F632B0, prev 0/31F63270, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F632F8, prev 0/31F632B0, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F63338, prev 0/31F632F8, desc: INSERT_LEAF off: 219, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 766, lsn: 0/31F63380, prev 0/31F63338, desc: INSERT_LEAF off: 206, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap2 len (rec/tot): 85/ 85, tx: 766, lsn: 0/31F633C0, prev 0/31F63380, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [106], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 53/ 8121, tx: 766, lsn: 0/31F63418, prev 0/31F633C0, desc: INSERT_LEAF off: 286, blkref #0: rel 1663/5/2673 blk 8 FPW rmgr: Btree len (rec/tot): 72/ 72, tx: 766, lsn: 0/31F653F0, prev 0/31F63418, desc: INSERT_LEAF off: 118, blkref #0: rel 1663/5/2674 blk 7 rmgr: Standby len (rec/tot): 42/ 42, tx: 766, lsn: 0/31F65438, prev 0/31F653F0, desc: LOCK xid 766 db 5 rel 16405 rmgr: Transaction len (rec/tot): 437/ 437, tx: 766, lsn: 0/31F65468, prev 0/31F65438, desc: COMMIT 2024-07-09 21:09:51.133656 CST; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16405
执行 INSERT 操作后（如示例所示），使用 RUNNING_XACTS 操作捕获快照，执行 INSERT 并提交事务。
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/31F65620, prev 0/31F65468, desc: RUNNING_XACTS nextXid 767 latestCompletedXid 766 oldestRunningXid 767 rmgr: Heap len (rec/tot): 65/ 65, tx: 767, lsn: 0/31F65658, prev 0/31F65620, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 767, lsn: 0/31F656A0, prev 0/31F65658, desc: COMMIT 2024-07-09 21:10:14.008853 CST rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/31F656C8, prev 0/31F656A0, desc: RUNNING_XACTS nextXid 768 latestCompletedXid 766 oldestRunningXid 767; 1 xacts: 767 rmgr: Heap len (rec/tot): 65/ 65, tx: 768, lsn: 0/31F65700, prev 0/31F656C8, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 768, lsn: 0/31F65748, prev 0/31F65700, desc: COMMIT 2024-07-09 21:10:14.012714 CST rmgr: Heap len (rec/tot): 66/ 66, tx: 769, lsn: 0/31F65770, prev 0/31F65748, desc: INSERT off: 3, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 769, lsn: 0/31F657B8, prev 0/31F65770, desc: COMMIT 2024-07-09 21:10:14.016042 CST rmgr: Heap len (rec/tot): 66/ 66, tx: 770, lsn: 0/31F657E0, prev 0/31F657B8, desc: INSERT off: 4, flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 770, lsn: 0/31F65828, prev 0/31F657E0, desc: COMMIT 2024-07-09 21:10:14.019345 CST
执行的 ALTER 操作，执行 CREATE INDEX(pk_region)，然后执行事务的 COMMIT。
postgres=# select oid,relname,relkind from pg_class where oid='16408'; oid | relname | relkind -------+-----------+--------- 16408 | pk_region | i (1 row) postgres=# rmgr: Storage len (rec/tot): 42/ 42, tx: 771, lsn: 0/31F65880, prev 0/31F65850, desc: CREATE base/5/16408 rmgr: Standby len (rec/tot): 42/ 42, tx: 771, lsn: 0/31F658B0, prev 0/31F65880, desc: LOCK xid 771 db 5 rel 16408 rmgr: Heap len (rec/tot): 203/ 203, tx: 771, lsn: 0/31F658E0, prev 0/31F658B0, desc: INSERT off: 9, flags: 0x00, blkref #0: rel 1663/5/1259 blk 0 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F659B0, prev 0/31F658E0, desc: INSERT_LEAF off: 124, blkref #0: rel 1663/5/2662 blk 2 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F659F0, prev 0/31F659B0, desc: INSERT_LEAF off: 92, blkref #0: rel 1663/5/2663 blk 2 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F65A38, prev 0/31F659F0, desc: INSERT_LEAF off: 288, blkref #0: rel 1663/5/3455 blk 1 rmgr: Heap2 len (rec/tot): 176/ 176, tx: 771, lsn: 0/31F65A78, prev 0/31F65A38, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [22], blkref #0: rel 1663/5/1249 blk 57 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F65B28, prev 0/31F65A78, desc: INSERT_LEAF off: 222, blkref #0: rel 1663/5/2658 blk 15 rmgr: Btree len (rec/tot): 64/ 64, tx: 771, lsn: 0/31F65B70, prev 0/31F65B28, desc: INSERT_LEAF off: 214, blkref #0: rel 1663/5/2659 blk 10 rmgr: Heap len (rec/tot): 54/ 8046, tx: 771, lsn: 0/31F65BB0, prev 0/31F65B70, desc: INSERT off: 2, flags: 0x01, blkref #0: rel 1663/5/2610 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 3117, tx: 771, lsn: 0/31F67B38, prev 0/31F65BB0, desc: INSERT_LEAF off: 112, blkref #0: rel 1663/5/2678 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 3433, tx: 771, lsn: 0/31F68780, prev 0/31F67B38, desc: INSERT_LEAF off: 167, blkref #0: rel 1663/5/2679 blk 1 FPW rmgr: Heap len (rec/tot): 54/ 3990, tx: 771, lsn: 0/31F694F0, prev 0/31F68780, desc: INSERT off: 16, flags: 0x00, blkref #0: rel 1663/5/2606 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6A4A0, prev 0/31F694F0, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2579 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 5413, tx: 771, lsn: 0/31F6ADE8, prev 0/31F6A4A0, desc: INSERT_LEAF off: 113, blkref #0: rel 1663/5/2664 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 5861, tx: 771, lsn: 0/31F6C328, prev 0/31F6ADE8, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2665 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6DA10, prev 0/31F6C328, desc: INSERT_LEAF off: 112, blkref #0: rel 1663/5/2666 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 2373, tx: 771, lsn: 0/31F6E370, prev 0/31F6DA10, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2667 blk 1 FPW rmgr: Heap2 len (rec/tot): 85/ 85, tx: 771, lsn: 0/31F6ECB8, prev 0/31F6E370, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [107], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 53/ 7609, tx: 771, lsn: 0/31F6ED10, prev 0/31F6ECB8, desc: INSERT_LEAF off: 5, blkref #0: rel 1663/5/2673 blk 5 FPW rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70AE8, prev 0/31F6ED10, desc: INSERT_LEAF off: 114, blkref #0: rel 1663/5/2674 blk 7 rmgr: Heap2 len (rec/tot): 85/ 85, tx: 771, lsn: 0/31F70B30, prev 0/31F70AE8, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [108], blkref #0: rel 1663/5/2608 blk 12 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70B88, prev 0/31F70B30, desc: INSERT_LEAF off: 287, blkref #0: rel 1663/5/2673 blk 8 rmgr: Btree len (rec/tot): 72/ 72, tx: 771, lsn: 0/31F70BD0, prev 0/31F70B88, desc: INSERT_LEAF off: 116, blkref #0: rel 1663/5/2674 blk 7 rmgr: XLOG len (rec/tot): 49/ 169, tx: 771, lsn: 0/31F70C18, prev 0/31F70BD0, desc: FPI , blkref #0: rel 1663/5/16408 blk 1 FPW rmgr: XLOG len (rec/tot): 49/ 137, tx: 771, lsn: 0/31F70CC8, prev 0/31F70C18, desc: FPI , blkref #0: rel 1663/5/16408 blk 0 FPW rmgr: Heap len (rec/tot): 188/ 188, tx: 771, lsn: 0/31F70D58, prev 0/31F70CC8, desc: INPLACE off: 8, blkref #0: rel 1663/5/1259 blk 0 rmgr: Heap len (rec/tot): 188/ 188, tx: 771, lsn: 0/31F70E18, prev 0/31F70D58, desc: INPLACE off: 9, blkref #0: rel 1663/5/1259 blk 0 rmgr: Transaction len (rec/tot): 293/ 293, tx: 771, lsn: 0/31F70ED8, prev 0/31F70E18, desc: COMMIT 2024-07-09 21:10:29.438970 CST; inval msgs: catcache 55 catcache 54 catcache 7 catcache 6 catcache 32 catcache 19 catcache 55 catcache 54 catcache 55 catcache 54 relcache 16408 relcache 16405 snapshot 2608 relcache 16405 relcache 16408
执行 UPDATE 操作，如示例中的 HOT_UPDATE，然后执行事务的 COMMIT。
rmgr: Heap len (rec/tot): 84/ 84, tx: 772, lsn: 0/31F71038, prev 0/31F71000, desc: HOT_UPDATE old_xmax: 772, old_off: 4, old_infobits: [], flags: 0x00, new_xmax: 0, new_off: 5, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 772, lsn: 0/31F71090, prev 0/31F71038, desc: COMMIT 2024-07-09 21:10:37.981162 CST
最后，在执行示例中的 COMMIT 和 DELETE 操作后，可以通过 RUNNING_XACTS 查看 DELETE、事务 COMMIT 和快照。
rmgr: Heap len (rec/tot): 54/ 54, tx: 773, lsn: 0/31F710B8, prev 0/31F71090, desc: DELETE xmax: 773, off: 3, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/16405 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 773, lsn: 0/31F710F0, prev 0/31F710B8, desc: COMMIT 2024-07-09 21:10:46.696138 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/31F71118, prev 0/31F710F0, desc: RUNNING_XACTS nextXid 774 latestCompletedXid 773 oldestRunningXid 774
pg_walinspect
pg_walinspect 模块提供了用于检查和调试 WAL 的 SQL 函数，这些函数可以通过 SQL 而不是单独的工具访问。
安装 pg_walinspect
postgres=# CREATE EXTENSION pg_walinspect ; CREATE EXTENSION postgres=# SELECT * FROM pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------+---------------+----------+--------------+----------------+------------+-----------+-------------- 13541 | plpgsql | 10 | 11 | f | 1.0 | | 16410 | pg_walinspect | 10 | 2200 | t | 1.1 | | (2 rows) postgres=#
pg_walinspect 的用法和演示
使用与 pg_waldump 相同的 SQL 命令来检查 WAL。执行该操作的用户需要被授予pg_read_server_files权限。
select start_lsn,end_lsn,xid,resource_manager,record_type,record_length,description::varchar(50) from pg_get_wal_records_info('0/31F4FD10','0/31F71150');
输出结果：
start_lsn | end_lsn | xid | resource_manager | record_type | record_length | description ------------+------------+-----+------------------+---------------+---------------+---------------------------------------------------- 0/31F4FD10 | 0/31F4FD40 | 0 | Storage | CREATE | 42 | base/5/16405 0/31F4FD40 | 0/31F50C58 | 766 | Heap | INSERT | 3838 | off: 20, flags: 0x00 0/31F50C58 | 0/31F520D0 | 766 | Btree | INSERT_LEAF | 5213 | off: 256 0/31F520D0 | 0/31F53A50 | 766 | Btree | INSERT_LEAF | 6525 | off: 120 0/31F53A50 | 0/31F55320 | 766 | Heap2 | MULTI_INSERT | 6321 | ntuples: 1, flags: 0x02 0/31F55320 | 0/31F56F78 | 766 | Btree | INSERT_LEAF | 7229 | off: 165 0/31F56F78 | 0/31F58368 | 766 | Btree | INSERT_LEAF | 5077 | off: 113 0/31F58368 | 0/31F58440 | 766 | Heap | INSERT | 211 | off: 21, flags: 0x00 0/31F58440 | 0/31F58480 | 766 | Btree | INSERT_LEAF | 64 | off: 256 0/31F58480 | 0/31F5A098 | 766 | Btree | INSERT_LEAF | 7165 | off: 18 0/31F5A098 | 0/31F5A0F0 | 766 | Heap2 | MULTI_INSERT | 85 | ntuples: 1, flags: 0x02, offsets: [105] 0/31F5A0F0 | 0/31F5A138 | 766 | Btree | INSERT_LEAF | 72 | off: 165 0/31F5A138 | 0/31F5B698 | 766 | Btree | INSERT_LEAF | 5469 | off: 108 0/31F5B698 | 0/31F5BE38 | 766 | Heap | INSERT | 1946 | off: 8, flags: 0x00 0/31F5BE38 | 0/31F5C850 | 766 | Btree | INSERT_LEAF | 2553 | off: 123 0/31F5C850 | 0/31F5DB20 | 766 | Btree | INSERT_LEAF | 4809 | off: 96 0/31F5DB20 | 0/31F5F540 | 766 | Btree | INSERT_LEAF | 6661 | off: 287 0/31F5F540 | 0/31F5FE40 | 766 | Heap2 | MULTI_INSERT | 2303 | ntuples: 2, flags: 0x02 0/31F5FE40 | 0/31F61C48 | 766 | Btree | INSERT_LEAF | 7661 | off: 214 0/31F61C48 | 0/31F62CD8 | 766 | Btree | INSERT_LEAF | 4213 | off: 206 0/31F62CD8 | 0/31F62D28 | 766 | Btree | INSERT_LEAF | 80 | off: 214 0/31F62D28 | 0/31F62D68 | 766 | Btree | INSERT_LEAF | 64 | off: 207 0/31F62D68 | 0/31F63090 | 766 | Heap2 | MULTI_INSERT | 806 | ntuples: 6, flags: 0x02, offsets: [16, 17, 18, 19, 0/31F63090 | 0/31F630D8 | 766 | Btree | INSERT_LEAF | 72 | off: 214 0/31F630D8 | 0/31F63118 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F63118 | 0/31F63160 | 766 | Btree | INSERT_LEAF | 72 | off: 217 0/31F63160 | 0/31F631A0 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F631A0 | 0/31F631E8 | 766 | Btree | INSERT_LEAF | 72 | off: 214 0/31F631E8 | 0/31F63228 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F63228 | 0/31F63270 | 766 | Btree | INSERT_LEAF | 72 | off: 218 0/31F63270 | 0/31F632B0 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F632B0 | 0/31F632F8 | 766 | Btree | INSERT_LEAF | 72 | off: 214 0/31F632F8 | 0/31F63338 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F63338 | 0/31F63380 | 766 | Btree | INSERT_LEAF | 72 | off: 219 0/31F63380 | 0/31F633C0 | 766 | Btree | INSERT_LEAF | 64 | off: 206 0/31F633C0 | 0/31F63418 | 766 | Heap2 | MULTI_INSERT | 85 | ntuples: 1, flags: 0x02, offsets: [106] 0/31F63418 | 0/31F653F0 | 766 | Btree | INSERT_LEAF | 8121 | off: 286 0/31F653F0 | 0/31F65438 | 766 | Btree | INSERT_LEAF | 72 | off: 118 0/31F65438 | 0/31F65468 | 766 | Standby | LOCK | 42 | xid 766 db 5 rel 16405 0/31F65468 | 0/31F65620 | 766 | Transaction | COMMIT | 437 | 2024-07-09 21:09:51.133656+08; inval msgs: catcach 0/31F65620 | 0/31F65658 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 767 latestCompletedXid 766 oldestRunningXi 0/31F65658 | 0/31F656A0 | 767 | Heap | INSERT+INIT | 65 | off: 1, flags: 0x00 0/31F656A0 | 0/31F656C8 | 767 | Transaction | COMMIT | 34 | 2024-07-09 21:10:14.008853+08 0/31F656C8 | 0/31F65700 | 0 | Standby | RUNNING_XACTS | 54 | nextXid 768 latestCompletedXid 766 oldestRunningXi 0/31F65700 | 0/31F65748 | 768 | Heap | INSERT | 65 | off: 2, flags: 0x00 0/31F65748 | 0/31F65770 | 768 | Transaction | COMMIT | 34 | 2024-07-09 21:10:14.012714+08 0/31F65770 | 0/31F657B8 | 769 | Heap | INSERT | 66 | off: 3, flags: 0x00 0/31F657B8 | 0/31F657E0 | 769 | Transaction | COMMIT | 34 | 2024-07-09 21:10:14.016042+08 0/31F657E0 | 0/31F65828 | 770 | Heap | INSERT | 66 | off: 4, flags: 0x00 0/31F65828 | 0/31F65850 | 770 | Transaction | COMMIT | 34 | 2024-07-09 21:10:14.019345+08 0/31F65850 | 0/31F65880 | 771 | Standby | LOCK | 42 | xid 771 db 5 rel 16405 0/31F65880 | 0/31F658B0 | 771 | Storage | CREATE | 42 | base/5/16408 0/31F658B0 | 0/31F658E0 | 771 | Standby | LOCK | 42 | xid 771 db 5 rel 16408 0/31F658E0 | 0/31F659B0 | 771 | Heap | INSERT | 203 | off: 9, flags: 0x00 0/31F659B0 | 0/31F659F0 | 771 | Btree | INSERT_LEAF | 64 | off: 124 0/31F659F0 | 0/31F65A38 | 771 | Btree | INSERT_LEAF | 72 | off: 92 0/31F65A38 | 0/31F65A78 | 771 | Btree | INSERT_LEAF | 64 | off: 288 0/31F65A78 | 0/31F65B28 | 771 | Heap2 | MULTI_INSERT | 176 | ntuples: 1, flags: 0x02, offsets: [22] 0/31F65B28 | 0/31F65B70 | 771 | Btree | INSERT_LEAF | 72 | off: 222 0/31F65B70 | 0/31F65BB0 | 771 | Btree | INSERT_LEAF | 64 | off: 214 0/31F65BB0 | 0/31F67B38 | 771 | Heap | INSERT | 8046 | off: 2, flags: 0x01 0/31F67B38 | 0/31F68780 | 771 | Btree | INSERT_LEAF | 3117 | off: 112 0/31F68780 | 0/31F694F0 | 771 | Btree | INSERT_LEAF | 3433 | off: 167 0/31F694F0 | 0/31F6A4A0 | 771 | Heap | INSERT | 3990 | off: 16, flags: 0x00 0/31F6A4A0 | 0/31F6ADE8 | 771 | Btree | INSERT_LEAF | 2373 | off: 114 0/31F6ADE8 | 0/31F6C328 | 771 | Btree | INSERT_LEAF | 5413 | off: 113 0/31F6C328 | 0/31F6DA10 | 771 | Btree | INSERT_LEAF | 5861 | off: 114 0/31F6DA10 | 0/31F6E370 | 771 | Btree | INSERT_LEAF | 2373 | off: 112 0/31F6E370 | 0/31F6ECB8 | 771 | Btree | INSERT_LEAF | 2373 | off: 114 0/31F6ECB8 | 0/31F6ED10 | 771 | Heap2 | MULTI_INSERT | 85 | ntuples: 1, flags: 0x02, offsets: [107] 0/31F6ED10 | 0/31F70AE8 | 771 | Btree | INSERT_LEAF | 7609 | off: 5 0/31F70AE8 | 0/31F70B30 | 771 | Btree | INSERT_LEAF | 72 | off: 114 0/31F70B30 | 0/31F70B88 | 771 | Heap2 | MULTI_INSERT | 85 | ntuples: 1, flags: 0x02, offsets: [108] 0/31F70B88 | 0/31F70BD0 | 771 | Btree | INSERT_LEAF | 72 | off: 287 0/31F70BD0 | 0/31F70C18 | 771 | Btree | INSERT_LEAF | 72 | off: 116 0/31F70C18 | 0/31F70CC8 | 771 | XLOG | FPI | 169 | 0/31F70CC8 | 0/31F70D58 | 771 | XLOG | FPI | 137 | 0/31F70D58 | 0/31F70E18 | 771 | Heap | INPLACE | 188 | off: 8 0/31F70E18 | 0/31F70ED8 | 771 | Heap | INPLACE | 188 | off: 9 0/31F70ED8 | 0/31F71000 | 771 | Transaction | COMMIT | 293 | 2024-07-09 21:10:29.43897+08; inval msgs: catcache 0/31F71000 | 0/31F71038 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 772 latestCompletedXid 771 oldestRunningXi 0/31F71038 | 0/31F71090 | 772 | Heap | HOT_UPDATE | 84 | old_xmax: 772, old_off: 4, old_infobits: [], flags 0/31F71090 | 0/31F710B8 | 772 | Transaction | COMMIT | 34 | 2024-07-09 21:10:37.981162+08 0/31F710B8 | 0/31F710F0 | 773 | Heap | DELETE | 54 | xmax: 773, off: 3, infobits: [KEYS_UPDATED], flags 0/31F710F0 | 0/31F71118 | 773 | Transaction | COMMIT | 34 | 2024-07-09 21:10:46.696138+08 0/31F71118 | 0/31F71150 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 774 latestCompletedXid 773 oldestRunningXi (86 rows)
其它
上述日志操作解释：
·CREATE：创建对象。(表、索引等） ·RUNNING_XACTS： 捕获当前活动事务的快照。 ·INSERT_LEAF：操作 B 树索引。 ·提交： 提交事务。 ·DELETE： 删除对象的行或列。 ·HOT_UPDATE：更新对象的行或列。
