Azure PostgreSQL弹性服务器pg_dump/psql/pg_restore迁移报错求助

核心原因 #

Azure PostgreSQL弹性服务器中，默认的postgres角色是系统锁定角色，无法直接登录；而你的备份文件是通过本地postgres角色生成的，包含SET ROLE postgres或OWNER TO postgres语句，导致使用Azure管理员角色恢复时因权限不足报错。

解决方案 #

方法1：为Azure管理员角色授予postgres权限 #

通过授权让你的管理员角色拥有切换到postgres角色的权限，无需修改备份文件：

1. 连接到Azure目标数据库：

psql "host=project-development2.postgres.database.azure.com port=5432 user=myuser dbname=staging sslmode=require"

2. 执行SQL授权：

GRANT postgres TO myuser;

3. 重新执行恢复命令：

psql "host=project-development2.postgres.database.azure.com port=5432 user=myuser dbname=staging sslmode=require" -1 -f ./project-io-staging-4-Sep-2024.sql

方法2：使用Azure管理员角色重新备份（推荐） #

直接用与目标服务器匹配的角色生成备份，从根源避免权限问题：

• 如果源库可以创建同名角色：先在本地库创建myuser角色并赋予所有对象权限，再执行备份：

pg_dump -U myuser -h localhost -p 5432 staging -n public --format=p > project-io-staging-new.sql

• 如果源库只有postgres角色：备份时指定--role参数，让备份文件以myuser作为对象所有者：

pg_dump -U postgres -h localhost -p 5432 staging -n public --role=myuser --format=p > project-io-staging-new.sql

• 恢复到Azure服务器：

psql "host=project-development2.postgres.database.azure.com port=5432 user=myuser dbname=staging sslmode=require" -c "drop schema public cascade;"
psql "host=project-development2.postgres.database.azure.com port=5432 user=myuser dbname=staging sslmode=require" -n public -1 -f ./project-io-staging-new.sql

方法3：修改现有备份文件的角色引用 #

如果无法重新备份，可批量替换备份文件中的角色信息：

• Linux/macOS使用sed命令替换：

sed -i '' 's/SET ROLE postgres;/SET ROLE myuser;/g' project-io-staging-4-Sep-2024.sql
sed -i '' 's/OWNER TO postgres;/OWNER TO myuser;/g' project-io-staging-4-Sep-2024.sql

• Windows使用PowerShell替换：

(Get-Content project-io-staging-4-Sep-2024.sql) -replace 'SET ROLE postgres;', 'SET ROLE myuser;' | Set-Content project-io-staging-4-Sep-2024.sql
(Get-Content project-io-staging-4-Sep-2024.sql) -replace 'OWNER TO postgres;', 'OWNER TO myuser;' | Set-Content project-io-staging-4-Sep-2024.sql

替换完成后执行恢复命令即可。

并行备份恢复的正确操作 #

若使用并行备份恢复，需确保角色一致性：

1. 并行备份（指定角色）：

pg_dump -Fd -j 2 staging -h localhost -p 5432 -U postgres --role=myuser -N cron -f ./project-io-staging-new.dump

2. 并行恢复：

pg_restore -j 2 -d staging -h project-development2.postgres.database.azure.com -p 5432 -U myuser ./project-io-staging-new.dump

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