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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-1976067/，如需轉載，請註明出處，否則將追究法律責任。