PostgreSQL 12多Schema数据库完整备份及恢复异常解决

问题原因 #

你之前使用的 pg_dump -a ... 命令中，-a 参数是仅备份数据的意思，不会导出数据库的 schema 结构（包括 schema 本身、表、序列、约束、索引等核心基础对象）。本地空数据库没有这些结构，恢复数据时自然会出现“schema 不存在”“relation 不存在”这类错误。

正确的完整备份命令 #

要备份包含所有 schema 结构、数据、序列、约束、索引等的完整数据库，直接移除 -a 参数即可。你已经在用的 -Fc 是自定义格式备份，具备压缩率高、恢复灵活的优势，推荐保留：

pg_dump -d my_database -h localhost -U my_user -Fc > my_database.full.2023.08.25.pgdump

如果需要精准指定要备份的 schema（比如 public 和另外 3 个目标 schema），可以用 -n 参数逐个声明，避免备份无关内容：

pg_dump -d my_database -h localhost -U my_user -Fc -n public -n aaa -n bbb -n ccc > my_database.full.2023.08.25.pgdump

将上述命令中的 aaa、bbb、ccc 替换为你实际需要备份的另外 3 个 schema 名称。

恢复步骤 #

1. 提前创建本地空数据库（如果还未创建）：

createdb -h localhost -U my_user my_local_database

2. 使用 pg_restore 恢复备份（自定义格式备份必须用该工具）：

pg_restore -d my_local_database -h localhost -U my_user my_database.full.2023.08.25.pgdump

如果想让恢复过程自动创建目标数据库（无需提前手动创建），可以加上 -C 参数，此时需要连接到 PostgreSQL 默认的系统数据库 postgres：

pg_restore -C -d postgres -h localhost -U my_user my_database.full.2023.08.25.pgdump

注意事项 #

• 尽量保证本地 PostgreSQL 版本与源数据库版本一致，避免版本差异引发兼容性问题。
• 备份和恢复使用的数据库用户需要具备足够权限：备份需有所有备份对象的 SELECT 权限，恢复需有 CREATE、ALTER 等权限。
• 如果源数据库使用了第三方扩展（如 pg_trgm、uuid-ossp），需确保本地数据库提前安装对应扩展，或在备份时确认扩展定义已被包含。

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