PostgreSQL（バージョン6.x）をしばらく運用しているとlogファイルが巨大になってくる。logファイルなんだから消してもいいだろうと、適当にmvしたりする人がまれにいる。が、PostgreSQLのlogファイルは人間が目で見るためのテキストlogファイルではなく、PostgreSQLの動作に必要なバイナリファイルであり、それを消すとPostgreSQLがまともに動かなくなる。

という状態になってしまったデータを復旧させてくれないかと頼まれていろいろ調べてみた。

PostgreSQLはupdateなどでデータを書き換える際に、実際にデータを書き換えるのではなく、既存のデータに無効フラグを立てて見えなくしつつ、新規に新しい内容のデータ領域を作成してそちらを有効にする。updateをかければかけるほどデータ領域はひたすら増え続けていく。そのため、定期的なvacuumを行って不必要なデータ領域を削除したりする必要がある。

PostgreSQLではすべてのSQLコマンドはトランザクションで囲まれているものと見なされる。明示的にbegin;commit;で囲まれていないSQLコマンドも、内部的にはその前後がbegin;commit;で囲まれているものとして処理する。そして、すべての処理は32ビット連番のトランザクションIDが割り当てられている。ある処理で変更があったデータには、その処理のトランザクションIDが記録される。

先ほどの「書き換えられたデータに無効なフラグを立てる」という処理には、このトランザクションIDが関わっているようだ。ある行の内容を書き換えた場合、その行には古いデータと新しいデータの二つが存在することになる。それぞれには、そのデータが生成された処理のトランザクションIDが記録されている。現在のトランザクションIDと比較して、より現在のIDに近いトランザクションIDをもつデータが、その行の最新のデータとなる。

logファイルはどうやらトランザクションを管理するデータらしい。logファイルを削除するとトランザクションIDがリセットされてしまう。実データは別ファイル（baseディレクトリ以下）に記録されており、logファイルがおかしくなってもそちらは影響を受けない。しかし、トランザクションIDがリセットされると、実データファイル側に記録されているトランザクションIDとの間に矛盾が生じるため、データが見えなくなってしまう。たとえばトランザクションIDが1000の状態で記録されたデータは、トランザクションIDが100の状態では見えない。

そのようになったデータを再び見えるようにするためには、現在のトランザクションIDを、実データファイルに記録されている最新のトランザクションIDよりも大きくしてやればいい。トランザクションIDを直接操作する方法は見つけられなかったが、SQLコマンドを1回発行すれば現在のトランザクションIDが1増えるので、特に意味のないSQLコマンドを必要な回数だけ発行してやればトランザクションIDを増やすことができる。

データに記録されている最新のトランザクションIDを知る方法も見つけられなかった。もしもDBの利用頻度が分かっているのならば、たとえば「1日の平均トランザクション数×運用日数」などでトランザクション数を予想して、その回数分SQLコマンドを発行してやればいい。利用頻度が分からない場合は、oidを使ってトランザクションIDが最新に追いついたかどうかを類推する方法がある。

害がなさそうな適当なテーブルに対して、1行新しい行を作成し、その行のoidを見る。トランザクションIDはリセットされても、oidはリセットされないようなので、oidはリセットされる前の最新のoidよりも新しいものが割り当てられる。その値を基準として使うことになる。

トランザクションIDを進めていくと、少しずつデータが復旧していく（古いトランザクションIDをもつデータから見えるようになってくる）。そこで既存テーブルの中で新規行の作成が頻繁であったと思われるテーブルのもっとも大きなoidをチェックする。データが復旧していくとそのoidの値が大きくなっていく。その値が先ほど得た基準となるoidの値に限りなく近くなる＝データが元の状態に復旧した、ということになる。

トランザクションIDがリセットされると、システム管理テーブルの内容も見えなくなるので、テーブルのスキーマ情報なども得られなくなる。が、トランザクションIDを進めていくことで、システム管理テーブルの内容も回復し、スキーマ情報も得られるようになる。ただし、sequenceやindexなどのデータは完全に回復できないこともあるようだ。それらはスキーマと実データを使って、再生成・設定すればいいだろう。

私の場合は「select 1;」を100万回実行するたびに、oidをチェックし、対象テーブルの最新oidが増えなくなってからさらに念のため200万回ぶんトランザクションIDを進めた辺りで、だいたい元のデータが復旧したと判断した。そのあたりの判断はDBの設計によって異なるだろう。