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

谨记：心存敬畏，行有所止。