金仓数据库KingbaseES V8R6普通表toast表故障修复

案例说明：

数据库在日常的维护过程中，在执行表查询（select），如下图所示，出现“could not read block 0 in file "base/16385/16408": read only 512 of 8192 bytes”故障，通过对“base/16385/16408”的定位，故障和toast表有关。本案例复现了以上故障，并提供了解决方案。

适用版本：

KingbaseES V8R6

一、案例复现

TOAST存储的表不能单独创建，只有当普通表包含了main,extended或external存储格式的字段时，系统会自动创建一个和普通表关联的TOAST表。当一行记录(tuple)存储的(包括压缩后的大小)大小超过TOAST_TUPLE_THRESHOLD(默认2K)时，会存储到TOAST表。

1、创建测试表及插入数据

# 创建测试表
prod=# createtable test1(id intprimary key ,v_name varchar);
CREATETABLE

#查看表结构
prod=# \d+ test1
                                   Table "public.test1"
 Column|  Type   |Collation| Nullable |Default| Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     |integer||notnull|| plain    ||
 v_name |varchar|||| extended ||
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)
Access method: heap

# 插入大字段数据
prod=# insertinto test1 select generate_series(1,100),'a';
INSERT0100
prod=# insertinto test1 select generate_series(101,200),repeat(md5(random()::text),8000);
INSERT0100
prod=# insertinto test1 select generate_series(201,300),'b';
INSERT0100---当字段的storage标识为“extended“时，如果字段值默认超过2k，会对字段执行压缩，如果压缩后的size
仍大于2k，则将字段值存入toast表，在源表建立到toast表的指针。

2、查看toast表信息

#获取源表对应的toast表OID
prod=# select oid,reltoastrelid,relname from pg_class where relname='test1';
  oid  | reltoastrelid | relname
-------+---------------+---------16405|16408| test1
(1row)

#获取toast表名称
prod=# select oid,relname from pg_class where oid=16408;
  oid  |    relname
-------+----------------16408| pg_toast_16405
(1row)

#获取toast表数据文件存储路径
prod=# select pg_relation_filepath('pg_toast.pg_toast_16405');
 pg_relation_filepath
----------------------
 base/16385/16408
(1row)

prod=# selectcount(*) from  pg_toast.pg_toast_16405;
 count
-------200
(1row)

prod=# \d+  pg_toast.pg_toast_16405;
TOAST table "pg_toast.pg_toast_16405"
   Column|  Type   | Storage
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  |integer| plain
 chunk_data | bytea   | plain

二、模拟toast表数据文件故障

#模拟toast表数据文件故障
[kingbase@node102 ~]$ ddif=/dev/zero of=/data/kingbase/v8r6_c6/data/base/16385/16408 bs=512 count=1
1+0 records in
1+0 records out
512 bytes (512 B) copied, 0.000188319 s, 2.7 MB/s

[kingbase@node102 ~]$ ls -lh /data/kingbase/v8r6_c6/data/base/16385/16408
-rw------- 1 kingbase kingbase 512 Nov  3 11:21 /data/kingbase/v8r6_c6/data/base/16385/16408

#重启数据库并清理缓存
[kingbase@node102 bin]$ ./sys_ctl stop -D /data/kingbase/v8r6_c6/data
waiting for server to shut down.... done
server stopped

[root@node102 ~]#  echo 3 > /proc/sys/vm/drop_caches

[kingbase@node102 bin]$ ./sys_ctl start -D /data/kingbase/v8r6_c6/data
waiting for server to start....2022-11-03 11:22:12.466 CST [15429] LOG:  sepapower extension initialized
.......
server started


#查询源表数据出现块故障
prod=# select * from test1;
ERROR:  could not read block 0 in file "base/16385/16408": read only 512 of 8192 bytes

# toast表数据为空
prod=# select * from  pg_toast.pg_toast_16405 limit 2;
 chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)

# 可以对源表做行统计查询
prod=# select count(*) from test1;
 count
-------
   300
(1 row)

三、解决toast表故障步骤

1、尝试重建索引并对表分析

# 尝试重建索引
prod=# reindex table test1;
REINDEX
prod=# reindex table pg_toast.pg_toast_16405;
REINDEX

# 执行表分析
prod=# vacuum analyze test1;
VACUUM

# 源表查询依然出现故障
prod=# select * from test1;ERROR:  missing chunk number 0 for toast value 16413 in pg_toast_16405

2、定位源表发生故障的记录

# 尝试通过手工方式定位
prod=#  select*from test1 orderby id limit 1offset1;
 id | v_name
----+--------2| a
(1row)

prod=#  select*from test1 orderby id limit 1offset100;
ERROR:  missing chunk number 0for toast value16413in pg_toast_16405

prod=#  select*from test1 orderby id limit 100offset0;
 id  | v_name
-----+--------1| a
   2| a
   3| a
   4| a
   5| a
   6| a
   7| a
   8| a
   9| a
........

prod=#  select*from test1 orderby id limit 100offset1;
ERROR:  missing chunk number 0for toast value16413in pg_toast_16405

#通过脚本定位故障记录

[kingbase@node102~]$ cat chk_toast.sh
#!/bin/bash
CNT=300         #300是表的总行数
CMD_DIR='/opt/Kingbase/ES/V8R6_C6/Server/bin'
PORT=54325for ((i=1; i<=CNT;i++))
do
  $CMD_DIR/ksql -U system  prod -p $PORT  -c "SELECT * FROM test1 order by id  LIMIT 1 offset $i" >/dev/null|| echo $i
done

# 执行脚本
[kingbase@node102~]$ sh chk_toast.sh
ERROR:  missing chunk number 0for toast value16413in pg_toast_16405
100
ERROR:  missing chunk number 0for toast value16414in pg_toast_16405
101
ERROR:  missing chunk number 0for toast value16415in pg_toast_16405
102
........
ERROR:  missing chunk number 0for toast value16511in pg_toast_16405
198
ERROR:  missing chunk number 0for toast value16512in pg_toast_16405
199---如上所示，从第100行开始到199行，字段v_name的字段值存储在toast表，因toast表故障，因此无法从源表访问。

3、清理源表中故障记录

# 确定源表需清理的记录
prod=#  select*from test1 orderby id limit 1offset99;
 id  | v_name
-----+--------100| a
(1row)

prod=#  select*from test1 orderby id limit 1offset199;
ERROR:  missing chunk number 0for toast value16512in pg_toast_16405

prod=#  select*from test1 orderby id limit 1offset200;
 id  | v_name
-----+--------201| b
(1row)

#删除源表中故障记录
prod=# deletefrom test1 where id >100and id <201;
DELETE100

#清理故障记录后源表可以正常查询
prod=# select*from test1;
 id  | v_name
-----+--------1| a
   2| a
   3| a
   4| a
   5| a
   6| a
   7| a
   8| a
   9| a
  10| a
  11| a
  12| a
  13| a
  14| a
.......

prod=# selectcount(*) from test1;
 count
-------200
(1row)


prod=# select*from pg_toast.pg_toast_16405;
 chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0rows)

#源表插入数据
prod=# insertinto test1 select generate_series(101,200),repeat(md5(random()::text),8000);
INSERT0100

#toast表生成新的记录
prod=# selectcount(*) from pg_toast.pg_toast_16405;
 count
-------200
(1row)

prod=# insertinto test1 select generate_series(301,400),repeat(md5(random()::text),8000);
INSERT0100
prod=# selectcount(*) from pg_toast.pg_toast_16405;
 count
-------400
(1row)


prod=# selectcount(distinct chunk_id) from pg_toast.pg_toast_16405 ;
 count
-------200
(1row)

---从以上所示，toast表故障问题已经解决。

四、总结

因toast表坏块问题，将导致源表不可访问，如果有有效的备份，可以通过备份来进行故障的恢复；如果没有有效的备份，可以通过对源表故障记录的清理，完成toast表的恢复。