Check the logical integrity of the structure of the B-Tree indexes and tables related to the target index relation with amcheck.
Check the logical integrity of the structure of the B-Tree indexes and tables related to the target index relation with amcheck.
WARNING: bt_index_parent_check(..) used here will block INSERT/UPDATE/DELETE! Do not run in the production database.
(used bt_index_parent_check with heapallindexed argument is true) \
When the heapallindexed argument to verification functions is true, an additional phase of verification is performed against the table associated with the target index relation.
The amcheck module provides functions that allow you to verify the logical consistency of the structure of relations. If the structure appears to be valid, no error is raised.
Read more in the documentation.
Requirements
Notes: this extension is already supplied with PostgreSQL packages for version 11 and above. No additional packages are required.
amcheck functions may only be used by superusers.
1. Create extension on the target database
(specify the name of the target database)
psql -d <dbname> -c 'create extension if not exists amcheck'
2. Create SQL script "amcheck.sql"
cat <<EOF > amcheck.sql
----------------------------------------------------------------------------------------
-- Check btree indexes (amcheck)
--
-- WARNING: bt_index_parent_check(..) used here will block INSERT/UPDATE/DELETE!
----------------------------------------------------------------------------------------
set statement_timeout to 0;
do \$\$
declare
r record;
sql text;
ts_pre timestamptz;
begin
for r in
select
row_number() over(order by tc.reltuples) as i,
count(*) over() as cnt,
c.oid,
i.indisunique,
c.relname,
c.relpages::int8,
tc.reltuples::int8 as tuples
from pg_index i
join pg_opclass op on i.indclass[0] = op.oid
join pg_am am on op.opcmethod = am.oid
join pg_class c on i.indexrelid = c.oid
join pg_class tc on i.indrelid = tc.oid
join pg_namespace n on c.relnamespace = n.oid
where
am.amname = 'btree'
--and n.nspname = 'public'
and c.relpersistence <> 't' -- don't check temp tables
and c.relkind = 'i'
and i.indisready
and i.indisvalid
order by tc.reltuples
loop
ts_pre := clock_timestamp();
raise info '[%] Processing %/%: index: % (index relpages: %; heap tuples: ~%)...',
ts_pre::timestamptz(3), r.i, r.cnt, r.relname, r.relpages, r.tuples;
perform bt_index_parent_check(index => r.oid, heapallindexed => true);
raise info '[%] DONE %/%: index: %. Time taken: %',
clock_timestamp()::timestamptz(3), r.i, r.cnt, r.relname, (clock_timestamp() - ts_pre);
end loop;
end \$\$;
EOFNotes: In this example, commented the condition "and n.nspname = 'public'" to check all available indexes.
3. Check btree indexes
(specify the name of the target database)
psql -d <dbname> -f amcheck.sql 2>&1 | tee -a /tmp/amcheck.$(date "+%F").log
An example of output:
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:48.853+03] Processing 1/189: index: pg_toast_2600_index (index relpages: 1; heap tuples: ~0)...
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:48.859+03] DONE 1/189: index: pg_toast_2600_index. Time taken: 00:00:00.005419
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:48.859+03] Processing 2/189: index: pg_toast_2604_index (index relpages: 1; heap tuples: ~0)...
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:48.861+03] DONE 2/189: index: pg_toast_2604_index. Time taken: 00:00:00.002093
...
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:49.677+03] Processing 188/189: index: pg_depend_reference_index (index relpages: 66; heap tuples: ~12313)...
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:49.766+03] DONE 188/189: index: pg_depend_reference_index. Time taken: 00:00:00.088657
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:49.766+03] Processing 189/189: index: pg_depend_depender_index (index relpages: 58; heap tuples: ~12313)...
psql:amcheck.sql:49: INFO: [2021-03-24 21:54:49.822+03] DONE 189/189: index: pg_depend_depender_index. Time taken: 00:00:00.055827Wait until the checks are completed.
Notes: The exact time of execution depends on the number and size of indexes, as well as from server hardware resources.
If the structure of the index is valid, no error is raised. An example of a invalid index:
ERROR: item order invariant violated for index "index_name_idx"
DETAIL: Lower index tid=(2,111) (points to heap tid=(104,6)) higher index tid=(2,112) (points to heap tid=(106,10)) page lsn=8/680B33C8.If the problem is detected, try re-create the index: REINDEX INDEX CONCURRENTLY <index_name>;
Repairing Corruption
No error concerning corruption raised by amcheck should ever be a false positive. amcheck raises errors in the event of conditions that, by definition, should never happen, and so careful analysis of amcheck errors is often required.
There is no general method of repairing problems that amcheck detects. An explanation for the root cause of an invariant violation should be sought. pageinspect may play a useful role in diagnosing corruption that amcheck detects. A REINDEX may not be effective in repairing corruption.