Skip to content

Instantly share code, notes, and snippets.

@threesquared

threesquared/postgres-find-corrupted-Indexes.md

Last active June 22, 2022 10:26
Show Gist options
  • Save threesquared/21b6a523b6b85331b165dcc2af175e3e to your computer and use it in GitHub Desktop.
Save threesquared/21b6a523b6b85331b165dcc2af175e3e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres-find-corrupted-Indexes.md

The new PostgreSQL 14.4 release fixes an issue with all versions of PostgreSQL 14 that can lead to silent corruption of indexes. You can find out if any indexes are affected with pg_amcheck:

CREATE EXTENSION amcheck;
GRANT EXECUTE ON FUNCTION verify_heapam(relation regclass, on_error_stop boolean, check_toast boolean, skip text, startblock bigint, endblock bigint, blkno OUT bigint, offnum OUT integer, attnum OUT integer, msg OUT text) TO root;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass) TO root;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass, boolean) TO root;

Then run

pg_amcheck -d db_name -h host -U root --relation 'schema.table*' --heapallindexed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment