ミワラボ

PostgreSQL で DELETE してしまったデータを復旧する方法

April 20, 2007 8:12 PM

Tweet

ML に流れていたのでメモ。
有益な情報に感謝。

[pgsql-jp: 38276] Re: データの復旧について

：
 
VACUUM していなければ、データベースファイル内に残っている可能性があります。
トランザクションIDをDELETE 文を発行した時点よりも昔に「巻き戻す」ことで、
削除したデータが再び見えるようにできるかもしれません。
ただし、DELETE 実行前後の多少の操作が失われることは覚悟してください。
 
0. 状況再現
# select count(*) from accounts; → 500000
# delete from accounts; → DELETE 500000
# select count(*) from accounts; → 0
 
1. データベースを停止します。
安全のため、ここでバックアップ（ファイルコピー）をしておくのが良いでしょう。
 
2. pg_controldata で現在のトランザクションIDを確認します。
$ pg_controldata
Latest checkpoint's NextXID: 0/74007
 
3. pg_resetxlog でトランザクションIDを巻き戻します。
$ pg_resetxlog -x 74000 $PGDATA ← 2.で確認したXIDよりも小さな値。
 
4. データベースを再起動します。
 
5. 確認
# select count(*) from accounts; → 500000
 
6. うまくいったら、すぐに pg_dump で全データをダンプします。
トランザクションを幾つか実行して、ID 削除したトランザクションまで
進んでしまうと、再びデータが見えなくなってしまいます。