postgreSQL的闪回
postgreSQL的闪回
PostgreSQL基于MVCC机制的闪回实战
前言
闪回查询（Flashback Query）是一种在数据库中执行时间点查询的技术。
1.闪回查询
1.1 概述
闪回查询（Flashback Query）是一种在数据库中执行时间点查询的技术。它允许查询数据库中过去某个时间点的数据状态，并返回相应的查询结果。通常闪回查询分为表级以及行级的闪回查询。
PostgreSQL数据库由于MVCC的机制，对于DML的操作，更改或者删除的元祖暂时标记为死元祖并未真正的在物理上清理，直到vacuum运行时才清理这些死元祖，这为行级的闪回查询提供了可能。
1.2 flashback前提
1.延迟VACUUM，确保误操作的数据还没有被垃圾回收。
vacuum_defer_cleanup_age = 5000000
# 延迟500万个事务再回收垃圾，
误操作后在500万个事务内，
如果发现了误操作，才有可能使用本文提到的方法闪回。
2.记录未被freeze，确保无操作的数据，
以及后面提交的事务号没有被freeze(抹去)。
vacuum_freeze_min_age = 50000000
# 事务年龄大于5000万时，才可能被抹去事务号。
3、开启事务提交时间跟踪，确保可以从xid得到事务结束的时间
track_commit_timestamp = on
# 开启事务结束时间跟踪，开启事务结束时间跟踪后，
会开辟一块共享内存区存储这个信息。
2.pg_dirtyread插件
pg_dirtyread是PostgreSQL数据库的一个扩展插件。当在PG执行了误操作SQL（如UPDATE或DELETE) 后，它可以从表中读取未被vacuum的死元祖，可用于查看意外删除或更改的受损数据，达到类似“闪回查询”的功能。pg_dirtyread基于MVCC多版本机制，通过检索查询旧版本，获取指定老版本数据，实现行级的数据还原。
2.1安装插件pg_dirtyread
pg_dirtyread 不存在于 contrib 目录下，
因此需要单独编译
GitHub地址：https://github.com/df7cb/pg_dirtyread
1.下载安装包：
安装包：pg_dirtyread-2.6.tar.gz
https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.6.tar.gz
2.授权解压
cp /opt/pg_dirtyread-2.6.tar.gz /home/postgres/
chown postgres:postgres /home/postgres/pg_dirtyread-2.6.tar.gz
su - postgres
tar -xzvf pg_dirtyread-2.6.tar.gz
cd pg_dirtyread-2.6
3.编译和安装
make
make install
4.安装插件
（注意：需要在哪个数据库下使用这个插件，就需要在哪个数据库下面安装这个插件）
psql
postgres=# CREATE EXTENSION pg_dirtyread;
postgres=# select * from pg_available_extensions;
3.安装插件pageinspect
pageinspect模块提供函数让你从低层次观察数据库页面的内容，这对于调试目的很有用。所有这些函数只能被超级用户使用。
pageinspect的源码在postgres源码包的contrib目录下，解压postgre源码包后进入对应的目录。（pg16默认已经安装）
[root@centos79 ~]# find / -name contrib
/pgccc/soft/postgresql-15.6/contrib
/usr/share/git-core/contrib
/usr/share/doc/git-1.8.3.1/contrib
/home/postgres/pg_dirtyread-2.6/contrib
cd /pgccc/soft/postgresql-15.6/contrib/pageinspect/
make && make install
4.闪回案例
4.1删除找回
官方案例
-创建测试表
CREATE TABLE foo (bar bigint, baz text);
-- 测试方便，先把自动vacuum关闭掉。
ALTER TABLE foo SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);
--插入数据
INSERT INTO foo VALUES (1, 'Test'), (2, 'New Test');
--删除所有数据
DELETE FROM foo;
postgres=# select * from foo;
postgres=# SELECT * FROM pg_dirtyread('foo') as t(bar bigint, baz text);
4.2drop列恢复
这里使用dropped_N来访问第N列，从1开始计数。
局限：由于PG删除了原始列的元数据信息，因此需要在表列名中指定正确的类型，这样才能进行少量的完整性检查。包括类型长度、类型对齐、类型修饰符，并且采取的是按值传递。
CREATE TABLE ab(a text, b text);
INSERT INTO ab VALUES ('Hello', 'World');
ALTER TABLE ab DROP COLUMN b;
DELETE FROM ab;
postgres=# select * from ab;
postgres=# SELECT * FROM pg_dirtyread('ab') ab(a text, dropped_2 text);
a | dropped_2
-------+-----------
Hello | World
(1 row)
可以看到，虽然b列被drop掉了，但是仍然可以读取到数据。
如何指定列：这里使用dropped_N来访问第N列，从1开始计数。
4.3基于时间点闪回
pg_xact_commit_timestamp函数：查询事务提交时间
如果只想恢复到其中的某一个时间点的数据：
- 首先需要通过系统函数 pg_xact_commit_timestamp，得到每个元祖写入事务的提交时间（xmin）以及删除/更新事务提交时间（xmax）。
- 加以处理后，进而实现基于时间点的闪回查询。
--设置参数
track_commit_timestamp = on
--模拟数据
create table bak (id int,info text);
insert into bak values(1,'aaa'),(2,'bbb'),(3,'ccc');
delete from bak;
--通过事务提交时间，查询数据历史版本
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 ( 'bak' ) AS T ( tableoid OID, ctid TID, xmin XID, xmax XID, cmin CID, cmax CID, ID INT, info TEXT );
根据xmin_time，xmax_time，我们可以查看每个元祖的历史版本操作，何时插入以及何时进行更新/删除的
-- 加上时间条件
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 ( 'bak' ) AS T ( tableoid OID, ctid TID, xmin XID, xmax XID, cmin CID, cmax CID, ID INT, info TEXT )
WHERE pg_xact_commit_timestamp ( xmin )>='2025-09-23 16:34:45'
闪回查询某个时间点的数据
根据事务提交顺序，逆序，逐个事务排除，逐个事务回退，其语法为：
postgres=# INSERT INTO foo SELECT * FROM pg_dirtyread('foo') as t(bar bigint, baz text) where bar = 1;
INSERT 0 1
postgres=# SELECT * FROM foo;
bar | baz
-----+----------
2 | New Test
1 | Test
(2 rows)
字段说明：
1.pg_dirtyread 只是把页面里还没被 vacuum 掉的 行版本（tuple） 捞出来。
它给的信息只有：
- xmin：插入该行的事务号
- xmax：把该行标记为“不可见”的事务号（0 表示仍然可见）
- ctid：物理位置
没有任何字段记录“操作类型”。
2.pg_xact_commit_timestamp() 只能告诉你 该事务的提交时间，同样不会记录操作类型。
3.在 PostgreSQL 内部，UPDATE 会被拆成“插入新元组 + 把旧元组标记为无效”，所以
- 你看到一条 xmin=123 的行，可能是 123 做的 INSERT，也可能是 123 做的 UPDATE 插入的新版本；
- 你看到一条 xmax=456 的行，可能是 456 做的 UPDATE 把旧版本标脏，也可能是 456 做的 DELETE；
从 pg_dirtyread 本身无法区分。
5.总结
PostgreSQL数据库由于MVCC的机制，对于DML的操作，更改或者删除的元祖暂时标记为死元祖并未真正的在物理上清理，直到vacuum运行时才清理这些死元祖，这为行级的闪回查询提供了可能。
6.实现原理分析
来简单的进行分析一下，pg_dirtyread是如何实现的。这个插件很简单就一个函数，我们再将这个函数的实现再简化一下，保留一些主干，就得到了如下的伪代码。
Datum
pg_dirtyread(PG_FUNCTION_ARGS)
{
if (SRF_IS_FIRSTCALL())
{
// 初次调用函数 做一些初始化的动作
// 获取一些信息 比如获取输入的表的OID 打开表
relid = PG_GETARG_OID(0);
heap_open(relid, AccessShareLock);
// 根据输入的字段信息 生成相应的映射关系
usr_ctx->map = dirtyread_convert_tuples_by_name(usr_ctx->reltupdesc, ...
// 设定扫描表的策略
usr_ctx->scan = heap_beginscan(usr_ctx->rel, SnapshotAny, 0, NULL ...
}
// 每次需要执行的动作 尝试扫描表，获取一个表中的元组
if ((tuplein = heap_getnext(usr_ctx->scan, ForwardScanDirection)) != NULL)
{
// 将获取的到元组数据 根据map的映射关系 填充生成待返回的元组 然后return
tuplein = dirtyread_do_convert_tuple(tuplein, usr_ctx->map, usr_ctx->oldest_xmin);
...
}
else
{
// 扫描到表的最后了 没有下一个元组数据量 做一些资源释放和清理工作
heap_endscan(usr_ctx->scan);
heap_close ...
}
}
关于字段名匹配的函数dirtyread_convert_tuples_by_name实际匹配调用的是dirtyread_convert_tuples_by_name_map，简化一下就是
AttrNumber *
dirtyread_convert_tuples_by_name_map(TupleDesc indesc, TupleDesc outdesc, const char *msg)
{
for (i = 0; i < n; i++)
{
// ...
for (j = 0; j < indesc->natts; j++)
{
Form_pg_attribute inatt = TupleDescAttr(indesc, j);
// ...
if (strcmp(attname, NameStr(inatt->attname)) == 0)
{
/* Found it, check type */
// ...
}
}
/* Check dropped columns */
if (attrMap[i] == 0)
if (strncmp(attname, "dropped_", sizeof("dropped_") - 1) == 0)
{
// ...
}
/* Check system columns */
if (attrMap[i] == 0)
for (j = 0; system_columns[j].attname; j++)
if (strcmp(attname, system_columns[j].attname) == 0)
{
// ...
}
}
// ...
}
system_columns的声明则是如下
static const struct system_columns_t {
char *attname;
Oid atttypid;
int32 atttypmod;
int attnum;
} system_columns[] = {
{ "ctid", TIDOID, -1, SelfItemPointerAttributeNumber },
#if PG_VERSION_NUM < 120000
{ "oid", OIDOID, -1, ObjectIdAttributeNumber },
#endif
{ "xmin", XIDOID, -1, MinTransactionIdAttributeNumber },
{ "cmin", CIDOID, -1, MinCommandIdAttributeNumber },
{ "xmax", XIDOID, -1, MaxTransactionIdAttributeNumber },
{ "cmax", CIDOID, -1, MaxCommandIdAttributeNumber },
{ "tableoid", OIDOID, -1, TableOidAttributeNumber },
{ "dead", BOOLOID, -1, DeadFakeAttributeNumber }, /* fake column to return HeapTupleIsSurelyDead */
{ 0 },
};
7.pg_dirtyread使用注意事项和优缺点
pg_dirtyread 使用注意事项（生产环境可落地版）
前置条件
- 1.数据被 DELETE/UPDATE 后尚未发生 VACUUM（含 autovacuum）。
– 第一时间执行以下sql，关闭自动VACUUM，防止VACUUM
ALTER TABLE <tbl>
SET (autovacuum_enabled = off, toast_autovacuum_enabled = off);
- 2.参数 track_commit_timestamp = on（否则拿不到事务提交时间）。
- 3.已安装扩展
CREATE EXTENSION pg_dirtyread;
权限与隔离
- 只对普通堆表生效，不支持 TRUNCATE、DROP、VACUUM FULL、unlogged 表、临时表。
- 需要超级用户或具有表 SELECT + 扩展 EXECUTE 权限。
- 建议把受损库克隆到测试机再插件扫描，避免脏读造成 IO 抖动。
列定义必须 100 % 匹配
- 原表加减列、TYPE 修改后，插件仍按旧元组物理布局读取；少一列都会 core 宕。
- 若记不清旧结构，先用以下sql把系统列扫出来，再拼完整字段列表。
SELECT tableoid, ctid, xmin, xmax, cmin, cmax, dead FROM pg_dirtyread('tbl') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean);
时间窗口
- 高并发更新场景下，dead 元组可能 5 ~ 15 min 就被 autovacuum 回收；
- 关闭 autovacuum 后也要盯紧表膨胀，必要时手动 VACUUM FREEZE 只针对其他表。
恢复流程（实战顺序）
- a) 禁用 autovacuum →
-- 确认提交时间可查
SHOW track_commit_timestamp; -- 需要 on
-- 确认扩展已装
CREATE EXTENSION IF NOT EXISTS pg_dirtyread;
--立即关掉 autovacuum（仅对该表）
ALTER TABLE cities
SET (autovacuum_enabled = off, toast_autovacuum_enabled = off);
- b) 用 pg_dirtyread 把数据抽到中间表 tmp_rescue →
--用 pg_dirtyread 把“死”行抽出来（假设原表结构：id int, name text, population float4, elevation int4）
-- 2-1 建中间救援表，与源表结构相同
CREATE TABLE tmp_rescue (LIKE cities INCLUDING ALL);
-- 2-2 把被 DELETE/UPDATE 标脏但尚未被回收的行捞进来
INSERT INTO tmp_rescue
SELECT t.*
FROM pg_dirtyread('cities') AS t(tableoid oid, ctid tid,
xmin xid, xmax xid,
cmin cid, cmax cid,
id int, name text,
population float4, elevation int4)
WHERE xmax <> 0 -- 已被删或旧版本
AND pg_xact_commit_timestamp(xmin) >= '2025-10-29 16:34:45';
- c) 与当前生产数据做 LEFT JOIN 找缺失行 →
与当前生产数据对比，找出真正丢失的行
（用主键 id 做 LEFT JOIN，NULL 一侧即为需要补回的行）
-- 3-1 仅保留生产库已不存在的主键
DELETE FROM tmp_rescue r
USING cities c
WHERE c.id = r.id;
-- 3-2 验证一下数量
SELECT count(*) AS missing_rows FROM tmp_rescue;
- d) 用 INSERT … SELECT 或 COPY 补回；
把缺失数据写回生产表
-- 4-1 最简单的 INSERT
INSERT INTO cities
SELECT * FROM tmp_rescue;
-- 如果表很大，可改用 COPY 提速：
-- COPY (SELECT * FROM tmp_rescue) TO '/tmp/rescue.csv' CSV;
-- COPY cities FROM '/tmp/rescue.csv' CSV;
- e) 确认无误后重新开启 autovacuum。
ALTER TABLE cities RESET (autovacuum_enabled, toast_autovacuum_enabled);
- f清理中间表
DROP TABLE tmp_rescue;
局限性
- 只能看到“行版本”，无法区分 INSERT/UPDATE/DELETE；
- 无法恢复被 TRUNCATE、DROP、重写式 DDL 清掉的行；
- 更新链太长时，旧版本可能跨页，插件只能扫到尚未被 reuse 的 ctid。
浙公网安备 33010602011771号