这是我参与8月更文挑战的第12天，活动详情查看：8月更文挑战
领导：昨晚有同事，执行 update 没加条件，误更新了 pg 生产库6万多条数据，花了3个小时才还原回来，我搜了下 WalMiner 日志挖掘可以恢复数据，你研究下。
WalMiner 安装部署
postgres 用户下编译安装
USE_PGXS=1 MAJORVERSION=12 make
USE_PGXS=1 MAJORVERSION=12 make install
gitee.com/movead/XLog…
安装包、readme上面都有，主要做下测试恢复。（表字段多时需要有主键）
构建测试表
syd=# create table user_info (id int,name varchar);
CREATE TABLE
syd=# insert into user_info (id,name) select generate_series(1,10000),'name_'||generate_series(1,10000);
INSERT 0 10000
Time: 107.785 ms
syd=# select * from user_info limit 100;
id | name
----+------
1 | name_1
2 | name_2
3 | name_3
4 | name_4
5 | name_5
6 | name_6
7 | name_7
8 | name_8
9 | name_9
10 | name_10
11 | name_11
12 | name_12
13 | name_13
14 | name_14
15 | name_15
16 | name_16
17 | name_17
18 | name_18
19 | name_19
20 | name_20
……
……
全表更新数据
syd=# update user_info set name = 'name';
UPDATE 100000
Time: 3045.620 ms (00:03.046)
syd=# select * from user_info limit 20;
id | name
----+------
1 | name
2 | name
3 | name
4 | name
5 | name
6 | name
7 | name
8 | name
9 | name
10 | name
11 | name
12 | name
13 | name
14 | name
15 | name
16 | name
17 | name
18 | name
19 | name
20 | name
(20 rows)
Time: 0.933 ms
切换下 wal 日志
syd=# select pg_switch_wal();
0/96376D0
Time: 10.758 ms
WalMiner 添加 wal 日志
--根据更新数据时间找到记录更新操作的归档（可多不可少）
[root@yuan pg_wal]# ll
total 163840
-rw------- 1 postgres postgres 16777216 Jul 13 15:53 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 13 15:53 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jul 14 16:00 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Aug 9 16:06 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Aug 9 16:06 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Aug 9 16:17 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Aug 9 16:31 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Aug 12 10:53 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Aug 12 11:10 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Aug 12 11:10 00000001000000000000000A
drwx------ 2 postgres postgres 6 Jul 9 18:02 archive_status
--本环境选择添加7、8、9、A号 wal 日志
syd=# create extension walminer;
CREATE EXTENSION
syd=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+--------------------------------
pglogical | 2.3.4 | pglogical | PostgreSQL Logical Replication
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
walminer | 3.0 | syd | analyse wal to SQL
syd=# select walminer_wal_add('/app/pgsql/data/pg_wal/000000010000000000000007');
select walminer_wal_add('/app/pgsql/data/pg_wal/000000010000000000000008');
walminer_wal_add
--------------------
1 file add success
(1 row)
syd=# select walminer_wal_add('/app/pgsql/data/pg_wal/000000010000000000000008');
select walminer_wal_add('/app/pgsql/data/pg_wal/000000010000000000000009');
walminer_wal_add
--------------------
1 file add success
(1 row)
syd=# select walminer_wal_add('/app/pgsql/data/pg_wal/000000010000000000000009');
walminer_wal_add
--------------------
1 file add success
(1 row)
syd=# select walminer_wal_add('/app/pgsql/data/pg_wal/00000001000000000000000A');
walminer_wal_add
--------------------
1 file add success
(1 row)
解析 wal 日志
syd=# select walminer_all();
NOTICE: Switch wal to 000000010000000000000007 on time 2021-08-12 11:31:07.258559+08
NOTICE: Switch wal to 000000010000000000000008 on time 2021-08-12 11:31:07.266243+08
NOTICE: Switch wal to 000000010000000000000009 on time 2021-08-12 11:31:58.508183+08
NOTICE: Switch wal to 00000001000000000000000A on time 2021-08-12 11:32:14.171562+08
walminer_all
---------------------
pg_minerwal success
(1 row)
解析日志表中查询验证恢复数据所需要 sql
syd=# select undo_text from walminer_contents where undo_text like 'UPDATE syd.user_info%'limit 100;
undo_text
-----------------------------------------------------------------------
UPDATE syd.user_info SET name='name_1' WHERE id=1 AND name='name'
UPDATE syd.user_info SET name='name_2' WHERE id=2 AND name='name'
UPDATE syd.user_info SET name='name_3' WHERE id=3 AND name='name'
UPDATE syd.user_info SET name='name_4' WHERE id=4 AND name='name'
UPDATE syd.user_info SET name='name_5' WHERE id=5 AND name='name'
UPDATE syd.user_info SET name='name_6' WHERE id=6 AND name='name'
UPDATE syd.user_info SET name='name_7' WHERE id=7 AND name='name'
UPDATE syd.user_info SET name='name_8' WHERE id=8 AND name='name'
UPDATE syd.user_info SET name='name_9' WHERE id=9 AND name='name'
UPDATE syd.user_info SET name='name_10' WHERE id=10 AND name='name'
UPDATE syd.user_info SET name='name_11' WHERE id=11 AND name='name'
UPDATE syd.user_info SET name='name_12' WHERE id=12 AND name='name'
UPDATE syd.user_info SET name='name_13' WHERE id=13 AND name='name'
UPDATE syd.user_info SET name='name_14' WHERE id=14 AND name='name'
UPDATE syd.user_info SET name='name_15' WHERE id=15 AND name='name'
UPDATE syd.user_info SET name='name_16' WHERE id=16 AND name='name'
UPDATE syd.user_info SET name='name_17' WHERE id=17 AND name='name'
UPDATE syd.user_info SET name='name_18' WHERE id=18 AND name='name'
UPDATE syd.user_info SET name='name_19' WHERE id=19 AND name='name'
UPDATE syd.user_info SET name='name_20' WHERE id=20 AND name='name'
……
……
--行数也正好能对应起来
syd=# select count(undo_text) from walminer_contents where undo_text like 'UPDATE syd.user_info%';
count
--------
100000
(1 row)
构造脚本恢复数据
--导出脚本
syd=# \pset footer
Default footer is off.
syd=# \pset t
Tuples only is on.
syd=# \o huifu.sql
syd=# select undo_text||';' from walminer_contents where undo_text like 'UPDATE syd.user_info%';
--大概看下脚本内容（主要看头尾别有多余字符）
[postgres@yuan ~]$ ll
total 8128
-rw-rw-r-- 1 postgres postgres 7577791 Aug 12 11:39 huifu.sql
[postgres@yuan ~]$ more huifu.sql
UPDATE syd.user_info SET name='name_1' WHERE id=1 AND name='name';
UPDATE syd.user_info SET name='name_2' WHERE id=2 AND name='name';
UPDATE syd.user_info SET name='name_3' WHERE id=3 AND name='name';
UPDATE syd.user_info SET name='name_4' WHERE id=4 AND name='name';
UPDATE syd.user_info SET name='name_5' WHERE id=5 AND name='name';
UPDATE syd.user_info SET name='name_6' WHERE id=6 AND name='name';
UPDATE syd.user_info SET name='name_7' WHERE id=7 AND name='name';
UPDATE syd.user_info SET name='name_8' WHERE id=8 AND name='name';
UPDATE syd.user_info SET name='name_9' WHERE id=9 AND name='name';
UPDATE syd.user_info SET name='name_10' WHERE id=10 AND name='name';
UPDATE syd.user_info SET name='name_11' WHERE id=11 AND name='name';
UPDATE syd.user_info SET name='name_12' WHERE id=12 AND name='name';
UPDATE syd.user_info SET name='name_13' WHERE id=13 AND name='name';
UPDATE syd.user_info SET name='name_14' WHERE id=14 AND name='name';
UPDATE syd.user_info SET name='name_15' WHERE id=15 AND name='name';
UPDATE syd.user_info SET name='name_16' WHERE id=16 AND name='name';
UPDATE syd.user_info SET name='name_17' WHERE id=17 AND name='name';
UPDATE syd.user_info SET name='name_18' WHERE id=18 AND name='name';
UPDATE syd.user_info SET name='name_19' WHERE id=19 AND name='name';
UPDATE syd.user_info SET name='name_20' WHERE id=20 AND name='name';
……
……
--确认没问题后执行脚本（虽然关了自动提交，但还是花了20多分钟，后期再研究下能不能加速这个更新过程）
syd=# \set AUTOCOMMIT off
syd=# \i huifu.sql
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
……
……
syd=# commit;
COMMIT
数据验证
syd=# select * from user_info;
id | name
----+------
1 | name_1
2 | name_2
3 | name_3
4 | name_4
5 | name_5
6 | name_6
7 | name_7
8 | name_8
9 | name_9
10 | name_10
11 | name_11
12 | name_12
13 | name_13
14 | name_14
15 | name_15
16 | name_16
17 | name_17
18 | name_18
19 | name_19
20 | name_20
……
……
