Postgresql 损坏的索引怎么查出来,解决了他

POSTGRESQL 中如果你的表文件有损坏,则在查询中会直接告诉你某些文件缺失,你无法对这个表进行查询,或操作. 如果是索引可能就没有这么的简单了.

当一个索引文件有问题的情况下,会遇到什么情况

1  查询的时候明明有索引,但查询的时候大部分情况走全表扫描

2  通过pg_stat_user_tables可以看到 idx_scan 明显比 seq_scan要小的多

3  部分数据有时候无法获得

(postgresql 页面的结构)

当读取信息时会遇到错误,并在错误日志中对数据无法读取的信心进行记录.

通过pg_class 和   pg_namespace 进行查询,发现到底 24643 是那个文件

select n.nspname AS schema, c.relname AS realtion 

from pg_class c 

inner join pg_namespace n on (c.relnamespace = n.oid) where c.relfilenode = '3456'; 

文件和物理文件就有了一一对应.

而此时会有一个问题,就是INDEX 和 数据是一体的,到底是数据损坏还是索引损坏的问题. 要验证这个问题,有一个比较笨的方法,就是通过pg_dump的方式把表导出,如果这个表被导出的情况下,则证明表的索引已经损坏了.

如果觉得这样的操作对于大表不是太合理,可以通过查询 explain analyze 的方式来进行,查看查询中的数据结果是否一致.

例如

postgres=# explain analyze select * from pgbench_accounts where  aid>1 and aid <10000 ;                                                           QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on pgbench_accounts  

(cost=174.61..21830.00 rows=11126 width=97)

(actual time=1.775..4.469 rows=9998 loops=1)   

Recheck Cond: ((aid > 1) AND (aid < 10000))  

Heap Blocks: exact=164   ->  Bitmap Index Scan on idx_pgbench_accounts  (cost=0.00..171.83 rows=11126 width=0) (actual time=1.720..1.720 rows=9998 loops=1)    

Index Cond: ((aid > 1) AND (aid < 10000)) Planning time: 0.242 ms Execution time: 5.286 ms (7 rows)

postgres=# set enable_indexscan='off';

SET 

postgres=# explain analyze select * from pgbench_accounts where aid >10000000; 

                                                         

QUERY PLAN 

Seq Scan on pgbench_accounts  (cost=0.00..4889345.00 rows=89592207 width=97) (actual time=1859.666..15070.333 rows=90000000 loops=1)   

Filter: (aid > 10000000)  

Rows Removed by Filter: 10000000 Planning time: 0.161 ms Execution time: 18394.457 ms (12 rows) 

明显两次查询的结果是不一致的,使用索引和不使用索引的结果是不同的. 这已经能证明索引出了问题.

当然可以通过 pg_catcheck 来进行系统的数据的完整性的检查.

在索引出现问题后,我们通过reindex的方式,在一个 maintenance windows 的情况下进行相关的数据索引的建立.

或者采用POSTGRESQL 12 版本中的 reindex concurrently 的方式进行在线的索引建立.

如果你的POSTGRESQL 版本低于12 ,则也可以通过建立一个重复的索引,通过 create index concurrently 的方式,在建立索引后,在清理失效的索引.(PG 11)