如何为PostgreSQL创建恢复时不报错的只读备份用户

我已经尝试多日，希望在PostgreSQL数据库中创建一个仅可用于备份的只读用户，目前我在应用中通过生成.bat文件调用pg_dump的方式实现备份，具体实现代码如下：

cxMemo1.Clear;
cxMemo1.Lines.Add('@Echo off ');
cxMemo1.Lines.Add('set PGUSER=backup');
cxMemo1.Lines.Add('set PGPASSWORD=backup');
cxMemo1.Lines.Add('c:');
cxMemo1.Lines.Add('cd C:\Program Files (x86)\PostgreSQL\10\bin\ ');
cxMemo1.Lines.Add('@echo "Loading..." ');
cxMemo1.Lines.Add('pg_dump.exe -h localhost -F custom -p 5432  -f ' +Path+ ' BDFOCOG');

cxMemo1.Lines.SaveToFile(ExtractFilePath(Application.ExeName) +'backup.bat');
ShellExecute(Application.Handle, nil, PChar('backup.bat'), nil, nil, SW_HIDE);

我使用设置为只读的PostgreSQL超级用户完成备份后尝试恢复，数据虽完成创建但出现报错（见Image1：）。

编辑1：Image2展示了我将备份用户的default_transaction_read_only属性设为true后，使用pg_restore执行恢复时出现的报错，错误触发在数据库恢复外键及其他变更操作的环节（见Image2：）。

编辑2：目前所有数据都已成功迁移，数据库表面运行正常，但我担心PostgreSQL和pg_restore提示恢复未成功的情况下可能存在数据损坏风险。请问我该如何解决该问题，实现数据库备份的干净恢复？

解决方案 #

现有恢复的风险排查 #

你遇到的报错本质是恢复操作的权限/事务配置错误导致的元数据写入失败，和备份文件本身的完整性无关，你可以通过两步验证确认现有数据是否完好：

• 核对业务数据一致性：对所有业务表分别执行SELECT COUNT(*) FROM 表名，对比原库和恢复后库的行数，数值一致说明业务数据没有丢失
• 检查约束有效性：执行SELECT conname, conrelid::regclass FROM pg_constraint WHERE convalidated = false;，如果返回空结果，说明所有外键、唯一约束、非空约束等都已正常生效，不存在隐性数据问题
  如果两项验证都通过，现有恢复的数据库可以正常使用，没有数据损坏风险。

正确的只读备份用户配置+无错恢复流程 #

你之前的问题核心是两点：一是给备份用户额外设置了default_transaction_read_only属性，二是恢复时误用了只有只读权限的备份用户。按以下步骤配置即可实现全程无报错的干净备份恢复：

1. 创建最小权限的专用备份用户（无需使用超级用户，符合安全规范）
   使用postgres超级用户登录数据库，执行以下SQL：

-- 创建备份用户
CREATE ROLE backup_user WITH LOGIN PASSWORD '自定义安全密码';
-- 授予数据库连接权限
GRANT CONNECT ON DATABASE BDFOCOG TO backup_user;
-- 授予所有表的读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
-- 授予序列读权限，避免自增字段备份异常
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;
-- 配置自动授予后续新建表/序列的读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup_user;

2. 删除多余的只读配置
   pg_dump本身会自动在只读事务中执行备份操作，不需要单独给用户配置默认只读事务，执行以下SQL删除配置即可：

ALTER ROLE backup_user RESET default_transaction_read_only;

3. 恢复操作规范
   恢复时不要使用backup_user用户，改用有写入、建表、修改元数据权限的用户（比如postgres超级用户，或业务库的管理员用户），参考恢复命令：

pg_restore.exe -h localhost -p 5432 -U postgres -d BDFOCOG -v 备份文件路径

内容的提问来源于stack exchange，提问作者Douglas Conceição