丸太、丸木の意味がある。こんな意味があったとは知らなかった。
この時、丸太の家の事を「ログハウス」と呼ぶ理由がわかった。
(日本ログハウス協会) http://www.log-house.gr.jp/

ログには記録という意味も辞書には書いてあった。
「丸太」と「記録」とは何の関連性があるのか、わからない。
もしかしたら、データを記録する磁気テープは
丸太のように巻き付けているため「ログ」と
呼ぶようになった可能性はあると思うが、
詳しい事は、英語学者でないので、謎のままにしておく (^^)

梅沢＠私も事務職^^; です。
奮闘記、興味深く読ませていただきました。

>　私が書いています「システム奮闘記：その63」で
>PostgreSQLのWALログや、WALログを活用したデータの復旧、
>PITRについて取り上げてみましたので、お知らせします。
>　http://www.osssme.com/

本筋には関係ありませんが、上記のページ中の
「ログには記録という意味も辞書には書いてあった。
「丸太」と「記録」とは何の関連性があるのか、わからない。」
についてなら、たまたま知っています。

昔々の話ですが。
羅針盤の発明によって、船の進行方向が正しく把握できるように
なって大航海時代時代に突入した訳ですが、船の速度を測る方法
はありませんでした。で、当時の船長は船首から丸太を海に投げ
入れ、それが船尾に達するまでの時間を測って航海日誌に記録し、
現在位置を推定していたのだそうです。
ここから、log に「丸太」→「記録」の意味が生まれたそうです。

因みに、ちょっと調べたところ
    http://www5e.biglobe.ne.jp/~yotteman/logbook.htm
のII-11 を見ると今でも船の対水スピードをログと呼んでいるみ
たいですね。

でれでは。

ファイルを更新すると、内容はメモリ上に保管される。
そして、時間をおいて、まとめてハードディスクに書き込む。

ハードディスクへの書き込み速度が遅いため、ファイルの更新の度に
ハードディスクに書き込んでいては、OSの性能が落ちるからだ。

そのため、ファイルの作成、更新があっても、すぐにはハードディスクには
反映させずに、後からまとめて反映させる。
つまり、まとめて反映させる事で、ハードディスクへの接続回数を減らし
それによって、ファイルの操作の高速化を実現させているのだ。

データベース上で、INSERTやUPDATEでレコードの追加や更新が
行われる度に、ハードディスクに更新を反映させていると、
書き込みに時間がかかるため、データベースの性能が落ちる。

操作したいテーブルがあると、それをハードディスクから
メモリに読み込み、メモリ上でテーブルを操作できるようにする。

レコードの追加・更新がある度にメモリ上に読み込んだのテーブルに
内容を反映させる事で、データベースの処理の高速化を実現させている。

そしてある程度、更新された時、まとめてハードディスクに反映させている。

トランザクション処理中に、レコードの追加・更新が発生すると、
メモリ上に読み込んだテーブルに反映される。

ただ、PostgreSQLの場合、追記型データベースなので、更新の場合、
メモリ上のレコードの書き換えではなく、メモリ上のレコードに
更新レコードを追加していると思われる。

追記型については「システム奮闘記：その60」をご覧ください。
(PostgreSLQ入門。トランザクション ロック VACUUM)

トランザクションの正常終了(COMMIT)を行うと、
トランザクション中に行われた処理は確定となる。

だが、メモリ上に読み込んだデータベースだけ反映させるだけで
ハードディスクにあるテーブル(データの実体)を反映させないと
もし、COMMITした後で、停電等が起こり、システムがダウンすると
メモリ上のデータが全部吹き飛んで、確定したはずの内容が
全く反映されない問題が起こる。

COMMITを行う度に、すぐ後にハードディスクへ反映させると、
ハードディスクへのデータ書き込みで時間がかかるため、
データベースの性能が落ちる問題が発生する。

データベースは、高速で安全にデータの出し入れする箱なので
性能が落ちる仕掛けにするわけにはいかないようだ。

データベースのログ(更新・更新等の手続き情報)を、
COMMITがある度に書き込み事で、例え、COMMITの後に
停電が起こった場合、テーブルデータの内容が反映されていなくても
更新処理の手続き情報(ログ)を記録できているため、COMMIT後の
データベースへの更新等の反映情報を守る事ができる。

ログをハードディスクへ書き込むのだが、ログのデータが大きいと
高速化の意味が損なわれるため、できるだけ小さい形にして
ハードディスクへの書き込み時間を減らし、高速化を保つようにしている。

データベースは、ハードディスクへの接続を向上させるため
テーブルデータは同じトラックや近隣のトラックに固めている。

だが、テーブルデータと、ログは特にそういう事はしていないようで
全く違うトラック上に保管している事もある。

ハードディスクに記録されたデータを読み込むには、
データが保管されているトラックの位置までアームを移動させる必要がある。
この移動時間(シーク時間)が高速化の妨げになっているというのだ。

これによって、2つのデータの書き出し処理が起こっても、
別々のハードディスクに書き込まれるため、シーク時間の発生がなくなる。
これによってデータベースの性能向上がはかれるという。

もし、ハードディスクが破損したら、2種類のデータとも
オシャカになってしまう。
そうなれば、データの保全うんぬんの問題ではなくなる。

例え、テーブルデータが保管されたハードディスクが破損して
テーブルデータがオシャカになっても、ログのデータは無事なので
更新処理の手続きの情報は残る。

そして、データベースの障害復旧の話で後述している事だが、
もし、ログが生きていれば、データベースのデータの中身を
バックアップ時点から破損寸前まで復旧が可能になる。

上の図で線香の絵を載せてみた。
一応、私の家は浄土真宗なので、仏教式の葬式になる。
なので、お経も浄土真宗の「南無阿弥陀仏」を添えた。

浄土真宗は、坊さんが結婚しようが、肉を食べようが良い上
檀家や信者も「南無阿弥陀仏」と唱えれば、
救われるという非常に楽な宗教だ。

仏教は、元々、輪廻転生という思想を根幹としているため
殺生は認められていない。
だが、浄土真宗では、肉食が良いので、
必要な殺生は認められているのだ。

なので、システムのプロセスを止めるのに、
何のためらいもなく、killコマンドを使ったりして
プロセスを殺す事ができるのだ (^^)

仏教は肉食を禁じているが、創始者であるお釈迦さんは
肉を食べていた話がある。
お釈迦さんが亡くなった原因は食中毒と言われているが、
それは托鉢で頂いた肉で食あたりしたと言われている。
お釈迦さんが亡くなった後、輪廻転生などの思想から
肉食が禁止になった話がある。真相は歴史の闇の中だ (^^)

何らかの原因で、データベースシステムが動作不能になり
処理途中で異常終了してしまう状況を指す。
原因として、データベースそのものやOSのバグ、障害、
コンピューター自身の電源障害による停止

英和辞典を見ると「事実」とか「例」、「実例」という意味だ。
でも、それだけでは用語と、その意味とが結び付かない。

そこで英々辞典を調べてみるが同じだった。

そこでgoogleを使って「instance database trouble」の用語で
調べてみる事にした。

いくつかOracleやSQLServerなどの文章を眺めて、
「instance」の意味の推測をしたのだが・・・

  全くわからへん・・・（ＴＴ）

だった (--;;

レコードの追加・更新があっても、すぐにはハードディスクには
反映されない。

メモリ上に読み込んだデータテーブルに反映される。

COMMITされても、すぐにはハードディスクに反映されない。
そのため、ハードディスクに反映される前に、障害が発生した場合
ハードディスクのデータは、COMMIT以前の内容のままだ。

ログは、データベースの変更・追加等の手続き処理を記録している。

ある時点でテーブルデータのバックアップをとっているとする。
そして障害発生時までに、テーブルデータの変更等があり、
例え、障害発生時までハードディスクのテーブルデータの内容が
反映されていなくても、その間のログは記録されている。

つまり上図の場合だと、バックアップ時から障害発生までの間の
処理手続きの記録がある。

バックアップ時のテーブルデータを戻す。
そして、ログに記録されている処理を実行させる。
ログは障害直前までの処理の記録があるため、それに従って処理を行えば
データテーブルは障害直前まで状態にする事ができる。

ファイルシステムで、ext3、ReiserFS、JFS、XFSには
ジャーナル機能と呼ばれる物がついている。

ジャーナル機能だが、活用されるのは以下の場合の時だ。
例えば、停電などで、いきなり電源が切れた後、
Linuxを起動させる際、ファイルシステムの点検が行われる。
その際、ジャーナル機能があれば、ファイル更新履歴によって
不要なファイル点検が行われずに済み、点検速度があがるのだ。

詳しくは「システム奮闘記：その３１」をご覧ください。

チェックポイント(CHECKPOINT)は、メモリ上にあるテーブルデータで
COMMIT済みの処理を検出し、COMMIT済みの内容を
ハードディスクに反映させる処理だ。

これにより、トランザクション処理でログに記録された処理が
全てハードディスク内のテーブルデータに反映されるのだ。

チェックポイント(CHECKPOINT)が行われている時点で、
処理中のトランザクションは記録される。

上の図のように、トランザクションBは障害発生以前に
処理が終了しているため、ロールフォワード(ROLL FORWARD)
すなわち、障害復旧の際は、処理が行われた状態になる。

だが、トランザクションCのように、障害発生した時も
処理中の場合は、ロールバック(ROLL BACK)が起こる。
障害からの復旧の状態は、処理が行われなかった状態になる。
それは障害発生前にトランザクションの処理が終わっていないため、
障害発生直前の状態は、処理が終了していないと見なされるからだ。

チェックポイント(CHECKPOINT)が行われた後に
処理が開始され、障害発生前に終了したトランザクションDの場合は
障害発生直前で処理が終わっている事から、障害復旧の際は
処理終了の状態になる。

[postgres@server postgres]$ cd /usr/local/pgsql/data/
[postgres@server data]$ ls
PG_VERSION  global   pg_hba.conf    pg_xlog          postmaster.opts
base        pg_clog  pg_ident.conf  postgresql.conf  postmaster.pid

前回のチェックポイントからの間で処理されたトランザクションの
COMMITの状態を記録する役目がある。

トランザクションID(XID)が「1」は、COMMIT済みなので、
「XID1:COMMIT」という風に記録される。

トランザクションID(XID)が「2」は、ABORTされたので
「XID2:ABORT」という風に記録される。

トランザクションID(XID)が「3」は、処理途中なので
「XID3:IN_PROGRESS」という風に記録される。
PROGRESSは「進行」の意味なので、「IN_PROGRESS」は進行中の意味になる。

データのバックアップを取る前に、チェックポイント(CHECKPOINT)を行い
現在のテーブルデータの状態を、完全にハードディスクへ
反映させた状態にしておく。

そしてテーブルデータのバックアップを取る。

その後、トランザクションを開始して、レコードを追加する。
そして、COMMITでトランザクションを終了させる。
この時点で、手続き処理の履歴(ログ)は反映される。

擬似的に障害を発生させる事で、意図的にインスタンス障害発生の
状況をつくり出す。

バックアップで取っていたテーブルデータを戻す。

そしてREDO作業。
REDO作業とは「Re DO」(再実行)の意味で、ログに記録されている処理を
チェックポイント(CHECKPOINT)の時点から、障害発生直前までを行う事だ。

REDO作業が終われば、障害発生前に行ったレコード追加の処理が
ハードディスクに反映され、データ復旧となる。

ハードディスクには、最後のCHECKPOINT時点でのデータが保存されており
それ以降の変更はWALログにだけ保存されています。
そこで、WALログに記録されたSQL文を順に再実行(Redo)することで、
クラッシュ直前の状態にまで復旧できます。

postgres=# REDO ;
ERROR:  syntax error at or near "REDO"
LINE 1: REDO ;
        ^
postgres=#

postgres=# \h
Available help:
  ABORT                     CREATE OPERATOR CLASS     END
  ALTER AGGREGATE           CREATE OPERATOR           EXECUTE
  ALTER CONVERSION          CREATE ROLE               EXPLAIN

 (途中省略)

  CREATE INDEX              DROP USER
  CREATE LANGUAGE           DROP VIEW
postgres=#

ヘルプを使ったら「REDO」が表示されない事から、
「REDO」コマンドは存在しない事がわかる。

テーブルのレコードが1レコードの状態にして、
テーブルデータのバックアップを取る。

そして、2レコード追加を行い、「COMMIT」を実行させ
トランザクションの内容(レコード追加の内容)を確定させる。
その後、PostgreSQLを停止させ、擬似的に障害発生を起こす形を取る。

バックアップデータを戻し、その後、PostgreSQLを起動させ、
実際にデータが復旧して、テーブルのデータが
3レコードあるかを確認する。

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)
 
testdb=> 

[postgres@server data]$ psql -U postgres testdb
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testdb=# CHECKPOINT ;
CHECKPOINT
testdb=#

[postgres@server data]$ cp -r base base2
[postgres@server data]$

testdb=> BEGIN ;
BEGIN
testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 41561 1
testdb=> INSERT INTO test VALUES ( 3 , 'third' ) ;
INSERT 41562 1
testdb=> COMMIT ;
COMMIT
testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)
 
testdb=>

COMMIT後、SELECT文でレコードが追加されているか確認。
問題なく追加されている事がわかる。

[postgres@server data]$ pg_ctl stop
LOG:  smart shutdown request
LOG:  shutting down
waiting for postmaster to shut down......LOG:  database system is shut down
done
postmaster successfully shut down
[postgres@server data]$ 

PostgreSQLが終了した時のテーブルデータは不要なので、
「base」の中身を消去する。

そして、「base2」のディレクトリーに入っているバックアップデータを
元のディレクトリー「base」に戻す。

[postgres@server data]$ pg_ctl start
LOG:  database system was shut down at 2007-09-19 18:24:56 JST
LOG:  checkpoint record is at 0/85415C
LOG:  redo record is at 0/85415C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 593; next oid: 57945
postmaster successfully started
[postgres@server data]$ LOG:  database system is ready
[postgres@server data]$

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)
 
testdb=> 

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)
 
testdb=> 

[postgres@server data]$ psql -U postgres testdb
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testdb=# CHECKPOINT ;
CHECKPOINT
testdb=#

[postgres@server data]$ cp -r base base2
[postgres@server data]$

testdb=> BEGIN ;
BEGIN
testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 41561 1
testdb=> INSERT INTO test VALUES ( 3 , 'third' ) ;
INSERT 41562 1
testdb=> COMMIT ;
COMMIT
testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)
 
testdb=>

COMMIT後、SELECT文でレコードが追加されているか確認。
問題なく追加されている事がわかる。

[postgres@server data]$ ps aux
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0  1368   80 ?        S    Sep10   0:04 init [5]
root         2  0.0  0.0     0    0 ?        SW   Sep10   0:01 [keventd]

(途中省略)

postgres 20311  0.0  0.5  8456 1432 pts/2    S    18:01   0:00 postmaster
postgres 20312  0.0  0.4  8016 1248 pts/2    S    18:01   0:00 postgres: stats buffer process
postgres 20313  0.0  0.5  7056 1280 pts/2    S    18:01   0:00 postgres: stats collector process
postgres 20321  0.0  0.2  2676  728 pts/2    R    18:03   0:00 ps aux
postgres 20322  0.0  0.1  4328  476 pts/2    S    18:03   0:00 more
[postgres@server data]$ kill -9 20311

[postgres@server data]$ ls
PG_VERSION  base2   pg_clog      pg_ident.conf  postgresql.conf  postmaster.pid
base        global  pg_hba.conf  pg_xlog        postmaster.opts
[postgres@server data]$ rm postmaster.pid

[postgres@server data]$ rm -rf base
[postgres@server data]$ cp -r base2 base

PostgreSQLが終了した時のテーブルデータは不要なので、
「base」の中身を消去する。

そして、「base2」のディレクトリーに入っているバックアップデータを
元のディレクトリー「base」に戻す。

[postgres@server data]$ postmaster &
[1] 20327
[postgres@server data]$ LOG:  database system was interrupted at 2007-09-19 18:01:59 JST
LOG:  checkpoint record is at 0/849CA4
LOG:  redo record is at 0/849CA4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 572; next oid: 41561
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/849CE4
LOG:  ReadRecord: record with zero length at 0/84DE60
LOG:  redo done at 0/84DE3C
LOG:  database system is ready
 
[postgres@server data]$ 

ピンクの部分は異常終了をした時点の表示のようだ。
青い部分がチェックポイント(CHECKPOINT)の場所の確認のようだ。
赤い部分がREDO(再実行)をしているようだ。

この分だと、データの復旧ができている感じがする。

[postgres@server data]$ psql -U suga testdb
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)
 
testdb=>

障害発生後、PostgreSQLを起動させるだけで、
REDOが自動的に行われるため、特にバックアップデータを
戻したりする必要がないのではないかと思った。

ただ、障害発生後に、PostgreSQLを起動させれば良いのは、
あくまでハードディスクが正常な場合だけであって、
ハードディスクの障害などでデータがオシャカになった場合は、
バックアップデータが必要になる上、ログデータが無事である事が
大事になってくる。

PostgreSQL8.0までは、障害直前の状態まで復旧できても
任意の時点での復旧はできなかった。

データベースの運用上、これは大きな意味を持つ。

1つのレコードを持ったテーブルに、2つのレコードを追加する。
1つ1つレコードを追加するごとに時刻を記録する。
1つ目のレコードを追加した直後を「A時点」とし
2つ目のレコードを追加した直後を「B時点」とする。
この時刻はdateコマンドの結果を紙に書き留めておく。

この時、実験としてはAの時点の時刻が重要になる。

そして擬似的に障害を発生させる。
この時は、PostgreSQLのプロセスを殺すのだ。「南無阿弥陀仏 チーン」

データの復旧を行うのだが、全部データを復旧させたのでは
任意の時点まで復旧できる事を示す事ができない。

そこで「概要・その1」の図にある、A時点までの復旧を行う。
そうすれば、テーブルのデータは、レコードが1つ追加された状態に
復旧ができるわけだ。

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)

testdb=>

[postgres@server data]$ cp -r base base2
[postgres@server data]$

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 0 1
testdb=> \q
[postgres@server data]$ date
2007年  9月 21日 金曜日 13:34:43 JST
[postgres@server data]$

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> INSERT INTO test VALUES ( 3 , 'third' ) ;
INSERT 0 1
testdb=> \q
[postgres@server data]$ date
2007年  9月 21日 金曜日 13:35:10 JST
[postgres@server data]$

testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)

testdb=> 

[postgres@server data]$ ps aux
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.2  2092  528 ?        S    Sep20   0:00 init [5]
root         2  0.0  0.0     0    0 ?        SN   Sep20   0:00 [ksoftirqd/0]

(途中省略)

postgres 22402  0.2  1.3 38580 3452 pts/1    S    13:33   0:00 /usr/local/pgsql/bin/postgres
postgres 22404  0.0  0.4 38580 1236 ?        Ss   13:33   0:00 postgres: writer process
postgres 22405  0.0  0.3 10028  904 ?        Ss   13:33   0:00 postgres: stats collector process
postgres 22413  0.0  0.3  3920  792 pts/1    R+   13:35   0:00 ps aux
[postgres@server data]$ kill -9 22402

[postgres@server data]$ rm -rf base
[postgres@server data]$ cp -r base2 base

PostgreSQLが終了した時のテーブルデータは不要なので、
「base」の中身を消去する。

そして、「base2」のディレクトリーに入っているバックアップデータを
元のディレクトリー「base」に戻す。

recovery_target_time = '2007-09-21 13:34:43 JST'

restore_command = 'cp /usr/local/pgsql/data/pg_xlog/%f %p'

これで日本時間の2007年9月21日13時34分43秒の時点に復旧する設定だ。
1つ目のレコードを追加した後の時刻だ。
時刻以外にも、XID(トランザクションID)の何番に戻すという指定もできる。

2行目は、WALログのバックアップのディレクトリーの指定なのだが、
この時は・・・

アーカイブログって何やねん！

だったので、忍法「適当に設定してしまえの術」を使って、
WALログが保管される pg_xlog ディレクトリーを指定した。
WALログを使って復旧させるのだから、pg_xlogを指定すれば良いだろうと
単純に考えただけだった。
この設定については、後述しています。

[postgres@server data]$ pg_ctl start
server starting
[postgres@server data]$ LOG:  database system was interrupted at 2007-09-21 13:33:52 JST
LOG:  starting archive recovery
LOG:  restore_command = "cp /usr/local/pgsql/data/pg_xlog/%f %p"
LOG:  recovery_target_time = 2007-09-20 13:34:43+09
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000001.history': そのようなファイルやディレクトリはありません
LOG:  restored log file "000000010000000000000000" from archive
LOG:  checkpoint record is at 0/447EB0
LOG:  redo record is at 0/447EB0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/627; next OID: 16400
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  automatic recovery in progress
LOG:  redo starts at 0/447EF8
LOG:  recovery stopping before commit of transaction 628, time 2007-09-21 13:34:39 JST
LOG:  redo done at 0/44814C
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000002.history': そのようなファイルやディレクトリはありません
LOG:  selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000001.history': そのようなファイルやディレクトリはありません
LOG:  archive recovery complete
LOG:  database system is ready

[postgres@server data]$

赤い部分が復旧させたい時点の時刻だ。

実は私の入力間違いで本来なら9月21日の所を20日と打ってしまった。
だが、この間違いは、この原稿の編集中に気づいたのだった。
実験最中、そんな事に気づいていない私は、そのまま実験を続けた。

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)

testdb=>

上の単純なコピーだと、任意の時刻に復旧できないと思った。
何かコピーの前後にコマンドか設定ファイルの記述が必要かと思った。

データテーブルが入った「base」ディレクトリーのコピーを
行う前後でSQLコマンドを使う。

コピーの前は「SELECT pg_start_backup('xxx')」を行う。
この時「xxx」は任意の文字列が使える。

コピーの後は「SELECT pg_stop_backup()」

これらのSQL文は管理者権限で行う必要がある。

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)
 
testdb=> 

testdb=# SELECT pg_start_backup('test');
ERROR:  WAL archiving is not active
HINT:  archive_command must be defined before online backups can be made safely.
STATEMENT:  SELECT pg_start_backup('test');
ERROR:  WAL archiving is not active
HINT:  archive_command must be defined before online backups can be made safely.
testdb=#

(書き換え前)
#archive_command = ''           # command to use to archive a logfile segment

↓

(書き換え後)
archive_command = 'cp "%p" "/usr/local/pgsql/data/backup/%f"'

忍法「適当に設定してしまえの術」を使って、上の設定を行った。
この設定について、詳しい内容については後述しています。

testdb=# SELECT pg_start_backup('test');
 pg_start_backup
-----------------
 0/442E94
(1 row)

testdb=#

[postgres@server data]$ cp -r base base2
[postgres@server data]$

testdb=# SELECT pg_stop_backup();
 pg_stop_backup
----------------
 0/2000084
(1 row)

testdb=# LOG:  archived transaction log file "000000010000000000000000.00442E94.backup"
LOG:  archived transaction log file "000000010000000000000000"
LOG:  archived transaction log file "000000010000000000000001.00000020.backup"
LOG:  archived transaction log file "000000010000000000000001"
LOG:  archived transaction log file "000000010000000000000002.00000020.backup"
LOG:  archived transaction log file "000000010000000000000002"

testdb=#

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)

testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 0 1
testdb=> \q
[postgres@server data]$ date
2007年  9月 21日 金曜日 16:24:42 JST
[postgres@server data]$

青い部分は、レコード追加のSQL文。
赤い部分は、レコードを追加した後の時刻を見てみる。

実験では、この時刻の状態に復旧させたいため、この時刻を紙に記録しておく。
2007年9月21日16時24分42秒を指している。

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> INSERT INTO test VALUES ( 3 , 'third' ) ;
INSERT 0 1
testdb=> \q
[postgres@server data]$ date
2007年  9月 21日 金曜日 16:25:08 JST
[postgres@server data]$

testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)

testdb=> 

[postgres@server data]$ ps aux
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.2  2092  528 ?        S    Sep20   0:00 init [5]
root         2  0.0  0.0     0    0 ?        SN   Sep20   0:00 [ksoftirqd/0]

(途中省略)

postgres 22622  0.2  1.3 38680 3448 pts/1    S    16:23   0:00 /usr/local/pgsql/bin/postgres
postgres 22624  0.0  0.4 38680 1180 ?        Ss   16:23   0:00 postgres: writer process
postgres 22625  0.0  0.2 10128  752 ?        Ss   16:23   0:00 postgres: archiver process
postgres 22626  0.0  0.3 10128  900 ?        Ss   16:23   0:00 postgres: stats collector process
postgres 22636  0.0  0.3  3248  792 pts/1    R+   16:25   0:00 ps aux
[postgres@server data]$ kill -9 22622
[postgres@server data]$

[postgres@server data]$ rm -rf base
[postgres@server data]$ cp -r base2 base

PostgreSQLが終了した時のテーブルデータは不要なので、
「base」の中身を消去する。

そして、「base2」のディレクトリーに入っているバックアップデータを
元のディレクトリー「base」に戻す。

restore_command = 'cp /usr/local/pgsql/data/backup/%f %p'
recovery_target_time = '2007-09-21 16:24:42 JST'

この時、WALログを読み込むのだから、WALログが格納されている
ディレクトリーを指定すれば良いと思って、上の設定を行った。

この後の実験結果は問題なかったのだが、設定方法としては
間違いだったのを後で知る事になる。
それについては、後述しています。

[postgres@server data]$ pg_ctl start
server starting
[postgres@server data]$ LOG:  database system was interrupted at 2007-09-21 16:23:56 JST
LOG:  starting archive recovery
LOG:  restore_command = "cp /usr/local/pgsql/data/backup/%f %p"
LOG:  recovery_target_time = 2007-09-21 16:24:42+09
cp: cannot stat `/usr/local/pgsql/data/backup/00000001.history': そのようなファイルやディレクトリはありません
cp: cannot stat `/usr/local/pgsql/data/backup/000000010000000000000003': そのようなファイルやディレクトリはありません
LOG:  checkpoint record is at 0/3000068
LOG:  redo record is at 0/3000068; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/617; next OID: 16393
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  automatic recovery in progress
LOG:  redo starts at 0/30000B0
LOG:  recovery stopping before commit of transaction 621, time 2007-09-21 16:25:05 JST
LOG:  redo done at 0/3000298
cp: cannot stat `/usr/local/pgsql/data/backup/00000002.history': そのようなファイルやディレクトリはありません
LOG:  selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql/data/backup/00000001.history': そのようなファイルやディレクトリはありません
LOG:  archive recovery complete
LOG:  database system is ready
LOG:  archived transaction log file "00000002.history"

[postgres@server data]$

赤い部分は、戻したい時点の時刻だ。問題なしだ。
なんだかエラーが出ているが、本によれば気にしなくても良いという。

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
  2 | second
(2 row)

testdb=>

[postgres@server data]$ cp -r base base2
[postgres@server data]$

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)
 
testdb=> 

[postgres@server data]$ psql -U postgres testdb
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testdb=# CHECKPOINT ;
CHECKPOINT
testdb=#

[postgres@server data]$ cp -r base base2
[postgres@server data]$

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 0 1
testdb=> \q
[postgres@server data]$ date
2007年  9月 30日 日曜日 17:28:01 JST
[postgres@server data]$

青い部分がレコード追加の部分。
赤い部分がレコード追加後の時刻だ。
この時刻の時点に復旧させるために、時刻を紙に記録しておく。

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> INSERT INTO test VALUES ( 3 , 'third' ) ;
INSERT 0 1
testdb=> SELECT * FROM test ;
 id |  name  
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)

testdb=> \q
[postgres@server data]$

[postgres@server data]$ ps aux
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0  1372   80 ?        S    Sep22   0:04 init [5] 
root         2  0.0  0.0     0    0 ?        SW   Sep22   0:00 [keventd]

(途中、省略)

postgres  2810  0.0  0.4  5444 1224 pts/9    S    17:21   0:00 bash
postgres  2863  0.1  1.1 36196 2976 pts/9    S    17:26   0:00 /usr/local/pgsql/bin/postgres
postgres  2865  0.0  1.2 36220 3088 ?        S    17:27   0:00 postgres: writer process     
postgres  2866  0.0  0.5  7644 1512 ?        S    17:27   0:00 postgres: stats collector process   
postgres  2880  0.0  0.2  2672  724 pts/9    R    17:28   0:00 ps aux
[postgres@server data]$ kill -9 2863
[postgres@server data]$

[postgres@server data]$ mv -f base base-org
[postgres@server data]$ mv -f base2 base

1行目は、データベース障害直後の状態のデータ。
これは別の所に退避させておく。
今回は「base-org」のディレクトリ名に置く事にした。

2行目は、バックアップに取っていたデータの入った「base2」を
データベースのデータのディレクトリー「base」に戻す作業。

recovery_target_time = '2007-09-30 17:28:01 JST'

[postgres@server data]$ pg_ctl start
pg_ctl: another server may be running; trying to start server anyway
LOG:  database system was interrupted at 2007-09-30 17:27:41 JST
LOG:  starting archive recovery
LOG:  restore_command = "cp /usr/local/pgsql/data/pg_xlog/%f %p"
LOG:  recovery_target_time = 2007-09-30 17:28:01+09
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000001.history': そのようなファイルやディレクトリはありません
LOG:  restored log file "000000010000000000000000" from archive
LOG:  checkpoint record is at 0/6AA75C
LOG:  redo record is at 0/6AA75C; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 0/3381; next OID: 16985
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  automatic recovery in progress
server starting
[postgres@server data]$ LOG:  redo starts at 0/6AA7A4
LOG:  recovery stopping before commit of transaction 3384, time 2007-09-30 17:28:24 JST
LOG:  redo done at 0/6AA9CC
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000002.history': そのようなファイルやディレクトリはありません
LOG:  selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql/data/pg_xlog/00000001.history': そのようなファイルやディレクトリはありません
LOG:  archive recovery complete
LOG:  database system is ready

[postgres@server data]$

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> SELECT * FROM test ;
 id |  name  
----+--------
  1 | first
  2 | second
(2 rows)

testdb=>

データベースのデータのバックアップは手動で行う必要があるが、
WALログを自動的にバックアップのディレクトリーに移動してくれるのだ。

ある時点でテーブルデータと、コミットログのバックアップを取る。
その後、定期的にWALログのバックアップを自動的に取る仕組みだ。

そうする事で、障害が発生しても、ある時点のテーブルデータと
コミットログを戻し、その後、WALログを使って、障害直前や
その手前の任意の時刻の状態にデータを復旧させる事ができる。

PostgreSQLが稼働中にデータのバックアップを取るため、
どの時点でバックアップを開始して、どの時点で終了したのかを
記録しておく必要がある。その記録を行うための合図なのだ。

[postgres@server postgres]$ psql
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
postgres=# SELECT pg_start_backup('test') ;
 pg_start_backup
-----------------
 0/667614
(1 row)
 
postgres=#

バックアップの開始の合図で、印(赤字)を「test」にした。
あとになってからも、どのバックアップかがわかるようにするためだ。

postgres=# SELECT pg_stop_backup() ;
 pg_stop_backup
----------------
 0/667678
(1 row)
 
LOG:  archived transaction log file "000000010000000000000000.00667614.backup"
postgres=# LOG:  archived transaction log file "000000010000000000000000"
 
postgres=#

[postgres@server pg_xlog]$ cat 000000010000000000000000.00667614.backup
START WAL LOCATION: 0/667614 (file 000000010000000000000000)
STOP WAL LOCATION: 0/667678 (file 000000010000000000000000)
CHECKPOINT LOCATION: 0/667614
START TIME: 2007-10-10 09:39:00 JST
LABEL: test
STOP TIME: 2007-10-10 09:43:41 JST
[postgres@server pg_xlog]$

赤い部分が開始の合図の時刻で、青い部分が終了の合図の時刻だ。
この間にバックアップが取られた事がわかるようになっている。

あと、これはWALログのファイル「000000010000000000000000」の
「667614」という場所から「667678」の場所までの間に
バックアップを取られたという意味も持っている

初期設定ではWALログの固まりは、16Mのファイルに保存される。
16Mのファイルの固まりは3つだ。

# - Checkpoints -
 
#checkpoint_segments = 3                # in logfile segments, min 1, 16MB each

初期設定では「3」になっている。
ここの数字を変える事で、pg_xlogに保管されるWALログファイルの固まりの
数が決まる。

時間の経過と共に古いWALログが破棄されていくため
もし、障害が発生し、テーブルデータから障害発生直前まで
データを復旧させたくても、古いWALログが破棄されているため
復旧が行えなくなる!!

# - Archiving -
                                                                                                                        
#archive_command = ''           # command to use to archive a logfile segment
archive_command = '/bin/cp "%p" "/home/postgres/backup/%f"'

WALログのバックアップを保管するディレクトリーを
「/home/postgres/backup」に指定。

この設定の意味は、WALログのバックアップを採取する際、
cpコマンドを使って「/home/postgres/backup」にコピーする意味だ。

<html><head><title>何十万レコードを追加するプログラム</title></head>
<body>

<?php

$conn = pg_connect("dbname=testdb user=suga");

for ( $i = 1 ; $i < 400000 ; $i++)
    {
    $name = "No".$i ;
    $sql = "INSERT INTO test VALUES ( $i , '$name' )" ;
    pg_exec($sql);
    }
print $sql."<br>" ;

pg_close($conn);
?>

</body></html>

赤い部分で40万回の繰り返しを行う。
青い部分はレコード追加のSQL文だ。

SQL文を40万個、データベース上で使う事で、WALログが溜っていく様子を
見るという方法だ。

[postgres@server pg_xlog]$ ls -l
合計 49216
-rw-------    1 postgres postgres 16777216 10月 19 09:38 000000010000000000000000
-rw-------    1 postgres postgres 16777216 10月 19 09:40 000000010000000000000001
-rw-------    1 postgres postgres 16777216 10月 19 09:43 000000010000000000000002
drwx------    2 postgres postgres     4096 10月 16 09:10 archive_status
[postgres@server pg_xlog]$

まだWALログが足らないようだ。
そのため、最初からあった「000000010000000000000000」ファイルが
残っている。
このディレクトリーには3つのWALファイルしか保管されないので、
もし、新たなWALログのファイルが生成されると
「000000010000000000000000」ファイルは破棄されるはずだ。

[postgres@server pg_xlog]$ ls -l
合計 65620
-rw-------    1 postgres postgres 16777216 10月 19 09:50 000000010000000000000002
-rw-------    1 postgres postgres 16777216 10月 19 09:53 000000010000000000000003
-rw-------    1 postgres postgres 16777216 10月 19 09:38 000000010000000000000004
-rw-------    1 postgres postgres 16777216 10月 19 09:40 000000010000000000000005
drwx------    2 postgres postgres     4096 10月 16 09:10 archive_status
[postgres@server pg_xlog]$

WALログファイルが大量に生成されたため、古いファイルの
「000000010000000000000000」と「000000010000000000000001」が
破棄されている事がわかる。

WALログのファイルが4つになっている。
つまりpostgresql.confで設定した数に1つ追加した形になる。

1個余分にファイル(4番目のファイル)があるのは、
余分にWALログが書き込まれていない空のファイル
(容量は16Mバイトあるが)を用意しておいて
3番目のファイルにWALログが書き込まなくなった場合に
即座に、1番目のファイルを指定させたディレクトリーに移動させ、
4番目のファイルにWALログが書き込めるように準備していると
思ったりする。

# - Archiving -
                                                                                                                        
#archive_command = ''           # command to use to archive a logfile segment
archive_command = '/bin/cp "%p" "/home/postgres/backup/%f"'

[postgres@server pg_xlog]$ ls -l
合計 65624
-rw-------    1 postgres postgres 16777216 10月 10 15:35 000000010000000000000001
-rw-------    1 postgres postgres 16777216 10月 10 15:37 000000010000000000000002
-rw-------    1 postgres postgres 16777216 10月 10 15:39 000000010000000000000003
-rw-------    1 postgres postgres 16777216 10月 10 15:31 000000010000000000000004
drwx------    2 postgres postgres     4096 10月 10 15:39 archive_status
[postgres@server pg_xlog]$

「000000010000000000000000」ファイルがない。
無事、バックアップ先に指定したディレクトリーに保管されているか
確かめる事にした。

[postgres@server backup]$ pwd
/home/postgres/backup
[postgres@server backup]$ ls
000000010000000000000000
[postgres@server backup]$

pg_xlogのディレクトリーに入らなくなったWALログのファイルが
無事、バックアップのディレクトリーに移動しているのがわかる。

ある時点でとったテーブルデータとコミットログ。
その後に記録したWALログのデータだ。
WALログには2つある。pg_xlog ディレクトリーに記録された物と
pg_xlogディレクトリーが満杯になって、指定されたディレクトリーに
保管されたWALログがある。

これらを使って、障害直前までの任意の時点の状態に
データを復旧させる事ができる。

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=> SELECT * FROM test ;
 id | name
----+-------
  1 | first
(1 row)

testdb=>

「test」という名前のテーブルに1レコード入れている状態だ。
これがバックアップを取る前の状態としておく。

# - Archiving -
                                                                                                                        
#archive_command = ''           # command to use to archive a logfile segment
archive_command = '/bin/cp "%p" "/home/postgres/backup/%f"'

[postgres@server data]$ psql testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=# SELECT pg_start_backup('test');
 pg_start_backup
-----------------
 0/443B58
(1 row)

testdb=#

[postgres@server data]$ cp -r base base2
[postgres@server data]$ cp -r pg_clog pg_clog2
[postgres@server data]$

とりあえず、この時点でのテーブルデータのディレクトリー「base」と
コミットログのディレクトリー「pg_clog」のバックアップを取る。

それぞれ「base2」、「pg_clog2」にしておいた。

postgres=# SELECT pg_stop_backup() ;
 pg_stop_backup
----------------
 0/443BBC
(1 row)
 
LOG:  archived transaction log file "000000010000000000000000.00443B58.backup"
postgres=# LOG:  archived transaction log file "000000010000000000000000"
 
postgres=#

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit 

testdb=> INSERT INTO test VALUES ( 2 , 'second' ) ;
INSERT 0 1
testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
(2 rows)
 
testdb=> \q
[postgres@server data]$ date
2007年 10月 10日 水曜日 15:25:21 JST
[postgres@server data]$

2レコード目を追加した直後を、復元したい時点にするため
時刻を紙に記録しておく。10月10日15時25分21秒だ。
10月10日とは、昔の体育の日だなぁ。

<html><head><title>大量にレコードを追加させる</title></head>
<body>

<?php

$conn = pg_connect("dbname=testdb user=suga");

for ( $i = 3 ; $i < 300000 ; $i++)
    {
    $name = "No".$i ;
    $sql = "INSERT INTO test VALUES ( $i , '$name' )" ;
    pg_exec($sql);
    }

pg_close($conn);

print $name."<br>\n" ;
?>

</body></html>

20万レコード(厳密には19万9997レコード)を追加するプログラムだ。
これだけレコードを追加すれば、大量のWALログが発生して、
pg_xlogは満杯になり、古いWALログのファイルはバックアップに
移動する事が期待できる。

[postgres@server pg_xlog]$ ls -l
合計 65624
-rw-------    1 postgres postgres      234 10月 10 15:31 000000010000000000000000.00443B58.backup
-rw-------    1 postgres postgres 16777216 10月 10 15:35 000000010000000000000001
-rw-------    1 postgres postgres 16777216 10月 10 15:37 000000010000000000000002
-rw-------    1 postgres postgres 16777216 10月 10 15:39 000000010000000000000003
-rw-------    1 postgres postgres 16777216 10月 10 15:31 000000010000000000000004
drwx------    2 postgres postgres     4096 10月 10 15:39 archive_status
[postgres@server pg_xlog]$

これを見ると、一番古いWALログのファイル「000000010000000000000000」が
バックアップのディレクトリーに移動しているのがわかる。

[postgres@server pg_xlog]$ ps aux
USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0  1372   80 ?        S    Oct01   0:04 init [5]
root         2  0.0  0.0     0    0 ?        SW   Oct01   0:00 [keventd]

(途中省略)

postgres 29533  0.0  0.5 36196 2660 pts/2    S    15:29   0:00 /usr/local/pgsql/bin/postgres
postgres 29535  0.0  3.0 36220 15836 ?       S    15:29   0:00 postgres: writer process
postgres 29536  0.0  0.2  7644 1208 ?        S    15:29   0:00 postgres: archiver process
postgres 29537  0.0  0.2  7644 1196 ?        R    15:29   0:00 postgres: stats collector process
postgres 29571  0.0  0.1  2680  732 pts/2    R    15:46   0:00 ps aux
[postgres@server pg_xlog]$
[postgres@server pg_xlog]$ kill -9 29533
[postgres@server pg_xlog]$

[postgres@server data]$ rm  postmaster.pid
[postgres@server data]$

[postgres@server data]$ rm -rf base
[postgres@server data]$ mv -f base2 base
[postgres@server data]$ rm -rf pg_xlog
[postgres@server data]$ mv -f pg_clog2 pg_clog

まずは、障害直前のテーブルデータが格納された「base」を消去して
バックアップの「base2」を「base」に移動させる。

そして、障害直前のコミットログのデータが格納された「pg_clog」を消去して
バックアップの「pg_clog2」を「pg_clog」に移動させる。

restore_command = 'cp /home/postgres/backup/%f %p'
recovery_target_time = '2007-10-10 15:25:21 JST'

pg_xlogが満杯になって、指定されたディレクトリに移動した
WALログのファイルを復旧に使うため、その移動先のディレクトリから
WALログのファイルを取り込む設定を行う。

そして、復旧させたい時刻も記述しておく。

[postgres@server pg_xlog]$ pg_ctl start
server starting
[postgres@server pg_xlog]$ LOG:  database system was interrupted at 2007-10-10 15:29:41 JST
LOG:  starting archive recovery
LOG:  restore_command = "cp /home/postgres/backup/%f %p"
LOG:  recovery_target_time = 2007-10-10 15:32:08+09
cp: cannot stat `/home/postgres/backup/00000001.history': そのようなファイルやディレクトリはありません
LOG:  restored log file "000000010000000000000000.00443B58.backup" from archive
LOG:  restored log file "000000010000000000000000" from archive
LOG:  checkpoint record is at 0/443B58
LOG:  redo record is at 0/443B58; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 0/630; next OID: 16393
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  automatic recovery in progress
LOG:  redo starts at 0/443BA0
LOG:  restored log file "000000010000000000000001" from archive
LOG:  recovery stopping before commit of transaction 635, time 2007-10-10 15:33:32 JST
LOG:  redo done at 0/1000284
cp: cannot stat `/home/postgres/backup/00000002.history': そのようなファイルやディレクトリはありません
LOG:  selected new timeline ID: 2
cp: cannot stat `/home/postgres/backup/00000001.history': そのようなファイルやディレクトリはありません
LOG:  archive recovery complete
LOG:  database system is ready
LOG:  archived transaction log file "00000002.history"
 
[postgres@server pg_xlog]$

[postgres@server data]$ psql -U suga testdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testdb=> SELECT * FROM test ;
 id |  name
----+--------
  1 | first
  2 | second
(2 rows)
 
testdb=>

誤ってバッチ処理を走らせてしまった場合がある。
バッチ処理を走らせたら、どんどんデータベースのデータが書き変わる。

例えば、業務関連で売上の締めを行った場合が上げられる。
企業によっては請求書発行や経理処理だけでなく、
月別の売上実績データ、個人データといった販売分析の処理も走るだろう。

もし、誤って、これらの処理が走った場合、
1つ1つの操作を見て、データを復元させるのは非常に困難だ。

テーブルデータとコミットログのバックアップデータを戻し、
操作誤りが発生する時刻の直前まで、WALログを使って
データを復元させるのだ。
こうすれば正確にデータの復元が可能になる。

# - Settings -
 
#fsync = on                             # turns forced synchronization on or off

上は初期設定の状態で、この時は「on」になっている。
つまり「COMMIT」があれば、すぐにハードディスクのWALログファイルに
反映されるのだ。
だが、「off」にすれば、「COMMIT」があっても、すぐには反映されない。
その分、ハードディスクへの書き込み回数が減り、データベースの性能が
向上するというわけだ。

<html><head><title>単純な性能測定プログラム</title></head>
<body>

<?php

$conn = pg_connect("dbname=testdb user=suga");

// 処理開始時のUNIX時間を出力
$sunix = date("U");
print "start-time : $sunix<BR>" ;

for ( $i = 0 ; $i < 100000 ; $i++)
    {
    $name = "No".$i ;
    $sql = "INSERT INTO test VALUES ( $i , '$name' )" ;
    pg_exec($sql);
    }

// 処理終了時のUNIX時間を出力
$lunix = date("U");
print "stop-time : $lunix<BR>" ;


$sa = $lunix - $sunix ;

// 開始から終了までのかかった秒を算出
print "sa = $sa <BR>";

pg_close($conn);

?>

</body></html>

10万レコードを追加するのに、どれくらい時間がかかるのかを
測定するプログラムなのだ。

色々な要因があるのだが、あくまでも目安という事で、
単純なプログラムを書きました。

start-time : 1192760982
stop-time : 1192761126
sa = 144

# - Settings -
 
fsync = off                             # turns forced synchronization on or off

設定を「off」にしたので、「COMMIT」があっても、
すぐには、ハードディスクのWALログには反映されない。

start-time : 1192761297
stop-time : 1192761357
sa = 60