How to identify and resolve index corruption in Postgres v14.0 - v14.3
A bug has ben identified in PostgreSQL 14.0-14.3 that can cause database index corruption in indexes created concurrently. Sourcegraph utilizes concurrent index creation, and if you are on these versions, you may experience slow queries or database corruption. To learn more about this bug see postgres’s out-of-cycle release announcment or migops article on the subject.
Identify your database version
To identify which version of Sourcegraph you are running in a default Sourcegraph deployment. You can access your database via the
kubectl cli tools and run the following command:
You may also check for index corruption in your database using the
amcheck by running the following query in your database
create extension amcheck; select bt_index_parent_check(c.oid, true), c.relname, c.relpages from pg_index i join pg_opclass op ON i.indclass = op.oid join pg_am am ON op.opcmethod = am.oid join pg_class c ON i.indexrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid where am.amname = 'btree' -- Don't check temp tables, which may be from another session: and c.relpersistence != 't' -- Function may throw an error when this is omitted: and i.indisready AND i.indisvalid;
This query will return error if there are corrupted indexes.
If you are impacted, you can remediate this by upgrading to a newer version of Postgres (14.4+) and running the following commands in your database
Determine database name:
REINDEX DATABASE <dbname>;
You may want to use the amcheck query above to verify the reindex has resolved index corruption.
Upgrading your database
In default Sourcegraph deployments internal PostgreSQL instances are used and may be upgraded via the pg_upgrade. For external databases consult your service providers documentation. For a deeper look at database upgrade operations please consult our PostgreSQL documentation.
If you have any questions, please reach out to support on Slack or email [email protected]