Postgresql之amcheck验证索引完整性
2021/12/20 19:21:42
本文主要是介绍Postgresql之amcheck验证索引完整性,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Postgresql10版本开始提供了该插件验证索引或者是表的逻辑一致性。比如系统升级后,collate和原来不一致,数据库里索引的顺序和该collate不匹配。物理磁盘损坏,文件系统损坏,内存故障等,导致数据库中的索引也损坏。
可以通过该插件进行验证检测,注意该插件只验证,不会修补。如果通过该插件找出相应的索引,可以使用reindex命令重建索引,如果数据库很小,也可以考虑使用reindexdb重建整个库的索引。
另外从PG14版本开始新增了verify_heapam,可以验证表的数据页。
相关函数:
bt_index_check --加accessshared,和select类似 bt_index_parent_check --加ShareLock锁,和DML均会冲突,所以使用该函数要注意
使用例子:
bt_index_check
test=# 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[0] = 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; bt_index_check | relname | relpages ----------------+---------------------------------+---------- | pg_depend_reference_index | 43 | pg_depend_depender_index | 40 | pg_proc_proname_args_nsp_index | 31 | pg_description_o_c_o_index | 21 | pg_attribute_relid_attnam_index | 14 | pg_proc_oid_index | 10 | pg_attribute_relid_attnum_index | 9 | pg_amproc_fam_proc_index | 5 | pg_amop_opr_fam_index | 5 | pg_amop_fam_strat_index | 5 (10 rows)
bt_index_parent_check
--验证tbl表的b-tree索引 create extension if not exists amcheck; set statement_timeout to 0; do $$ declare r record; sql text; ts_pre timestamptz; e_message text; e_detail text; e_context text; e_hint text; errcount int := 0; begin raise info '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 and tc.relname = 'tbl' -- comment this out to check the whole DB --and c.relname in ('index_projects_on_name_and_id', 'index_projects_on_lower_name', 'index_projects_api_name_id_desc') 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; begin perform bt_index_parent_check(index => r.oid, heapallindexed => true); raise info '[%] SUCCESS %/% – index: %. Time taken: %', clock_timestamp()::timestamptz(3), r.i, r.cnt, r.relname, (clock_timestamp() - ts_pre); exception when others then get stacked diagnostics e_message = message_text, e_detail = pg_exception_detail, e_context = pg_exception_context, e_hint = pg_exception_hint; errcount := errcount + 1; raise warning $err$[%] FAILED %/% – index: %. ERROR: % CONTEXT: % DETAIL: % HINT: % $err$, clock_timestamp()::timestamptz(3), r.i, r.cnt, r.relname, e_message, e_detail, e_context, e_hint; end; end loop; if errcount = 0 then raise info 'Btree index scan with amcheck successfully finished. 0 errors.'; else raise exception 'Index corruption detected by amcheck, % errors, see details in the log.', errcount; end if; end $$; INFO: begin!... INFO: [2021-12-20 14:52:14.679+08] Processing 1/1: index: tbl_pkey (index relpages: 552; heap tuples: ~200300)... INFO: [2021-12-20 14:52:14.681+08] SUCCESS 1/1 – index: tbl_pkey. Time taken: 00:00:00.002516 INFO: Btree index scan with amcheck successfully finished. 0 errors. DO
参考:
https://www.postgresql.org/docs/current/amcheck.html
https://elephanttamer.net/?p=61
这篇关于Postgresql之amcheck验证索引完整性的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-24怎么切换 Git 项目的远程仓库地址?-icode9专业技术文章分享
- 2024-12-24怎么更改 Git 远程仓库的名称?-icode9专业技术文章分享
- 2024-12-24更改 Git 本地分支关联的远程分支是什么命令?-icode9专业技术文章分享
- 2024-12-24uniapp 连接之后会被立马断开是什么原因?-icode9专业技术文章分享
- 2024-12-24cdn 路径可以指定规则映射吗?-icode9专业技术文章分享
- 2024-12-24CAP:Serverless?+AI?让应用开发更简单
- 2024-12-23新能源车企如何通过CRM工具优化客户关系管理,增强客户忠诚度与品牌影响力
- 2024-12-23原创tauri2.1+vite6.0+rust+arco客户端os平台系统|tauri2+rust桌面os管理
- 2024-12-23DevExpress 怎么实现右键菜单(Context Menu)显示中文?-icode9专业技术文章分享
- 2024-12-22怎么通过控制台去看我的页面渲染的内容在哪个文件中呢-icode9专业技术文章分享