postgresql - 如何恢复 postgres 备份 - 错误 : cannot drop the currently open database

Question

我正在尝试从不再配置的系统中恢复 postgres 备份，但我遇到了一系列错误，具体取决于我的尝试。

• 我只有备份，没有原始服务器可供引用
• 我过去曾从这些生产备份中成功恢复，但自从上次成功后我的本地环境变得一团糟(例如通过 Homebrew 重新安装了 postgres)
• 我相信一位前同事(不再可用)已成功恢复此特定备份
• 我遇到过同样的错误:
  • 尝试使用 postgresql@9.4 进行备份
  • 尝试使用以前已知良好的数据库版本(使用 postgres 11)

(我已阅读相关/建议的问题，但它们似乎不相关)

备份是使用以下命令创建的(在 bash 脚本中):

pg_dump --schema=public -Fc

用于恢复脚本的命令(由备份脚本回显为帮助文本)是:

dropdb ${PGDATABASE}
createdb ${PGDATABASE}
time pg_restore \
    -j4 \
    -d ${PGDATABASE} \
    --create \
    --no-privileges \
    --no-owner \
    --clean \
    --if-exists \
    --exit-on-error \
    "${dirname}/${filename}"

当我在本地运行恢复命令时，我得到以下信息:

time pg_restore -j4 -d ${PGDATABASE} --create --no-privileges --no-owner --clean --if-exists --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4954; 1262 962277 DATABASE mydb mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop the currently open database
    Command was: DROP DATABASE IF EXISTS mydb;

real    0m0.049s
user    0m0.019s
sys    0m0.012s

以前，我只会在运行几分钟后出现“磁盘空间不足错误”，但正如您从 time 输出中看到的那样，这个错误几乎是立即发生的!

备份是使用压缩 -Fc 创建的，我可以使用 pg_restore backup.pg_dump > backup.sql 将备份解压缩为纯文本，但是我无法在其中找到与 DROP DATABASE 相关的任何命令:/

-- Dumped from database version 9.6.11
-- Dumped by pg_dump version 11.3 (Ubuntu 11.3-1.pgdg14.04+1)

我看到数据库最初是从 9.6.11 转储的，但我有理由相信我之前使用 PG 10 成功恢复了...

我试过很多东西，比如:

time pg_restore -d ${PGDATABASE} --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7; 2615 1033373 SCHEMA public mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

ptim:dropdb mydb; createdb mydb
ptim:directory ptim$ dropdb mydb

ptim:directory ptim$ time pg_restore -d ${PGDATABASE} --create --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

# guess I misunderstood the create flag!

ptim:directory ptim$ dropdb mydb; createdb mydb
dropdb: database removal failed: ERROR:  database "mydb" does not exist

ptim:directory ptim$ time pg_restore -d ${PGDATABASE} --create --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4954; 1262 962277 DATABASE mydb mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  database "mydb" already exists
    Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

我也尝试从明文转储中恢复，但没有成功:

psql -U ptim -d mydb -1 -f ../backups/backup__2019-09-03_16-00-19.sql

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
psql:../backups/backup__2019-09-03_16-00-19.sql:23: ERROR:  schema "public" already exists
psql:../backups/backup__2019-09-03_16-00-19.sql:26: ERROR:  current transaction is aborted, commands ignored until end of transaction block

# snip... repeated, and varied errors follow

有什么建议吗？!

Answer 1

感谢@a_horse_with_no_name 的提示，我了解到:

-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.

(在 dba.stackexchange: why pg_restore ignores --create 中有进一步解释。请注意，--create 在 pg_restore 的较新版本中具有更多不相关的效果)

我的解决方案是删除 --create 参数:

dropdb ${PGDATABASE}
createdb ${PGDATABASE}
time pg_restore \
    -j4 \
    -d ${PGDATABASE} \
    --no-privileges \
    --no-owner \
    --clean \
    --if-exists \
    --exit-on-error \
    backup.pg_dump