holly's wiki

サービス無停止で可能

基本オプションはpsqlと同じなので、その他をかいておく。データ挿入は普通はCOPY文になる

  -f, --file             dumpをファイルに書き出す
  -a, --data-only        データのみ。CREATE TABLEなどは含まない
  -b, --blobs            large object. superuser権限でしないと意味がない
  -c, --crean            テーブルを念のため削除してからdumpを作成
  -C, --create           CREATE DATABASEからdumpに含める。-c, -C両方を指定することは不可
  -s, --schema-only      データを含まない。CREATE TABLEなどのみ
  -t, --table            指定したテーブルのみdump
      --inserts          データ登録をCOPY xxx FROM STDINではなくINSERTでdumpする（互換性はあるがrestore時遅くなる）
      --columns-inserts  columnまで指定したINSERTでdumpする
  -o, --oid              oidもバックアップを取る
  -F, --format           c|t|d|pから選択。c or t(tar)だとpg_restoreで復旧可能。binaryになるので、lessなどでは参照不可

insert指定が無ければデータ登録は

--
-- Data for Name: accounts; Type: TABLE DATA; Schema: public; Owner: kurt
--
COPY accounts (id, name, email, password, salt, valid, created, updated) FROM stdin;
1       kurt    kurt-cobain@nirvana.com cafdb19e1d6d2c3e05da348bfe85e9c5        WsTKFjS6        t       2011-04-27 00:45:01.147532      2011-04-27 00:45:01.147532
2       akiko   akiko-jazz@melcedes.com 04113dd41e2f28b51a6766a4d81f033b        _VgcxUwh        t       2011-04-27 00:45:01.234814      2011-04-27 00:45:01.234814
\.

COPY文となるが、--insertをつけると

--
-- Data for Name: accounts; Type: TABLE DATA; Schema: public; Owner: kurt
--
INSERT INTO accounts VALUES (1, 'kurt', 'kurt-cobain@nirvana.com', 'cafdb19e1d6d2c3e05da348bfe85e9c5', 'WsTKFjS6', true, '2011-04-27 00:45:01.147532', '2011-04-27 00:45:01.147532');
INSERT INTO accounts VALUES (2, 'akiko', 'akiko-jazz@melcedes.com', '04113dd41e2f28b51a6766a4d81f033b', '_VgcxUwh', true, '2011-04-27 00:45:01.234814', '2011-04-27 00:45:01.234814');

となる。当然遅くなる

あんまり使わない。pg_dumpで-Fでp以外で取得した場合に使う

# -d指定がない場合は標準出力にdumpファイルの内容がプレーンで出力
pg_restore -d <DB> <DUMPFILE>

一部のテーブルだけ復旧だけとかにも使える

pg_restore -d <DB> -t <TABLE> <DUMPFILE>

全てごっそり。バージョン違いのDBに全てを移設するときにもよい

su - postgres
pg_dumpall -U postgres -f dumpall.sql
pkill postmaster
rm -fr $PGDATA/*
initdb -D $PGDATA --encoding=UTF-8 --no-locale
pg_ctl start
psql -U postgres  -f dumpall.sql

何か発生した場合に直前の状態に戻したり、日時指定で戻したりできる優れもの http://www.postgresql.jp/document/9.0/html/continu...

別ディスクなどが望ましい。別ディスクがmountされた/dataに保存するとする

mkdir -p /data/pgsql
mkdir -p /data/pgsql/archlog
mkdir -p /data/pgsql/data
chmod 700 /data/pgsql/{archlog,data}
chown -R postgres.postgres /data/pgsql

%fはWALセグメントファイル名、%pはWALセグメントファイルの絶対パス

wal_level = archive
archive_mode = on
#archive_command = 'test ! -f /data/pgsql/archlog/%f && /bin/cp %p /data/pgsql/archlog/%f' 
archive_command = '/bin/cp -f %p /data/pgsql/archlog/%f'
# WAL はトランザクションが完了するなど、安全なタイミングで archive されるのでトラフィックによっては、長い間 archive されない場合もあるみたいなので、60秒おきに強制的に新しいWALファイルに切り替える
archive_timeout = 60

変更後はpostgresを再起動する

psql -U postgres -d template1 -c "SELECT pg_start_backup('/data/pgsql/data')"
pg_start_backup
-----------------
 0/5000020
(1 row)

# backup pg_xlogはとっていてもあまり意味ないので除外しておく
rsync -av --delete --exclude=pg_xlog --exclude=postmaster.pid $PGDATA/ /data/pgsql/data/

psql -U postgres -d template1 -c "SELECT pg_stop_backup()"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
   pg_stop_backup
  ----------------
   0/E0000A0
  (1 row)

成功すると/data/pgsql/archlogに.backupで終わるファイルができているので中を見る

# <WAL ファイル名>.<チェックポイント番号>.backup
000000010000000000000005.00000020.backup

START WAL LOCATION: 0/5000020 (file 000000010000000000000005)
STOP WAL LOCATION: 0/60000A0 (file 000000010000000000000006)
CHECKPOINT LOCATION: 0/5000020
START TIME: 2011-05-28 01:42:37 JST
LABEL: /backup/pgsql/data
STOP TIME: 2011-05-28 01:48:13 JST

のようになっている。STARTで始まるWALセグメントファイルよりも古いファイルはもう不要なため、削除してもかまわない。recovery.confでrecovery_target_timeを指定する場合はSTOP TIME以降の日時を指定すること

とりあえずデータを登録している

for i in $(seq 1 100); do
      echo "INSERT INTO login_logs(account_id, content) VALUES(1, 'test post $i');"
done | psql -U kurt kurt

削除（事故がおきたと仮定する）この時に発生したと思われる時間をおぼえておくこと

truncate login_logs;

復旧作業を行う

# postgresを停止する
pg_ctl stop -m fast

# 現在のWALログを退避
rsync -av $PGDATA/pg_xlog  /data/pgsql/

# $PGDATA（データベースクラスタ）をとりあえずリネームする
cd /usr/local/pgsql
mv $PGDATA $PGDATA.$(date +%Y%m%d)
 
# ベースバックアップを復元
rsync -av --delete --exclude=pg_xlog /data/pgsql/data /usr/local/pgsql/

# WALログを復元
rsync -av --delete /data/pgsql/pg_xlog/ $PGDATA/pg_xlog/

# 2011-05-28 15:30:28 の時点に戻してみる（事故る前の最新） recovery.confを作成する
# recovery_target_timeは指定が無ければ可能なかぎり最新の情報で戻そうとする

cat <<EOL >$PGDATA/recovery.conf
restore_command = '/bin/cp /data/pgsql/archlog/%f "%p"'
recovery_target_time = ' 2011-05-28 15:30:28 JST'
EOL

ここまで来たら起動する

pg_ctl start

問題なく復旧していることが確認できたら、再度ベースバックアップを取り直すこと

本当はあらかじめ作成しておくほうがよい。recovery_target_timeがない場合は可能なかぎり最新の状態で復旧する。他に

• recovery_target_xid TID指定
• recovery_target_timeline timelineIDで指定する。gitでいうcommitみたいな概念なんだそうな

などでも指定可能

pg_xlogをみると

000000010000000000000043.00000020.backup
00000001000000000000004D
00000001000000000000004E
00000002.history
000000020000000000000047
000000020000000000000048
       ^               ^
  timeline id   WALファイル番号

となっている。PITRを実行するたびにtimelineが増えていく

そのtimelineである。タイムライン情報: <タイムライン番号>.history

1       000000010000000000000047        no recovery target specified

timelineを利用すれば異なる以前の歴史にさかのぼってPITRを実行することが可能。recovery.confを実施するときは以下の感じ

restore_command = '/bin/cp /data/pgsql/archlog/%f "%p"'
recovery_target_timeline = '1'
recovery_target_time = '2011-12-05 00:00:00 JST'

次のトランザクションログファイルに移り、現在のファイルをアーカイブできるようにします。 （アーカイブを続けて使用することを前提とします。） 結果は、完了した現在のトランザクションログファイル内の終了トランザクションログの位置です。 前回のトランザクションログファイルの切り替えからトランザクションログに変化がなければ、pg_switch_xlogは何も行わず、前回のトランザクションログファイルの終了位置を返します。

PITRを使いやすくするためのツールといえばよい。たぶん。

http://code.google.com/p/pg-rman/wiki/readme_ja PGXSに対応しているので、それにならってインストール。PITRの設定は完了しているものとする。詳細に試したわけじゃないから細かい設定などはwebをみたほうがよい

cd /usr/local/src
wget http://pg-rman.googlecode.com/files/pg_rman-1.2.1.tar.gz
tar xvfz pg_rman-1.2.1.tar.gz
cd pg_rman-1.2.1
make USE_PGXS=1
make USE_PGXS=1 install

pg_rmanのバックアップの初期化を行う。とりあえずpg_rmanは/data/pgsql/pg_rmanにする。postgresの.bash_profileに以下を追加しておく

export BACKUP_PATH=/data/pgsql/pg_rman

初期化を実行する。もちろんpostgresユーザーで。postgresql.confの設定に基づいて動的に初期化が行われるようだ

pg_rman init -B $BACKUP_PATH
INFO: ARCLOG_PATH is set to '/data/pgsql/archlog'
INFO: SRVLOG_PATH is set to '/usr/local/pgsql/data/pg_log'

$BACKUP_PATH/pg_rman.ini に設定が記録される。追加で何個か設定しておく

cat <<EOL >> $BACKUP_PATH/pg_rman.ini
BACKUP_MODE='F'
KEEP_DATA_GENERATIONS=7
KEEP_DATA_DAYS=10
KEEP_ARCLOG_FILES=10
KEEP_ARCLOG_DAYS=10
EOL

とりあえずフルバックアップ。ログはsyslogにしているから対象外にするオプションを指定したほうがよさそうだ

# --no-password or -w でpasswordはきかれなくなる
# --backup-mode=archiveにするとWALログだけバックアップを取る
pg_rman backup --backup-mode=full --no-password
pg_rman validate

かならず停止させた状態で行う。PITRの基本と同じ。検証なので強制終了させる

pg_ctl -m immediate stop

recovery.conf作成。作成するだけで起動はしない

pg_rman restore

指定できるオプションは以下

• --recovery-target-timeline TIMELINE_ID どの時系列で理科張りするか指定。省略時は$PGDATA/global/pg_controlから取得
• --recovery-target-time TIMESTAMP 時刻指定。省略時は可能な限り最新の状態までリカバリを行う
• --recovery-target-xid XID トランザクションIDで指定。xlogdumpとかないとわからないかもしれない？
• --recovery-target-inclusive 指定したリカバリ対象の直後で停止するかしないか。default: true

起動

pg_ctl start

pg_rman show [DATE]

pg_rman delete DATE