ポスグレカレンダーのノルマは昨日のぶんで達成したが、触発されてポスグレ小ネタを連投してみる。今回は、闇の技術を伝承する。ゆめゆめ使わぬよう。
TL;DR
- Postgresは追記型のMVCCを実装している
- XIDを巻き戻すことでデータベースを過去の状態に戻せる
Postgresのトランザクション管理
事前知識として、PostgresのトランザクションがXIDという連番で管理されていることについて、説明する。
追記型アーキテクチャとは
PostgresのトランザクションID（XID）は、32bit符号なし整数で、トランザクションが開くたびにXIDが発行され、その値は1ずつ増えていく。
RDBMSは何らかの形で同時実行制御としてMVCCを採用しているが、Postgresはその中でも、「追記型」を採用している。これは有名な話。
追記型というのは、データを更新するときに、そのデータの値を書き換えるのではなく、そのデータには無効フラグ的なものを立て、新たなデータを追加する方式。削除の場合も同じく、データそのものを削除するかわりに無効フラグ的なものを立てる。トランザクションがコミットされたら、その時点でデータの更新、削除は確定される。
無効フラグが立ったデータはそのままだと場所だけとって無駄なので、定期的にお掃除する。これがVACUUM。
トランザクションの巻き戻し
複数のユーザが同時にデータベースを参照・操作する場合に、それぞれからみたデータベースの状態は、「現在のXID」によって決まる。クエリの実行自体は同時であっても、XIDによる順序付けによりデータの整合性を担保している。
ということは、「現在のXID」を巻き戻すことができれば、「過去に戻る」ことができるはず。
実験
あるテーブルで特定のレコードを削除したかったのに、うっかりWHERE句を付け忘れて、テーブルのレコードを全部消してしもた！しかもコミットまでしてしもた！というあるあるシチュエーション。ねーよという声は受け付けておりません。これをxlogを巻き戻して復旧してみる。
まず、実験用のデータを作る。
CREATE TABLE person ( person_id int, person_name text ); INSERT INTO person (person_id, person_name) VALUES (0, 'yamada'),(1, 'suzuki'),(2,'tanaka');
これで、当然、行数は3行となっている。
SELECT COUNT(*) FROM person; --> 3
ここで、suzukiだけを削除したかったが、WHERE句を書き忘れてしまった！というパターンを考える。
DELETE FROM person; -- WHERE句を書き忘れ SELECT COUNT(*) FROM person; --> 0
トランザクションをコミットする前だったら、普通にロールバックすればいい。でも、コミットしてしまったあとだったら絶望。絶望ビリー。
そこで、トランザクションを巻き戻すことでデータの復旧を試みる。データベースを停止して、 pg_controldataで、現在のXIDを確認する。
$ pg_controldata -D /usr/local/var/postgres/ pg_control version number: 960 Catalog version number: 201608131 Database system identifier: 6414423454956411087 Database cluster state: shut down pg_control last modified: Fri Dec 8 23:48:41 2017 Latest checkpoint location: 0/1C6FF20 Prior checkpoint location: 0/1C6FEB0 Latest checkpoint's REDO location: 0/1C6FF20 Latest checkpoint's REDO WAL file: 000000010000000000000001 Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:1073 Latest checkpoint's NextOID: 17144 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 858 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint: Fri Dec 8 23:48:41 2017 （略）
Latest checkpoint's NextXID: 0:1073
ここが現在のXIDを示している。これを、ひとつ前のXIDが適用された時点まで巻き戻してみる。 pg_resetxlogという邪悪なユーティリティを使う。
$ pg_resetxlog -x 1072 /usr/local/var/postgres/ Transaction log reset
これで、トランザクションが巻き戻ったはずなので、再確認する。
$ pg_controldata -D /usr/local/var/postgres/ （略） Latest checkpoint's NextXID: 0:1072 （略）
巻き戻っているっぽい。サーバーを起動する。
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/log/postgres.log start server starting
そして、例のテーブルをみてみる。
SELECT COUNT(*) FROM person; --> 3
見事、3レコードが復活している。おかえりなさいませ。
VACUUMしてしまった場合
DELETE後にVACUUMしてしまった場合、同じ操作をしてxlogを巻き戻しても、データは戻ってこない。
SELECT COUNT(*) FROM person; --> 0
これは、VACUUMによって不要になったXIDを凍結するため、今回巻き戻し先となった1072というXIDが何も操作のないトランザクションと成り代わったためだと思われる。
山田、鈴木、田中はもう帰ってこない。帰ってこないんだ。
所感
どんな副作用が起こるかわからないので、使わないに越したことはない。リスクと仕組みを理解した上で、用法と用量を守ってどうぞ。特に本番環境では言語道断。複数のトランザクションが並行で走っているはずなので、どれがどこまで巻き戻るかわからない。
例えば、すごく限定された開発環境で、やべっ苦労して作ったダミーデータ飛ばしちゃったょ。。。ﾘｽｶしょ。。。という気分のときとかは使ってもいい。と思う。
蛇足
Postgresをpg_ctlで停止しようとしたらエラーになった。
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/log/postgres.log stop waiting for server to shut down............................................................... failed pg_ctl: server does not shut down
Macでlaunchdの管理下になっているとこうなるらしい。なので、launchctlで止めてやればいい。
$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
pg_ctlで起動したのにいつのまにかlaunchd配下になっている理由は謎に包まれているのでだれか教えてください。