PostgreSQL 9.5备份恢复至9.6时遇约束错误求助

我来帮你拆解并解决这次跨版本恢复遇到的两类核心问题——唯一约束冲突和外键依赖失败，下面是一步步的排查和修复方案：

问题根源分析 #

你遇到的错误本质上是两个独立但关联的问题：

1. 唯一约束创建失败：恢复数据后，目标表中存在违反唯一约束的重复记录（比如xtb_rulesets里有两条name=FEL.DATI_RIEPILOGO的行），导致执行ALTER TABLE ADD CONSTRAINT时触发报错。虽然原数据库有这些约束，但要么是恢复时目标库残留了旧数据，要么是备份过程中数据导出的逻辑导致重复导入。
2. 外键约束创建失败：外键依赖的表（比如xtb_cultures）没有对应的唯一约束/主键，这大概率是因为依赖表的约束创建失败，或者恢复顺序导致外键先于依赖约束被创建。

分步解决方案 #

第一步：先清理重复数据，解决唯一约束冲突 #

因为唯一约束错误是导致后续外键问题的源头，先处理这个：

1. 暂停现有恢复，重新分步导入
   先跳过约束导入，只导入表结构和数据，方便清理重复：

   • 第一步：导入空表结构（不含约束）

     pg_restore --clean --if-exists --no-owner --schema-only --exclude-table-data=xtb_process_runs --exclude-table-data=xtb_app_properties --exclude-table-data=xtb_doc_properties --exclude-table-data=xtb_export_properties --exclude-table-data=xtb_file_storage --exclude-table-data=xtb_import_properties --exclude-table-data=xtb_org_prop_template --exclude-table-data=xtb_org_props --exclude-table-data=xtb_process_properties --exclude-table-data=xtb_rule_functions --exclude-table-data=xtb_rules --exclude-table-data=xtb_templates --exclude-table-data=xtb_trigger_properties --exclude-table-data=xtb_triggers --exclude-table-data=xtb_user_preferences --exclude-table-data=xtb_variables --exclude-table-data=xtb_events -h localhost -p 5432 -d DATABASE_NAME -U user DATABASE_DUMP.dmp
     

   • 第二步：删除所有会报错的约束（避免后续导入数据时触发）

     -- 替换为你报错的约束名
     ALTER TABLE xtb_rulesets DROP CONSTRAINT IF EXISTS uc_xtb_ruleset_key;
     ALTER TABLE xtb_currencies DROP CONSTRAINT IF EXISTS uq_currency_iso;
     -- 其他报错的唯一约束同理执行
     

   • 第三步：导入数据

     pg_restore --clean --if-exists --no-owner --data-only --exclude-table-data=xtb_process_runs --exclude-table-data=xtb_app_properties --exclude-table-data=xtb_doc_properties --exclude-table-data=xtb_export_properties --exclude-table-data=xtb_file_storage --exclude-table-data=xtb_import_properties --exclude-table-data=xtb_org_prop_template --exclude-table-data=xtb_org_props --exclude-table-data=xtb_process_properties --exclude-table-data=xtb_rule_functions --exclude-table-data=xtb_rules --exclude-table-data=xtb_templates --exclude-table-data=xtb_trigger_properties --exclude-table-data=xtb_triggers --exclude-table-data=xtb_user_preferences --exclude-table-data=xtb_variables --exclude-table-data=xtb_events -h localhost -p 5432 -d DATABASE_NAME -U user DATABASE_DUMP.dmp
     

2. 查找并清理重复数据
   针对每个报错的唯一约束，查询重复记录并清理（根据业务逻辑保留一条，这里以保留ID最大的记录为例）：

   • 处理xtb_rulesets表：

     -- 先确认重复记录
     SELECT name, COUNT(*) FROM xtb_rulesets GROUP BY name HAVING COUNT(*) > 1;
     -- 删除重复记录，保留ID最大的那条
     DELETE FROM xtb_rulesets
     WHERE id NOT IN (
         SELECT MAX(id) FROM xtb_rulesets GROUP BY name
     );
     

   • 处理xtb_currencies表：

     SELECT iso_code, COUNT(*) FROM xtb_currencies GROUP BY iso_code HAVING COUNT(*) > 1;
     DELETE FROM xtb_currencies
     WHERE id NOT IN (
         SELECT MAX(id) FROM xtb_currencies GROUP BY iso_code
     );
     

   其他报错的唯一约束对应的表，执行类似的查询和清理操作。

第二步：修复外键依赖问题 #

当唯一约束的重复数据清理完成后，按顺序恢复约束：

1. 重新创建唯一约束

   ALTER TABLE ONLY xtb_rulesets ADD CONSTRAINT uc_xtb_ruleset_key UNIQUE (name);
   ALTER TABLE ONLY xtb_currencies ADD CONSTRAINT uq_currency_iso UNIQUE (iso_code);
   -- 其他唯一约束同理执行
   

2. 检查并修复依赖表的约束
   针对外键错误引用表"xtb_cultures"没有匹配给定键的唯一约束，先确认依赖表的约束是否存在：

   -- 查看xtb_cultures的所有约束
   SELECT conname, contype FROM pg_constraint WHERE conrelid = 'xtb_cultures'::regclass;
   

   如果没有对应culture_id的主键或唯一约束，先创建：

   -- 如果culture_id是主键，创建主键约束
   ALTER TABLE xtb_cultures ADD PRIMARY KEY (culture_id);
   -- 如果只需要唯一约束，执行下面这条
   ALTER TABLE xtb_cultures ADD CONSTRAINT uq_cultures_culture_id UNIQUE (culture_id);
   

3. 重新创建外键约束

   ALTER TABLE ONLY xtb_profiles ADD CONSTRAINT fk_profile_to_culture FOREIGN KEY (culture_id) REFERENCES xtb_cultures (culture_id);
   -- 其他外键约束同理执行
   

第三步：优化备份/恢复命令，避免后续问题 #

为了防止下次恢复再踩坑，调整备份和恢复的参数：

• 备份命令：添加--quote-all-identifiers避免标识符大小写敏感问题，确保备份的兼容性：

  ssh user@remoteserver "pg_dump --format=c -c --if-exists -O --schema=public --quote-all-identifiers --exclude-table-data=xtb_process_runs --exclude-table-data=xtb_app_properties --exclude-table-data=xtb_doc_properties --exclude-table-data=xtb_export_properties --exclude-table-data=xtb_file_storage --exclude-table-data=xtb_import_properties --exclude-table-data=xtb_org_prop_template --exclude-table-data=xtb_org_props --exclude-table-data=xtb_process_properties --exclude-table-data=xtb_rule_functions --exclude-table-data=xtb_rules --exclude-table-data=xtb_templates --exclude-table-data=xtb_trigger_properties --exclude-table-data=xtb_triggers --exclude-table-data=xtb_user_preferences --exclude-table-data=xtb_variables --exclude-table-data=xtb_events -d DATABASE_NAME" > DATABASE_DUMP.dmp
  

• 恢复命令：如果目标库可以清空，先删除重建库再恢复，确保没有残留数据：

  # 谨慎操作！确保目标库没有重要数据
  dropdb -U user DATABASE_NAME
  createdb -U user DATABASE_NAME
  # 执行恢复
  pg_restore --clean --if-exists --no-owner -h localhost -p 5432 -d DATABASE_NAME -U user DATABASE_DUMP.dmp
  

额外排查点 #

• 检查原数据库的约束是否是延迟约束：如果原数据库的唯一约束是DEFERRABLE INITIALLY DEFERRED，可能存在未提交事务导致备份数据不一致，可在原数据库执行：

  SELECT conname, condeferrable, condeferred FROM pg_constraint WHERE conname IN ('uc_xtb_ruleset_key', 'uq_currency_iso');
  

• 检查备份文件是否损坏：用pg_restore --list DATABASE_DUMP.dmp查看备份的TOC内容，确认约束和数据的顺序是否正确。

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