PostgreSQL中删除的数据能否恢复

喜欢ヅ旅行 2022-07-30 13:25 1269阅读 0赞

### 作者：沃趣科技首席数据库架构师 唐成     问题的提出 ###

*  有人问PostgreSQL数据库中刚刚删除的数据能否被恢复？
 *  或更进一步，如果如要在一个事务中做了一系列的更新、删除、插入的操作后，把这个事务提交之后又后悔了，能否恢复到之前的状态？

当然如果数据库有备份，可以直接从备份的数据中恢复，本文讨论的是没有备份的情况下能否恢复。

### 理论分析 ###

从PostgreSQL多版本实现的原理上，这是有可能的。因为PostgreSQL的多版本原理是旧数据并不删除：

*  对于删除数据的操作，只是把行上的xmax改成当前的事务id
 *  对于更新操作，只是把原先行上xmax改成当前的事务id，并插入一个新行，而新行上的xmin置为当前的事务id
 *  事务的状态是记录在commit log中的，如果事务提交，只是把commit log中相应的事务状态改成“已提交状态（TRANSACTION\_STATUS\_COMMITTED ）”，如果事务回滚，则把commit log中的事务状态改成“事务回滚（TRANSACTION\_STATUS\_ABORTED ）”

所以从理论上说，只要把在commit log中刚提交事务状态从“TRANSACTION\_STATUS\_COMMITTED”改成“TRANSACTION\_STATUS\_ABORTED”，原先的事务就会做废，就能回到事务之前的状态。   
  
但这个恢复有一个前提就是旧版本的数据没有被vacuum垃圾回收进程清理掉，如果旧版本的数据已被vacuum垃圾回收进程给清理掉了，就不能恢复了。所以如果作了删除数据的操作后，马上把数据库停下来，这时autovacuum进程还没有把旧版本的数据给清理掉时，数据是可以恢复的。   
  
但仅仅是把commit log中的事务状态改一下，就能恢复数据吗？答案也是否定的，事情没有这么简单，原因是多版本的可见性判断不仅仅是由commit log中的事务状态的决定的，行上还有t\_infomask状态位中的hint信息来决定。如果hint已表示该行上的事务已被提交，则不需要再到commit log中来查看事务的状态了。这个功能主要是为了提高性能，因为到clog中判断行的可见性，而clog中只有8个块是缓存在共享内存中的，如果判断每个行都去查找clog，效率太低了。具体这一部分的内容可以见我的另一篇blog: PostgreSQL中行的可见性判断中t\_infomask字段的作用   
  
所以要想恢复数据，还需要把相应表文件中各行上的t\_infomask状态中的hint标志位给清除掉之后，数据才能恢复回来。

### 恢复的工具 ###

因为整个恢复的过程比较复杂，为此我写了一个工具叫pg\_fix，放在github上:https://github.com/osdba/pg\_fix，供大家研究使用。   
  
首先使用这个工具可以查询某一个表的数据文件中各行的状态：![0_wx_fmt_jpeg][]

使用这个工具可以清理表的数据文件中的t\_infomask中的hint信息，在清理hint状态之前，先查看行上的t\_maskinfo状态： ![0_wx_fmt_jpeg 1][]

然后执行下面命令清除行上的hint状态： ![0_wx_fmt_jpeg 2][]

清除完后，我们再看行上的t\_infomask状态： ![0_wx_fmt_jpeg 3][]

查询和改变事务的状态的方法如下：   
  
查询事务xid=11的状态的命令如下： ![0_wx_fmt_jpeg 4][]

修改事务xid=11的状态的命令如下： ![0_wx_fmt_jpeg 5][]

其中-s后的值表示要把事务改成什么状态，事务的状态值有四种，为0~3，意思如下：

*  \#define TRANSACTION\_STATUS\_IN\_PROGRESS 0x00
 *  \#define TRANSACTION\_STATUS\_COMMITTED 0x01
 *  \#define TRANSACTION\_STATUS\_ABORTED 0x02
 *  \#define TRANSACTION\_STATUS\_SUB\_COMMITTED 0x03

当然上面使用pg\_fix工具直接修改表中数据和commit log中事务的状态都必须是数据库停下来的情况。   
  
另本文的目的主要是为了研究PostgreSQL的一些原理，所以以上这些操作通常不要拿到生产数据库上去试！！！

[0_wx_fmt_jpeg]: /images/20220724/f59c824fe9ff42409f281d2073177c37.png
[0_wx_fmt_jpeg 1]: /images/20220724/e74a7ad907eb415f8ccdb0c85ffc5bde.png
[0_wx_fmt_jpeg 2]: /images/20220724/f8fc24d19b244828b1add6104dbb28a2.png
[0_wx_fmt_jpeg 3]: /images/20220724/85b62ed4d8904d269fd65013f033d987.png
[0_wx_fmt_jpeg 4]: /images/20220724/e8a1576d4fc641319dd2f17b65d13bcc.png
[0_wx_fmt_jpeg 5]: /images/20220724/a47ed546d0aa456b9ecb8f6f58cc5085.png