恢复PostgreSQL dump.sql遇问题：架构已存在与唯一约束冲突

可能原因 #

• dump文件包含全局共享对象：如果是用pg_dumpall导出的备份，会包含PostgreSQL实例级的全局对象（如角色、表空间），这些对象不属于单个数据库，而是整个实例共享的。即使你创建了空数据库，实例中已存在这些全局对象，导入时就会提示“已存在”。
• 数据库继承了模板对象：默认创建数据库时会继承template1的内容，template1中默认存在public模式等对象，若dump文件中包含这些对象的创建语句，就会冲突。
• dump文件本身存在重复定义：备份文件可能重复写入了相同对象的创建语句，或者导出时未使用增量/条件创建逻辑，导致导入时重复创建触发唯一约束错误。

解决方法 #

• 重新创建干净数据库：
  先删除现有数据库，再基于template0创建纯空白数据库（template0不含任何自定义默认对象）：

  dropdb -U <username> <db name>
  createdb -T template0 -U <username> <db name>
  

  之后再执行原导入命令。

• 处理全局对象冲突：

  • 如果不需要备份中的全局对象，直接编辑dump.sql，删除所有CREATE ROLE、CREATE TABLESPACE等实例级对象的创建语句。
  • 若需要全局对象，先单独导入这些部分，再导入数据库级对象；或者使用pg_dump重新导出单个数据库（不含全局对象）：

    pg_dump -U <username> -d <source db name> -f new_dump.sql
    

• 清理dump文件中的重复/强制创建语句：

  • 对于SQL格式的dump，批量替换创建语句为条件创建，比如将CREATE TABLE替换为CREATE TABLE IF NOT EXISTS（注意：此方法不适用于函数、模式等所有对象，需谨慎）：

    sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' dump.sql
    

  • 如果是自定义格式的备份（.dump或.backup），使用pg_restore并添加--clean参数，导入前自动清理现有对象：

    pg_restore -U <username> -d <db name> --clean -f <dump file path>
    

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