How to fix Index is corrupted in PostgreSQL

First, enable the amcheck extension and test your indexes:

CREATE EXTENSION IF NOT EXISTS amcheck;

Then check specific indexes:

SELECT * FROM bt_index_check('index_name'::regclass);

For a comprehensive check including heap verification:

SELECT * FROM bt_index_parent_check('index_name'::regclass);

If amcheck returns errors, the index is corrupted and needs rebuilding.

Always back up your database before attempting index repairs:

pg_dump dbname > dbname_backup.sql

If you have pg_basebackup available:

pg_basebackup -D /backup/path -Ft -z

This ensures you can recover if the repair process encounters unexpected issues.

For a single index (simplest approach, but locks the table):

REINDEX INDEX index_name;

To reindex all indexes on a table:

REINDEX TABLE table_name;

Note: REINDEX blocks writes and indexes-based reads during execution. For large indexes, this can cause significant downtime.

PostgreSQL 12 and later support non-blocking index rebuilds:

REINDEX INDEX CONCURRENTLY index_name;

This creates a new index in the background, then swaps it with the corrupted one. The table remains fully accessible during this operation, though it uses more disk space temporarily.

If running PostgreSQL 11 or earlier, use this non-blocking approach:

CREATE INDEX CONCURRENTLY index_name_new ON table_name (...) WHERE ...;
DROP INDEX CONCURRENTLY index_name;
ALTER INDEX index_name_new RENAME TO index_name;

Replace the index definition with your original index definition. This allows concurrent access throughout the operation.

If the corrupted index is UNIQUE, you may have duplicate rows. REINDEX will fail in this case. First, identify and remove duplicates:

WITH duplicates AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) as rn
  FROM table_name
)
DELETE FROM table_name WHERE id IN (
  SELECT id FROM duplicates WHERE rn > 1
);

After removing duplicates, proceed with REINDEX.

If a system catalog index is corrupted and preventing PostgreSQL from starting:

1. Stop the PostgreSQL server
2. Start with recovery mode:

postgres --single -D /var/lib/postgresql/data -c ignore_system_indexes=on

Or edit postgresql.conf:

ignore_system_indexes = on

3. Connect and rebuild the corrupted system index:

REINDEX INDEX system_index_name;

4. Disable the recovery setting and restart normally.