2.4バックアップとPITR

この節の要点

PostgreSQLのバックアップ手法を体系的に学びます。SQLレベルのpg_dump・pg_dumpall・pg_restore、ファイルシステムレベルバックアップ、PITR（ポイントインタイムリカバリ）の概念、WAL（トランザクションログ）とWALアーカイブ（archive_command）、非排他的低レベルバックアップ、そしてデータ入出力のCOPY文（SQL）と\copy（psql）を押さえます。

バックアップは「取っておけばいい」ものではなく、目的に応じて手法を選ぶ必要があります。単一データベースを別環境へ移すだけなら論理バックアップで十分ですが、障害発生の直前まで復旧したいなら物理バックアップとWALを組み合わせたPITRが要ります。この節では代表的な手法とその使い分けを整理します。

2.4.1論理バックアップ：pg_dump系とCOPY

pg_dump＝単一データベースをSQL文または独自バイナリ形式で書き出す論理バックアップツール（-F cでカスタム形式、-F pでプレーンSQL）。pg_dumpall＝クラスタ全体（全データベース＋ロール・テーブルスペース等のグローバル情報）をダンプします。pg_restore＝pg_dumpのカスタム形式などの出力をリストアするツール（-dでリストア先DB指定、-jで並列リストア）。プレーンSQL形式はpsqlで直接流し込みます。
COPY＝SQL文としてサーバー側で実行し、テーブルとファイルの間で高速にデータを入出力するコマンド（COPY テーブル TO/FROM 'ファイル' ）。サーバープロセスがファイルを直接読み書きするため、対象ファイルはサーバー上のパスである必要があります。\copy＝psqlのメタコマンド版で、クライアント（psqlを実行している側）のファイルシステムに対して読み書きします。権限やファイル配置の制約がCOPYと異なる点が実務上の要点です。

2.4.2物理バックアップとPITR

ファイルシステムレベルバックアップ＝データディレクトリ配下のファイル一式をそのままコピーする物理バックアップ（tarやcp等）。サーバー停止中に取得するか、稼働中なら後述の低レベルバックアップの手順（バックアップ開始・終了の合図）に従う必要があります。
WAL（Write-Ahead Log／トランザクションログ）＝データファイルへの変更を実際に適用する前に記録される先行書き込みログ。クラッシュ後の整合性復旧に使われるほか、WALアーカイブ（archive_mode = onとarchive_commandの設定で、生成済みWALセグメントを別の保管場所へコピーし続ける仕組み）によって過去の任意の時点までの再生が可能になります。
PITR（Point-In-Time Recovery／ポイントインタイムリカバリ）＝ベースバックアップ（物理バックアップ）＋アーカイブ済みWALを組み合わせ、任意の過去時点（誤操作の直前など）までデータベースを復元する仕組み。誤って重要なテーブルをDROPしてしまった直後まで戻す、といった障害対応の要になります。
非排他的低レベルバックアップ＝稼働中のクラスタから物理バックアップを取る手順（排他的方式は廃止傾向で非排他的方式が現行）。SQL関数でバックアップの開始を合図し、ファイルのコピーが終わったら終了を合図する、という手順を踏むことで、コピー中に書き込まれた変更もWALと組み合わせて整合性を保てます。

試験ポイント

「pg_dumpは単一DB・pg_dumpallはクラスタ全体（ロール等のグローバル情報含む）」「COPYはサーバー側パス・\copyはクライアント側パス」「PITR＝ベースバックアップ＋アーカイブ済みWAL」「WALアーカイブの有効化にはarchive_modeとarchive_command」「稼働中の物理バックアップは非排他的低レベルバックアップの手順（開始→コピー→終了の合図）に従う」が最頻出です。pg_dumpとファイルシステムレベルバックアップ（論理 vs 物理）の使い分けの根拠も問われます。

実務でのバックアップ設計を、目的別に整理してみましょう。開発環境へ本番の1データベースだけ複製したいという要件には、pg_dump -F c -f backup.dump mydbでカスタム形式のダンプを取り、開発環境でpg_restore -d mydb_dev backup.dumpと流し込むのが最も簡単です。サーバー全体（複数DB＋ロール）を丸ごと移行したいなら、単一DB用のpg_dumpではロールやテーブルスペースの情報が漏れるため、pg_dumpallが必要になります。一方、「障害発生の数分前まで」データを取り戻したいという、より厳しい要件には論理バックアップでは対応できません。ここで登場するのがPITRです。まず定期的に物理的なベースバックアップを取得し（稼働中に取るなら非排他的低レベルバックアップの手順で、バックアップ開始を合図するSQL関数を呼び、ファイル一式をコピーし終えたら終了を合図します）、同時にpostgresql.confでarchive_mode = onとarchive_command（生成されたWALセグメントを安全な保管先へコピーするシェルコマンド等）を設定してWALアーカイブを有効にしておきます。障害発生後は、直近のベースバックアップをリストアした上で、アーカイブ済みWALを目的の時刻まで再生（リプレイ）することで、その瞬間の状態まで復元できます。最後に、特定テーブルのデータだけを別システムへ流し込みたいといった単純なデータ移送には、COPY orders TO '/tmp/orders.csv' WITH CSVのようにサーバー上のパスへ書き出すか、サーバー側にファイルを置く権限が無い場合はpsql内で\copy orders TO '/home/user/orders.csv' WITH CSVとクライアント側のパスを指定する、という使い分けが実務で頻出します。

手法	種別	対象/特徴
pg_dump	論理	単一データベースを書き出す
pg_dumpall	論理	クラスタ全体＋ロール等グローバル情報
ファイルシステムレベル	物理	データディレクトリをそのままコピー
PITR（ベース+WAL）	物理	任意の過去時点まで復元

注意

ひっかけ： 「pg_dumpallを使えばロールやテーブルスペースの情報も含めて単一データベースだけを効率よくダンプできる」という誤解に注意です。pg_dumpallはクラスタ全体を対象とするツールで、単一DBの効率的な部分ダンプにはむしろpg_dumpを使います。また「COPYと\copyはどちらもクライアント側のファイルシステムを操作する」も誤り＝COPYはサーバー側パス、\copyがクライアント側パスです。「WALアーカイブを設定しなくてもPITRは実行できる」も誤り＝PITRにはベースバックアップと連続したアーカイブ済みWALの両方が必須です。

論理（pg_dump 系）・物理（FSレベル）・PITR（ベース+WAL）のバックアップ方式を示す図。 — PITR＝ベースバックアップ + WAL アーカイブ

2.4.3この節のまとめ

論理バックアップ：pg_dump（単一DB）・pg_dumpall（クラスタ全体＋グローバル情報）・pg_restore（カスタム形式等のリストア）
物理バックアップ：ファイルシステムレベルバックアップ・PITR＝ベースバックアップ＋アーカイブ済みWAL（archive_commandで有効化）・非排他的低レベルバックアップ（開始→コピー→終了の合図）
COPYはサーバー側パス（SQL文）・\copyはクライアント側パス（psqlメタコマンド）

進捗の記録にはログインが必要です。

理解度チェック

（軽い確認用）

Q1. サーバー上の複数のデータベースに加え、全ロールとテーブルスペースの定義も含めてクラスタ全体を1つのバックアップとして取得したい。使うべきコマンドは？

Q2. 誤って重要なテーブルをDROPしてしまい、障害発生の数分前の状態まで正確に復元したい。この要件を満たす手法は？

Q3. サーバー上にファイルを書き出す権限が無いクライアント端末から、psql経由でテーブルの内容をクライアント自身のローカルファイルへエクスポートしたい。使うべき機能は？

理解度を確認第2章「運用管理」の問題を解く