PostgreSQL恢复误删除数据

IT那活儿发布于2023-01-11 13:20 / 3807人阅读

PostgreSQL恢复误删除数据

今天早上发生了一件事，业务人员一不小心删除了某张表的数据。说实话大哥我玩儿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);

----------+-------+----------+----------+------+------+------+-----+----------

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;

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功能改进说明

海外云主机云服务器怎样恢复误删除的数据 rds误删除表数据恢复 mongodb误删除恢复恢复误删数据

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130098.html

PostgreSQL UDB，让31会议数据管理更高效可靠

摘要：相比自建，其可靠性更高，方便运维维护。宋体经过审慎考虑，用户同时选用三种数据库，针对性的满足不同目标。宋体宋体其中，相比于在上的快速高效是其优势，也是用户选型的重要砝码。PostgreSQL UDB用在大数据分析上，查询效率更高。相比自建，其可靠性更高，方便运维维护。 — 31会议运维经理汤雷如何用好PostgreSQL? PostgreSQL是业内一款十分流行的开源数...

vspiders 2019-08-26 16:13 评论0 收藏0
到底选择PostgreSOL还是MySQL？看这里

摘要：经过对比选型，用户同时选用三种数据库来针对性的满足不同目标。基于能够带来的这些特性优势，用户选择了。相比自建，其可靠性更高，方便运维维护。整个过程中用户不需要任何人工干预和配置修改，真正做到自动容灾。据DB-Engines 最新发布的2019年8月份数据库流行度排行榜（如下图）显示，名列前茅的MySQL和PostgreSQL数据库的流行趋势与去年同期相比依然稳增不减。 showImg...

沈俭 2019-08-19 10:30 评论0 收藏0
SegmentFault 技术周刊 Vol.42 - MySQL：从删库到跑路

摘要：肖鹏微博数据库那些事儿肖鹏，微博研发中心技术经理，主要负责微博数据库相关的业务保障性能优化架构设计，以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造，包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说，没有误删过库的人生是...

aboutU 2019-06-25 18:34 评论0 收藏0