Posted inDatabase

PostgreSQLバックアップの正しい方法：戦略、自動化、そして実践的なリストアテスト

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

バックアップは「完了」なのにリストアが失敗——実際に経験した話

PostgreSQLを数年使ってきて気づいたのは、ほとんどの人がバックアップを設定したら……そのまま忘れてしまうことだ。毎晩pg_dumpを実行してログに成功と出ていれば安心してしまう。実際にリストアが必要な場面になって初めて、事態の深刻さに気づく。

MySQL、PostgreSQL、MongoDBの三つすべてを扱ってきた。それぞれに強みがある。しかしPostgreSQLは、正しく使いこなせれば、最も体系的なバックアップエコシステムを持つデータベースだ。この記事では実際に使っているワークフローを紹介する。多くの人が省きがちなリストアテストの部分も含めて。

前提知識：どのバックアップ方式をいつ使うか

PostgreSQLには主要なバックアップ方式が3つある。間違った方式を選ぶと、インシデント発生時に何時間もロスすることになる：

  • pg_dump / pg_dumpall：論理バックアップ。SQLまたはカスタム形式にエクスポートする。データ移行、個別データベースのバックアップ、特定のテーブルだけを選択的にリストアする場合に適している。
  • pg_basebackup：ファイルシステムレベルでクラスター全体をバックアップする。はるかに高速で大規模データベースに適しており、ストリーミングレプリケーションの設定にも必要。
  • WAL Archiving + PITR：ポイントインタイムリカバリ——過去の任意の時点にリストアできる。より複雑だが、重要な本番環境における最後の防衛線となる。

シンプルな目安：10GB未満のデータベースはpg_dumpで十分。10GB超え、またはPITRが必要な場合はpg_basebackupとWALアーカイビングの組み合わせに投資しよう。

セットアップ：ディレクトリと権限の準備

バックアップスクリプトを書く前に、環境を適切に準備しておく必要がある——このステップを雑に済ませると、後でcron実行時のパーミッションエラーに悩まされることになる：

# バックアップディレクトリ構造を明確に作成する
sudo mkdir -p /var/backups/postgresql/{daily,weekly,monthly}
sudo chown postgres:postgres /var/backups/postgresql -R
sudo chmod 750 /var/backups/postgresql -R

# postgresユーザーが書き込めるか確認する
sudo -u postgres touch /var/backups/postgresql/daily/.test && echo "OK" || echo "Permission denied"

daily/weekly/monthlyの構造にしておくと、後のローテーション管理がずっと楽になる。すべて一つのディレクトリにダンプしていると、数週間後には何がどこにあるか分からなくなる。

cronをパスワード入力なしで実行するには、.pgpassファイルを使う：

# ~/.pgpass — format: hostname:port:database:username:password
echo "localhost:5432:*:postgres:your_password" > /var/lib/postgresql/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 600 /var/lib/postgresql/.pgpass

詳細設定：自動バックアップスクリプト

pg_dumpで各データベースをバックアップ

カスタム形式（-Fc）はプレーンSQLより60〜70%圧縮率が高い——10GBのデータベースがダンプファイルでは3〜4GBになる。さらに複数ワーカーによる並列リストアにも対応しており、プレーンSQLでは不可能なことだ：

#!/bin/bash
# /usr/local/bin/pg_backup_daily.sh

BACKUP_DIR="/var/backups/postgresql/daily"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/pg_backup.log"

echo "[$(date '+%Y-%m-%d %H:%M:%S')] === Daily backup started ===" >> "$LOG_FILE"

# 各データベースをバックアップ（テンプレートデータベースは除外）
for DB in $(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.dump"

    sudo -u postgres pg_dump \
        --format=custom \
        --compress=9 \
        --file="$BACKUP_FILE" \
        "$DB" 2>> "$LOG_FILE"

    if [ $? -eq 0 ]; then
        SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
        echo "[OK] $DB → $BACKUP_FILE ($SIZE)" >> "$LOG_FILE"
    else
        echo "[FAIL] Backup failed for database: $DB" >> "$LOG_FILE"
        # ここにアラート処理を追加: Slack webhookのcurl、メール送信など
    fi
done

# RETENTION_DAYS日より古いバックアップを削除
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
echo "[INFO] Cleaned up backups older than $RETENTION_DAYS days" >> "$LOG_FILE"

find ... -deleteの部分は、バックアップ処理そのものと同じくらい重要だ。ディスクがいっぱいになる→cronがサイレントに失敗する→新しいバックアップが作られない→インシデント発生。実際にこのケースを目撃したことがある。

cronでスケジュール設定

# postgresユーザーのcrontabを開く
sudo -u postgres crontab -e

# 毎日午前2時にデイリーバックアップ
0 2 * * * /usr/local/bin/pg_backup_daily.sh

# 毎週日曜日午前3時にウィークリーバックアップ
0 3 * * 0 /usr/local/bin/pg_backup_weekly.sh

pg_basebackupでクラスターをバックアップ（大規模データベース向け）

データベースが10GBを超えると、pg_dumpは著しく遅くなる。20GBのデータベースでpg_dumpを実行するとI/O次第で15〜25分かかるが、pg_basebackupはデータファイルを直接コピーするため、同じサイズでも5〜8分程度で済む。データベースが大きくなるほどこの差は広がる：

sudo -u postgres pg_basebackup \
    --pgdata=/var/backups/postgresql/basebackup/$(date +%Y%m%d) \
    --format=tar \
    --compress=9 \
    --progress \
    --checkpoint=fast \
    --verbose

# 結果を確認
du -sh /var/backups/postgresql/basebackup/$(date +%Y%m%d)

確認とモニタリング：最も重要なのに見落とされがちな部分

定期的なリストアテスト——必ず実施すること

これは苦い経験から学んだ教訓だ。本番環境の緊急リストアが必要になり、いざダンプファイルを開いてみると、ディスクがいっぱいで3週間もサイレントに失敗し続けていたことが発覚した。リストアをテストしていないバックアップは、存在しないも同然だ——最悪のタイミングに至って初めて機能しているかどうかが分かる。

#!/bin/bash
# /usr/local/bin/pg_test_restore.sh — 毎週実行

BACKUP_FILE=$(ls -t /var/backups/postgresql/daily/*.dump 2>/dev/null | head -1)
TEST_DB="restore_test_$(date +%Y%m%d)"

if [ -z "$BACKUP_FILE" ]; then
    echo "[FAIL] バックアップファイルが見つかりません！" >&2
    exit 1
fi

echo "=== Testing restore: $BACKUP_FILE ==="

# テスト用一時データベースを作成
sudo -u postgres createdb "$TEST_DB"

# 4つの並列ジョブでリストア
sudo -u postgres pg_restore \
    --dbname="$TEST_DB" \
    --jobs=4 \
    --verbose \
    "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    TABLE_COUNT=$(sudo -u postgres psql -Atc "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public';" "$TEST_DB")
    echo "[OK] リストア成功 — publicスキーマのテーブル数: $TABLE_COUNT"
else
    echo "[FAIL] リストア失敗！今すぐ確認が必要です。" >&2
fi

# テストデータベースを削除
sudo -u postgres dropdb "$TEST_DB"
echo "テストデータベースを削除しました。"

このスクリプトは最新のバックアップを自動で取得し、一時データベースにリストアしてテーブル数を確認した後、削除する。cronで週に一度実行し、結果をログに残す。失敗したら即座にアラートを出すこと——本番が燃えてからでは遅い。

バックアップファイルの整合性確認

# バックアップファイルが破損していないか確認（実際のリストアは不要）
sudo -u postgres pg_restore --list /var/backups/postgresql/daily/myapp_latest.dump > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "Backup integrity: OK"
else
    echo "WARNING: バックアップファイルが破損している可能性があります！"
fi

# 最近のバックアップサイズを確認——突然異常に小さくなっていたら要調査
ls -lh /var/backups/postgresql/daily/ | tail -7

シンプルだが効果的なコツがある：バックアップファイルのサイズを日次で追うことだ。例えば通常は2GBのバックアップが今日は200MBしかないのに、マイグレーションやデータ削除は行っていない——これはすぐに調査すべきサインだ。タイムアウト前にスクリプトが一部のデータしか取得できていない可能性がある。

緊急時のリストア手順

実際にインシデントが発生すると、頭が混乱しやすい。事前にランブックを用意しておき、考えなくても実行できるようにしておこう：

# ステップ1：リストアするバックアップを特定
ls -lt /var/backups/postgresql/daily/ | head -5

# ステップ2：リストア前にアプリを停止（重要！）
systemctl stop myapp

# ステップ3：リストア（--cleanで既存オブジェクトを事前削除、--jobsで並列化）
sudo -u postgres pg_restore \
    --dbname=myapp_production \
    --clean \
    --if-exists \
    --jobs=4 \
    /var/backups/postgresql/daily/myapp_20240301_020000.dump

# ステップ4：アプリ再起動前に簡単なサニティチェック
sudo -u postgres psql myapp_production -c "SELECT COUNT(*) FROM users;"
sudo -u postgres psql myapp_production -c "SELECT MAX(created_at) FROM orders;"

# ステップ5：アプリを再起動
systemctl start myapp

--clean --if-existsフラグは、リストア前にオブジェクトをドロップして再作成する——残存した古いデータとの競合を防ぐためだ。ステップ4のサニティチェックは5秒しかかからないが、ユーザー向けにアプリを再起動する前の自信につながる。

ベストプラクティスのまとめ

  • 3-2-1ルール：バックアップを3つ、2種類の異なるストレージ、1つはオフサイト（S3、Backblazeなど）
  • 週次リストアテスト：自動化すること、必要になるまで待ってはいけない
  • バックアップサイズの監視：突然異常に小さくなったファイル＝即座に調査が必要
  • カスタム形式-Fcを使用：SQLより圧縮率が高く、並列リストアに対応し、柔軟性が高い
  • マイグレーション前のバックアップ：小さな変更であっても、例外なく必ず実施する
  • 完全なログ＋失敗時のアラート：サイレントに失敗するバックアップが最悪のケースだ
  • 明確なリテンションポリシー：デイリー7日、ウィークリー4週間、マンスリー3ヶ月——ストレージとロールバック能力のバランスを取る

何年もデータベースを扱ってきて、一つのことが分かった：優れたバックアッププロセスとは、最も複雑なプロセスではない。それは、すべてが炎上している深夜3時に、実際にテストされており信頼できるプロセスだ。

Share:
Tags: