pg_restore:[アーカイバ (db)] はクエリを実行できませんでした。ERROR: スキーマ "public" は既に存在します。

pg_dump / pg_restore を使って PostgreSQL データベースをバックアップ、リストアしていますが、pg_restore からいくつかのエラーメッセージ(とゼロ以外の終了ステータス)が表示されます。超簡単な基本ケース(以下に概要を示します)を試してみましたが、まだこれらのエラーが発生します。

pg_restore:[archiver (db)] TOCの処理中にエラーが発生しました。
pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore:pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore:[archiver (db)]クエリを実行できませんでした。ERROR: スキーマ "public" は既に存在します。
    コマンドはCREATE SCHEMA publicです。

再現するための手順

1.Ubuntu 14.04をインストールします(VagrantはこのVagrant boxで使用しています)。 2.2. PostgreSQL 9.3 をインストールし、Linux ユーザーであれば誰でも PostgreSQL ユーザー "postgres" としてローカル接続できるように設定する。 3.3. テスト用データベースを作成する。I'しているだけです。 という感じです。 vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres psql (9.3.5) help"と入力すると、ヘルプが表示されます。

postgres=# create database mydb;
データベースの作成
postgres=# \ \
vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb
psql (9.3.5)
help"と入力するとヘルプが表示されます。

mydb=# create table data(entry bigint);
CREATE TABLE
mydb=# insert into data values(1);
INSERT 0 1
mydb=# insert into data values(2);
INSERT 0 1
mydb=# insert into data values(3);
INSERT 0 1
mydb=# \ \
</pre>

4.以下のように、データベースのバックアップを作成します。 pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump

5.5. mydbのデータテーブルからいくつかの行を削除して、データの復元が成功したかどうかを確認できるようにします。

6.6. データベースをリストアします。 pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump</pre&gt.PGPASSWORD="postgres" PGPASSWORD="postgres" Pg_restore --clean --create --dbname=postgres --username=postgres

データはリストアされましたが、手順6のpg_restoreコマンドはステータス1で終了し、次のような出力が表示されます。

pg_restore:[archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore:pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore:[archiver (db)]クエリを実行できませんでした。ERROR: スキーマ "public" は既に存在します。
    コマンドはCREATE SCHEMA publicです。

警告:復元時にエラーが無視されます。1

私はプログラムでこのコマンドを実行しており、リストアが失敗したかどうかを判断するために終了ステータスを使用する必要があるため、これを無視するわけにはいきません。当初、この問題は、データベースをpublic(デフォルトのスキーマ)に置いたことが原因ではないかと考えていました。データをリストアする前にpg_restoreが--createオプションを指定した結果、publicが作成されるのではないかと考えました(私のテーブルがそこにあるため、そのスキーマも作成しようとした可能性があります)。

私は何か間違ったことをしているのでしょうか?なぜこのエラーが表示されるのでしょうか?

ソリューション

このエラーは無害ですが、このエラーを取り除くには、このリストアを2つのコマンドに分ける必要があると思います。

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

pg_restore の --clean オプションは、あまり重要ではないように見えますが、実際には自明ではない問題を発生させます。

9.1までのバージョン用

pg_restore のオプションで --create--clean の組み合わせは、古いバージョンの PG (9.1 まで) ではエラーになっていました。9.1のmanpageを引用)確かに矛盾しているところがあります。

--clean データベースオブジェクトを再作成する前にクリーンアップ(削除)します。

--create データベースにリストアする前に、データベースを作成します。

なぜなら、新しいデータベースの内部をクリーニングすることに何の意味があるのでしょうか?

バージョン9.2から

この組み合わせが認められるようになり、ドキュメントにはこのように書かれています(9.3 manpageを引用)。

--clean データベースオブジェクトを再作成する前に、オブジェクトを削除します。(これは、宛先データベースにオブジェクトが存在しない場合、無害なエラーメッセージを生成する可能性があります。)

--create データベースにリストアする前に、データベースを作成します。cleanも指定された場合、ターゲットデータベースに接続する前に、データベースを削除して再作成します。

この2つを同時に指定すると、リストア時にこのようなシーケンスになります。

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

個々のオブジェクトには DROP がなく、最初に DROP DATABASE があるのみです。もし --create を使用しないのであれば、これは逆になってしまいます。

とにかく、このシーケンスでは template0 から mydb を作成すると、すでに public スキーマがインポートされているため、public スキーマがすでに存在するというエラーが発生します(これは正常で、テンプレートデータベースのポイントです')。

この場合、なぜ pg_restore によって自動的に処理されないのかがよくわかりません。おそらく、管理者が template0 をカスタマイズしたり、public の目的を変更したりすると、望ましくない副作用が発生するのでしょう。

解説 (1)

私の場合、postgresql-contrib version 11.2 の pg_restore を使って、 pg_dump 9.6 で作成したダンプを PostgreSQL クラスタ 9.6 にリストアしていたことが原因です。

pg_restoreを 9.6 にダウングレードしたら、このschema "public" already exists` というエラーはなくなり、リストアも以前と同様にできるようになりました。

解説 (1)