PostgreSQL的擴展（extensions）-常用的擴展-pg_dirtyread

pg_dirtyread 是 PostgreSQL 的一個特殊擴展，它允許讀取已被刪除但尚未被 VACUUM 清理的數據行，是數據恢復的重要工具。

原理：
pg_dirtyread 通過直接訪問表的底層頁面，繞過 PostgreSQL 正常的可見性規則檢查：

• 讀取表的物理頁面數據
• 忽略 xmax 標記（刪除事務ID）
• 返回所有行版本，包括被刪除的行

時間窗口限制：

• 只能讀取尚未被 VACUUM 清理的數據
• 常規表：通常保留幾小時到幾天
• 頻繁更新的表：保留時間更短

不支持的場景：

• TRUNCATE 操作刪除的數據
• DROP TABLE 刪除的表
• 已執行 VACUUM FULL 的表

一 下載并編譯安裝

1.1 下載

下載網址：
https://github.com/df7cb/pg_dirtyread/tags

1.2 編譯安裝

make
make install

1.3 創建 pg_dirtyread

–修改postgresql.conf文件

shared_preload_libraries = 'pg_stat_kcache,pg_stat_statements,auto_explain,pg_dirtyread'        # (change requires restart)

–創建extension

white=# create extension pg_dirtyread;
CREATE EXTENSION
white=# 
white=# select * from pg_EXTENSION;oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------14270 | plpgsql            |       10 |           11 | f              | 1.0        |           | 17620 | pg_repack          |       10 |         2200 | f              | 1.5.0      |           | 17659 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           | 17739 | pgstattuple        |       10 |         2200 | t              | 1.5        |           | 17840 | pg_bulkload        |       10 |         2200 | f              | 3.1.21     |           | 17861 | pg_dirtyread       |       10 |         2200 | t              | 2          |           | 
(6 rows)

二 測試

2.1 測試一：先delete，再關閉表的autovacuum。（找回失敗）

white=# select count(*) from yewu1.t3;count 
-------100
(1 row)white=# 
white=# delete from yewu1.t3 where id >10;
DELETE 90
white=# 
white=# select count(*) from yewu1.t3;count 
-------10
(1 row)white=# ALTER TABLE yewu1.t3 SET (
white(#       autovacuum_enabled = false, toast.autovacuum_enabled = false
white(#     );
ALTER TABLE
white=# 
white=# SELECT * FROM pg_dirtyread('yewu1.t3') as t(id int, name varchar(20));id |  name   
----+---------1 | haha_12 | haha_23 | haha_34 | haha_45 | haha_56 | haha_67 | haha_78 | haha_89 | haha_910 | haha_10
(10 rows)white=# 

2.2 測試二：先關閉表的autovacuum，再delete。（找回成功）

white=# select count(*) from yewu1.t3;count 
-------100
(1 row)white=# ALTER TABLE yewu1.t3 SET (
white(#       autovacuum_enabled = false, toast.autovacuum_enabled = false
white(#     );
ALTER TABLE
white=# 
white=# delete from yewu1.t3 where id > 10;
DELETE 90
white=# 
white=# select count(*) from yewu1.t3;count 
-------10
(1 row)white=# 
white=# SELECT * FROM pg_dirtyread('yewu1.t3') as t(id int, name varchar(20));id  |   name   
-----+----------1 | haha_12 | haha_23 | haha_34 | haha_45 | haha_5
。。。省略。。。96 | haha_9697 | haha_9798 | haha_9899 | haha_99100 | haha_100
(100 rows)white=# 
white=# 

查看autovacuum默認配置
在默認配置下，表發生較小的變化就會觸發autovacuum，進而影響pg_dirtyread，減少了其可用性。

#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#log_autovacuum_min_duration = 10min    # log autovacuum activity;
#autovacuum = on                        # Enable autovacuum subprocess?  'on'
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
#autovacuum_naptime = 1min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 50       # min number of row updates before
#autovacuum_vacuum_insert_threshold = 1000      # min number of row inserts
#autovacuum_analyze_threshold = 50      # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2    # fraction of inserts over table
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
#autovacuum_multixact_freeze_max_age = 400000000        # maximum multixact age
#autovacuum_vacuum_cost_delay = 2ms     # default vacuum cost delay for# autovacuum, in milliseconds;
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for# autovacuum, -1 means use

謹記：心存敬畏，行有所止。