PostgreSQL 通过pg_waldump 来分析pg_wal日志

读取MYSQL的binlog 并将其解析为可读的日志是一件简单的事情，mysqlbinlog 命令就可以将bin 日志解析， 那postgresql是否可以将pg_wal 中的日志进行解析，并且提供一些特殊的功能，如题目给出的，想查询某个时间短插入的数据量。

pg_waldump 可以解决这个问题，通过pg_waldump来解析pg_wal 日志来分析和解决一些问题。

pg_waldump 是PG 内部用来对 wal日志进行查看的命令，值需要pg_waldump执行命令对于要读取的日志有只读的权限即可。

在 pg_wal 中日志是有管理类型的，也就是日志记录的东西是什么的标签

下面我们进行一系列的操作创建一个数据库创建一个表，插入三条记录

postgres=# 

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C000328

(1 row)

postgres=# create database testn;

CREATE DATABASE

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C001CF8

(1 row)

postgres=# create table idd (id int);

CREATE TABLE

postgres=# 

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C019640

(1 row)

postgres=# insert into idd values (1);

INSERT 0 1

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C0196E0

(1 row)

postgres=# insert into idd values (2);

INSERT 0 1

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C019748

(1 row)

postgres=# insert into idd values (3);

INSERT 0 1

postgres=# select pg_current_wal_lsn();

 pg_current_wal_lsn 

--------------------

 1/7C0197B0

(1 row)

postgres=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());

 pg_current_wal_lsn |     pg_walfile_name      |      pg_walfile_name_offset       

--------------------+--------------------------+-----------------------------------

 1/7C0197E8         | 00000002000000010000001F | (00000002000000010000001F,104424)

(1 row)

最后通过pg_wal的一些函数，查询当前操作结束的lsn 号，以及pg_wal 的文件名。 粗体含有添加的注释

pg_waldump  /pgdata/data/pg_wal/00000002000000010000001F  -s 1/7C000328  

rmgr: Heap        len (rec/tot):     54/  5394, tx:     464894, lsn: 1/7C000328, prev 1/7C0002F0, desc: INSERT off 21, blkref #0: rel 1664/0/1262 blk 0 FPW

rmgr: Btree       len (rec/tot):     53/   369, tx:     464894, lsn: 1/7C001840, prev 1/7C000328, desc: INSERT_LEAF off 11, blkref #0: rel 1664/0/2671 blk 1 FPW

rmgr: Btree       len (rec/tot):     53/   313, tx:     464894, lsn: 1/7C0019B8, prev 1/7C001840, desc: INSERT_LEAF off 11, blkref #0: rel 1664/0/2672 blk 1 FPW

rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 1/7C001AF8, prev 1/7C0019B8, desc: RUNNING_XACTS nextXid 464895 latestCompletedXid 464893 oldestRunningXid 464894; 1 xacts: 464894

rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 1/7C001B30, prev 1/7C001AF8, desc: CHECKPOINT_ONLINE redo 1/7C001AF8; tli 2; prev tli 2; fpw true; xid 0:464895; oid 41008; multi 1; offset 0; oldest xid 561 in DB 16397; oldest multi 1 in DB 16397; oldest/newest commit timestamp xid: 0/0; oldest running xid 464894; online

rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 1/7C001BA0, prev 1/7C001B30, desc: RUNNING_XACTS nextXid 464895 latestCompletedXid 464893 oldestRunningXid 464894; 1 xacts: 464894

rmgr: Database    len (rec/tot):     42/    42, tx:     464894, lsn: 1/7C001BD8, prev 1/7C001BA0, desc: CREATE copy dir 16397/1663 to 32819/1663   # 创建数据库，创建数据库直接从template 拷贝文件到新建的数据库目录中， 从下图也可以看出地区新建的testn的目录是 32819

rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 1/7C001C08, prev 1/7C001BD8, desc: RUNNING_XACTS nextXid 464895 latestCompletedXid 464893 oldestRunningXid 464894; 1 xacts: 464894

rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 1/7C001C40, prev 1/7C001C08, desc: CHECKPOINT_ONLINE redo 1/7C001C08; tli 2; prev tli 2; fpw true; xid 0:464895; oid 41008; multi 1; offset 0; oldest xid 561 in DB 16397; oldest multi 1 in DB 16397; oldest/newest commit timestamp xid: 0/0; oldest running xid 464894; online

rmgr: Transaction len (rec/tot):     66/    66, tx:     464894, lsn: 1/7C001CB0, prev 1/7C001C40, desc: COMMIT 2021-09-22 01:39:38.164694 EDT; inval msgs: catcache 21; sync

rmgr: Storage     len (rec/tot):     42/    42, tx:          0, lsn: 1/7C001CF8, prev 1/7C001CB0, desc: CREATE base/16398/32820

#创建表idd

rmgr: Heap        len (rec/tot):     54/  7098, tx:     464895, lsn: 1/7C001D28, prev 1/7C001CF8, desc: INSERT off 39, blkref #0: rel 1663/16398/1247 blk 8 FPW

rmgr: Btree       len (rec/tot):     53/  8053, tx:     464895, lsn: 1/7C003900, prev 1/7C001D28, desc: INSERT_LEAF off 398, blkref #0: rel 1663/16398/2703 blk 1 FPW

rmgr: Btree       len (rec/tot):     53/  5845, tx:     464895, lsn: 1/7C005890, prev 1/7C003900, desc: INSERT_LEAF off 145, blkref #0: rel 1663/16398/2704 blk 1 FPW

rmgr: Heap        len (rec/tot):     54/  1818, tx:     464895, lsn: 1/7C006F80, prev 1/7C005890, desc: INSERT off 29, blkref #0: rel 1663/16398/2608 blk 55 FPW

rmgr: Btree       len (rec/tot):     53/  5973, tx:     464895, lsn: 1/7C0076A0, prev 1/7C006F80, desc: INSERT_LEAF off 120, blkref #0: rel 1663/16398/2673 blk 32 FPW

rmgr: Btree       len (rec/tot):     53/  7233, tx:     464895, lsn: 1/7C008E10, prev 1/7C0076A0, desc: INSERT_LEAF off 149, blkref #0: rel 1663/16398/2674 blk 45 FPW

rmgr: Heap        len (rec/tot):    203/   203, tx:     464895, lsn: 1/7C00AA70, prev 1/7C008E10, desc: INSERT off 40, blkref #0: rel 1663/16398/1247 blk 8

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C00AB40, prev 1/7C00AA70, desc: INSERT_LEAF off 398, blkref #0: rel 1663/16398/2703 blk 1

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C00AB80, prev 1/7C00AB40, desc: INSERT_LEAF off 21, blkref #0: rel 1663/16398/2704 blk 1

rmgr: Heap        len (rec/tot):     80/    80, tx:     464895, lsn: 1/7C00ABC8, prev 1/7C00AB80, desc: INSERT off 30, blkref #0: rel 1663/16398/2608 blk 55

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C00AC18, prev 1/7C00ABC8, desc: INSERT_LEAF off 120, blkref #0: rel 1663/16398/2673 blk 32

rmgr: Btree       len (rec/tot):     53/  6981, tx:     464895, lsn: 1/7C00AC60, prev 1/7C00AC18, desc: INSERT_LEAF off 109, blkref #0: rel 1663/16398/2674 blk 39 FPW

rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 1/7C00C7C0, prev 1/7C00AC60, desc: RUNNING_XACTS nextXid 464896 latestCompletedXid 464894 oldestRunningXid 464895; 1 xacts: 464895

rmgr: Heap        len (rec/tot):     54/  5590, tx:     464895, lsn: 1/7C00C7F8, prev 1/7C00C7C0, desc: INSERT off 1, blkref #0: rel 1663/16398/1259 blk 1 FPW

rmgr: Btree       len (rec/tot):     53/  2133, tx:     464895, lsn: 1/7C00DDD0, prev 1/7C00C7F8, desc: INSERT_LEAF off 102, blkref #0: rel 1663/16398/2662 blk 2 FPW

rmgr: Btree       len (rec/tot):     53/  5385, tx:     464895, lsn: 1/7C00E640, prev 1/7C00DDD0, desc: INSERT_LEAF off 34, blkref #0: rel 1663/16398/2663 blk 1 FPW

rmgr: Btree       len (rec/tot):     53/  3753, tx:     464895, lsn: 1/7C00FB50, prev 1/7C00E640, desc: INSERT_LEAF off 179, blkref #0: rel 1663/16398/3455 blk 4 FPW

rmgr: Heap        len (rec/tot):     54/  7930, tx:     464895, lsn: 1/7C010A18, prev 1/7C00FB50, desc: INSERT off 31, blkref #0: rel 1663/16398/1249 blk 33 FPW

rmgr: Btree       len (rec/tot):     53/  2965, tx:     464895, lsn: 1/7C012930, prev 1/7C010A18, desc: INSERT_LEAF off 102, blkref #0: rel 1663/16398/2658 blk 13 FPW

rmgr: Btree       len (rec/tot):     53/  3213, tx:     464895, lsn: 1/7C0134C8, prev 1/7C012930, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9 FPW

rmgr: Heap        len (rec/tot):    175/   175, tx:     464895, lsn: 1/7C014170, prev 1/7C0134C8, desc: INSERT off 32, blkref #0: rel 1663/16398/1249 blk 33

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C014220, prev 1/7C014170, desc: INSERT_LEAF off 102, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C014268, prev 1/7C014220, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):    175/   175, tx:     464895, lsn: 1/7C0142A8, prev 1/7C014268, desc: INSERT off 33, blkref #0: rel 1663/16398/1249 blk 33

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C014358, prev 1/7C0142A8, desc: INSERT_LEAF off 104, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C0143A0, prev 1/7C014358, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):     54/  6294, tx:     464895, lsn: 1/7C0143E0, prev 1/7C0143A0, desc: INSERT off 42, blkref #0: rel 1663/16398/1249 blk 48 FPW

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C015C78, prev 1/7C0143E0, desc: INSERT_LEAF off 102, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C015CC0, prev 1/7C015C78, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):    175/   175, tx:     464895, lsn: 1/7C015D00, prev 1/7C015CC0, desc: INSERT off 43, blkref #0: rel 1663/16398/1249 blk 48

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C015DB0, prev 1/7C015D00, desc: INSERT_LEAF off 105, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C015DF8, prev 1/7C015DB0, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):    175/   175, tx:     464895, lsn: 1/7C015E38, prev 1/7C015DF8, desc: INSERT off 44, blkref #0: rel 1663/16398/1249 blk 48

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C015EE8, prev 1/7C015E38, desc: INSERT_LEAF off 102, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C015F30, prev 1/7C015EE8, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):    175/   175, tx:     464895, lsn: 1/7C015F70, prev 1/7C015F30, desc: INSERT off 45, blkref #0: rel 1663/16398/1249 blk 48

rmgr: Btree       len (rec/tot):     72/    72, tx:     464895, lsn: 1/7C016038, prev 1/7C015F70, desc: INSERT_LEAF off 106, blkref #0: rel 1663/16398/2658 blk 13

rmgr: Btree       len (rec/tot):     64/    64, tx:     464895, lsn: 1/7C016080, prev 1/7C016038, desc: INSERT_LEAF off 156, blkref #0: rel 1663/16398/2659 blk 9

rmgr: Heap        len (rec/tot):     80/    80, tx:     464895, lsn: 1/7C0160C0, prev 1/7C016080, desc: INSERT off 31, blkref #0: rel 1663/16398/2608 blk 55

rmgr: Btree       len (rec/tot):     53/  7289, tx:     464895, lsn: 1/7C016110, prev 1/7C0160C0, desc: INSERT_LEAF off 144, blkref #0: rel 1663/16398/2673 blk 40 FPW

rmgr: Btree       len (rec/tot):     53/  5833, tx:     464895, lsn: 1/7C017D90, prev 1/7C016110, desc: INSERT_LEAF off 131, blkref #0: rel 1663/16398/2674 blk 26 FPW

rmgr: Standby     len (rec/tot):     42/    42, tx:     464895, lsn: 1/7C019478, prev 1/7C017D90, desc: LOCK xid 464895 db 16398 rel 32820 

rmgr: Transaction len (rec/tot):    405/   405, tx:     464895, lsn: 1/7C0194A8, prev 1/7C019478, desc: COMMIT 2021-09-22 01:39:55.509888 EDT; inval msgs: catcache 74 catcache 73 catcache 74 catcache 73 catcache 50 catcache 49 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 32820

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 1/7C019640, prev 1/7C0194A8, desc: RUNNING_XACTS nextXid 464896 latestCompletedXid 464895 oldestRunningXid 464896

#开始插入数据，

rmgr: Heap        len (rec/tot):     59/    59, tx:     464896, lsn: 1/7C019678, prev 1/7C019640, desc: INSERT+INIT off 1, blkref #0: rel 1663/16398/32820 blk 0

rmgr: Transaction len (rec/tot):     34/    34, tx:     464896, lsn: 1/7C0196B8, prev 1/7C019678, desc: COMMIT 2021-09-22 01:40:16.577602 EDT

#数据插入完毕后进行commit

#开始插入数据

rmgr: Heap        len (rec/tot):     59/    59, tx:     464897, lsn: 1/7C0196E0, prev 1/7C0196B8, desc: INSERT off 2, blkref #0: rel 1663/16398/32820 blk 0

rmgr: Transaction len (rec/tot):     34/    34, tx:     464897, lsn: 1/7C019720, prev 1/7C0196E0, desc: COMMIT 2021-09-22 01:40:23.157417 EDT

#数据插入完毕后进行commit

#开始插入数据

rmgr: Heap        len (rec/tot):     59/    59, tx:     464898, lsn: 1/7C019748, prev 1/7C019720, desc: INSERT off 3, blkref #0: rel 1663/16398/32820 blk 0

rmgr: Transaction len (rec/tot):     34/    34, tx:     464898, lsn: 1/7C019788, prev 1/7C019748, desc: COMMIT 2021-09-22 01:40:29.057529 EDT

#数据插入完毕

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 1/7C0197B0, prev 1/7C019788, desc: RUNNING_XACTS nextXid 464899 latestCompletedXid 464898 oldestRunningXid 464899

通过观察实际上日志中主要的针对上面的操作有意义的 rmgr  为 database , storage , heap , transaction

另外在日志中我们也发现一些关于checkpoint 操作的 xlog 信息。同时也可以找到一些关于事务commit 中的时间

实际上pg_waldump 的使用方式比较灵活

1  可以在知道其实的lsn号和日志的名字的基础上进行日志分析的输出

如上面的案例

2  可以在不知道lsn号的情况下，进行盲盒方式的日志扫描，如直接将两个日志按照先后，写到命令后，直接进行两个日志及其之间的信息的打印。

pg_waldump 00000002000000010000001F 000000020000000100000025

或者在知道自己的操作主要的目标后，对需要查看的rmgr 类型进行限定

pg_waldump  /pgdata/data/pg_wal/00000002000000010000001F  -s 1/7C000328  -b -f -r transaction

或者我们也可以通过xid 来获取对应的pg_wal 中的记录

对于pg_wal 日志的理解和分析，可以通过pg_waldump命令来进行更详细的分解和解析。同时 pg_waldump 还有一个命令可以对当前的日志进行一个分析和汇总，这里面就包含每种数据类型所占的比例以及统计分析。

pg_waldump 00000002000000010000001F 000000020000000100000025  -z