如何用生产库刷新QA库并保留权限（AWS Aurora PostgreSQL）

核心思路 #

要实现完全替换QA库为生产副本，同时保留QA的自定义用户与权限，关键是先备份QA的权限配置，待QA库同步生产数据/结构后，再恢复原有权限，避免每次刷新后手动重置。

步骤1：备份QA库的用户与权限 #

先导出QA库中所有自定义用户、角色及对应的权限配置，保存为SQL脚本：

导出自定义角色（排除AWS内置角色）

执行以下SQL查询，将结果保存为qa_roles.sql：

SELECT 'CREATE ROLE ' || quote_ident(rolname) || ' WITH ' ||
       CASE WHEN rolsuper THEN 'SUPERUSER ' ELSE '' END ||
       CASE WHEN rolinherit THEN 'INHERIT ' ELSE '' END ||
       CASE WHEN rolcreaterole THEN 'CREATEROLE ' ELSE '' END ||
       CASE WHEN rolcreatedb THEN 'CREATEDB ' ELSE '' END ||
       CASE WHEN rolcanlogin THEN 'LOGIN ' ELSE '' END ||
       CASE WHEN rolreplication THEN 'REPLICATION ' ELSE '' END ||
       CASE WHEN rolbypassrls THEN 'BYPASSRLS ' ELSE '' END ||
       COALESCE('PASSWORD ''' || rolpassword || ''' ', '') ||
       COALESCE('VALID UNTIL ''' || rolvaliduntil || ''' ', '') || ';'
FROM pg_roles 
WHERE rolname NOT IN ('postgres', 'rdsadmin', 'rds_superuser', 'rds_replication', 'rds_iam', 'rds_password');

导出数据库与表权限

执行以下SQL查询，将结果保存为qa_permissions.sql：

-- 数据库级权限
SELECT 'GRANT ' || privilege_type || ' ON DATABASE ' || quote_ident(datname) || ' TO ' || quote_ident(grantee) || ';'
FROM pg_database d
JOIN pg_privileges p ON p.objoid = d.oid
JOIN pg_roles r ON p.grantee = r.oid
WHERE d.datname = 'your_qa_db_name'; -- 替换为实际QA库名

-- 表级权限
SELECT 'GRANT ' || privilege_type || ' ON TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' TO ' || quote_ident(r.rolname) || ';'
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_privileges p ON p.objoid = c.oid
JOIN pg_roles r ON p.grantee = r.oid
WHERE c.relkind = 'r' 
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

步骤2：将QA库替换为生产副本 #

根据你的需求，选择以下两种方式之一：

方式A：使用Aurora跨区域快照恢复（适合全量快速替换）

1. 暂停QA库的所有业务连接，避免数据冲突。
2. 从生产Aurora集群创建跨区域快照，同步到QA所在区域。
3. 用该快照恢复为新的DB实例，替换原QA库（操作前建议备份原QA库的快照，以防意外）。

方式B：使用pg_dump/pg_restore（适合灵活控制导出范围）

由于AWS Aurora限制pg_dumpall，改用pg_dump导出生产库的结构与数据（不含权限和所有者）：

# 导出生产库（-x 不导权限，-O 不导所有者，-Fc 用自定义压缩格式）
pg_dump -h <生产集群端点> -U <生产超级用户> -d <生产库名> -x -O -Fc -f production_dump.dmp

将导出文件传输到QA区域后，导入QA库（-c 参数会先删除现有对象，确保完全替换）：

# 导入到QA库
pg_restore -h <QA集群端点> -U <QA超级用户> -d <QA库名> -c production_dump.dmp

步骤3：恢复QA的自定义权限 #

连接到恢复后的QA库，执行之前备份的权限脚本：

# 先恢复角色
psql -h <QA集群端点> -U <QA超级用户> -d <QA库名> -f qa_roles.sql

# 再恢复权限
psql -h <QA集群端点> -U <QA超级用户> -d <QA库名> -f qa_permissions.sql

执行完成后，验证用户角色、数据库/表权限是否与刷新前一致。

可选：自动化流程 #

将上述步骤编写成Shell脚本，通过AWS Lambda、EC2定时任务（Cron）或AWS Step Functions实现定期自动刷新，减少手动操作成本。执行前务必确保QA库无业务写入，执行后做简单的正确性验证（如查询关键表数据、测试用户登录权限）。

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