读取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: 464894rmgr: 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
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
用grpc_cb代替grpc++ jinq0123/grpc_cb 是 Google gRpc 的C++库。 它依赖于 grpc, 采用回调接口，简化了使用，用来代替 grpc++ 库。 使用简介如下。 定义服务 用 proto 文件定义服务： // See …
