pg_restore错误：角色XXX不存在 [英] pg_restore error: role XXX does not exist

问题描述

尝试将数据库从一个系统复制到另一个系统。涉及的版本是9.5.0（源）和9.5.2（目标）。

Trying to replicate a database from one system to another. The versions involved are 9.5.0 (source) and 9.5.2 (target).

源数据库名称为 foodb ，所有者为 pgdba ，目标数据库名称将被命名为 foodb_dev ，所有者为 pgdev 。

Source db name is foodb with owner pgdba and target db name will be named foodb_dev with owner pgdev.

所有命令都将在承载副本的目标系统上运行。

All commands are run on the target system that will host the replica.

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;

运行没有错误。

相应的 pg_restore 是：

    pg_restore --no-owner --if-exists -1 -c -U pgdev -d foodb_dev schema_backup.dump

这会抛出错误：

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

如果我以纯文本格式生成转储文件（ -Fp ），我看到它包含一些条目，如：

If I generate the dump file in plain text format (-Fp) I see it includes several entries like:

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;

尝试为用户 pgdba 设置权限当然，在目标系统上只有用户 pgdev 的用户甚至不存在谁，因此，来自 pg_restore 。

that try to set privileges for user pgdba who 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.

在源数据库上，例如 dump_thread 表的特权：

On the source db the privileges for example of the dump_thread table:

# \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          |

一种快速的解决方案是简单地添加用户 pgdba 在目标群集上完成。

A quick solution would be to simply add a user pgdba on the target cluster and be done with it.

但是-无所有者不应避免在转储中不包括所有者特定的命令

But shouldn't the --no-owner take care of not including owner specific commands in the dump in the first place?

推荐答案

我意识到-无主与 -x 不同。我在所有 pg_dump 命令中添加了 -x ，这意味着：

I realized the --no-owner is not the same as the -x. I added the -x to all pg_dump commands, which means:

-x, --no-privileges          do not dump privileges (grant/revoke)

实际上从转储中排除了有问题的 GRANT / REVOKE 命令。问题已解决。

which in effect excludes the offending GRANT/REVOKE commands from the dump. Problem resolved.

这篇关于pg_restore错误：角色XXX不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！