PostgreSQL特定Schema备份与恢复问题求助

问题详情 #

尝试备份并恢复PostgreSQL中的特定Schema abc 时遇到多类报错，以下是试过的操作及对应错误：

方法1：目录格式备份恢复 #

备份命令：

pg_dump -Fd -j 4 -v -h db-cluster-ha.postgresql.svc.cluster.local -n 'abc' -U abc -d abc -f abc

恢复命令：

pg_restore -Fd -j 4 -v -U abc_bk -h db-cluster-ha.postgresql.svc.cluster.local -d abc abc

已执行的权限配置（未解决问题）：

CREATE USER abc_bk WITH PASSWORD 'abc_bk';
GRANT USAGE, CREATE ON SCHEMA 'abc' TO abc_bk;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA 'abc' TO abc_bk;
ALTER DATABASE abc SET search_path TO "abc";

恢复报错：

pg_restore: error: could not execute query: ERROR: 关系 "abc.document" 不存在。

方法2：COPY数据恢复 #

恢复命令：

pg_restore -U abc -h db-cluster-ha.postgresql.svc.cluster.local -d abc -n 'abc'abc.sql

恢复报错：

ERROR: 未终止的引号字符串，位于 "'Subject" 附近。

方法3：--inserts选项备份 #

备份命令：

pg_dump --inserts -j 4 -v -h db-cluster-ha.postgresql.svc.cluster.local -n 'abc' -U abc -d abc -f inserts_abc.sql

备份报错：

ERROR: 未终止的引号字符串，位于 "'Subject" 附近。

解决步骤 #

方法1：修复"关系不存在"错误 #

1. 先确认并创建Schema
   恢复前必须确保目标数据库中存在abc Schema，执行以下SQL：

CREATE SCHEMA IF NOT EXISTS abc;

2. 补充权限配置
   现有权限只覆盖了表，还需授予序列、函数等对象的权限，同时给用户设置默认搜索路径：

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc TO abc_bk;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA abc TO abc_bk;
ALTER USER abc_bk SET search_path TO abc, public;

3. 恢复时明确指定Schema
   修改pg_restore命令，添加--schema=abc参数，确保对象正确归属到目标Schema：

pg_restore -Fd -j 4 -v -U abc_bk -h db-cluster-ha.postgresql.svc.cluster.local -d abc --schema=abc abc

方法2、3：修复"未终止引号字符串"错误 #

这类错误源于数据中含未转义的特殊字符（如单引号），或命令参数冲突：

1. 移除并行参数-j
--inserts选项和并行备份-j不兼容，生成INSERT语句时无法并行，方法3的备份命令需改为：

pg_dump --inserts -v -h db-cluster-ha.postgresql.svc.cluster.local -n 'abc' -U abc -d abc -f inserts_abc.sql

2. 使用可靠的备份格式
   优先选择目录格式-Fd或自定义格式-Fc，这类二进制格式不会出现字符转义问题，比SQL文本格式更稳定。
3. 强制转义标识符
   如果必须用SQL文本格式，添加--quote-all-identifiers选项确保所有标识符被正确转义：

pg_dump --inserts --quote-all-identifiers -v -h db-cluster-ha.postgresql.svc.cluster.local -n 'abc' -U abc -d abc -f inserts_abc.sql

4. 避免手动修改备份文件
   如果备份文件被手动编辑过，大概率会破坏语法结构，务必使用原始备份文件进行恢复。

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