DELETEしてしまったレコードの復元

やってしまったけどいい経験になった．

WEB家計簿の保守作業してたら間違って詳細項目を40行くらい一気に消してしまった．バックアップとってないし絶望的だと思っていたが，PostgreSQLが追記型なのでVacuumをかけていなければ消してしまった行を復元することが可能．救われた．マジで．

以下手順．
1.念のため今のデータのバックアップをとっておく

2.PostgreSQL停止

3.pg_controldataで出力されるデータのうち以下をメモ
「Latest checkpoint's NextXID:          0/102657」
↑
これが次のトランザクションID

4.pg_resetxlog -x 102656 $PGDATA←データディレクトリを指定
=>NextXIDが102656にセットされる(つまり消す前にタイムスリップ！)
[注意]:PostgreSQLを停止せずにこのコマンドを実行するとデータが破壊
されるおそれがあるので注意．(実際は-fつけないと実行できないが)

5.PostgreSQL起動

該当テーブルをSELECTすると戻った．しかしあくまでタイムスリップなので，「消した」事実は
戻らないので注意．

6.復元されていることを確認したらすぐさまバックアップ

7.リストア

これで完全に復元完了．

ちなみに・・・
DELETEしたときはxmaxという(隠された)列にDELETEしたトランザクションのIDが付加される．
このトランザクションIDよりも小さなトランザクションからはDELETE前の行が見えるという仕組み．

xminという列もあり．それはそのタプルが作成されたときのトランザクションIDが付加される．

xminが自トランザクションIDよりも大きい場合に見えないというのは理解できるのだけれど，
xmaxが謎．１時間くらい考えたけど，謎．
例えば，トランザクションIDが10のAとトランザクションIDが20のBがいたとして，

xmin xmax   id
    1        0      1
    2        0       2
   10       0       3
   15       0       4
   20       0       5

というテーブルがあったとすると，id<=3の行は，A,Bどちらの行にも見えるが，
id=15,id=20は，Aにとって"未来"の行であるため見えない．

xmin xmax   id
    1        9       1
    2      15       2
   10     25       3
   15       0       4
   20       0       5

今度はこのようなテーブルがあったとすると，id=2,3の行がB以降のトランザクションによって削除されているわけだけれど，Aにとっては見えて良いはずなのに，それらがコミットされた途端Aからは見えなくなってしまう(もちろんBからも)．これが謎．

READ COMMITTED/SERIALIZABLEについて復習せねば．．．






http://miwa.offside.ne.jp/blog/2007/04/postgresql-delete.html

ネットワークからpostgresへの接続

二つのファイル編集します．

まず，postgresql.conf

listen_address ='localhost'
                     ↓
listen_address ='*'

これですべてのアドレスからの受付準備


次にpg_hba.conf
         host        all      all    0.0.0.0/0     trust
を最終行に追加，または
host        all      all    LAN内アドレス　　　255.255.255.0   trust
で，ＬＡＮ内からのみデータベースをいじれるようにできる．

この設定でもしクライアントからいじれなかったら，ファイアウォールとかでポート5432を解放して
あげる必要があるかも．

postgreSQLのバックアップ

1. pg_dump **** > /tmp/バックアップファイル名　でバックアップ作成
2. createdb  --encoding [ENCODE] ****で文字エンコードを指定してデータベースを作成
3. psql ****</tmp/バックアップファイル名　で復帰

その他のpostgresコマンド
dropdb [dbname]---データベースの消去