PostgreSQL のDBバックアップ＆復元コマンド

はじめに

データベースのバックアップ、それをレストア（復元）するシンプルな手順です。別のサーバーへデータベースを引っ越す場合にも使えます。

やっていく

下準備

Windows版の PostgreSQL を標準でインストールするとProgram Files配下に入ります。
パスを通しておきます。以下は PostgreSQL 15 の例です。

$env:PATH += ";" + "C:\Program Files\PostgreSQL\15\bin"

バックアップ

pg_dump --verbose --host=localhost --port=5432 --username=postgres --format=custom --encoding=UTF-8 --dbname=example_db --file C:\backup\example_db.backup

実行時にパスワードを入力してバックアップを行います。

バックアップオプション

• --host --port
  接続先サーバーを指定します。
• --username
  対象データベースの所有者または相当のユーザーを指定します。
• --dbname
  バックアップ対象のデータベースを指定します。
• --file
  バックアップ先のファイル名を指定します。

詳細は pg_dump / 日本語翻訳 pg_dump リファレンスで。

復元（レストア）

対象データベースの中身を削除＆復元します。
この方法では復元先のデータベース自体を予め用意しておく必要があります。

pg_restore --verbose --host=localhost --port=5432 --username=postgres --no-owner --no-privileges --single-transaction --clean --if-exists --format=custom --dbname=example_db C:\backup\example_db.backup

実行時にパスワードを入力して復元を行います。

復元オプション

• --clean --if-exists
  復元対象のテーブルやスキーマ等を削除してから復元します。
• --no-owner 所有者を復元しません。
  --username で指定したユーザーが新しい所有者です。
• --no-privileges アクセス権限を復元しません。
  GRANT が実行されないです。細かな設定がある場合はこのオプションを抜きます。
• --single-transaction
  途中で失敗した時に丸ごとロールバックしたい場合に指定します。
• --dbname
  復元先のデータベースです。

復元時のバックアップファイル指定では ``--file` は付きません。別オプションの意味になるので注意が必要です。

詳細は pg_restore / 日本語翻訳 pg_restore リファレンスで。

ハマりどころ

真っ新なデータベースに復元する際に --clean を指定しないと最初からある public スキーマの作成エラー（二重作成）になってエラー報告が出ます。

public 以外のスキーマーを利用している場合で真っ新なデータベースに対して --clean 付きで実行すると、まだ無いスキーマーに対して DROP SCHEMA を実行しようとしてエラーになります。

真っ新なデータベースに対して復元するなら --clean と --if-exists の両方を指定するのが手間が無くて良いかなと思いました。

補足：照合順序

既定の照合順序は復元先として用意したデータベースの「照合順序」になります。
バックアップ対象のデータベースと揃えておく必要があります。

意図的に照合順序を変えたい場合は「バックアップ→希望する照合順序の空データベースを作成→復元」のステップを踏むことになります。

まとめ

PostgreSQL をよく使いますが、バックアップ・復元のコマンドはあまり出番が無いのでほぼコピー＆ペーストで使える形でまとめました。

データベース管理・引っ越し作業の参考になれば幸いです。
それではまた！