PostgreSQL转储恢复失败：排序规则创建顺序与视图依赖冲突

这是PostgreSQL的固有问题吗？ #

是的，这属于pg_dump在依赖关系解析上的已知bug。当视图直接或间接引用自定义排序规则时，pg_dump的依赖分析逻辑可能无法正确识别排序规则是视图的前置依赖，导致生成的转储文件中视图创建语句排在排序规则之前。这类问题在特定版本组合中更容易触发，你使用的16.3版本pg_dump仍存在该排序逻辑缺陷。

可行的解决方式 #

1. 手动调整转储文件的创建顺序 #

打开生成的steel42prod_2024-09-05.sql文件，定位到CREATE COLLATION public.de_pos的语句块，将其剪切到所有引用该排序规则的表、视图创建语句之前。确保排序规则先被创建，后续对象依赖它时就不会出现不存在的错误。

2. 分阶段导出与恢复 #

• 第一阶段：导出时排除问题视图，先恢复包含排序规则和表的基础结构：

  pg_dump --host=localhost --port=5432 --username=steel42 --file=steel42prod_base.sql --clean --no-owner --superuser=steel42 --if-exists --role=steel42 --exclude-table-data=document_storage --exclude-table=mengenübersicht_global --verbose steel42prod
  

  执行基础结构恢复：

  psql --echo-all --host=localhost --port=5432 --username=steel42 --set ON_ERROR_STOP=on steel42 <steel42prod_base.sql
  

• 第二阶段：单独导出并恢复视图：

  pg_dump --host=localhost --port=5432 --username=steel42 --file=steel42prod_view.sql --clean --no-owner --superuser=steel42 --if-exists --role=steel42 --table=mengenübersicht_global --verbose steel42prod
  

  执行视图恢复：

  psql --echo-all --host=localhost --port=5432 --username=steel42 --set ON_ERROR_STOP=on steel42 <steel42prod_view.sql
  

3. 升级目标端psql版本 #

源端pg_dump为16.3，目标端psql是16.2，尝试将目标端psql升级到16.3或更高版本，部分依赖解析问题可能在新版本中被修复，能直接解决创建顺序颠倒的问题。

4. 临时启用ON_ERROR_STOP=off（不推荐长期使用） #

如你所述，设置ON_ERROR_STOP=off可以完成恢复，因为psql会跳过报错继续执行，后续排序规则创建后，视图可能在后续流程中被正确创建。但这种方式存在风险，可能掩盖其他潜在错误，仅适合紧急临时恢复场景。

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