Postgresql数据库表结构异常

起因
生产环境某次进行备份数据库，直接报错

错误内容：

ERROR: cache lookup failed for relation 78105

排查过程
查看该表内容

database=# \d table_name
ERROR: cache lookup failed for relation 78105

但select该表的时候是可以出数据的，insert和update和delete数据都是可以的。

查询网上，表信息都在pg_class 与 pg_depend中

database=# select relname, oid from pg_class where relname='table_name';
       relname      |  oid
--------------------+-------
     tableb_name    | 78105
database=# delete from pg_class where oid=78105;
DELETE 1
database=# delete from pg_depend where objid=78105;
DELETE 1

3.将表相关联的索引，序列和约束都删掉

select oid, relname from pg_class where relname like '%table_name%';

将查询出来该表相关的 ix_table_name_id, table_name_id_seq, table_name_pkey等删除掉

delete from pg_class where oid=xxx;
delete from pg_depend where objid=xxx;

然后尝试重建表，报了一个没有见过的错误

ERROR： type 'table_name' already exists
HINT: A relation has an associated type of the same name, so you must use ...

此处应该发现若是表存在的话应该报的错误是

ERROR: relation "table_name" already exists

既然type 已存在, 就将该type删除

下面是在 DROP TYPE的时候还报 cache lookup failed for relation 78015的错误，感觉没有删除干净有查询到pg_depend表的refobjid中存在该id的引用

database=# delete from pg_depend where refobjid=78105;
DELETE 4

最后删除该表TYPE

database=# DROP TYPE table_name;
DROP TYPE

最后重建表就OK了 将表中的数据再插入进去就OK了（再删除之前一定要先备份表中数据。）

---

转载至：https://blog.51cto.com/u_14612701/3811667