PostgreSQL索引坏块处理

今天在给客户解决问题，数据库是PostgreSQL。之前也没有用过这个数据库，基本都是MySQL，出现问题好棘手。做个记录。

查看项目err日志，发现报错信息：

查询数据库语句，然后是：无法在文件"base/12345/123"中读取块0: No error

咱也不知道是什么，大概知道是查询数据库发生了问题，百度了一番，有人说可能是表损坏，或者是索引损坏。base后的第一个数字是数据库的oid,第二个数字是表的relfilenode值

我们知道无论是什么数据库，都会有数据库文件，那么找到数据库文件，这个数据库文件是在base目录下。

启动数据库，在navicat建立的连接处，进行如下操作：

1.首先判断是哪个数据库中出了问题，连接任意数据库，执行：结果可以看到是哪个库出的问题。

#12345是报错base/12345/123中的值

select datname from pg_database where oid = '12345';

如果知道是哪个表，也可以根据表名查询：

select oid, relname,relfilenode from pg_class where relname='tabname';

或者

select oid,datname from pg_database where datname='tabname';

用函数pg_relation_filepath能根据oid或者名称返回关系对应的文件路径

select pg_relation_filepath('tablename');

2.查询是哪个表发生了异常

select relname,relkind from pg_class where relfilenode = '123';

如果查询结果中 relkind = r，表示损坏的是表

比如：

tb_test, r

relname = tb_test：表示损坏的表是tb_test

如果查询结果中relkind = i，表示损坏的是一个索引

需要注意，损坏的可能是普通索引，也可能是主键或唯一键。如果索引的名称中有“_pkey”等很可能属于主键，而名称中含有 “_key”则很可能属于唯一键。如果表名是“pg_”开头的，则说明它是系统表，系统表下列解决方法不好用，具体如何解决我暂未探索

pgclass.relkind 的值的几种：

r： 表示ordinary table（普通表）；

i： 表示index（索引）；

S： 表示sequence（序列）；

V： 表示view（视图）；

m： 表示materialized view（物化视图）；

c： 表示composite type（复合类型）;

t： 表示TOAST table（TOAST 表）；

f： 表示foreign table（外部表）

3.修复

如果损坏的是表，以tb_test为例，则依次执行下列命令即可完成修复：

set zero_damaged_pages = on;

vacuum tb_test;

reindex table tb_test;

select relname ,relkind from pg_class where relfilenode='123';

如果损坏的是普通索引，以dept_number_index为例， 则依次执行：

set zero_damaged_pages = on;

reindex index tb_test_index_update_flag;
select relname ,relkind from pg_class where relfilenode='123';

如果损坏的是主键或唯一键，首先要找到它所在的表，以tb_dept_pkey为例：

select tablename,indexname from pg_indexes where indexname = 'tb_dept_pkey';

查询结果：

tb_dept, tb_dept_pkey

然后获取索引的定义：

select pg_get_constraintdef((select oid from pg_constraint where conname = ' tb_dept_pkey '));

查询结果：PRIMARY KEY (dept_id)

然后重新创建这个约束：

Alter table drop constriant tb_dept_pkey;

Alter table add constraint tb_dept_pkey PRIMARY KEY (dept_id);