pg_restore: [archiver (db)] 执行查询失败：错误：模式 "public" 已存在

Question

我正在使用pg_dump / pg_restore来备份和恢复一个PostgreSQL数据库，但是从pg_restore中得到了一些错误信息（以及非零的退出状态）。我尝试了一个非常简单的基本情况（如下所述），但仍然出现了这些错误：

pg_restore: [archiver (db)] 在处理TOC时发生错误：
pg_restore: [archiver (db)] 来自TOC条目5的错误；2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] 无法执行查询：ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

重现步骤：

1. 安装一个新鲜的、原始的Ubuntu 14.04发行版（我正在使用带有this Vagrant box的Vagrant）。
2. 安装PostgreSQL 9.3，并配置为允许来自任何Linux用户的本地连接，作为PostgreSQL用户"postgres"。

3. 创建一个测试数据库。我只是执行以下操作：

   vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres
   psql (9.3.5)
   Type "help" for help.
   
   postgres=# create database mydb;
   CREATE DATABASE
   postgres=# \q
   vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb
   psql (9.3.5)
   Type "help" for 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=# \q
   

4. 创建数据库备份，如下所示：

   PGPASSWORD="postgres" pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump

5. 从mydb数据表中删除一些行，这样我们就能够判断是否成功恢复了数据。

6. 使用以下命令恢复数据库：

   PGPASSWORD="postgres" pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump

数据已恢复，但在第6步中，pg_restore命令退出状态为1，并显示以下输出：

pg_restore：[archiver（db）]处理TOC时出错：
pg_restore：[archiver（db）]来自TOC条目5的错误；2615 2200 SCHEMA public postgres
pg_restore：[archiver（db）]无法执行查询：ERROR：模式“public”已存在
    命令是：CREATE SCHEMA public;



警告：还原过程中忽略了错误：1

我不能忽视这个问题，因为我正在以编程方式运行此命令，并且需要使用退出状态来确定还原是否失败。最初，我想知道这个问题是否是因为我将数据库放在public（默认模式）中。我推断，在恢复数据之前，pg_restore会通过--create选项创建public模式（可能也会尝试创建该模式，因为那是我的表所在的位置），但当我将上述步骤与我的表放在不同模式中时，结果相同，错误消息也相同。 我做错了什么吗？为什么会出现这个错误？

- KSletmoe

Answer 1

这个错误是无害的，但为了解决它，我认为你需要将这个恢复操作分成两个命令，就像这样：

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

在pg_restore中，--clean选项看起来并不起眼，但实际上引发了一些非平凡的问题。 对于9.1版本及以下版本： 在旧版PG（9.1及以下）中，--create和--clean选项的组合曾经是一个错误。确实存在一些矛盾之处（引用自9.1手册）：

--clean 在重新创建数据库对象之前清理（删除）它们

和

--create 在恢复数据之前创建数据库。

因为在全新的数据库中进行清理有什么意义呢？ 从9.2版本开始： 现在接受了这种组合，并且文档中如下说明（引用自9.3手册）：

--clean 在重新创建数据库对象之前清理（删除）它们。（如果目标数据库中不存在某些对象，则可能会生成一些无害的错误消息。）

--create 在恢复数据之前创建数据库。如果还指定了--clean选项，则在连接到目标数据库之前删除并重新创建目标数据库。

现在将两者结合在一起会导致在恢复过程中出现这种序列：

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

每个单独的对象都没有“DROP”，只有在开始时有一个“DROP DATABASE”。如果不使用“--create”，情况将相反。 无论如何，这个序列会引发“public”模式已经存在的错误，因为从“template0”创建“mydb”已经导入了它（这是正常的，这就是模板数据库的目的）。 我不确定为什么“pg_restore”没有自动处理这种情况。也许当管理员决定自定义“template0”和/或更改“public”的用途时，这可能会导致不希望的副作用，即使我们不应该这样做。

- Daniel Vérité

---

我正在使用9.6版本，并且在没有使用clean的情况下指定了--create，但这并没有解决问题。 - Cerin

Answer 2

在我的情况下，原因是我使用的是postgresql-contrib版本11.2中的pg_restore来恢复由pg_dump 9.6创建的转储文件到一个PostgreSQL集群9.6。 当我将pg_restore降级回9.6后，这个"schema "public" already exists"错误消失了，并且恢复过程正常工作。

- Lu Liu

---

我遇到了类似的问题，但是与其尝试降级我的Postgres Docker镜像中的pg_restore命令，我选择盲目地添加了--schema=public，这样做起效了。 - jozxyqk

Answer 3

在我的情况下，我能够通过先运行CREATE DATABASE target_db;然后再运行pg_restore与--schema=public -d target_db来解决这个问题。这假设你只想要公共模式，但我认为这是一个相当常见的情况。

- spume

---

这是我在从9.5迁移到14时找到的唯一有效解决方案。使用--create选项的接受解决方案失败，因为备份文件中包含了一些扩展。创建一个空白数据库，添加扩展，然后改用--schema=public选项运行以下命令可以解决问题：pg_restore --dbname=existingdb --username=postgres --schema=public filename.backup。 - Neil Cresswell

An error occurred:

marked(): input parameter is undefined or null
Please report this to https://github.com/markedjs/marked.

- undefined

Answer 4

恢复包括公共模式，创建数据库也是如此。因此，在创建数据库后删除模式，以便恢复可以在没有错误的情况下创建它。 注意：以下假设-h -U -p是默认值，并且PGPASSWORD或.pgpass已设置。

    psql << XENDX
    drop database if exists DB_NAME;
    create database DB_NAME;
    \c DB_NAME;
    drop schema if exists public;
    create schema public;
    XENDX
    
    pg_restore -d DB_NAME FILE_CREATED_WITH_pg_dump

- DaJudge

Answer 5

我用以下方法解决了这个问题： 在db.sql文件中。

drop database if exists DB_name;
create database DB_name;
drop schema if exists public;
create schema public;
\q

然后

PGPASSWOD=DB_pass psql -U DB_user -h 127.0.0.1 < db.sql

然后

PGPASSWOD=DB_pass pg_restore -h 127.0.0.1 \ 
    -U DB_user -d DB_name --create --no-acl --no-owner DB_dump_FILE

我的pg_restore版本是11.6。

- user9869932

Answer 6

当使用版本12的pgsql-pg_restore恢复由pg_dump 9.6制作的转储时，我遇到了相同的错误。将其更改为使用版本9的pg_restore，问题得到解决。

- Y Bai

---

这对问题没有任何新的贡献，因为在一个回答中已经提到了。 - nbk