amcheck reports following error:
select bt_index_parent_check(34465);
ERROR: item order invariant violated for index "admin_access_rights_pkey"
DETAIL: Lower index tid=(282,71) (points to heap tid=(201,46)) higher index tid=(282,72) (points to heap tid=(197,38)) page lsn=396C/DDF2FD00
How can I fix such issue?
The documentation says that REINDEX might not help.
A REINDEX may not be effective in repairing corruption.
Such error might be caused by:
PRIMARY KEYkill -9)Check for duplicated PRIMARY KEY:
SELECT PRIMARY_KEY,count(*) FROM my_table GROUP BY PRIMARY_KEY HAVING count(*)>1;
Delete duplicated keys, if you find any:
DELETE FROM my_table a
USING (
SELECT min(ctid) AS ctid,
PRIMARY_KEY
FROM my_table
GROUP BY PRIMARY_KEY
HAVING count(*) > 1 ) b
WHERE a.PRIMARY_KEY = b.PRIMARY_KEY
AND a.ctid <> b.ctid;
In many cases it seems to be enough to rebuild the index using REINDEX:
REINDEX INDEX admin_access_rights_pkey;
For a corrupted index, I prefer the slower and safer version over REINDEX CONCURRENTLY. I've seen some issues (hopefully now fixed) when using REINDEX CONCURRENTLY.
Another option is to use pg_repack which allows table repacking and index rebuilding in background. If successful, index will be moved to replace the original one. This approach might avoid unnecessary lock. Though rebuilding large tables might take significant time.
psql -d dbname -c "CREATE EXTENSION pg_repack"
pg_repack -d dbname -i 'schema_name.index_name'
pg_repack might be running for whole database:
pg_repack -d dbname --dry-run
-N / --dry-run only print repacked resources-j / --jobs=4 number of parallel jobs-x / --only-indexes rebuild only indexes-c / --schema=public specific schema only