postgresql pg_restore 错误：角色 XXX 不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37271402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pg_restore error: role XXX does not exist
提问by Thalis K.
Trying to replicate a database from one system to another. The versions involved are 9.5.0 (source) and 9.5.2 (target).
尝试将数据库从一个系统复制到另一个系统。涉及的版本是 9.5.0（源）和 9.5.2（目标）。
Source db name is foodbwith owner pgdbaand target db name will be named foodb_devwith owner pgdev.
源 db 名称foodb与 ownerpgdba一起命名foodb_dev，目标 db 名称将与 owner一起命名pgdev。
All commands are run on the target system that will host the replica.
所有命令都在将托管副本的目标系统上运行。
The pg_dumpcommand is:
该pg_dump命令是：
pg_dump -f schema_backup.dump --no-owner -Fc -U pgdba -h $PROD_DB_HOSTNAME -p $PROD_DB_PORT -d foodb -s --clean;
This runs without errors.
这运行没有错误。
The corresponding pg_restoreis:
对应的pg_restore是：
pg_restore --no-owner --if-exists -1 -c -U pgdev -d foodb_dev schema_backup.dump
which throws error:
抛出错误：
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3969; 0 0 ACL public pgdba
pg_restore: [archiver (db)] could not execute query: ERROR: role "pgdba" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM pgdba;
GRANT ALL ON SCHEMA public TO pgdba;
GRANT ...
If I generate the dump file in plain text format (-Fp) I see it includes several entries like:
如果我以纯文本格式 ( -Fp)生成转储文件，我会看到它包含几个条目，例如：
REVOKE ALL ON TABLE dump_thread FROM PUBLIC;
REVOKE ALL ON TABLE dump_thread FROM pgdba;
GRANT ALL ON TABLE dump_thread TO pgdba;
GRANT SELECT ON TABLE dump_thread TO readonly;
that try to set privileges for user pgdbawho of course doesn't even exist as a user on the target system which only has user pgdev, and thus the errors from pg_restore.
尝试为用户设置权限，这些用户pgdba当然甚至不作为目标系统上的用户存在，只有 user pgdev，因此来自pg_restore.
On the source db the privileges for example of the dump_threadtable:
在源数据库上，例如dump_thread表的特权：
# \dp+ dump_thread
Access privileges
-[ RECORD 1 ]-----+--------------------
Schema | public
Name | dump_thread
Type | table
Access privileges | pgdba=arwdDxt/pgdba+
| readonly=r/pgdba
Column privileges |
Policies |
A quick solution would be to simply add a user pgdbaon the target cluster and be done with it.
一个快速的解决方案是简单地pgdba在目标集群上添加一个用户并完成它。
But shouldn't the --no-ownertake care of not including owner specific commands in the dump in the first place?
但是，难道不应该--no-owner首先在转储中不包括所有者特定的命令吗？
回答by Thalis K.
I realized the --no-owneris not the same as the -x. I added the -xto all pg_dumpcommands, which means:
我意识到这--no-owner与-x. 我在-x所有pg_dump命令中添加了，这意味着：
-x, --no-privileges do not dump privileges (grant/revoke)
which in effect excludes the offending GRANT/REVOKEcommands from the dump. Problem resolved.
这实际上从转储中排除了有问题的GRANT/REVOKE命令。问题解决了。
