PostgreSQL 9升级至11时数据库导出导入异常问题咨询

出现这个问题的核心原因是导入流程前期创建表、Schema、函数等对象的步骤全部失败，仅执行到了最后配置表访问权限（ACL）的环节，你截取的日志只保留了最后几条执行记录，前面476个报错都没有展示，按以下步骤排查修复即可：

1. 先确认具体报错 #

打开你生成的log1126.log，翻到日志最靠前的ERROR级别记录，90%以上的场景都是以下三类问题：

• 目标数据库实例不存在源库的对象所有者：你导出时连接源库的用户是sce，源库中大部分业务对象的所有者默认都是sce，如果目标PostgreSQL 11实例没有提前创建sce角色，所有创建对象的语句都会直接报role "sce" does not exist错误，后续所有建表、建索引的步骤都会连锁失败。
• 目标库权限配置错误：你创建kd5库时用的是postgres用户，未给导入用户分配对应Schema的创建、写入权限，导致建表时直接报权限不足。
• 少数场景是源库安装了PostGIS等第三方扩展，目标库没提前安装对应扩展，导致创建扩展相关对象失败。

2. 修复前置依赖后重新导入 #

• 先在目标PostgreSQL 11实例创建缺失的角色，最核心的是先创建sce用户：

  -- 连接到目标实例的postgres系统库执行
  CREATE ROLE sce WITH LOGIN PASSWORD '你的业务用户密码';
  ALTER ROLE sce CREATEDB; -- 按需给用户分配库创建权限
  

  如果源库还有其他自定义业务角色，需要全部提前创建，否则对应所有者的对象导入都会失败。如果是测试环境不想逐个匹配角色，可以在后续导入命令中加--no-owner参数，导入后的所有对象所有者会变成执行导入的用户，不需要提前创建源端角色
• 清理之前导入失败的残留库，重新创建干净的目标库：

  -- 先断开所有连接到kd5库的会话再执行删库
  DROP DATABASE IF EXISTS kd5;
  CREATE DATABASE kd5 OWNER sce;
  

  如果要导入扩展相关对象，提前连到新建的kd5库执行CREATE EXTENSION 扩展名称;安装对应扩展。
• 执行导入命令，建议加上--exit-on-error参数，遇到错误直接终止，不用等跑完几百个无效步骤才发现问题：

  # 用sce用户导入，和源端所有者匹配
  pg_restore -h localhost -d kd5 -U sce ./mypath/db_kd --no-tablespaces --verbose --exit-on-error 2>log_restore_fix.log
  

  如果你选择不创建源端角色，用postgres用户导入的话，命令调整为：

  pg_restore -h localhost -d kd5 -U postgres ./mypath/db_kd --no-tablespaces --no-owner --verbose --exit-on-error 2>log_restore_fix.log
  

3. 导入后验证 #

导入完成后，用对应所有者用户连接kd5库，执行\dt即可看到所有业务表。如果用超级用户连接看不到表，先执行SET ROLE 表所有者用户;再查询即可。

补充说明 #

你用PostgreSQL 11自带的pg_dump连接PostgreSQL 9源库做导出的操作是符合官方规范的，跨大版本升级必须使用目标版本对应的pg_dump工具导出源数据，这部分操作没有问题。

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