DBAtlas · データベース運用アトラス

PostgreSQL の論理バックアップとリストア (pg_dump / pg_restore)

PostgreSQLレベル: 中級所要時間: 20〜90 分 (データ量による)

pg_dump、pg_dumpall、pg_restore を使い、テーブル、スキーマ、データベース、またはクラスタ全体の単位で論理バックアップとリストアを行います。構造のみ (DDL) またはデータのみ、別サーバー/別バージョンへの移行、ダンプからの単一テーブルのリストア、systemd/cron によるスケジュール実行・検証・保持期間付きのバックアップを扱います。どの手順も最後に行数を比較して検証します。streaming replication と Patroni クラスタでは、ダンプはレプリカ (HAProxy の読み取りポートまたは standby) から取得し、リストアは必ず primary/leader に対して行います。

論理バックアップはダンプ時点に戻すだけで、特定時点へのリカバリ (PITR) はできません。本番環境の主バックアップは物理バックアップにしてください: pgBackRest (推奨)、または追加ツールなしの pg_basebackup + WAL アーカイブ。大規模データベースを数秒の停止で新バージョンへ移すには logical replication、同じサーバー上でのインプレースアップグレードには pg_upgrade を使います。

構成: サーバー 1 台、streaming replication (primary + standby)、または Patroni クラスタ。移行時は移行元 + 移行先、リモートからのスケジュールバックアップではさらにバックアップサーバー

シナリオ

  • 単一テーブルのエクスポート / インポート — 選択したテーブルの構造とデータ (インデックス、制約、トリガーを含む) をダンプし、別のデータベースにロードします。
  • スキーマのエクスポート / インポート — スキーマ内のすべてのオブジェクト (テーブル、ビュー、関数、シーケンス) をダンプします。
  • 単一データベースのバックアップ / リストア — データベース全体 (構造 + データ) を 1 つのダンプに取得し、同じデータベースまたは別のデータベースにリストアします。ロール (globals) は含まれません。
  • クラスタ全体 (ロール + テーブル空間 + 全データベース) — pg_dumpall -g で globals を、データベースごとに個別の pg_dump を取得します。リストアは一時的なテストクラスタでリハーサルします。
  • スキーマ構造のみ (DDL) — データを含まないテーブル、インデックス、ビュー、関数の定義。レビュー、バージョン管理、空の環境の構築に使います。
  • データのみ — 移行先に構造がすでにある状態で、行 (COPY) だけを転送します。
  • 別サーバー / 別バージョンへの移行 — 移行先サーバーで新バージョンの pg_dump がネットワーク経由で移行元に接続し、パイプまたはファイルで転送します。
  • ダンプから単一テーブル / オブジェクトをリストアする — 既存の custom/directory 形式のダンプから、リストファイル (-L) を使って 1 つのテーブルとそのインデックス、制約、データだけをリストアします。
  • スケジュールバックアップ (systemd timer / cron) — 毎日の globals + データベースのバックアップ、読み取り可能性の検証、保持期間、ログ、失敗通知。

開始前の確認

  • …Patroni クラスタが稼働しています。このサーバーには PostgreSQL … のクライアントツール (…) がインストールされており、HAProxy/VIP (…) の書き込み (…)… および読み取り (…)… ポートに到達できます。postgres ロールのパスワード (Patroni では /etc/patroni/patroni.env 内の PATRONI_SUPERUSER_PASSWORD) が手元にあります。…primary で PostgreSQL … が稼働しており、postgres ユーザーはローカル (peer) で接続できます。standby … は streaming 状態で、primary からその … ポートに到達できます。ダンプはネットワーク経由で standby に接続するため、postgres ロールにパスワードが必要です (ない場合は primary で \password postgres を実行します。パスワードは WAL で standby に伝わります)。…PostgreSQL … が稼働しており、OS の postgres ユーザーはローカル (peer) で接続できます。DBAtlas の PostgreSQL 単一サーバーのインストール ガイドで構築した場合、パスは同じです。…
  • …クラスタが正常であること: Leader が 1 つ、少なくとも 1 つのレプリカが streaming で、遅延が小さいこと。Patroni ノードのいずれかで確認します。…standby … が primary に接続されており、遅延が小さいこと (ダンプはその時点の standby のイメージを取得します)。…
  • バックアップディレクトリ … がデータディスクとは別のディスクにあり、十分な空き容量があること: 圧縮した custom/directory 形式のダンプは通常データベースサイズの 10〜30% です (インデックスは定義だけがダンプに入ります)。plain 形式や非圧縮のダンプはそれより大きくなります。
  • pg_dump のメジャーバージョンが、バックアップ対象サーバーと 同じかそれより新しい こと (新しい pg_dump は古いサーバーを読めますが、逆はできません)。pg_restore は、ダンプを作成した pg_dump と同じか、より新しいバージョンである必要があります。
  • 移行先の pg_dump/pg_restore (…) のメジャーバージョンが移行元サーバー (…) と同じかそれより新しいこと: 移行は移行先の新しいツールで行います。
  • 移行元で使っている拡張 (postgis、pgvector、pg_partman…) のソフトウェアが移行先サーバーにもインストールされていること。論理バックアップが運ぶのは CREATE EXTENSION の行だけで、拡張そのものではありません。
  • ユーザー定義のテーブル空間がある場合 (下のクエリが行を返す場合)、移行先に同じディレクトリを用意するか、--no-tablespaces でデフォルトのテーブル空間にリストアする必要があります。
  • 移行先サーバー … に PostgreSQL … がインストール済みで、稼働しており、空であること (postgres、template0、template1 のみ)。移行先から移行元の … ポートに到達できること。
  • バックアップサーバー … から …HAProxy/VIP アドレス … の … ポート…standby … の … ポート…データベースサーバーの … ポート… に到達でき、PGDG リポジトリ (または社内ミラー) にアクセスできること。
  • サーバーからメールを送信できること: ローカル MTA (postfix) が社内 SMTP リレーに転送するよう設定されていること。
  • 大規模なリストアや移行のためのメンテナンスウィンドウが計画済みであること。移行先を上書きする場合は、移行先のバックアップ/スナップショットを取得済みであること。

ガイドのフェーズ

  • 事前準備 — バックアップディレクトリを作成する · 接続パスワードを .pgpass に保存する · standby の pg_hba.conf でアクセスを許可する · 接続先のロールを確認する · データベースサイズと長時間のトランザクションを確認する
  • リストア (インポート) — リストア先データベースを準備する · リストア先を準備する: 拡張、スキーマ、トリガー関数 · リストアする · リストア先を上書きしてリストアする · 統計情報を収集する (ANALYZE) · 行数を比較する
  • スキーマ構造のみ (DDL) — DDL のダンプを取得する · リストア先データベースを作成する · DDL をリストア先に適用する · 構造が同一であることを確認する
  • データのみ — データのダンプを取得する · リストア先データベースを作成する · リストア先にテーブル構造を作成する · リストア先のテーブルを空にする · データをロードする · 統計情報を収集し、行数を比較する
  • クラスタ全体のバックアップ — globals とすべてのデータベースをバックアップする · バックアップが読み取れることを確認する
  • リストアのリハーサル (一時的なテストクラスタ) — テスト用ディレクトリを準備する · 一時クラスタを作成して起動する · globals をロードする · データベースをロードする · ANALYZE と行数の比較 · 一時クラスタを停止して削除する
  • 移行元サーバーでのアクセス許可 — pg_hba.conf で移行先サーバーを許可する · pg_hba で移行先サーバーを許可する (Patroni) · ファイアウォールで移行先にポートを開放する · アプリケーションを停止する (切り替え時点)
  • 移行先サーバーでの転送 — 移行元への接続パスワードを .pgpass に保存する · バージョンと拡張の互換性を確認する · ロールを移行する (globals) · データベースを転送する (ファイル経由、並列) · データベースを転送する (パイプ経由) · 統計情報を収集する (ANALYZE) …
  • 切り替え後 — 移行元の一時アクセスを削除する · 移行元の一時アクセスを削除する (Patroni) · アプリケーションの接続先を移行先に切り替える
  • ダンプから単一テーブルをリストアする — ダンプの内容を確認する · リストファイルを作成する (-L) · リストア先データベースを準備する · テーブルをリストアする · テーブルを元のデータベースにリストアする · 行数を比較する …
  • バックアップサーバーとバックアップ用ロール — バックアップ用ロールを作成する · ラージオブジェクトに読み取り権限を付与する · バックアップ用ロールにパスワードを設定する · pg_hba.conf でバックアップサーバーを許可する · pg_hba でバックアップサーバーを許可する (Patroni) · PostgreSQL クライアントツールをインストールする (バックアップサーバー) …
  • バックアップスクリプト — ツールのフルパスを確認する · 通知ツールをインストールする · バックアップスクリプトを作成する · スクリプトを実行可能にする · 最初のバックアップを手動で取得する
  • スケジュール (systemd timer) — systemd サービスを作成する · systemd timer を作成する · timer を有効にし、サービスを手動で起動する
  • スケジュール (cron) — cron サービスを準備する · cron ジョブを作成する · cron ファイルのパーミッションを設定し、cron 環境で試す · cron がジョブを実行したことを確認する
  • 検証と監視 — 最新のバックアップをテスト用データベースにリストアする · 最新のバックアップの経過時間を監視する
  • 次のステップ: 物理バックアップ — 論理バックアップの不足を補う
環境に合わせた手順プランを開く →