postgresql 带有 -C 选项的 pg_restore 不会创建数据库

pg_restore with -C option does not create the database

提问by Swapnil17

I am using pg_dump and pg_restore for backup and restore of postgres database.

我正在使用 pg_dump 和 pg_restore 来备份和恢复 postgres 数据库。

Here is some information from documentation that will be relevant for this question For Pg_restore, -C option is described as follows

以下是与此问题相关的文档中的一些信息对于 Pg_restore，-C 选项描述如下

-C

--create

Create the database before restoring into it. If --clean is also specified, > > drop and recreate the target database before connecting to it. When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

-C

- 创建

在恢复之前创建数据库。如果还指定了 --clean，则在连接之前删除并重新创建目标数据库。使用此选项时，以 -d 命名的数据库仅用于发出初始 DROP DATABASE 和 CREATE DATABASE 命令。所有数据都恢复到出现在存档中的数据库名称。

However even when I use this option with pg_restore, I get following error

但是，即使我将此选项与 pg_restore 一起使用，我也会收到以下错误

pg_restore: [archiver (db)] connection to database "test" failed: FATAL: > database "test" does not exist

pg_restore: [archiver (db)] 连接到数据库“test”失败：致命：> 数据库“test”不存在

As per the description the -C option should have created the missing database. However it does not in my case.

根据描述，-C 选项应该创建了丢失的数据库。但是，在我的情况下并非如此。

Following are the steps that I did for backup and restore:

以下是我为备份和恢复所做的步骤：

1. Use pg_dump to backup database

1. 使用 pg_dump 备份数据库

pg_dump -N backup -d test --format custom -v -h xxhostxx -p 5432 -U xxuserxx --lock-wait-timeout 300000 -f test_pg_dump.dmp

Note: not using -C option since it is meaningful for the plain-text formats only

注意：不使用 -C 选项，因为它仅对纯文本格式有意义

1. Deleted the testdatabase

2. use pg_resore to restore database

   pg_restore -C -d test -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp**
   

2. 删除了测试数据库

3. 使用 pg_resore 恢复数据库

   pg_restore -C -d test -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp**
   

I cannot understand what is the issue here! Am I doing anything wrong ? Let me know if more information is needed.

我不明白这里有什么问题！我做错了什么吗？如果需要更多信息，请告诉我。

采纳答案by Jendrusk

Exactly like @Eelke said - you've got in file wrote 'create database' so this database does not exist when you're running script... That's what for there is always 'postgres' database. Try this:

就像@Eelke 所说的那样-您在文件中写入了“创建数据库”，因此在运行脚本时该数据库不存在……这就是始终存在“postgres”数据库的原因。试试这个：

pg_restore -C -d postgres -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp**

And this should:

这应该：

1. connect to postgres database
2. Create test database
3. Disconnect from postgres and connect to test
4. Upload data into database

1. 连接到 postgres 数据库
2. 创建测试数据库
3. 断开与 postgres 的连接并连接到测试
4. 上传数据到数据库

Of course check who is owner of postgres database - in most cases you have to run this as user 'postgres'.

当然，检查谁是 postgres 数据库的所有者——在大多数情况下，您必须以用户“postgres”的身份运行它。

回答by Eelke

The following quote doesn't mean what you might think it means. I also had to read it thrice before realizing what they were saying.

以下引用并不意味着您可能认为的含义。在意识到他们在说什么之前，我还必须阅读三次。

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

使用此选项时，以 -d 命名的数据库仅用于发出初始 DROP DATABASE 和 CREATE DATABASE 命令。所有数据都恢复到出现在存档中的数据库名称。

It means that pg_restore will initially connect to the database specified with -d. It will NOT create that database. It creates a database with the name from the archive you are restoring and restores the data into that database.

这意味着 pg_restore 将最初连接到用 -d 指定的数据库。它不会创建该数据库。它使用您正在恢复的存档中的名称创建一个数据库，并将数据恢复到该数据库中。

回答by Andres Leon Rangel

It never worked for me so this command creates the database

它从来没有对我有用所以这个命令创建了数据库

createdb -h HOST -U USER -W DB_NAME

then execute the pg restore

然后执行 pg restore

pg_restore -d DB_NAME -v -h HOST -p PORT -U USER DUMP_FILE.dump**

End of story

故事结局