This is the 12th day of my participation in the August Challenge
Leader: Last night, a colleague mistakenly updated more than 60,000 data in the PG production database without any conditions during the update. It took three hours to restore the data. I searched the WalMiner log and mined the data.
WalMiner installation and deployment
Gitee.com/movead/XLog… The installation package, readme, and the following test recovery are included.
Building test tables
syd=# create table user_info (id int,name varchar); The CREATE TABLE Syd = # insert into user_info (id, name) select generate_series (1100), 'name_' | | generate_series 00 (1100); 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 20 | | name_19 name_20... ...Copy the codeFull table update data
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 14 15 | | name | name name 16 17 18 | | name | name name 19 20 | | name name (20 rows) Time: 0.933 msCopy the codeExample Switch to wal logs
syd=# select pg_switch_wal(); 0/96376 d0 Time: 10.758 msCopy the codeWalMiner added WAL logs
[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 -- Wal logs 7, 8, 9, and A Syd =# are added in this environment create extension walminer; CREATE EXTENSION syd=# \dx List of installed extensions Name | Version | Schema | Description -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- pglogical | 2.3.4 | pglogical | PostgreSQL Logical The 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)Copy the codeParsing WAL Logs
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)
Copy the codeSQL is required to parse the query in the log table to verify the recovered data
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)Copy the codeConstruct scripts to recover data
Syd =# \pset footer Default footer is off. Syd =# \pset t Tuples only is on. Syd =# \ ohuifu. 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:39huifu.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'; ... ... -- Execute the script after confirming that everything is ok (it took more than 20 minutes to execute the script even though autosubmit was turned off, Syd =# \set AUTOCOMMIT off Syd =# \ ihufu. SQL UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1...... ... syd=# commit; COMMITCopy the codeData validation
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 20 | | name_19 name_20... ...Copy the code