作者：吳守陽
pg_dirtyread 是一個(gè) PostgreSQL 擴(kuò)展，它利用了 PostgreSQL 的多版本并發(fā)控制（MVCC）機(jī)制來讀取未提交的事務(wù)所影響的數(shù)據(jù)。此插件可以用于在開發(fā)和測(cè)試環(huán)境中快速查看事務(wù)對(duì)數(shù)據(jù)的即時(shí)影響，當(dāng)關(guān)鍵表中的數(shù)據(jù)被意外刪除后，業(yè)務(wù)系統(tǒng)可能會(huì)出現(xiàn)服務(wù)中斷、數(shù)據(jù)丟失、數(shù)據(jù)不一致等問題，影響用戶服務(wù)。
作者 | 吳守陽
審校 | 重樓
目錄
1、概述
2、恢復(fù)機(jī)制
3、優(yōu)缺點(diǎn)
4、安裝
5、操作實(shí)例
1）基于表數(shù)據(jù)Delete閃回
2）基于表字段數(shù)據(jù)閃回
3）基于時(shí)間點(diǎn)閃回
6、支持查詢被刪除列的歷史數(shù)據(jù)
7、注意事項(xiàng)
8、總結(jié)
概述
pg_dirtyread 是一個(gè)PostgreSQL 擴(kuò)展，它利用了PostgreSQL 的多版本并發(fā)控制（MVCC）機(jī)制來讀取未提交的事務(wù)所影響的數(shù)據(jù)。此插件可以用于在開發(fā)和測(cè)試環(huán)境中快速查看事務(wù)對(duì)數(shù)據(jù)的即時(shí)影響，當(dāng)關(guān)鍵表中的數(shù)據(jù)被意外刪除后，業(yè)務(wù)系統(tǒng)可能會(huì)出現(xiàn)服務(wù)中斷、數(shù)據(jù)丟失、數(shù)據(jù)不一致等問題，影響用戶服務(wù)。通過使用pg_dirtyread，你可以快速查看未提交的刪除或更新事務(wù)，恢復(fù)數(shù)據(jù)，從而恢復(fù)正常服務(wù)、確保數(shù)據(jù)完整、數(shù)據(jù)一致。
恢復(fù)機(jī)制
在 PostgreSQL 中，事務(wù)提交后，如果數(shù)據(jù)尚未被 VACUUM 清理，仍有可能通過某些方式恢復(fù)這些數(shù)據(jù)。這是因?yàn)?PostgreSQL 采用了多版本并發(fā)控制（MVCC）機(jī)制，允許舊的元組（稱為 Dead 元組）在事務(wù)提交后繼續(xù)保留在系統(tǒng)中，直到被 VACUUM 清除。具體來說：
- MVCC 機(jī)制：即使事務(wù)已提交，刪除或更新的行仍作為舊版本存在，允許在 VACUUM 運(yùn)行之前進(jìn)行恢復(fù)。
- VACUUM 的作用：VACUUM 操作會(huì)清理表中的 Dead 元組，回收存儲(chǔ)空間并重置行標(biāo)識(shí)符（TID），以便新行可以重用這些空間。一旦執(zhí)行了 VACUUM，Dead 元組被清除，數(shù)據(jù)就無法再通過常規(guī)手段恢復(fù)。
優(yōu)缺點(diǎn)
- pg_dirtyread 插件非常方便，它可以通過安裝來找回?cái)?shù)據(jù)。
- 該插件會(huì)返回所有數(shù)據(jù)，包括那些未被刪除的數(shù)據(jù)。
- 由于 PostgreSQL 的 MVCC 機(jī)制，pg_dirtyread 只能用于找回 DML 操作的數(shù)據(jù)。
- 基于時(shí)間點(diǎn)恢復(fù)要配置參數(shù)track_commit_timestamp = on
- 支持PostgreSQL16及以前版本
安裝
#安裝依賴
yum install postgresql16-devel clang
wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.7.tar.gz
tar xvf 2.7.tar.gz
mv pg_dirtyread-2.7/ /jesong/pgdata/contrib/
cd /jesong/pgdata/contrib/
make PG_CONFIG=/usr/pgsql-16/bin/pg_config
make install PG_CONFIG=/usr/pgsql-16/bin/pg_config
# 登陸數(shù)據(jù)庫 安裝插件
postgres=# CREATE EXTENSION pg_dirtyread;
postgres=# select * from pg_available_extensions;
postgres=# \dx數(shù)據(jù)恢復(fù)前置條件
#如果發(fā)現(xiàn)錯(cuò)誤操作，導(dǎo)致數(shù)據(jù)意外刪除，要第一時(shí)間關(guān)閉表上的vacuum
#關(guān)閉vacuum
ALTER TABLE foo SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false);
#表未被vacuum
postgres=# select * from pg_stat_all_tables where relname='foo';
-[ RECORD 1 ]-------+-----------------------------
last_vacuum |
last_autovacuum |
vacuum_count | 0
autovacuum_count | 0操作示例
1)基于表數(shù)據(jù)Delete閃回
--創(chuàng)建測(cè)試表
CREATE TABLE saas (id bigint, name text, mail text);
-- 測(cè)試方便，先把自動(dòng)vacuum關(guān)閉掉。
ALTER TABLE saas SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);
--插入數(shù)據(jù)
INSERT INTO saas VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');
--刪除所有數(shù)據(jù)
DELETE FROM saas ;
postgres=# select * from saas;
--獲取到了已刪除的數(shù)據(jù)
postgres=# SELECT * FROM pg_dirtyread('saas') as t(id bigint, name text, mail text);
id | name | mail
----+-----------+------------
1 | Test1 | 111@qq.com
2 | New Test2 | 111@qq.com
(2 rows)2)基于表字段數(shù)據(jù)閃回
CREATE TABLE saas1 (id bigint, name text, mail text);
INSERT INTO saas1 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');
ALTER TABLE saas1 DROP COLUMN mail ;
DELETE FROM saas1;
postgres=# select * from saas1;
--獲取到了已刪除列的數(shù)據(jù)
postgres=# SELECT * FROM pg_dirtyread('saas1') t(id bigint, name text, dropped_3 text);
id | name | dropped_3
----+-----------+------------
1 | Test1 | 111@qq.com
2 | New Test2 | 111@qq.com
(2 rows)指定列的規(guī)則：使用dropped_N來訪問第N列，從1開始計(jì)數(shù)。
局限性：
由于 PostgreSQL 刪除操作會(huì)移除原始列的元數(shù)據(jù)信息，因此在使用 pg_dirtyread 時(shí)，需要在表列名中顯式指定正確的數(shù)據(jù)類型。這包括類型長度、對(duì)齊方式和類型修飾符，并且數(shù)據(jù)是按值傳遞的。
1.顯式指定類型:
在讀取 Dead 元組時(shí)，需要明確指定列的數(shù)據(jù)類型，包括長度和其他修飾符。
2.完整性檢查:
為了進(jìn)行有效的數(shù)據(jù)恢復(fù)和完整性檢查，必須確保類型信息的準(zhǔn)確性。
3)基于時(shí)間點(diǎn)閃回
如果你想恢復(fù)到某個(gè)特定時(shí)間點(diǎn)的數(shù)據(jù)，可以通過 pg_xact_commit_timestamp 系統(tǒng)函數(shù)來獲取每個(gè)事務(wù)的提交時(shí)間。
具體步驟如下：
1.獲取提交時(shí)間:
使用 pg_xact_commit_timestamp 函數(shù)來獲取每個(gè)事務(wù)的提交時(shí)間。這包括寫入事務(wù)的提交時(shí)間（xmin）和刪除或更新事務(wù)的提交時(shí)間（xmax）。
2.基于時(shí)間點(diǎn)的閃回查詢:
有了這些提交時(shí)間，你可以根據(jù)需要恢復(fù)到的具體時(shí)間點(diǎn)來過濾數(shù)據(jù)，從而實(shí)現(xiàn)基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)。
--參數(shù)配置
track_commit_timestamp = on
--模擬數(shù)據(jù)
CREATE TABLE saas2 (id bigint, name text, mail text);
INSERT INTO saas2 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');
INSERT INTO saas2 VALUES (3, 'Test3','111@qq.com'), (4, 'New Test4','111@qq.com');
DELETE FROM saas2;
--查詢數(shù)據(jù)歷史版本
select pg_xact_commit_timestamp(xmin) as xmin_time,
pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*
from pg_dirtyread('saas2') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,
cmax cid,id bigint, name text, mail text);
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | mail
-------------------------------+------------------------------+----------+-------+------+------+------+------+----+-----------+------------
2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,1) | 822 | 824 | 0 | 0 | 1 | Test1 | 111@qq.com
2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,2) | 822 | 824 | 0 | 0 | 2 | New Test2 | 111@qq.com
2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,3) | 823 | 824 | 0 | 0 | 3 | Test3 | 111@qq.com
2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,4) | 823 | 824 | 0 | 0 | 4 | New Test4 | 111@qq.com
(4 rows)
--查詢某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)
SELECT
pg_xact_commit_timestamp ( xmin ) AS xmin_time,
pg_xact_commit_timestamp ( CASE xmax WHEN 0 THEN NULL ELSE xmax END ) AS xmax_time,*
FROM
pg_dirtyread ( 'saas2' ) AS t ( tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, id bigint, name text, mail text )
WHERE pg_xact_commit_timestamp ( xmin ) <= '2024-09-13 16:51:10' and pg_xact_commit_timestamp ( CASE xmax WHEN 0 THEN NULL ELSE xmax END ) <= '2024-09-13 16:51:10';
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | mail
-------------------------------+------------------------------+----------+-------+------+------+------+------+----+-----------+------------
2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,1) | 822 | 824 | 0 | 0 | 1 | Test1 | 111@qq.com
2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,2) | 822 | 824 | 0 | 0 | 2 | New Test2 | 111@qq.com
2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,3) | 823 | 824 | 0 | 0 | 3 | Test3 | 111@qq.com
2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,4) | 823 | 824 | 0 | 0 | 4 | New Test4 | 111@qq.com
(4 rows)支持查詢被刪除列的歷史數(shù)據(jù)
--創(chuàng)建表
CREATE TABLE saas3 (id bigint, name text, mail text);
INSERT INTO saas3 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');
INSERT INTO saas3 VALUES (3, 'Test3','111@qq.com'), (4, 'New Test4','111@qq.com');
--添加字段
alter table saas3 add column qq text;
--寫入數(shù)據(jù)
postgres=# INSERT INTO saas3 VALUES (5, 'Test3','111@qq.com','qq1223'), (6, 'New Test4','111@qq.com','qq234');
--刪除列
postgres=# alter table saas3 drop column mail;
--刪除某條數(shù)據(jù)
postgres=# delete from saas3 where id=6;
--獲取被刪除的列數(shù)據(jù)
postgres=# select pg_xact_commit_timestamp(xmin) as xmin_time,
pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*
from pg_dirtyread('saas3') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,
cmax cid,id bigint, name text, dropped_3 text,qq text)
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | dropped_3 | qq
-------------------------------+-------------------------------+----------+-------+------+------+------+------+----+-----------+------------+--------
2024-09-13 17:26:43.600404+08 | | 16480 | (0,1) | 839 | 0 | 0 | 0 | 1 | Test1 | 111@qq.com |
2024-09-13 17:26:43.600404+08 | | 16480 | (0,2) | 839 | 0 | 0 | 0 | 2 | New Test2 | 111@qq.com |
2024-09-13 17:26:45.03857+08 | | 16480 | (0,3) | 840 | 0 | 0 | 0 | 3 | Test3 | 111@qq.com |
2024-09-13 17:26:45.03857+08 | | 16480 | (0,4) | 840 | 0 | 0 | 0 | 4 | New Test4 | 111@qq.com |
2024-09-13 17:28:47.577755+08 | | 16480 | (0,5) | 842 | 0 | 0 | 0 | 5 | Test3 | 111@qq.com | qq1223
2024-09-13 17:28:47.577755+08 | 2024-09-13 17:31:58.565021+08 | 16480 | (0,6) | 842 | 844 | 0 | 0 | 6 | New Test4 | 111@qq.com | qq234
(6 rows)
--按時(shí)間獲取被刪除的列數(shù)據(jù)
postgres=# select pg_xact_commit_timestamp(xmin) as xmin_time,
pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*
from pg_dirtyread('saas3') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,
cmax cid,id bigint, name text, dropped_3 text,qq text)
WHERE pg_xact_commit_timestamp ( xmin ) <= '2024-09-13 17:28:48'
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | dropped_3 | qq
-------------------------------+-------------------------------+----------+-------+------+------+------+------+----+-----------+------------+--------
2024-09-13 17:26:43.600404+08 | | 16480 | (0,1) | 839 | 0 | 0 | 0 | 1 | Test1 | 111@qq.com |
2024-09-13 17:26:43.600404+08 | | 16480 | (0,2) | 839 | 0 | 0 | 0 | 2 | New Test2 | 111@qq.com |
2024-09-13 17:26:45.03857+08 | | 16480 | (0,3) | 840 | 0 | 0 | 0 | 3 | Test3 | 111@qq.com |
2024-09-13 17:26:45.03857+08 | | 16480 | (0,4) | 840 | 0 | 0 | 0 | 4 | New Test4 | 111@qq.com |
2024-09-13 17:28:47.577755+08 | | 16480 | (0,5) | 842 | 0 | 0 | 0 | 5 | Test3 | 111@qq.com | qq1223
2024-09-13 17:28:47.577755+08 | 2024-09-13 17:31:58.565021+08 | 16480 | (0,6) | 842 | 844 | 0 | 0 | 6 | New Test4 | 111@qq.com | qq234
(6 rows)注意事項(xiàng)
- 適用范圍: pg_dirtyread 主要適用于 MVCC 機(jī)制下的 DML 操作（如 DELETE 和 UPDATE），對(duì)于 DDL 操作（如 DROP TABLE）導(dǎo)致的數(shù)據(jù)丟失則無能為力。
- 事務(wù)提交后: 一旦事務(wù)提交，數(shù)據(jù)恢復(fù)變得更加困難。如果 VACUUM 沒有運(yùn)行，未提交的事務(wù)產(chǎn)生的 Dead 元組仍然存在，可以通過 pg_dirtyread 查看這些數(shù)據(jù)。但是一旦 VACUUM 清除了 Dead 元組，數(shù)據(jù)就無法通過 pg_dirtyread 恢復(fù)。
- 安全性問題:使用 pg_dirtyread 讀取未提交的數(shù)據(jù)可能會(huì)帶來數(shù)據(jù)一致性和安全性問題。因此，在生產(chǎn)環(huán)境中應(yīng)謹(jǐn)慎使用，并確保數(shù)據(jù)安全。
總結(jié)
pg_dirtyread 是一個(gè) PostgreSQL 擴(kuò)展，它主要用于開發(fā)和測(cè)試環(huán)境中快速查看和恢復(fù)由于誤操作導(dǎo)致的數(shù)據(jù)丟失。它利用 MVCC 機(jī)制來讀取未提交的數(shù)據(jù)，適用于 DML 操作。在事務(wù)提交前，pg_dirtyread 提供了一種便捷的數(shù)據(jù)恢復(fù)方法。然而，在事務(wù)提交后，如果沒有及時(shí)運(yùn)行 VACUUM，仍然有機(jī)會(huì)恢復(fù)數(shù)據(jù)，但一旦 VACUUM 清除了 Dead 元組，數(shù)據(jù)恢復(fù)將變得不可行。因此，在生產(chǎn)環(huán)境中應(yīng)謹(jǐn)慎使用 pg_dirtyread，并依賴備份和 WAL 日志歸檔等更為可靠的恢復(fù)機(jī)制。
作者介紹
吳守陽，51CTO社區(qū)編輯，擁有8年DBA工作經(jīng)驗(yàn)，熟練管理MySQL、Redis、MongoDB等開源數(shù)據(jù)庫。精通性能優(yōu)化、備份恢復(fù)和高可用性架構(gòu)設(shè)計(jì)。善于故障排除和自動(dòng)化運(yùn)維，保障系統(tǒng)穩(wěn)定可靠。具備良好的團(tuán)隊(duì)合作和溝通能力，致力于為企業(yè)提供高效可靠的數(shù)據(jù)庫解決方案。
責(zé)任編輯：華軒
來源：
51CTO
