PostgreSQL中刪除的數據能否恢復

作者：沃趣科技首席數據庫架構師 唐成

問題的提出

• 有人問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，供大家研究使用。 

首先使用這個工具可以查詢某一個表的數據文件中各行的狀態：

使用這個工具可以清理表的數據文件中的t_infomask中的hint信息，在清理hint狀態之前，先查看行上的t_maskinfo狀態： 

然后執行下面命令清除行上的hint狀態： 

清除完后，我們再看行上的t_infomask狀態： 

查詢和改變事務的狀態的方法如下： 

查詢事務xid=11的狀態的命令如下： 

修改事務xid=11的狀態的命令如下： 

其中-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的一些原理，所以以上這些操作通常不要拿到生產數據庫上去試！！！