PostgreSQL flashback(闪回) 功能实现与介绍

PostgreSQL , 脏读 , 事务 , flashback , 闪回 , drop , truncate , dml

闪回的需求往往是救命的需求，因为通常情况下数据库正常运行是不需要闪回的，往往是出现了误操作，被攻击，被注入后，数据库的数据被删除或恶意纂改并且纂改的事务已提交，也就是说纂改已经被持久化了。

这种情况下需要闪回来救命，回到被破坏前的状态。

DML闪回和DDL闪回。

DML闪回指对INSET, UPDATE, DELETE操作的闪回。DDL闪回指DROP, TRUNCATE操作的闪回。

1、物理回退，相当于使用物理备份和归档进行时间点恢复，全库恢复到误操作前的状态。

（可以新建一个库用于恢复，恢复到目标时间点，恢复后，将误操作前的数据导出来，再导入线上数据库。）

2、在当前库回退，在当前库，将误操作影响的数据找出来。

1、物理回退，PG内核已支持时间点恢复，只要有误操作前的全量备份和所有归档即可。

2、当前库回退，使用HOOK，可以实现DROP和TRUNCATE操作的回收站功能。

3、使用延迟垃圾回收、脏读、行头事务号、事务提交日志，可以实现DML操作的闪回。

以前写的一些闪回方案：

<a href="https://github.com/digoal/blog/blob/master/201408/20140828_01.md">《PostgreSQL 闪回 - flash back query emulate by trigger》</a>

<a href="https://github.com/digoal/blog/blob/master/201504/20150429_01.md">《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》</a>

<a href="https://github.com/digoal/blog/blob/master/201404/20140403_01.md">《PostgreSQL 回收站功能 - 基于HOOK的recycle bin pgtrashcan》</a>

<a href="https://github.com/digoal/blog/blob/master/201608/20160823_04.md">《PostgreSQL 最佳实践 - 任意时间点恢复源码分析》</a>

<a href="https://github.com/digoal/blog/blob/master/201608/20160823_03.md">《PostgreSQL 最佳实践 - 在线增量备份与任意时间点恢复》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151220_06.md">《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 6 任意时间点恢复》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130205_01.md">《PostgreSQL PITR THREE recovery target MODE: name,xid,time USE CASE - 2》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130204_01.md">《PostgreSQL PITR THREE recovery target MODE: name,xid,time USE CASE - 1》</a>

本文将详细介绍“使用延迟垃圾回收、脏读、行头事务号、事务提交日志，实现DML操作的闪回。”的方法。

1、延迟VACUUM，确保误操作的数据还没有被垃圾回收。

2、记录未被freeze，确保无操作的数据，以及后面提交的事务号没有被freeze(抹去)。

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

4、事务提交时间跟踪未抹去。可以加大BUFFER来增加可跟踪的事务数。（重启数据库不影响，有持久化已有的跟踪记录）

1、将xid转换为txid的函数，见本文末尾。（因为记录的行头部，存储的是XID，而不是TXID，而查询事务提交状态，用的是TXID，因此需要转换一下。）

2、脏读插件pg_dirtyread（用于读取脏页）

<a href="https://github.com/ChristophBerg/pg_dirtyread">https://github.com/ChristophBerg/pg_dirtyread</a>

3、根据txid查询事务提交状态

4、根据xid查询事务提交时间

5、使用脏读插件，将需要flashback的数据（连同头信息xmin,xmax）写入临时表，根据以上两种方法生成如下字段：

写入事务提交状态、事务提交时间。(xmin)

删除事务提交状态、事务提交时间。(xmax)

例如

1、根据pg_xlogdump找到精确的误操作xid，以及事务提交对应的时间戳。（或者使用用户提供的时间戳，大概的误操作前的时间。）

参考：

<a href="https://github.com/digoal/blog/blob/master/201512/20151210_01.md">《PostgreSQL 使用pg_xlogdump找到误操作事务号》</a>

2、回退到过去的某个时间点（采用基于临时表的VIEW来展现） （根据事务提交顺序，逆序，逐个事务排除，逐个事务回退。）

例子

3、找出被误操作事务 删除、更新、插入 的记录（OLD ROW(被删除、更新前的), NEW ROW(更新后的、误插入的)）。

flashback的前提，前面已经讲了，但是可能带来一些负面影响。

1、由于设置了vacuum_defer_cleanup_age，因此每次都会被触发VACUUM，扫描表，但是又有一些垃圾不能被回收；

2、可能导致表膨胀。

1、提高autovacuum_naptime，从而降低频繁扫描的概率。

2、膨胀可能无法解决。建议修改内核，实现可以仅针对重要的表设置 vacuum_defer_cleanup_age。从而避免全库膨胀。

Name

Return Type

Description

txid_status(bigint)

txid_status

report the status of the given transaction: committed, aborted, in progress, or null if the transaction ID is too old

pg_xact_commit_timestamp(xid)

timestamp with time zone

get commit timestamp of a transaction

<a href="https://www.postgresql.org/docs/10/static/functions-info.html">https://www.postgresql.org/docs/10/static/functions-info.html</a>

开启事务结束时间跟踪后，会开辟一块共享内存，跟踪事务结束时间。

xid是32位的整型，会被FREEZE，循环使用，xmin,xmax都是XID类型。通过xid可以得到事务结束时间。

txid是64位整型，不会被循环使用，通过epoch可以从xid转换为txid。通过txid可以得到事务的提交状态。

我们可以自定义一个函数，将xid转换为txid。

编译

拷贝到数据库软件LIB目录

创建函数

测试

为什么会有EPOCH呢？因为XID是循环使用的，每一个循环都会使得EPOCH自增1。从而使得TXID可以持续增长。

<a href="https://github.com/digoal/blog/blob/master/201109/20110930_03.md">《Use pg_resetxlog simulate tuple disappear within PostgreSQL》</a>

<a href="https://github.com/digoal/blog/blob/master/201109/20110930_01.md">《PostgreSQL xid(int4) to txid(int8)》</a>

误操作后，如果又发生了DDL，例如新增字段，修改字段等。使用本文提供的闪回方法，有没有问题？

测一下就知道了，不会有问题。

本文介绍了原地闪回的一种方法：

1、新增脏读功能、新增表级vacuum_defer_cleanup_age(避免全库膨胀)功能、开启track_commit_timestamp。可以实现dml flashback（闪回）。

2、增加回收站(通过HOOK)功能，可以实现DDL flashback。

全库闪回，可以参考PostgreSQL的PITR的功能。

原地闪回的另一种方法：利用redo的undo内容进行闪回，这种方法可以避免膨胀的问题，回退到什么时间点，与保留的REDO文件数有关。使用redo来闪回也会引入一个问题，UNDO需要记录更多的内容，导致REDO文件内容变多。因此建议也是设置表级redo的UNDO内容。