How to fix Cannot reindex system catalog in PostgreSQL

If you're using reindexdb --concurrently or REINDEX ... CONCURRENTLY, this is expected behavior. PostgreSQL intentionally skips system catalogs during concurrent reindexing operations.

To reindex only user tables concurrently:

REINDEX DATABASE CONCURRENTLY;

Or reindex system catalogs separately (non-concurrently):

REINDEX SYSTEM;

If system indexes appear corrupted, reindex them without the CONCURRENTLY option. Connect to your database and run:

REINDEX SYSTEM;

For a specific corrupted index:

REINDEX INDEX pg_class_oid_index;

If you know which table's indexes are corrupted:

REINDEX TABLE pg_class;

If system catalog corruption is so severe that you cannot connect to PostgreSQL:

1. Stop the PostgreSQL server:

sudo systemctl stop postgresql

2. Edit postgresql.auto.conf (or postgresql.conf):

sudo nano /var/lib/postgresql/data/postgresql.auto.conf

3. Add the line:

ignore_system_indexes = true

4. Start PostgreSQL:

sudo systemctl start postgresql

5. Connect and reindex:

REINDEX SYSTEM;
REINDEX DATABASE;

6. Remove the ignore_system_indexes parameter and restart PostgreSQL.

If the above methods fail, start PostgreSQL in single-user mode with the -P flag to bypass system indexes:

pg_ctl stop -D /var/lib/postgresql/data
postgres -D /var/lib/postgresql/data -P

Then run from a separate terminal:

psql -h localhost -U postgres

Inside psql:

REINDEX SYSTEM;
REINDEX DATABASE;
\q

Exit single-user mode by typing q at the postgres prompt.

After reindexing completes successfully:

1. Exit single-user mode or the ignore_system_indexes parameter mode
2. Stop PostgreSQL gracefully:

sudo systemctl stop postgresql

3. Remove ignore_system_indexes from config if you added it
4. Restart PostgreSQL normally:

sudo systemctl start postgresql

5. Verify database is functioning:

psql -U postgres -c "SELECT 1;"