database - 从没有角色和/或权限的 .sql 转储文件恢复 postgres 架构？

Question

尝试从另一台服务器上创建的 .sql 转储文件恢复 postgres 数据库架构时收到以下错误:

REVOKE psql:backup.sql:158885: 错误:角色“server_name”不存在

转储文件是使用 pg_dump -U username "server_name"-n schema_name > schema_name.sql 创建的

如何创建没有角色和/或权限的转储文件？

Answer 1

通过使用标志 --no-owner 和 --no-acl

Do not output commands to set ownership of objects to match the original database. By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script). With -O, any user name can be used for the initial connection, and this user will own all the created objects.

防止恢复访问权限(授予/撤销命令):

pg_dump --no-owner --no-acl -U username "server_name" -n schema > schema.sql

https://www.postgresql.org/docs/9.2/static/app-pgrestore.html