作者：吳守陽
pg_dirtyread 是一個 PostgreSQL 擴展，它利用了 PostgreSQL 的多版本并發控制（MVCC）機制來讀取未提交的事務所影響的數據。此插件可以用于在開發和測試環境中快速查看事務對數據的即時影響，當關鍵表中的數據被意外刪除后，業務系統可能會出現服務中斷、數據丟失、數據不一致等問題，影響用戶服務。
作者 | 吳守陽
審校 | 重樓
目錄
1、概述
2、恢復機制
3、優缺點
4、安裝
5、操作實例
1）基于表數據Delete閃回
2）基于表字段數據閃回
3）基于時間點閃回
6、支持查詢被刪除列的歷史數據
7、注意事項
8、總結
概述
pg_dirtyread 是一個PostgreSQL 擴展，它利用了PostgreSQL 的多版本并發控制（MVCC）機制來讀取未提交的事務所影響的數據。此插件可以用于在開發和測試環境中快速查看事務對數據的即時影響，當關鍵表中的數據被意外刪除后，業務系統可能會出現服務中斷、數據丟失、數據不一致等問題，影響用戶服務。通過使用pg_dirtyread，你可以快速查看未提交的刪除或更新事務，恢復數據，從而恢復正常服務、確保數據完整、數據一致。
恢復機制
在 PostgreSQL 中，事務提交后，如果數據尚未被 VACUUM 清理，仍有可能通過某些方式恢復這些數據。這是因為 PostgreSQL 采用了多版本并發控制（MVCC）機制，允許舊的元組（稱為 Dead 元組）在事務提交后繼續保留在系統中，直到被 VACUUM 清除。具體來說：
- MVCC 機制：即使事務已提交，刪除或更新的行仍作為舊版本存在，允許在 VACUUM 運行之前進行恢復。
- VACUUM 的作用：VACUUM 操作會清理表中的 Dead 元組，回收存儲空間并重置行標識符（TID），以便新行可以重用這些空間。一旦執行了 VACUUM，Dead 元組被清除，數據就無法再通過常規手段恢復。
優缺點
- pg_dirtyread 插件非常方便，它可以通過安裝來找回數據。
- 該插件會返回所有數據，包括那些未被刪除的數據。
- 由于 PostgreSQL 的 MVCC 機制，pg_dirtyread 只能用于找回 DML 操作的數據。
- 基于時間點恢復要配置參數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
# 登陸數據庫 安裝插件
postgres=# CREATE EXTENSION pg_dirtyread;
postgres=# select * from pg_available_extensions;
postgres=# \dx數據恢復前置條件
#如果發現錯誤操作，導致數據意外刪除，要第一時間關閉表上的vacuum
#關閉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)基于表數據Delete閃回
--創建測試表
CREATE TABLE saas (id bigint, name text, mail text);
-- 測試方便，先把自動vacuum關閉掉。
ALTER TABLE saas SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);
--插入數據
INSERT INTO saas VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');
--刪除所有數據
DELETE FROM saas ;
postgres=# select * from saas;
--獲取到了已刪除的數據
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)基于表字段數據閃回
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;
--獲取到了已刪除列的數據
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)指定列的規則：使用dropped_N來訪問第N列，從1開始計數。
局限性：
由于 PostgreSQL 刪除操作會移除原始列的元數據信息，因此在使用 pg_dirtyread 時，需要在表列名中顯式指定正確的數據類型。這包括類型長度、對齊方式和類型修飾符，并且數據是按值傳遞的。
1.顯式指定類型:
在讀取 Dead 元組時，需要明確指定列的數據類型，包括長度和其他修飾符。
2.完整性檢查:
為了進行有效的數據恢復和完整性檢查，必須確保類型信息的準確性。
3)基于時間點閃回
如果你想恢復到某個特定時間點的數據，可以通過 pg_xact_commit_timestamp 系統函數來獲取每個事務的提交時間。
具體步驟如下：
1.獲取提交時間:
使用 pg_xact_commit_timestamp 函數來獲取每個事務的提交時間。這包括寫入事務的提交時間（xmin）和刪除或更新事務的提交時間（xmax）。
2.基于時間點的閃回查詢:
有了這些提交時間，你可以根據需要恢復到的具體時間點來過濾數據，從而實現基于時間點的數據恢復。
--參數配置
track_commit_timestamp = on
--模擬數據
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;
--查詢數據歷史版本
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)
--查詢某個時間點的數據
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)支持查詢被刪除列的歷史數據
--創建表
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;
--寫入數據
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;
--刪除某條數據
postgres=# delete from saas3 where id=6;
--獲取被刪除的列數據
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)
--按時間獲取被刪除的列數據
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)注意事項
- 適用范圍: pg_dirtyread 主要適用于 MVCC 機制下的 DML 操作（如 DELETE 和 UPDATE），對于 DDL 操作（如 DROP TABLE）導致的數據丟失則無能為力。
- 事務提交后: 一旦事務提交，數據恢復變得更加困難。如果 VACUUM 沒有運行，未提交的事務產生的 Dead 元組仍然存在，可以通過 pg_dirtyread 查看這些數據。但是一旦 VACUUM 清除了 Dead 元組，數據就無法通過 pg_dirtyread 恢復。
- 安全性問題:使用 pg_dirtyread 讀取未提交的數據可能會帶來數據一致性和安全性問題。因此，在生產環境中應謹慎使用，并確保數據安全。
總結
pg_dirtyread 是一個 PostgreSQL 擴展，它主要用于開發和測試環境中快速查看和恢復由于誤操作導致的數據丟失。它利用 MVCC 機制來讀取未提交的數據，適用于 DML 操作。在事務提交前，pg_dirtyread 提供了一種便捷的數據恢復方法。然而，在事務提交后，如果沒有及時運行 VACUUM，仍然有機會恢復數據，但一旦 VACUUM 清除了 Dead 元組，數據恢復將變得不可行。因此，在生產環境中應謹慎使用 pg_dirtyread，并依賴備份和 WAL 日志歸檔等更為可靠的恢復機制。
作者介紹
吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。
責任編輯：華軒
來源：
51CTO
