今天早上发生了一件事，业务人员一不小心删除了某张表的数据。说实话大哥我玩儿PG也不久，很多运维经验也是边踩坑边总结，所以在误删除恢复这件事上哥的经验还是比较匮乏的。而且还有一件比较可怕的事情是该数据库的物理备份还没有弄好。
于是查了一通，发现有一个插件pg_dirtyread，可以帮助我们来处理。关于插件的介绍就是“Readdead but unvacuumed tuples from a PostgreSQLrelation”。这里有一个前提条件就是unvacuumedtuples。于是立马下载，make编译，然后安装。信心满满的使用查询的sql，竟然发现数据恢复不了。
现实啪啪打脸，只怪自己学艺不深。研究一通之后发现并不是插件问题。以下是在测试库上使用插件的过程。使用了https://github.com/df7cb/pg_dirtyread中的demo。
postgres=# CREATE TABLE foo (bar bigint, baz text); CREATE TABLE postgres=# INSERT INTO foo VALUES (1, 'Test'), (2, 'New Test'); INSERT 0 2 postgres=# DELETE FROM foo WHERE bar = 1; DELETE 1 postgres=# select * from foo; bar | baz -----+---------- 2 | New Test (1 row) postgres=# SELECT * FROM pg_dirtyread('foo') postgres-# AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean, postgres(# bar bigint, baz text); tableoid | ctid | xmin | xmax | cmin | cmax | dead | bar | baz ----------+-------+----------+----------+------+------+------+-----+---------- 2784670 | (0,1) | 17635311 | 17635312 | 0 | 0 | t | 1 | Test 2784670 | (0,2) | 17635311 | 0 | 0 | 0 | f | 2 | New Test |
从测试库上可以清楚的看到，dead为true的死元组数据可以通过pg_dirtyread插件查到，但是我们生产为什么查不到呢？
在生产库根本就查不到dead为true的元组数据。这说明系统进程autovacuumed已经执行了清理。
对于这种情况，在Oracle中就只能基于时间点的恢复和挖掘redolog了。而PG挖掘wal日志的方法网上又搜了一通，发现可以使用瀚高公司的walminer插件。
安装方法其实很简单。https://gitee.com/movead/XLogMiner。先下载插件。然后进入到walminer的路径。
cd /home/postgres/XLogMiner/walminer USE_PGXS=1 MAJORVERSION=12 make #MAJORVERSION支持‘9.5’,‘9.6’,‘10’,‘11’,‘12’,‘13’ USE_PGXS=1 MAJORVERSION=12 make install postgres=# create extension walminer; CREATE EXTENSION |
安装完成之后需要先加载wal日志，这里你可以选择全部加载，也可以选择出故障时间点的日志进行加载。加载完可以列出加载的日志信息。
postgres=# select walminer_wal_add('pg_wal'); walminer_wal_add --------------------- 64 file add success (1 row) postgres=# select walminer_wal_list(); walminer_wal_list --------------------------------------------------------- (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000023) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000024) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000025) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000026) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000027) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000028) (/pg_data1/pg12/pgdata/pg_wal/000000010000024E00000029) |
接下来执行walminer_all()就可以解析添加的全部wal日志，然后查看walminer_contents表就可以看到之前执行的sql记录了，包括反向的undosql都已经生成好了。
postgres=# select walminer_all(); NOTICE: Switch wal to 000000010000024D000000F6 on time 2020-10-26 16:32:34.782724+08 NOTICE: Con not find relfilenode 2777602 in dictionary, ignored related records NOTICE: Switch wal to 000000010000024D000000F7 on time 2020-10-26 16:32:34.87947+08 walminer_all --------------------- pg_minerwal success (1 row) postgres=# select * from walminer_contents; sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | sch ema | relation | start_lsn | commit_lsn -------+----------+--------+---------+--------+-------------------------------+--------------------------------------------------------+-------------------------------------------------------+----------+---- ----+----------+--------------+-------------- 1 | 17635311 | 0 | 1 | t | 2020-10-26 16:31:42.373436+08 | INSERT INTO public.foo(bar ,baz) VALUES(1 ,'Test') | DELETE FROM public.foo WHERE bar=1 AND baz='Test' | t | pub lic | foo | 24D/F7CFCB70 | 24D/F7CFCC30 2 | 17635311 | 0 | 1 | t | 2020-10-26 16:31:42.373436+08 | INSERT INTO public.foo(bar ,baz) VALUES(2 ,'New Test') | DELETE FROM public.foo WHERE bar=2 AND baz='New Test' | t | pub lic | foo | 24D/F7CFCBB8 | 24D/F7CFCC30 1 | 17635312 | 0 | 3 | t | 2020-10-26 16:31:45.936969+08 | DELETE FROM public.foo WHERE bar=1 AND baz='Test' | INSERT INTO public.foo(bar ,baz) VALUES(1 ,'Test') | t | pub lic | foo | 24D/F7CFCC30 | 24D/F7CFCC98 (3 rows) |
这里操作方法和Oracle中的logminger类似。具体可以参考redeme。
对于今天早上出现的这类问题，比较闹心的一点是经验不足，很多东西都要现学现查。如果能够提前部署好这些插件，并在最短的时间做出选择，就能快速的恢复数据。同时当前生产库也没有部署备份，无法从备份基于时间点的恢复。因此，对我们来说，PG运维仍然任重而道远，这里给大家分享出来，也是为了小伙伴们少走弯路。
参考文献
pg_dirtyread
Walminer2.0Beta功能改进说明
