pg_restore：[存档（db）]无法执行查询：错误：模式“ public”已经存在

我正在使用pg_dump / pg_restore备份和还原PostgreSQL数据库，但是从pg_restore收到了一些错误消息（以及非零退出状态）。我尝试了一个超级简单的基本案例（下面概述），但仍然出现以下错误：

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

重现步骤：

安装一个全新的原始Ubuntu 14.04发行版（我在此Vagrant框中使用Vagrant）。
安装PostgreSQL 9.3，配置为允许来自任何Linux用户的PostgreSQL用户“ postgres”进行本地连接。

创建测试数据库。我只是在做：

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

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

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

从mydb的数据表中删除一些行，以便我们能够确定是否成功恢复了数据。

使用以下方法恢复数据库：

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

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

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;



WARNING: errors ignored on restore: 1

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

我做错了吗？为什么会看到此错误？

#1 楼

该错误无害，但要消除该错误，我认为您需要将此恢复分成两个命令，例如：

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联机帮助页）：

--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模式已存在的错误，因为从mydb创建template0已经导入了它（这很正常，这是模板数据库的关键所在）。

我不确定为什么pg_restore无法自动处理这种情况。也许当管理员决定自定义template0和/或更改public的目的时，即使我们不应该这样做，也会导致不良的副作用。

我使用的是9.6，并且指定--create不干净无法解决该问题。

–塞林
18年11月29日在19:47

#2 楼

就我而言，原因是我使用的是来自postgresql-contrib版本11.2的pg_restore，将pg_dump 9.6创建的转储恢复到PostgreSQL集群9.6。

我将pg_restore降级到9.6后， schema "public" already exists错误消失了，恢复过程像以前一样进行。

但是您是否使用pg_restore 9.6将转储还原到了postgres 11.2数据库中？

– Mariano Ruiz
19-10-24在12:32

@MarianoRuiz我认为我的原始答案很明确：“我使用的是来自postgresql-contrib版本11.2的pg_restore，将pg_dump 9.6创建的转储恢复到PostgreSQL集群9.6。”因此，您的问题是：不，我没有。我的pg_restore是11.2而pg集群是9.6

–刘璐
19年11月21日在8:22

#3 楼

还原包括公共模式，因此创建数据库。因此，在创建数据库之后删除架构，以便还原可以正确地创建它。

注意：以下假定-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

#4 楼

我用以下方法解决了问题：

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

#5 楼

就我而言，我能够通过首先运行CREATE DATABASE target_db;然后与pg_restore一起运行--schema=public -d target_db来解决此问题。假设您只需要公共模式，但是我认为这是一个很常见的情况。

编程黑洞网