There has been some discussions over at meta discourse lately about PostgresSQL 10 and 12 indexes being corrupt. We have yet to see this in practice using Discourse; however, it is certainly a good idea check indexes and verify they are not corrupt, especially before doing a backup and restore.
amcheck module provides functions that allow you to verify the logical consistency of the structure of PostgreSQL indexes
In this Discourse configuration, we have both web-app and data containers, so we will install
amcheck in the data container.
# ./launcher enter data # apt install postgresql-10-amcheck
Verify the Indexes
We need to create the
amcheck_next extension (one time only) and then run the query:
# su postgres $ psql discourse psql (10.12 (Debian 10.12-2.pgdg100+1)) Type "help" for help. discourse=# CREATE EXTENSION amcheck_next; CREATE EXTENSION discourse=#
Now that the initial setup is done, we can run this query:
discourse=# SELECT bt_index_check(c.oid), c.relname, c.relpages discourse-# FROM pg_index i discourse-# JOIN pg_opclass op ON i.indclass = op.oid discourse-# JOIN pg_am am ON op.opcmethod = am.oid discourse-# JOIN pg_class c ON i.indexrelid = c.oid discourse-# JOIN pg_namespace n ON c.relnamespace = n.oid discourse-# WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog' discourse-# -- Don't check temp tables, which may be from another session: discourse-# AND c.relpersistence != 't' discourse-# -- Function may throw an error when this is omitted: discourse-# AND i.indisready AND i.indisvalid discourse-# ORDER BY c.relpages DESC LIMIT 10; bt_index_check | relname | relpages ----------------+-----------------------------------+---------- | pg_depend_depender_index | 150 | pg_attribute_relid_attnam_index | 147 | pg_depend_reference_index | 123 | pg_attribute_relid_attnum_index | 101 | pg_class_tblspc_relfilenode_index | 42 | pg_class_relname_nsp_index | 38 | pg_proc_proname_args_nsp_index | 38 | pg_class_oid_index | 27 | pg_description_o_c_o_index | 24 | pg_type_typname_nsp_index | 20
This Discourse PostgreSQL 10 query shows a session that performs verification of catalog indexes. Verification of the presence of heap tuples as index tuples is requested for unique indexes only.
As we can see, no error is raised, all indexes tested appear to be logically consistent.
The Query (Copy & Paste)
SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique), 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' AND n.nspname = 'pg_catalog' -- 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 c.relkind = 'i' AND i.indisready AND i.indisvalid ORDER BY c.relpages DESC LIMIT 10;
No error concerning corruption raised by
amcheck should ever be a false positive. In practice,
amcheck is more likely to find software bugs than problems with hardware.
amcheck raises errors in the event of conditions that, by definition, should never happen, and so careful analysis of
amcheck errors is often required.
Repairing Postgres Index Corruption
There is no general method of repairing problems that
An explanation for the root cause of an invariant violation should be sought. pageinspect may play a useful role in diagnosing corruption that
REINDEX may not be effective in repairing corruption, but you can try and run the above query again.
I have performed this procedure on two Discourse installations, both running PostgreSQL 10 with a "two container" configuration and a single container configuration. The process went well in both instances. I recommend anyone who in concerned about corrupt PostgreSQL indexes consider this procedure.