【豆知識】事前にデータベースオブジェクトを削除する手間を省いて、リストアを実行したい
PostgreSQLインサイド

実現方法

データベースオブジェクトをリストアで復元する際、事前にデータベースオブジェクトを削除しておかないと、すでに存在する旨のエラーとなります。リストアと同時にリストア先のデータベースオブジェクトの整理（削除）を行うことで、このような手間を省き、エラーを回避できます。その方法は2つあります。

• A）バックアップ時に、データベースオブジェクトを整理するSQLコマンドが記載されたバックアップファイルを作成
  pg_dumpコマンドに-cオプションまたは--cleanオプションを指定してバックアップし、psqlコマンドでリストアします。
• B）データベースオブジェクトを整理した後、リストアを実行
  pg_restoreコマンドに-cオプションまたは--cleanオプションを指定してリストアします。

ここでは（A）の方法を説明します。
pg_dumpコマンドの出力結果には、バックアップした時点の状態のデータベースを再構成するために必要なSQLコマンドが書き出されます。これに-cまたは--cleanオプションを指定すると、データベースオブジェクトを作成するコマンドの前に、データベースオブジェクトを削除するコマンドが書き出されます。このため、リストアのとき、データベースを再作成する前にデータベースオブジェクトが整理されます。
また、--if-existsオプションを同時に指定すると、リストア先のデータベースの中に存在しないオブジェクトがある場合に発生する害のないエラーを回避できます。

実行例

pg_dumpコマンドに-cオプションを指定してバックアップしたオブジェクトを、psqlコマンドでリストアする場合の例

1. 対象データベース（mydb）に対し、pg_dumpコマンドを使ってバックアップ

$ pg_dump -c --if-exists mydb > mydb_dump.sql

1. psqlコマンドを使って、バックアップ時点にリカバリー

$ psql mydb < mydb_dump.sql

ポイント

• リストア実行時、削除できない旨のエラーが出力された場合は、対象のオブジェクトを削除しても問題ないか確認してください。また、リストア完了後のオブジェクトの内容に問題がないか確認してください。

• -cオプションと共に--if-existsオプションを指定しなかった場合、以下のようなエラーが出力されることがあります。これは、リストア時に読み込むファイルに、存在しないオブジェクトに対するDROP文が記述されているからです。この場合のエラーに害はありません。

  エラーメッセージの例

  pg_dumpコマンドに-cオプションを指定してバックアップ後、テーブルが削除されていた場合は、リストア時に以下のようなエラーが出力されます。

  ERROR:  テーブル"テーブル名"は存在しません (注)
  文:  DROP TABLE テーブル名; (注)
  ERROR:  テーブル"テーブル名"は存在しません

  • 注
    サーバログです。ログ収集機構が有効になっている場合、画面に表示されません。

  -cオプションと共に--if-existsオプションを指定してバックアップすると、IF EXISTS句が追加されたDROP文が書き出されるため、上記のエラー出力を回避できます。なお、--if-existsオプションはPostgreSQL 9.4からサポートされています。

参考

PostgreSQL 11.1 文書

• Documentation（PostgreSQLオフィシャル）
  • VI. Reference
    • II. PostgreSQL Client Applications
      • pg_dump — extract a PostgreSQL database into a script file or other archive file
• PostgreSQL日本語ドキュメント（日本PostgreSQLユーザ会）
  • VI. リファレンス
    • II. PostgreSQLクライアントアプリケーション
      • pg_dump — PostgreSQLデータベースをスクリプトファイルまたは他のアーカイブファイルへ抽出する

PostgreSQLインサイド

• 技術を知る：PostgreSQLのバックアップとリカバリー ～ 論理バックアップ ～

2019年9月17日公開

こちらもおすすめ

• 【豆知識】データベースを別のデータベースクラスタにリストアしたい
• 【豆知識】エンコーディング（符号化方式UTF-8形式）を指定して、テーブルのデータをクライアントに抽出したい

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