Postgres database is down - index "fk_alf_nasp_n" is not a btree

sepgs2004 — Tue, 06 Jul 2021 20:08:40 GMT

All of a sudden, Alfresco server is down and would not start back again.

### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: index "fk_alf_nasp_n" is not a btree
### The error may exist in alfresco/ibatis/#resource.dialect#/node-common-SqlMap.xml
### The error may involve alfresco.node.select_NodeAspects-Inline
### The error occurred while setting parameters
### SQL: select node.id as node_id, node.version as node_version, aspects.qname_id as qname_id from alf_node node join alf_node_aspects aspects on (aspects.node_id = node.id) WHERE aspects.node_id in ( ? )
### Cause: org.postgresql.util.PSQLException: ERROR: index "fk_alf_nasp_n" is not a btree

Also, when we login to postgres and execute the following query
Select * from alf_node_aspects

Executing this query also gives the same error.

At this point, I have no clue on how to solve this.
In the postgres log, this error occurs immediately after this query:

select node.id as node_id,
node.version as node_version,
aspects.qname_id as qname_id
from
alf_node node join alf_node_aspects aspects
on (aspects.node_id = node.id)
WHERE aspects.node_id in ($1)

Re: Postgres database is down - index "fk_alf_nasp_n" is not a btree

sepgs2004 — Tue, 06 Jul 2021 20:50:42 GMT

Recreating the index fixed the issue. I wonder why and how this issue could have happened, so it does not repeat more.

reindex index fk_alf_nasp_n;

topic Postgres database is down - index "fk_alf_nasp_n" is not a btree in Alfresco Forum

Postgres database is down - index "fk_alf_nasp_n" is not a btree

Re: Postgres database is down - index "fk_alf_nasp_n" is not a btree