postgresql数据库pg_dirtyread插件闪回技术 —— 筑梦之路

   闪回查询（Flashback Query）是一种在数据库中执行时间点查询的技术。它允许查询数据库中过去某个时间点的数据状态，并返回相应的查询结果。通常闪回查询分为表级以及行级的闪回查询。PostgreSQL数据库由于MVCC的机制，对于DML的操作，更改或者删除的元祖暂时标记为死元祖并未真正的在物理上清理，直到vacuum运行时才清理这些死元祖，这为行级的闪回查询提供了可能。

前提条件

1.延迟VACUUM，确保误操作的数据还没有被垃圾回收。
vacuum_defer_cleanup_age = 5000000  
# 延迟500万个事务再回收垃圾，误操作后在500万个事务内，如果发现了误操作，才有可能使用本文提到的方法闪回。


2.记录未被freeze，确保无操作的数据，以及后面提交的事务号没有被freeze(抹去)。
vacuum_freeze_min_age = 50000000   
# 事务年龄大于5000万时，才可能被抹去事务号。


3、开启事务提交时间跟踪，确保可以从xid得到事务结束的时间
track_commit_timestamp = on     
# 开启事务结束时间跟踪，开启事务结束时间跟踪后，会开辟一块共享内存区存储这个信息。

 pg_dirtyread插件

  pg_dirtyread是PostgreSQL数据库的一个扩展插件。当在PG执行了误操作SQL（如UPDATE或DELETE) 后，它可以从表中读取未被vacuum的死元祖，可用于查看意外删除或更改的受损数据，达到类似“闪回查询”的功能。pg_dirtyread基于MVCC多版本机制，通过检索查询旧版本，获取指定老版本数据，实现行级的数据还原。

使用限制

• pg_dirtyread很好的解决了误操作导致的数据修改删除问题。但是它最大的缺点就是受制于autovacuum进程，如果autovacuum进程清理掉了死元组，pg_dirtyread就没办法工作了。所以当出现误删数据之后，需先关闭autovacuum，查询误操作的表是否已经发生了vacuum。
• 闪回查询通常分为表级以及行级的闪回查询。pg_dirtyread目前仅只支持普通表的误DML操作（insert、update、delete）、以及DDL操作中的drop column的闪回查询，实现行级别的闪回查询。对其他DDL操作，如drop table等无法进行闪回操作。

安装插件pg_dirtyread

 

GitHub地址：https://github.com/df7cb/pg_dirtyread

# 下载

wget  https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.6.tar.gz

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

make

make install

# 登陆数据库 安装插件

postgres=# CREATE EXTENSION pg_dirtyread;
postgres=# select * from pg_available_extensions;
postgres=# \dx

 

pageinspect插件

  pageinspect模块提供函数让你从低层次观察数据库页面的内容，这对于调试目的很有用。所有这些函数只能被超级用户使用。pageinspect的源码在postgres源码包的contrib目录下，解压postgre源码包后进入对应的目录。

# 查找

find / -name contrib

# 登陆数据库安装插件

postgres=# create extension pageinspect;
postgres=# select * from pg_available_extensions;
postgres=# \dx

 闪回案例

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);

 2. drop列闪回

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);

虽然b列被drop掉了，但是仍然可以读取到数据。

如何指定列：
这里使用dropped_N来访问第N列，从1开始计数。

局限：
由于PG删除了原始列的元数据信息，因此需要在表列名中指定正确的类型，这样才能进行少量的完整性检查。包括类型长度、类型对齐、类型修饰符，并且采取的是按值传递。

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，我们可以查看每个元祖的历史版本操作，何时插入以及何时进行更新/删除的。 

闪回查询某个时间点的数据

根据事务提交顺序，逆序，逐个事务排除，逐个事务回退。其语法为：

$ts表示要查询某个表在ts这个时间点上的数据，ts指一个具体的历史时间。

A is distinct from B：表示排除A表达式与B表达式相匹配的行。 

 

支持查询被删除列的历史数据

DML误操作后，如果又发生了DDL，例如新增字段，修改字段等，只要表没有被重写（例如通过 VACUUM FULL 或 CLUSTER），pg_dirtyread支持检索删除列的内容。使用dropped_N来访问第N列，从1开始计数。