PostgreSQL 運用メモ

◆ VACUUM の実行

PostgreSQL ドキュメント定常的なデータベース保守作業の俺様要約

• PostgreSQL はレコードが更新/削除されても物理削除は行わない
• これは VACUUM で行うので定期的な実行が必要
• 更新頻度の高いテーブルほど頻繁に VACUUM のすること
• 普通の VACUUM と VACUUM FULL の 2 種類がある
• 普通の VACUUM は使われていない領域に再利用可能なマークを付ける
• 普通の VACUUM は不要領域がファイルの最後でない限りファイル縮小は行わない
• 普通の VACUUM は他のトランザクションにほとんど影響しない
• VACUUM FULL は不要領域の占有領域を削除しファイルを縮小する
• VACUUM FULL は他のトランザクションに与える影響も甚大
• 一般的には1日1回普通の VACUUM をしておけばよい
• 全レコードを削除する場合は TRANCATE を使えば VACUUM する必要がない
• トランザクション ID は 32 ビットで周回する
• トランザクション ID が周回が発生するとデータ損失が起きる
• 7.2 以降は 10 億トランザクション発行する前に VACUUM が必要
• 残りトランザクション数が1000万を下回ると警告が出る
• 残りトランザクション数が100万を下回ると発行が出来なくなる

俺様の理解

• Java の Gabage Collection で例えるなら普通の VACUUM = Mark & Swept、VACUUM FULL = Compact 、Windows で例えるなら　VACUUM FULL = デフラグ
• 定期処理は1日1回普通の VACUUM だけで十分 (VACUUM FULL はディスク容量を見ながらの不定期で良い?)

◆ バックアップとリストア

PostgreSQL ドキュメントバックアップとリストアの俺様要約

• pg_dump は運用中の実行が可能
• pg_dump はテーブル構成や全レコードを SQL で出力する
• 出力した SQL を psql に流し込むと再構築が可能
• ユーザ等、クラスタまとめてバックアップしたい場合は pg_dumpall を使用
• ファイルが大きくなる場合はパイプで圧縮すなり分割するなり
• 単純なファイルコピーによるバックアップは postgreSQL の完全停止が必要 (全切断だけではNG)
• ポイントインタイムリカバリ (PITR) という JFS やロールフォーワードのような障害復旧機構がある
• RITR は WAL ファイルにログ (ジャーナル) を記録している
• WAL ファイルを累積的に保管するだけで任意の時点でのスナップショットが再現できる
• WAL ファイルをバックアップ機で再生すればホットスタンバイを実現できる

俺様の理解

• 小規模システムなら pg_dump の定期バックアップで十分
• pg_dump の定期バックアップが現実的でない大規模システムは WAL ファイルで