PostgreSQL的WAL日志的解析工具

说明

这类插件或者独立运行的程序，主要两个用途：数据恢复和CDC

一、XLogMiner

说明

• 旧版：XLogMiner，新版：WALMiner
• 旧版需依赖pg库，新版独立运行
• 需要原数据生成数据字典才能进行解析

安装

操作系统：Centos7

1. yum安装PG13

2. 安装pg-devel

# llvm-toolset-7-clang >= 4.0.1
yum install centos-release-scl-rh -y 
yum install llvm-toolset-7-clang -y

# llvm5.0-devel >= 5.0
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-14.noarch.rpm
rpm -Uvh epel-release*rpm
yum install llvm5.0-devel -y


yum install postgresql13-devel -y

3. 安装升级gcc

yum install -y gcc-c++
yum -y install centos-release-scl
yum -y install devtoolset-9-gcc devtoolset-9-gcc-c++ devtoolset-9-binutils
scl enable devtoolset-9 bash
echo "source /opt/rh/devtoolset-9/enable" >> /etc/profile
gcc -v

4. 编译安装WalMiner

export PATH=/usr/pgsql-13/bin:$PATH
USE_PGXS=1 MAJORVERSION=13 make
USE_PGXS=1 MAJORVERSION=13 make install

5. pg数据库中加载WalMiner插件

sudo -u postgres psql
psql (13.12)
Type "help" for help.

postgres=# create extension walminer;

二、wal2json

Github: https://github.com/eulerto/wal2json.git

• 该插件主要用于实时获取数据库变更CDC
• 阿里云rds_postgres默认集成改插件，只需将pg日志级别改为wal_level:logical

逻辑解码插件wal2json实用

# 创建一个复制槽slot, 命名为test_slot, 用来接收CDC事件, 并使用wal2json解析 
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

# 查询复制槽slot信息
select * from pg_replication_slots where slot_name='test_slot'; 

# 读取并清理变更信息
select * from pg_logical_slot_get_changes('test_slot', NULL, NULL) 

# 释放复制槽slot
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');

其它sql

# 将cdc_user账号添加到Replication角色中
ALTER USER cdc_user WITH REPLICATION;


# 查询pg数据库日志级别
SELECT name,
       setting,
       short_desc,
       source
FROM pg_settings
WHERE name ='wal_level';

# 查询Replication Slot造成的WAL日志堆积量
SELECT pg_size_pretty(
  pg_wal_lsn_diff(
    pg_current_wal_insert_lsn(), 
    restart_lsn)
	) AS wal_delay 
FROM pg_replication_slots;

**注意：**一直不消费Replication Slot，会造成的WAL日志堆积量，占满磁盘

三、 test_decoding

同wal2json

四、 pgoutput

五、pg_dirtyread

数据闪回，查询被删除的数据