云貝教育 |【技術文章】PostgreSQL中誤刪除數據怎么辦（一）

原文鏈接：【PostgreSQL】PostgreSQL中誤刪除數據怎么辦（一） - 課程體系 - 云貝教育 (yunbee.net)

在我們學習完PG的MVCC機制之后，對于DML操作，被操作的行其實并未被刪除，只能手工vacuum或自動vacuum觸發才會清理掉這些無效數據，也就是死元組。

基于這種機制，在發生死元組清理動作之前，只需要將其中不可見的行中的數據解析出來，或者發生臟讀，就可以獲取到誤刪除的數據。雖然PG不支持臟讀，但今天介紹的pg_dirtyread插件，可以實現臟讀。

一、安裝pg_dirtyread

下載地址

GitHub - df7cb/pg_dirtyread: Read dead but unvacuumed tuples from a PostgreSQL relation

這個網頁上有詳細的安裝說明

編譯安裝

unzip pg_dirtyread-master.zip
cd pg_dirtyread-master/
make
make install

二、使用示例

2.1、在對應庫創建EXTENSION

create extension pg_dirtyread ;

2.2、創建表并禁用autovacuum

testdb=# CREATE TABLE t1 (id int, name text);
CREATE TABLE

2.3、插入并通過heap_page_items查看數據

testdb=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'),(3,'ccc');
INSERT 0 3
testdb=#
testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0));tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |   1104 |      0 |     0 | (0,1)  | \x0100000000000000096161612 |   1104 |      0 |     0 | (0,2)  | \x0200000000000000096262623 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363
(3 rows)

2.4、刪除數據

testdb=# DELETE FROM t1 WHERE id = 1;
DELETE 1
testdb=# DELETE FROM t1 WHERE id = 2;
DELETE 1testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0));tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |   1104 |   1105 |     0 | (0,1)  | \x0100000000000000096161612 |   1104 |   1106 |     0 | (0,2)  | \x0200000000000000096262623 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363
(3 rows)

這里發現被刪除的數據還在塊中

2.5、發現數據被誤刪除后第一時間關掉表上的vacuum

這一步很關鍵！！！

ALTER TABLE t1 SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);

2.6、查看表vacuum情況

testdb=# \x
Expanded display is on.
testdb=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid               | 49546
schemaname          | public
relname             | t1
seq_scan            | 3
seq_tup_read        | 6
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 3
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 1
n_dead_tup          | 2
n_mod_since_analyze | 5
n_ins_since_vacuum  | 3
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

last_vacuum和last_autovacuum都是空的，表示還未被vacuum過。

2.7、使用pg_dirtyread查看表，dead為t表示數據已?經刪除

testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boole an,id int, name text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+----+------49546 | (0,1) | 1104 | 1105 |    0 |    0 | t    |  1 | aaa49546 | (0,2) | 1104 | 1106 |    0 |    0 | t    |  2 | bbb49546 | (0,3) | 1104 |    0 |    0 |    0 | f    |  3 | ccc
(3 rows)

刪除數據后，需要查詢一下，pg_dirtyread中的dead列才會更新。也就是pg_dirtyread需要掃描一次表中的page才知道該行是不是被修改過。

testdb=# delete from t1;
DELETE 3testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+-----+-----
49541 | (0,1) | 1102 | 1108 | 0 | 0 | f | 1 | aaa
49541 | (0,2) | 1102 | 1108 | 0 | 0 | f | 2 | bbb
49541 | (0,3) | 1102 | 1108 | 0 | 0 | f | 3 | ccc
(3 rows)testdb=# select * from t1;
id | name
-----+-----
(0 rows)testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+-----+-----
49541 | (0,1) | 1102 | 1108 | 0 | 0 | t | 1 | aaa
49541 | (0,2) | 1102 | 1108 | 0 | 0 | t | 2 | bbb
49541 | (0,3) | 1102 | 1108 | 0 | 0 | t | 3 | ccc
(3 rows)

2.8 恢復到某個時間

如果做不完全恢復，即恢復數據到某個時刻，需要使用函數pg_xact_commit_timestamp將事務ID進行轉換。

testdb=# alter system set track_commit_timestamp=on;
ALTER SYSTEM#刪除一條數據
testdb=# select * from t1;
id | name
----+------
5 | EEE
(1 row)testdb=# delete from t1;
DELETE 1#查看刪除時間
testdb=# SELECT pg_xact_commit_timestamp(xmin) xmin_time
,pg_xact_commit_timestamp(xmax) xmax_time
,*
FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text)
where xmax<>0;
-[ RECORD 1 ]----------------------------
xmin_time | 2023-12-03 16:27:03.830358+08
xmax_time | 2023-12-06 10:10:29.115887+08
tableoid | 49776
ctid | (0,2)
xmin | 7207
xmax | 7235
cmin | 0
cmax | 0
dead | f
id | 5
name | EEE

xmax_time 就是數據具體刪除時間

2.9、pg_dirtyread還支持被刪除的列

testdb=# select * from t1;
id | name
----+------
3 | ccc
(1 row)testdb=#
testdb=# ALTER TABLE t1 DROP COLUMN name;
ALTER TABLE
testdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);
id | dropped_2
----+-----------
1 | aaa
2 | bbb
3 | ccc
3 | ccc
(4 rows)testdb=# select * from t1;
id
----
3
(1 row)

三、如果表上已經發生了vacuum

3.1、對表進行vacuum回收死元組

postgres=# vacuum t1;
VACUUM

3.2、查看塊中的數據被清理

testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data F
ROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |        |        |       |        |2 |        |        |       |        |3 |        |        |       |        |4 |   1110 |      0 |     0 |  (0,4) | \x030000000000000009636363
(4 rows)

3.3、再次用pg_dirtyread查看死元組的數據已經被清理了

testdb=# \x
Expanded display is on.
testdb=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid | 49546
schemaname | public
relname | t1
seq_scan | 8
seq_tup_read | 33
idx_scan |
idx_tup_fetch |
n_tup_ins | 4
n_tup_upd | 0
n_tup_del | 3
n_tup_hot_upd | 0
n_live_tup | 1
n_dead_tup | 0
n_mod_since_analyze | 7
n_ins_since_vacuum | 0
last_vacuum | 2023-12-01 14:55:44.099392+0821 last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 1
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0testdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);
id | dropped_2
----+-----------
3 | ccc
(1 row)

這種場景下，就無法通過臟塊或解析死元組中的數據信息來恢復數據庫，下一篇介紹WALMINER恢復誤刪除的數據。

總結

如果不小心誤刪除了數據，可以通過特殊手段來恢復數據的，具體恢復步驟如下：

1. 對表執行禁用vacuum（特別強調，這一步非常重要）

ALTER TABLE t1 SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

2. 使用pg_dirtyread插件查詢被刪除的數據，同時將數據抽取到中間表

create table t1_bak select id ,name from ((SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text))) as foo;

另外，如何要找的數據己被vacuum，還可以通過分析數據具體被刪除的時間，然后通過WalMiner解析wal日志，找到對應的時間點，生成undo sql(如果執行的delete，undo sql就是insert語句)。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/207454.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/207454.shtml
英文地址，請注明出處：http://en.pswp.cn/news/207454.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！