【豆知識】データベースを別のデータベースクラスタにリストアしたい
PostgreSQLインサイド

実現方法

バックアップしたデータベースを別のデータベースクラスタにリストアしたい場合があります。例えば、開発環境のデータベースクラスタにあるデータベースの1つをテスト環境のデータベースクラスタに移行したいような場合です。このような場合、データベースオブジェクトの所有者がリストア先に存在しないとリストア時にエラーが発生するため、事前に、バックアップしたオブジェクトに関わるロールをリストア先に作成してから、リストアしてください。

実行例

データベース“mydb”のすべてのデータベースオブジェクトをユーザー（以降“ロール”）“admin”が作成しているものとします。このデータベースに対し、以下のようにロール“admin”からpg_dumpコマンドを使ってアーカイブファイル形式でバックアップしたことを前提とします。

$ pg_dump -Fc mydb > mydb_dump.custom

リストア先に同名のロール“admin”が存在しない場合は、以下のようにロール“admin”を作成してからリストアを実行してください。

1. psqlでロール“admin”を作成

postgres=# CREATE ROLE admin WITH LOGIN PASSWORD 'パスワード';

1. バックアップしたアーカイブファイル（mydb_dump.custom）からリストア
   pg_restoreコマンドの-dオプションには、接続可能な任意のデータベース名（例えばpostgres）を指定します。

$ pg_restore -C -d postgres mydb_dump.custom

ポイント

• ロールの情報はデータベースクラスタ内で全データベースに共通するグローバルオブジェクトとして管理されているため、データベース単位にバックアップするpg_dumpコマンドではバックアップされません。このため、リストア先にバックアップを実行したロールと同じロールが存在しない場合は、リストア時に以下のようなエラーメッセージが出力されます。

  エラーメッセージの例

  この例では、バックアップ元の管理者名が“admin”ですが、リストアを実行したロールが“admin”以外のため、エラーが出力されます。

  could not execute query: ERROR: ロール"admin"は存在しません
  コマンド: ALTER SCHEMA myschema OWNER TO admin;

  • 備考
    “admin”には管理者名が表記されます。myschemaには既存のスキーマ名が表記されます。

  なお、テーブルスペースもグローバルオブジェクトのため、pg_dumpコマンドではバックアップされません。ロールやテーブルスペースをバックアップするためには、pg_dumpallコマンドを利用してください。

• 上記のようなエラーメッセージの最後に「リストアにてエラーを無視しました」旨のメッセージが出力された場合は、エラーが発生したあとに何らかのデータベースオブジェクトがリストアされ、意図しない結果になっていることがあります。
  pg_restoreコマンドを再実行するときは、事前にリストア済のテーブルを削除してください。リストア先にテーブルが残ったままpg_restoreコマンドの実行を繰り返すと、テーブル内のデータに追加で書きこまれる場合があります。
• pg_restoreコマンド実行後は、エラーメッセージを確認して、リストアできていないデータベースオブジェクトがないかを確認してください。

参考

PostgreSQL 11.1 文書

• Documentation（PostgreSQLオフィシャル）
  • III. Server Administration
    • 25. Backup and Restore
  • VI. Reference
    • I. SQL Commands
      • CREATE ROLE
• PostgreSQL日本語ドキュメント（日本PostgreSQLユーザ会）
  • III. サーバの管理
    • 25. バックアップとリストア
  • VI. リファレンス
    • I. SQLコマンド
      • CREATE ROLE

2019年11月8日公開

こちらもおすすめ

• 【豆知識】エンコーディング（符号化方式UTF-8形式）を指定して、テーブルのデータをクライアントに抽出したい
• 【豆知識】事前にデータベースオブジェクトを削除する手間を省いて、リストアを実行したい

本コンテンツに関するお問い合わせ