Posted inデータベース

PostgreSQL Point-in-Time Recovery (PITR): データベースを任意の秒単位で復元する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

PITRに救われた経験 — 障害が起きる前に設定すべき理由

数年前、PostgreSQL を使った SaaS プロジェクトに携わっていたとき、開発者の一人が WHERE 句なしで UPDATE を実行してしまった — 誰もが恐れる典型的な悪夢だ。8万件のユーザーレコードの status カラムがすべて 'inactive' に書き換えられた。最後のフルバックアップは6時間前のもので、6時間分の実ユーザーの操作が失われた。

その出来事がきっかけで、Point-in-Time Recovery をきちんと設定することを決意した。MySQL、PostgreSQL、MongoDB とさまざまなプロジェクトで使ってきたが、PostgreSQL の WAL ベース PITR は、どの RDBMS と比べても最も信頼性の高いリカバリ機構の一つだと感じている。最後のバックアップチェックポイントではなく、任意の秒単位でロールバックできる点が強みだ。

ここでは、苦労して学んだ教訓を踏まえながら、正しい設定方法を解説する。

PITR の内部動作

PostgreSQL はすべての変更をデータファイルに適用する前に Write-Ahead Log (WAL) に書き込む。このログは順序通りで追記専用、非常に耐久性が高い。PITR の仕組みは以下の通りだ：

  1. ベースバックアップを取得する — データディレクトリの一貫したスナップショット。
  2. WAL セグメントを安全な場所に継続的にアーカイブする。
  3. リカバリ時に、ベースバックアップから目的のタイムスタンプまで WAL セグメントを再生する。

ベースバックアップから最新のアーカイブ済み WAL までの任意の時点に、秒単位で復元できる。毎晩の pg_dump とは本質的に異なる。ダンプはスナップショットを提供するが、PITR はタイムマシンを提供する。

インストールと前提条件

PITR は PostgreSQL に組み込まれており、追加パッケージは不要だ。必要なのは：

  • PostgreSQL 12 以上（以下の例は 14/15 を使用するが、概念は 12 以上に適用される）
  • WAL アーカイブ用の別ストレージ（ローカルパス、NFS、S3 など）
  • 十分なディスク容量：WAL セグメントは 1 つ 16MB で、高負荷なデータベースでは急速に増える

このガイドではローカルディレクトリ（/var/lib/postgresql/wal_archive）にアーカイブする。本番環境では S3 などのオブジェクトストレージや専用バックアップサーバーを使おう — データと同じディスクには絶対に置かないこと。

# アーカイブディレクトリを作成する
sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive
sudo chmod 700 /var/lib/postgresql/wal_archive

設定：ステップバイステップ

ステップ 1: postgresql.conf で WAL アーカイブを有効にする

postgresql.conf（Debian/Ubuntu では通常 /etc/postgresql/14/main/postgresql.conf）を編集する：

sudo nano /etc/postgresql/14/main/postgresql.conf

以下のパラメータを設定する：

# PITR には WAL レベルを 'replica' 以上にする必要がある
wal_level = replica

# アーカイブを有効にする
archive_mode = on

# WAL セグメントをアーカイブ先にコピーするコマンド
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

# 不完全な WAL セグメントをアーカイブするまでの待機時間
archive_timeout = 60    # 秒 — トラフィックの少ないデータベースで重要

archive_command は通常のシェルコマンドだ。PostgreSQL は %p をソース WAL ファイルのパスに、%f をファイル名に置き換える。test ! -f のガードは既存のアーカイブを上書きしないようにする。このチェックは絶対に削除しないこと。

設定を反映するために PostgreSQL をリロードする：

sudo systemctl reload postgresql

ステップ 2: ベースバックアップを取得する

PITR には出発点としてのベースバックアップが必要だ — これは避けられない。pg_basebackup を使おう：

sudo -u postgres pg_basebackup \
  -D /var/lib/postgresql/base_backup \
  -Ft \
  -z \
  -P \
  -Xs \
  -R

フラグの説明：

  • -D — バックアップの出力先ディレクトリ
  • -Ft — tar 形式（移動しやすい）
  • -z — gzip 圧縮
  • -P — 進捗を表示する
  • -Xs — バックアップ中に WAL をストリーミング（一貫性を確保）
  • -R — standby.signal と最小限の postgresql.auto.conf を書き込む（レプリカに便利、それ以外では無害）

cron でスケジュールを組もう。毎週日曜日の午前2時にフルベースバックアップを実行し、その間はアーカイブ済み WAL に頼る運用にしている：

# 毎週日曜日の午前2時にベースバックアップを実行する
0 2 * * 0 postgres pg_basebackup -D /var/lib/postgresql/base_backup_$(date +\%F) -Ft -z -P -Xs 2>&1 >> /var/log/pg_basebackup.log

ステップ 3: Point-in-Time Recovery を実行する

例えば、14:37 JST に誰かが壊滅的な DELETE を実行したとしよう。被害の2分前、2026-04-24 14:35:00 JST の状態に復元する必要がある。具体的な手順は以下の通りだ。

1. PostgreSQL を停止する：

sudo systemctl stop postgresql

2. 念のため現在の（壊れた）データディレクトリをバックアップする：

sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.broken

3. ベースバックアップを復元する：

sudo mkdir -p /var/lib/postgresql/14/main
sudo tar -xzf /var/lib/postgresql/base_backup/base.tar.gz \
  -C /var/lib/postgresql/14/main
sudo chown -R postgres:postgres /var/lib/postgresql/14/main

4. リカバリ設定を作成する：

PostgreSQL 12 以降では、リカバリ設定は postgresql.conf（または postgresql.auto.conf）に記述する。リカバリモードを起動するには recovery.signal ファイルも必要だ：

# シグナルファイルを作成する
sudo -u postgres touch /var/lib/postgresql/14/main/recovery.signal

# リカバリ設定を追記する
sudo -u postgres tee -a /var/lib/postgresql/14/main/postgresql.auto.conf <<EOF
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-04-24 14:35:00+09'
recovery_target_action = 'promote'
EOF

restore_command は archive_command の鏡像だ — 再生中に各 WAL セグメントをどこから取得するかを PostgreSQL に伝える。

5. PostgreSQL を起動してログを確認する：

sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-14-main.log

次のようなログが表示される：

LOG:  starting point-in-time recovery to 2026-04-24 14:35:00+09
LOG:  restored log file "000000010000000000000001" from archive
...
LOG:  recovery stopping before commit of transaction 1234567, time 2026-04-24 14:35:02+09
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

データが正しい状態であることを確認したら、インスタンスをプロモートする：

sudo -u postgres psql -c "SELECT pg_wal_replay_resume();"

確認とモニタリング

アーカイブが機能しているか確認する

アーカイブが動いていると思い込まないこと — 必ず確認しよう。アーカイブを有効にした直後に WAL スイッチを強制し、ファイルが実際にアーカイブディレクトリに届いているか確認する：

# WAL スイッチを強制してすぐにアーカイブをトリガーする
sudo -u postgres psql -c "SELECT pg_switch_wal();"

# アーカイブディレクトリを確認する
ls -lh /var/lib/postgresql/wal_archive/

# pg_stat_archiver でアーカイブ状況を確認する
sudo -u postgres psql -c "
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;
"

failed_count がゼロでない場合、archive_command がサイレントに失敗している。すぐに修正しよう。壊れたアーカイブはアーカイブがない状態と機能的に同じだ。

アーカイブの遅延を監視する

負荷の高いデータベースでは、WAL の生成速度がアーカイブ速度を上回ることがある。差分に注目しよう：

sudo -u postgres psql -c "
SELECT
  pg_current_wal_lsn() AS current_lsn,
  last_archived_wal,
  now() - last_archived_time AS archive_lag
FROM pg_stat_archiver;
"

archive_lag が増え続けている場合、ストレージまたはネットワークが追いついていない。問題になる前に、より高速なアーカイブ先に切り替えよう。

定期的にリカバリをテストする

多くのチームがこれをスキップする。そして痛い目を見る。テストしていないバックアップはバックアップではない。

四半期ごとにリカバリ演習を実施しよう：スペアサーバーにベースバックアップを復元し、WAL アーカイブを再生して、本番スナップショットと行数を照合する。私はこのための小さなスクリプトを用意している：

#!/bin/bash
# PITR 演習 — テストインスタンスで1時間前の状態に復元する
TARGET_TIME=$(date -d '1 hour ago' '+%Y-%m-%d %H:%M:%S%z')
echo "[PITR DRILL] 目標リカバリ時刻: $TARGET_TIME"

# ... （ベースバックアップの復元、restore_command の設定、recovery_target_time の指定）
# その後、行数の確認と重要なテーブルのスポットチェックを行う
sudo -u postgres psql -p 5433 mydb -c "SELECT COUNT(*) FROM users;"

保持ポリシー

WAL アーカイブは急速に増える。書き込みの多いデータベースでは1日に数ギガバイトの WAL が生成されることもある。保持期間を過ぎたアーカイブを削除するクリーンアップジョブを追加しよう：

# 14日間の WAL アーカイブを保持する
find /var/lib/postgresql/wal_archive -type f -mtime +14 -delete

pg_archivecleanup を使うとさらに良い — 特定のバックアップの開始時点を基準に不要なセグメントだけを削除するため、まだ必要なファイルを誤って削除しない：

# 特定のバックアップの開始時点より古い WAL を削除する
sudo -u postgres pg_archivecleanup /var/lib/postgresql/wal_archive 000000010000000000000020

本番環境から学んだ重要な教訓

  • archive_timeout を設定する — 設定しないと、アクセスの少ないデータベースでは何時間もセグメントがアーカイブされず、リカバリウィンドウの末尾に大きな空白が生じる。
  • オフサイトストレージにアーカイブする — データファイルと WAL アーカイブが同じディスクにある場合、ハードウェア障害で両方が消える。最低限の構成として S3 か別サーバーを使おう。
  • pg_stat_archiver.failed_count にアラートを設定する — PagerDuty、Grafana、何でも使っているものに組み込もう。サイレントなアーカイブ失敗は、いざそのファイルが必要になる午前3時まで気づかれない。
  • リカバリ手順を書き留めておく — 障害が午前3時に発生したとき、プレッシャーの中で recovery_target_time の正確な構文を Google 検索したくはないはずだ。1ページのステップバイステップ手順を、誰でもアクセスできる場所に保管しておこう。
  • 必要になる前に演習する — リカバリ演習は、設定のずれやストレージ問題をインシデントになる前に発見できる。
Share:
Tags: