この投稿はPostgreSQL Advent Calendar 2013の23日目の記事です。

皆さんはDELETEしたレコードを復活させたことはありますか?
私はないです。

「WALに残ってればできる(んじゃないかなー多分。)」ということは知っていましたが、実際にやったことはなかっったのでやってみました。 
といっても「DELETEしたデータを復活」するわけではなく、「データを巻き戻す、WALからリカバリする」って感じですね。
PITRについてはこちらが素晴らしい記事なので、そちらをご確認ください。

  • まずテーブルに適当にレコード突っ込んでみる
  • [postgres@localhost ~]$ psql test
    psql (9.3.1)
    Type "help" for help.
    
    test=#
    test=# \d+
                               List of relations
     Schema |    Name     |   Type   |  Owner   |    Size    | Description
    --------+-------------+----------+----------+------------+-------------
     public | test        | table    | postgres | 8192 bytes |
     public | test_id_seq | sequence | postgres | 8192 bytes |
    (2 rows)
    
    test=# \d+ test
                                                 Table "public.test"
     Column |  Type   |                     Modifiers                     | Storage  | Stats target | Description
    --------+---------+---------------------------------------------------+----------+--------------+-------------
     id     | integer | not null default nextval('test_id_seq'::regclass) | plain    |              |
     data   | text    |                                                   | extended |              |
    Has OIDs: no
    
    test=#
    test=# SELECT * FROM test;
     id | data
    ----+------
    (0 rows)

    test=# insert into test (data) values ('1'); INSERT 0 1 test=# insert into test (data) values ('2'); INSERT 0 1 test=# insert into test (data) values ('3'); INSERT 0 1
    • XIDを確認 
    [postgres@localhost ~]$ pg_controldata
    pg_control version number:            937
    …
    Latest checkpoint's NextXID:          0/1813
    …
    
    • DELETEする
      test=# DELETE FROM test ;
      DELETE 3
      test=#
      
      • XIDをもう一回確認してみる。…値が変わってる!!!
      [postgres@localhost ~]$ pg_controldata
      pg_control version number:            937
      …
      Latest checkpoint's NextXID:          0/1817
      …
      
      • postgres止める
      [postgres@localhost ~]$ /usr/local/pgsql/bin/pg_ctl stop
      waiting for server to shut down.... done
      server stopped
      [postgres@localhost ~]$
      • データ巻き戻す
      [postgres@localhost ~]$ pg_resetxlog -x 1816 $PGDATA
      Transaction log reset
      [postgres@localhost ~]$
      

      • 再起動
      [postgres@localhost ~]$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -l /var/log/postgres.log
      server starting
      [postgres@localhost ~]$
      • 確認してみる
      [postgres@localhost ~]$ psql test
      psql (9.3.1)
      Type "help" for help.
      
      test=# SELECT * FROM test;
       id | data
      ----+------
        1 | 1
        2 | 2
        3 | 3
      (3 rows)
      
      (この後、トランザクションIDが回ってしまうと、また消されてしまうようなのですぐにdumpしておくらしい。)

      おー。すごい。なるほど。
      これはいい…じゃなく、これをやらないようにしないと運用としてはダメだと思うので、 あくまで知識として覚えておくといいのかもしれません。

      バックアップ周りは前述したPITRの方法がありますが、でっかいストレージ持ってて、バックアップも履歴管理したいのであれば、NTT DATA様謹製のpg_rmanがあります。

      もし、バックアップに対してシビアな管理をする案件があれば、使うべきなのかもしれません。

      明日はコミッター@fujii_masaoさんの登場です!乞うご期待!

      余談

      Advent Calenderボツネタ集をアップしました。 
      そのうち書きます。 

      Add Comments

      名前
       
        絵文字
       
       
      プロフィール

      john_doe_

      Twitter
      instagram(SnapWidget)
      タグクラウド
      • ライブドアブログ