[postgres@pg03 ~]$ psql -h 192.168.1.3 -U postgres -d tdb
psql: FATAL: cache lookup failed for access method 403
使用客户端新建连接访问数据库时出现报错,无法建立连接,而访问其他数据库正常。
postgresql后端服务进程在初始化阶段加载系统字典表时,由于系统字典表pg_am损坏导致加载失败,初始化失败报错退出。
PG后端服务进程在被fork出来之后会进行如下函数调用:
PostgresMain -> InitPostgres -> RelationCacheInitializePhase3 ->
load_critical_index -> RelationBuildDesc -> RelationInitIndexAccessInfo
其中,load_critical_index加载索引的顺序如下:
load_critical_index(ClassOidIndexId,
RelationRelationId);
load_critical_index(AttributeRelidNumIndexId,
AttributeRelationId);
load_critical_index(IndexRelidIndexId,
IndexRelationId);
load_critical_index(OpclassOidIndexId,
OperatorClassRelationId);
load_critical_index(AccessMethodProcedureIndexId,
AccessMethodProcedureRelationId);
load_critical_index(RewriteRelRulenameIndexId,
RewriteRelationId);
load_critical_index(TriggerRelidNameIndexId,
TriggerRelationId);
第一个加载的系统索引是ClassOidIndexId, OID为2662,查询系统表可看到如下信息:
postgres=# select relname,relnamespace,relam from pg_class where oid=2662;
relname | relnamespace | relam
--------------------+--------------+-------
pg_class_oid_index | 11 | 403
(1 row)
postgres=# select oid,* from pg_am;
oid | amname | amhandler | amtype
------+--------+-------------+--------
403 | btree | bthandler | i
405 | hash | hashhandler | i
783 | gist | gisthandler | i
2742 | gin | ginhandler | i
4000 | spgist | spghandler | i
3580 | brin | brinhandler | i
(6 rows)
该索引所使用的正是报错中所提及的access method 403.btree索引。
最终报错出现在RelationInitIndexAccessInfo函数中如下:
...
/*
* Look up the index's access method, save the OID of its handler function
*/
tuple = SearchSysCache1(AMOID, ObjectIdGetDatum(relation->rd_rel->relam));
if (!HeapTupleIsValid(tuple))
elog(ERROR, "cache lookup failed for access method %u",
relation->rd_rel->relam);
aform = (Form_pg_am) GETSTRUCT(tuple);
relation->rd_amhandler = aform->amhandler;
ReleaseSysCache(tuple);
...
由于tdb库下pg_am(AMOID)系统字典表文件被损坏,导致初始化后端服务进程时无法将正确数据读取到内存结构中,因此查询不到相应值。
如果没创建过额外的索引访问方法,如rum等第三方插件,则直接copy template0下的pg_am字典表文件到已损坏数据库目录下即可,否则需要额外创建数据库,将第三方插件安装之后再copy新建数据库下的pg_am字典表文件到已损坏数据库目录下。
[postgres@pg03 base]$ psql -d tdb
psql: FATAL: cache lookup failed for access method 403
[postgres@pg03 base]$ cp -r 13260/2601 399872/
[postgres@pg03 base]$ psql -d tdb
psql (11.5 )
Type "help" for help.
tdb=#
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。