データベースのバックアップとPITR｜pg_dump・WALアーカイブで任意時点に復元する方法

「毎晩pg_dumpを取っているから大丈夫」と思っていたところに、昼の15時にWHERE句を付け忘れたDELETEが本番で実行された。復元できるのは深夜2時のダンプだけで、13時間分の注文データが消える——バックアップはあるのに守れない、という事態は、バックアップの「種類」と「復元できる時点」を意識していないときに起こります。

この記事では、pg_dump/mysqldumpに代表される論理バックアップと、データファイルをそのまま複製する物理バックアップの違いを整理し、WAL（PostgreSQL）やbinlog（MySQL）を使って「任意の時点」に戻すポイントインタイムリカバリ（PITR）の仕組みと実際の復元手順、そして「復元テストをしていないバックアップは存在しないのと同じ」という原則をどう運用に落とすか、RDS/Cloud SQLの自動バックアップで何が設定されているべきかを解説します。ファイル単位のrsync/tarバックアップと3-2-1ルールはLinuxのバックアップと復元とバックアップ3-2-1ルールで扱っていますので、本記事はDB固有の話に絞ります。

論理バックアップと物理バックアップの違い

論理バックアップ：SQLとして書き出す

pg_dumpやmysqldumpは、DBに接続してテーブル定義とデータを読み出し、再実行すれば同じ状態を作れる形式（SQL文や独自形式）で書き出します。取得中もDBは通常どおり稼働し、特定のテーブルだけ・特定のスキーマだけといった部分的な取得や、別バージョン・別環境への移行にも使えます。欠点は、データ量に比例して取得も復元も遅いこと（数十GBを超えると復元に数時間かかることもある）と、取得した瞬間の状態にしか戻せないことです。

# PostgreSQL: カスタム形式（圧縮済み・並列復元可）で取得
pg_dump -Fc -h db.internal -U app -d appdb -f appdb_$(date +%F).dump
# ロール・テーブルスペースなどクラスタ全体の定義は別途
pg_dumpall --globals-only -h db.internal -U postgres -f globals.sql

# 復元（新しい空のDBへ。-j で並列化）
pg_restore -h db.internal -U app -d appdb_restore -j 4 appdb_2026-09-02.dump

# MySQL: InnoDBなら --single-transaction でロックせず一貫した状態を取得
mysqldump --single-transaction --routines --triggers \
  --source-data=2 -u app -p appdb | gzip > appdb_$(date +%F).sql.gz

mysqldumpの --source-data=2 は、ダンプ取得時点のbinlogファイル名と位置をコメントとして書き出します。後述するPITRでbinlogを適用する起点になるため、必ず付けておきます。

物理バックアップ：データファイルを複製する

物理バックアップは、DBが使っているデータディレクトリをそのままコピーします。PostgreSQLでは pg_basebackup、MySQLではPercona XtraBackup（またはMySQL Enterprise Backup）を使います。ファイルのコピーなので大容量でも速く、復元はファイルを戻して起動するだけです。一方で同じメジャーバージョン・同じアーキテクチャにしか戻せず、テーブル単位の取り出しはできません。そしてPITRの土台になるのは、この物理バックアップです。

	論理（pg_dump / mysqldump）	物理（pg_basebackup / XtraBackup）
取得・復元速度	遅い（データ量に比例）	速い（ファイルコピー）
部分復元	テーブル単位で可能	不可（クラスタ全体）
バージョン差	異なるバージョンへ移行可	同一メジャーバージョンのみ
復元できる時点	取得時点のみ	WAL/binlogと組み合わせて任意時点
向いている用途	小〜中規模、移行、テーブル単位の救出	大規模、短時間復旧、PITR

WAL/binlogによるポイントインタイムリカバリの仕組み

「ベース＋変更履歴」で任意の時点を再現する

PostgreSQLは全ての変更をまずWAL（Write-Ahead Log）に書き、その後データファイルへ反映します。つまりWALには「ある時点以降に起きたすべての変更」が時系列で記録されています。ここで、ある時点の物理バックアップ（ベースバックアップ）と、それ以降のWALをすべて保存しておけば、「ベースを戻し、WALを指定した時刻まで再生する」ことで、その時刻の状態を再現できます。これがPITRです。MySQLではbinlogが同じ役割を果たします。

鍵になるのはWALを消さずに別の場所へ退避し続ける（アーカイブする）ことです。PostgreSQLは通常、不要になったWALを自動で削除・再利用するため、設定しなければ履歴は残りません。

# postgresql.conf: WALアーカイブを有効にする
wal_level = replica
archive_mode = on
# 既に同名ファイルがあれば失敗させる（上書き事故の防止）。実運用ではS3等へ
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
archive_timeout = 300   # 更新が少なくても5分ごとにWALを切り替えて退避

# ベースバックアップ（週1回など定期実行）
pg_basebackup -h 127.0.0.1 -U repl_user -D /backup/base/$(date +%F) \
  -Ft -z -X stream -P

# アーカイブが止まっていないか監視する（failed_countが増えていたら危険）
SELECT last_archived_wal, last_archived_time, failed_count, last_failed_wal
FROM pg_stat_archiver;

実運用では cp ではなく、pgBackRestやWAL-GといったツールでS3などのオブジェクトストレージへ暗号化・圧縮しつつ送るのが定番です。これらはベースバックアップの世代管理と復元コマンドも提供します。

実際の復元手順｜15時のDELETEの直前に戻す

PostgreSQLでの手順

誤操作が15:02:30に実行されたとわかっている場合、15:02:00の状態に戻す手順は次の通りです。必ず本番とは別のサーバーで行い、確認後に切り替えます。

1. 復元先サーバーで同じメジャーバージョンのPostgreSQLを用意し、データディレクトリを空にする。
2. 直近のベースバックアップをデータディレクトリに展開する。
3. postgresql.conf（または postgresql.auto.conf）に復元設定を書き、データディレクトリに recovery.signal という空ファイルを置く。
4. PostgreSQLを起動する。WALが指定時刻まで再生され、リカバリが完了する。
5. データを確認し、問題なければアプリの接続先を切り替える。

# 2. ベースバックアップの展開
mkdir -p /var/lib/postgresql/16/main
tar -xzf /backup/base/2026-08-31/base.tar.gz -C /var/lib/postgresql/16/main
tar -xzf /backup/base/2026-08-31/pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal

# 3. 復元設定（postgresql.auto.conf に追記）
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-09-02 15:02:00 Asia/Tokyo'
recovery_target_action = 'promote'   # 到達したら読み書き可能として開く
touch /var/lib/postgresql/16/main/recovery.signal

# 4. 起動してログを確認
systemctl start postgresql
tail -f /var/log/postgresql/postgresql-16-main.log
# "recovery stopping before commit of transaction ..." → 指定時刻で停止した
# "database system is ready to accept connections"      → 復元完了

MySQLでは、直近のフルバックアップを復元した後、mysqlbinlog --start-position=<ダンプに記録された位置> --stop-datetime="2026-09-02 15:02:00" binlog.000123 binlog.000124 | mysql -u root -p のようにbinlogを時刻指定で流し込みます。PostgreSQLと同様に、binlogが有効（log_bin）で、保持期間（binlog_expire_logs_seconds）が十分に長いことが前提です。

復元テストをしていないバックアップは存在しないのと同じ

「取れている」と「戻せる」は別の話

バックアップの失敗は取得時ではなく復元時に発覚します。ダンプファイルが途中で切れていた、アーカイブがサイレントに止まって数週間分のWALが欠けていた、復元先のバージョンが違って起動しない、暗号鍵を保管している人が退職していた、手順書どおりにやったら丸1日かかった——いずれも実際によくある話です。対策は、定期的に実際に復元してみることしかありません。

1. 月に1回（重要なシステムでは週1回）、自動で「最新バックアップから一時環境へ復元し、行数や最新レコードの日時を検証するSQLを流し、結果を通知する」ジョブを動かす。
2. PITRを使うなら、「2時間前の状態に戻す」訓練を半年に1回は人手で行い、所要時間（RTO）を記録する。
3. 復元手順書に「誰が」「どこから鍵を取り出し」「どのコマンドを打つか」を、初めて見る人が実行できる粒度で書く。

復元ジョブが自動化されていれば、「バックアップは正常に取れています」というレポートではなく「昨夜のバックアップから復元でき、最新レコードは1:59のものでした」というレポートが毎朝届く状態になります。これが「バックアップが存在する」ことの本当の意味です。

トラブル事例：WALアーカイブが止まっていてPITRできなかった

症状

誤ったUPDATEの直前に戻すため、手順どおりベースバックアップを展開してリカバリを開始したところ、ログに could not open file "/backup/wal/0000000100000A2B00000047" のような restore_command の失敗が出て、目標時刻の3週間前で再生が止まりました。

原因

3週間前にバックアップ先のディスクが一杯になり、archive_command が失敗し続けていました。PostgreSQLはアーカイブに失敗したWALを削除せずに保持し続けますが、その後ディスク不足の対処として担当者が pg_wal 内の古いファイルを手動で削除してしまい、その区間のWALが永久に失われていました。pg_stat_archiver の failed_count を誰も監視しておらず、ベースバックアップだけは毎週成功していたため、異常に気づけなかったのです。

対処

1. 3週間前のベースバックアップ＋WALで戻せる直近の時点まで復元し、失われた期間はアプリのログとpg_dump（幸い前夜分があった）から手作業で補った。
2. pg_stat_archiver.failed_count と last_archived_time の停滞を監視項目に追加し、アーカイブが1時間止まったらアラートが出るようにした。
3. pg_wal 内のファイルを手動で消してはいけないことを運用ルールに明記し、ディスク監視のしきい値も見直した。

PITRは「ベースバックアップからWALが1つも欠けずに繋がっている」ことが絶対条件です。ベースバックアップの成功だけを見ていても、この条件は保証されません。

マネージドDBの自動バックアップで確認すべき設定

RDS / Cloud SQLは「有効にする」だけでは足りない

RDSやCloud SQLは、自動バックアップを有効にすればスナップショット（物理）とトランザクションログの保持を組み合わせたPITRを提供してくれます。自前でWALアーカイブを組む必要はありませんが、次の点は自分で決める必要があります。

• 保持期間: RDSは1〜35日、Cloud SQLもトランザクションログの保持日数を設定できます。「誤操作に気づくまでの最長期間」より長く設定します（月次処理のミスが翌月に発覚することもあります）。
• 復元先は新インスタンス: PITRで復元すると、既存インスタンスが巻き戻るのではなく、別のインスタンスが作られます。アプリの接続先を切り替える手順と、その間の書き込みをどう扱うかを事前に決めておきます。
• 削除時の挙動: インスタンスを削除すると自動バックアップも消えます（RDSは最終スナップショットの取得を選べます）。削除保護を有効にしておきます。
• 別リージョン・別アカウントへの複製: リージョン障害やアカウント乗っ取りに備え、スナップショットのクロスリージョンコピーや別アカウントへの共有を検討します。
• 論理バックアップの併用: マネージドのスナップショットは同じサービス内でしか復元できません。他クラウドへの移行やテーブル単位の救出に備え、pg_dumpを別途取得しておく価値があります。

なお、リードレプリカやレプリケーションは誤ったDELETEも即座に複製するため、バックアップの代替にはなりません。

まとめ

DBのバックアップで最初に決めるべきことは「どの時点に戻せる必要があるか」です。

1. 論理バックアップは取得時点にしか戻せない。物理バックアップ＋WAL/binlogアーカイブで任意の時点に戻せる。
2. PITRの条件は「ベースからWALが欠けずに繋がっている」こと。アーカイブの失敗を監視する。
3. 復元テストを自動化し、「戻せた」という結果を定期的に確認する。
4. マネージドDBでも保持期間・復元先の切替手順・削除保護は自分で設計する。

まずは pg_stat_archiver（またはbinlogの保持設定）を確認し、直近のバックアップから一時環境への復元を1回試してみてください。それが所要時間の初めての実測値になります。DBのバックアップ設計や復旧訓練の整備は、Harmonic Societyのシステム開発・インフラ支援でもお手伝いしています。