PostgreSQL 11升级至14时OID 323191关联不存在错误求助

问题描述 #

升级过程中执行pg_dump遇到以下错误：

pg_dump: error: query failed: ERROR: relation with OID 323191 does not exist
pg_dump: error: query was: LOCK TABLE "schema"."table_name" IN ACCESS SHARE MODE

尝试查询多个系统表查找OID 323191均无结果：

select * from pg_type where typnamespace=323191;
select * from pg_class where relnamespace = 323191;
select * from pg_operator where oprnamespace = 323191;
select * from pg_conversion where connamespace = 323191;
select * from pg_opclass where opcnamespace = 323191;
select * from pg_aggregate where aggfnoid = 323191 or aggtransfn  = 323191 or aggfinalfn = 323191;
select * from pg_proc where pronamespace = 323191;
select * from pg_proc where pronamespace = 323191;
select * from pg_class where relnamespace = 323191;
select * from pg_depend where relnamespace = 323191 or oid=323191;

解决建议 #

1. 确认目标表状态并排查依赖 #

• 先验证报错中的schema.table_name是否真实存在：

  SELECT * FROM pg_class c 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
  WHERE n.nspname = 'schema' AND c.relname = 'table_name';
  

• 如果表存在，检查该表的依赖对象，定位引用OID 323191的无效项：

  -- 查找所有引用该OID的依赖
  SELECT * FROM pg_depend WHERE refobjid = 323191;
  -- 检查目标表的约束是否关联无效OID
  SELECT conname, conrelid, confrelid 
  FROM pg_constraint 
  WHERE conrelid = (SELECT oid FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname='schema' AND c.relname='table_name');
  

2. 清理无效对象 #

如果发现无效约束、触发器或函数：

• 删除无效约束：先查出约束名，再删除重建（替换实际约束名和定义）

  -- 查找关联323191的约束
  SELECT conname FROM pg_constraint 
  WHERE conrelid = (SELECT oid FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname='schema' AND c.relname='table_name') 
  AND (conkey @> ARRAY[323191] OR confrelid=323191);
  
  -- 删除约束
  ALTER TABLE schema.table_name DROP CONSTRAINT constraint_name;
  -- 重新创建约束
  ALTER TABLE schema.table_name ADD CONSTRAINT constraint_name [约束定义];
  

• 删除无效触发器：检查触发器关联的函数是否存在，若不存在则删除触发器

  SELECT tgname, tgfoid FROM pg_trigger 
  WHERE tgrelid = (SELECT oid FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname='schema' AND c.relname='table_name');
  
  -- 验证函数是否存在
  SELECT * FROM pg_proc WHERE oid = tgfoid;
  
  -- 删除无效触发器
  DROP TRIGGER trigger_name ON schema.table_name;
  

3. 临时绕过问题继续升级 #

如果暂时无法修复无效对象，可通过pg_dump参数跳过问题表：

pg_dump -d your_database --exclude-table=schema.table_name > upgrade_dump.sql

导出后单独处理该表，或后续手动重建。

4. 检查系统表一致性 #

执行内置工具检查并修复可能的系统表损坏：

# 先停止PostgreSQL服务
pg_ctl stop -D /your/data/directory
# 校验数据目录完整性（若启用了校验和）
pg_checksums -D /your/data/directory verify
# 尝试全量导出，获取更多错误细节
pg_dumpall -d your_database > full_dump.sql

5. 改用pg_upgrade直接升级 #

若pg_dump报错无法解决，可尝试用pg_upgrade工具直接升级，它无需导出导入数据，直接处理系统目录：

# 安装PostgreSQL14并初始化新集群
initdb -D /var/lib/postgresql/14/main
# 停止两个版本的服务
systemctl stop postgresql@11-main
systemctl stop postgresql@14-main
# 执行升级
pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/14/bin -d /var/lib/postgresql/11/main -D /var/lib/postgresql/14/main
# 启动新集群并执行分析
systemctl start postgresql@14-main
./analyze_new_cluster.sh

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