Error in PostgreSQL: right sibling's left-link doesn't match: block 5 links to 8 instead of expected 2 in index "pg_toast_2619_index"

Question

We are facing the below error in a PostgreSQL 9.2 production database. Please help us to resolve it. Why are we facing this issue? What is the impact of this issue?

ERROR:  right sibling's left-link doesn't match: block 5 links to 8 instead of expected 2 in index "pg_toast_2619_index"

CONTEXT:  automatic vacuum of table "qovr.pg_toast.pg_toast_2619"

Sext

You've got corruption. Sometimes rebuilding indexes can help, good luck! – Within

Which index I need to rebuild. How to find it. – Sext

appears it's pg_toast_2619_index :) – Within

Answer 1

To solve the problem at hand:

First, find out the name of the table to which the TOAST index belongs:

SELECT r.oid::regclass
FROM pg_class r
   JOIN pg_class t ON r.reltoastrelid = t.oid
   JOIN pg_index i ON t.oid = i.indrelid
   JOIN pg_class ti ON i.indexrelid = ti.oid
WHERE ti.relname = 'pg_toast_2619_index'
  AND ti.relnamespace = 'pg_toast'::regnamespace;

The answer should be pg_statistic, the system catalog that stores table statistics.

Then, as superuser, reindex the table:

REINDEX TABLE pg_statistic;

To solve the bigger problem:

Find out what caused the index corruption:

• Test your hardware, particularly storage and RAM.

• Test if your storage processes a sync request correctly by writing the information to persistent storage rather than caching it where it can get lost.

• Check if you had any crashes lately.

• Move to a recent supported version of PostgreSQL and always apply bugfixes.

Unfaithful