闪回查询(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是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等无法进行闪回操作。
- 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的源码在postgres源码包的contrib目录下,解压postgre源码包后进入对应的目录。
- # 查找
-
- find / -name contrib
-
- # 登陆数据库安装插件
-
- postgres=# create extension pageinspect;
- postgres=# select * from pg_available_extensions;
- postgres=# \dx
- -创建测试表
- 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);
- 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删除了原始列的元数据信息,因此需要在表列名中指定正确的类型,这样才能进行少量的完整性检查。包括类型长度、类型对齐、类型修饰符,并且采取的是按值传递。
- 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开始计数。