尝试将一个系统中的数据库复制到另一个系统。涉及的版本是9.5.0(源)和9.5.2(目标)。
源数据库名称为foodb
,所有者为pgdba
,目标数据库名称将被命名为foodb_dev
,所有者为pgdev
。
所有命令都在托管副本的目标系统上运行。
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
)生成转储文件,我会看到它包括多个条目,例如:
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
的错误。
在源数据库中,例如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 |
一个快速的解决方案是在目标集群上简单地添加一个名为pgdba
的用户,然后完成操作。
但是,在转储数据之前,--no-owner
选项不应该负责排除拥有者特定命令吗?
pg_restore --no-privileges --no-owner ...
allows you to specify not to include ownership or privileges at the time of restore whether or not those privileges and ownerships were included in thepg_dump
- user1448319