opengauss 系统索引损坏修复

转载

食品安全辛吉飞 2024-10-30 09:33:56 博主文章分类：opengauss

文章标签 sql 数据库 PostgreSQL 文章分类 开源 指尖人生

MogDB=# select * from pg_indexes where tablename = 'pg_class';
 schemaname | tablename |             indexname             | tablespace |                                                         indexdef
                                                          
------------+-----------+-----------------------------------+------------+-----------------------------------------------------------------
----------------------------------------------------------
 pg_catalog | pg_class  | pg_class_oid_index                |            | CREATE UNIQUE INDEX pg_class_oid_index ON pg_class USING btree (
oid) TABLESPACE pg_default
 pg_catalog | pg_class  | pg_class_relname_nsp_index        |            | CREATE UNIQUE INDEX pg_class_relname_nsp_index ON pg_class USING
 btree (relname, relnamespace) TABLESPACE pg_default
 pg_catalog | pg_class  | pg_class_tblspc_relfilenode_index |            | CREATE INDEX pg_class_tblspc_relfilenode_index ON pg_class USING
 btree (reltablespace, relfilenode) TABLESPACE pg_default
(3 rows)


MogDB=# select pg_relation_filepath('pg_class_oid_index');
 pg_relation_filepath 
----------------------
 base/16133/24577
(1 row)

模拟损坏系统表

dd if=/dev/zero of=/mogdata/cluster_26000/base/16133/24577 bs=8k count=10000

重启数据库（可启动，无法进入数据库）

[sysomm@db1 ~]$ gsql -r 
gsql: FATAL:  index "pg_class_oid_index" contains unexpected zero page at block 0
HINT:  Please REINDEX it.

方法一：

单用户模式(mogdb --single -P -D /mogdata/cluster_26000 postgres)

[sysomm@db1 cluster_26000]$ mogdb --single -P -D /mogdata/cluster_26000 postgres  （库没关）
2024-10-30 09:29:09.274 [unknown] [unknown] localhost 46960732282944 0[0:0#0]  [BACKEND] FATAL:  could not lock file "postmaster.pid.lock": Resource temporarily unavailable

[sysomm@db1 cluster_26000]$ gs_ctl stop
[2024-10-30 09:31:02.985][98116][][gs_ctl]: gs_ctl stopped ,datadir is /mogdata/cluster_26000 
waiting for server to shut down.... done
server stopped
[sysomm@db1 cluster_26000]$ mogdb --single -P -D /mogdata/cluster_26000 postgres

PostgreSQL stand-alone backend 9.2.4
backend> REINDEX index pg_class_oid_index;

方法二：