同步操作将从 小桥河西/WalMiner 强制同步,此操作会覆盖自 Fork 仓库以来所做的任何修改,且无法恢复!!!
确定后同步将在后台操作,完成时将刷新页面,请耐心等待。
WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具,并能生成出对应的undo SQL语句。
WalMiner可用于以下使用场景
本分支基于WalMiner,并做了如下改进和变更
同时增加以下限制
需要将数据库日志级别配置为logical
wal_level=logical
编译安装
cd walminer
make && make install
psql -c 'create extension walminer'
回归测试
make installcheck
解析WAL。支持按LSN位置过滤。
redo 和undo SQL等
解析WAL。支持按时间过滤。
redo 和undo SQL等
解析WAL。支持按时间过滤。
redo 和undo SQL等
指定起始LSN位置,解析WAL。
建表
postgres=# create table tb1(id int primary key, c1 text);
CREATE TABLE
记录WAL位置
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
D/E7418F8
(1 row)
对于真实故障切换场景,可以从新主的时间线历史中找到新主和旧主发生分叉的WAL位置,然后以该位置作为起始LSN到旧主上解析WAL可以找到故障切换后新主丢失的数据。
[root@sndsdevdb18 walminer]# tail -1 /pgsql/data/pg_wal/00000003.history
2 1/31000098 no recovery target specified
更新数据
postgres=# insert into tb1 values(1,'xxx');
INSERT 0 1
postgres=# update tb1 set c1='yyy' where id=1;
UPDATE 1
postgres=# delete from tb1 where id=1;
DELETE 1
解析WAL
postgres=# \x
Expanded display is on.
postgres=# select * from wal2sql(NULL,'D/E7418F8');
NOTICE: Get data dictionary from current database.
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000C
NOTICE: wal record after time 2020-03-22 00:19:14+08 or d/c894f60 will be analyse completely
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000D
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000E
-[ RECORD 1 ]-----+---------------------------------------------------------------------------
xid | 1124529
virtualxid | 1
timestamptz | 2020-03-30 17:48:59.343712+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | INSERT
op_text | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'xxx');
op_undo | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='xxx' AND ctid = '(0,1)';
lsn | D/E7418F8
commit_end_lsn | D/E7419F8
-[ RECORD 2 ]-----+---------------------------------------------------------------------------
xid | 1124530
virtualxid | 1
timestamptz | 2020-03-30 17:49:30.965638+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | UPDATE
op_text | UPDATE "public"."tb1" SET "c1" = 'yyy' WHERE "id"=1;
op_undo | NULL
lsn | D/E741A30
commit_end_lsn | D/E741AB0
-[ RECORD 3 ]-----+---------------------------------------------------------------------------
xid | 1124531
virtualxid | 1
timestamptz | 2020-03-30 17:49:49.371617+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | DELETE
op_text | DELETE FROM "public"."tb1" WHERE "id"=1;
op_undo | NULL
lsn | D/E741AE8
commit_end_lsn | D/E741B58
以上输出对于解析原始redo SQL已经足够,如果要解析完整的undo SQL,可以打开解析历史FPI的开关进行解析,如下。
postgres=# select * from wal2sql(NULL,'D/E7418F8',NULL,2,true);
NOTICE: Get data dictionary from current database.
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000C
NOTICE: wal record after time 2020-03-22 00:19:14+08 or d/c894f60 will be analyse completely
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000D
NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000E
-[ RECORD 1 ]-----+---------------------------------------------------------------------------------------
xid | 1124529
virtualxid | 1
timestamptz | 2020-03-30 17:48:59.343712+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | INSERT
op_text | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'xxx');
op_undo | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='xxx' AND ctid = '(0,1)';
lsn | D/E7418F8
commit_end_lsn | D/E7419F8
-[ RECORD 2 ]-----+---------------------------------------------------------------------------------------
xid | 1124530
virtualxid | 1
timestamptz | 2020-03-30 17:49:30.965638+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | UPDATE
op_text | UPDATE "public"."tb1" SET "c1" = 'yyy' WHERE "id"=1 AND "c1"='xxx';
op_undo | UPDATE "public"."tb1" SET "c1" = 'xxx' WHERE "id"=1 AND "c1"='yyy' AND ctid = '(0,2)';
lsn | D/E741A30
commit_end_lsn | D/E741AB0
-[ RECORD 3 ]-----+---------------------------------------------------------------------------------------
xid | 1124531
virtualxid | 1
timestamptz | 2020-03-30 17:49:49.371617+08
record_database | postgres
record_user | postgres
record_tablespace | pg_default
record_schema | public
op_type | DELETE
op_text | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='yyy';
op_undo | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'yyy');
lsn | D/E741AE8
commit_end_lsn | D/E741B58
不使用历史image(FPI),仅基于WAL记录本身记录的信息,从WAL解析成SQL的能力如下
wal_level | replica identity FULL | 有主键或identity key | 主键有变更 | SQL类型 | 更新后tuple | 更新前tuple | redo解析 | undo解析 |
---|---|---|---|---|---|---|---|---|
logical | - | - | - | insert | FULL | - | YES | YES |
logical | YES | - | - | delete | - | FULL | YES | YES |
logical | YES | - | - | update | FULL | FULL | YES | YES |
logical | NO | 有主键 | - | delete | - | 仅KEY值 | YES(*1) | NO |
logical | NO | 有主键 | YES | update | FULL | 仅KEY值 | YES(*1) | NO |
logical | NO | 有主键 | NO | update | FULL | 无 | YES(*1) | NO |
logical | NO | 无主键 | - | delete | - | 无 | NO | NO |
logical | NO | 无主键 | - | update | FULL | 无 | NO(*2) | NO |
other | - | - | - | update | 仅被更新字段 | 无 | NO | NO |
基于上表,单纯从WAL记录解析SQL必须依赖logical,对于无主键的表还依赖于replica identity FULL。
通过解析历史image(FPI),可以去掉了对logical和replica identity FULL的依赖。但解析历史image也存在以下限制
发现bug或者有好的建议可以提issue。
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。