Azure PostgreSQL单服务器生产库迁预生产库：对象权限同步问题

问题场景 #

• 源环境：Azure Database for PostgreSQL 单服务器（版本11），87GB生产数据库
• 目标环境：同区域、同版本的预生产服务器
• 当前状态：通过pg_dump+pg_restore完成数据迁移，但因恢复命令带了--no-privileges参数，导致对象级权限未同步，只能手动逐个配置，效率极低
• 补充：目标端已存在应用所需的同名同密码角色，非应用角色密码与生产环境不同但不影响业务

问题根因 #

你的pg_restore命令中添加了--no-privileges参数，该参数会直接跳过所有权限相关的恢复逻辑。而你的pg_dump命令默认会包含对象权限信息，备份文件里其实已经有权限数据，只是恢复时被跳过了。

解决方案 #

方案1：调整恢复参数重新执行迁移（适合可重新迁移的场景） #

删除pg_restore命令中的--no-privileges参数，保留其他必要配置即可：

pg_restore.exe --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --role "xxxx" --dbname "xxxx" --section=pre-data --section=data --section=post-data --no-owner --disable-triggers --single-transaction --verbose "D:\xxxx.sql"

注：保留--no-owner是因为目标端已有同名角色，避免修改对象所有者时引发错误；若需要同步对象所有者，可直接移除该参数，但需确保目标端角色与源端完全匹配。

方案2：单独备份并恢复对象级权限（适合已完成数据恢复的场景） #

不想重新全量迁移的话，可单独导出源库权限再导入目标库：

1. 导出源库对象权限
   使用pg_dump仅导出权限相关内容，生成纯SQL文件：

pg_dump.exe --file "D:\PostgreSQL_backups\permissions_only.sql" --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --verbose --role "xxxx" --format=p --section=post-data --exclude-table-data=* "xxxx"

--exclude-table-data=*确保只导出结构和权限，不包含数据；--format=p生成纯SQL格式，方便直接执行或手动调整。

2. 导入权限到目标库
   将生成的SQL文件在目标库中执行，可通过psql或pgAdmin4运行：

psql.exe --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --dbname "xxxx" --file "D:\PostgreSQL_backups\permissions_only.sql"

执行前请检查SQL文件中的角色名称是否与目标端一致，避免因角色不存在导致报错。

方案3：用pg_dumpall同步全量角色与权限 #

若需要同步服务器级角色（含密码）和所有数据库的对象权限，可使用pg_dumpall：

1. 导出服务器级角色

pg_dumpall.exe --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --verbose --roles-only --file "D:\PostgreSQL_backups\server_roles.sql"

2. 导出单个数据库的对象权限
   参考方案2的pg_dump命令单独导出目标数据库的权限。
3. 依次恢复到目标服务器
   先执行server_roles.sql同步角色，再执行数据库权限SQL文件。

注意事项 #

• 目标端角色名称必须与源端完全一致，否则权限恢复会失败；若角色名称不同，可手动替换SQL文件中的角色名。
• Azure PostgreSQL单服务器的部分操作需要超级用户权限，确保使用的账号具备相应权限。
• 执行权限恢复前，建议先在测试环境验证，避免影响预生产业务。

内容的提问来源于stack exchange，提问作者Ivan IP