PostgreSQL恢復誤刪除數據

IT那活兒發布于2023-01-11 13:20 / 4307人閱讀

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功能改進說明

云服務器 GPU云服務器怎樣恢復誤刪除的數據 rds誤刪除表數據恢復 mongodb誤刪除恢復恢復誤刪數據

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/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