sys_ control文件损坏：
需要手工指定一些参数完成sys_resetwal相关操作
当前数据库信息
test=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+---------------------+--------+--------
public | sys_stat_statements | 视图 | system
public | t | 数据表 | system
public | t2 | 数据表 | system
public | t3 | 数据表 | system
public | t4 | 数据表 | system
public | t5 | 数据表 | system
public | t6 | 数据表 | system
public | t7 | 数据表 | system
public | t8 | 数据表 | system
public | tbl | 数据表 | system
(10 行记录)
test=# select * from t;
bl | var
----+-----------
1 | 12313
0 | 12231313
0 | 122se1
1 | 1sdas2se1
(4 行记录)
test=#
[kingbase@postgres ~]$ ps -ef|grep kingbase
root 812 1194 0 14:56 ? 00:00:00 sshd: kingbase [priv]
kingbase 817 812 0 14:56 ? 00:00:00 sshd: kingbase@pts/2
kingbase 818 817 0 14:56 pts/2 00:00:00 -bash
root 9502 9452 0 17:37 pts/0 00:00:00 su - kingbase
kingbase 9503 9502 0 17:37 pts/0 00:00:00 -bash
root 12554 1194 0 18:35 ? 00:00:00 sshd: kingbase [priv]
kingbase 12559 12554 0 18:35 ? 00:00:00 sshd: kingbase@pts/3
kingbase 12560 12559 0 18:35 pts/3 00:00:00 -bash
kingbase 14046 1 0 19:02 ? 00:00:00 /home/kingbase/V8R6C6B21/ES/V8/KESRealPro/V008R006C006B0021/Server/bin/kingbase -D data
kingbase 14047 14046 0 19:02 ? 00:00:00 kingbase: logger
kingbase 14049 14046 0 19:02 ? 00:00:00 kingbase: checkpointer
kingbase 14050 14046 0 19:02 ? 00:00:00 kingbase: background writer
kingbase 14051 14046 0 19:02 ? 00:00:00 kingbase: walwriter
kingbase 14052 14046 0 19:02 ? 00:00:00 kingbase: autovacuum launcher
kingbase 14053 14046 0 19:02 ? 00:00:00 kingbase: stats collector
kingbase 14054 14046 0 19:02 ? 00:00:00 kingbase: ksh writer
kingbase 14055 14046 0 19:02 ? 00:00:00 kingbase: ksh collector
kingbase 14056 14046 0 19:02 ? 00:00:00 kingbase: kwr collector
kingbase 14057 14046 0 19:02 ? 00:00:00 kingbase: logical replication launcher
kingbase 14062 12560 0 19:02 pts/3 00:00:00 ksql -Usystem -dtest
kingbase 14063 14046 0 19:02 ? 00:00:00 kingbase: system test [local] idle
kingbase 14118 818 0 19:03 pts/2 00:00:00 ps -ef
kingbase 14119 818 0 19:03 pts/2 00:00:00 grep --color=auto kingbase
[kingbase@postgres ~]$
[kingbase@postgres ~]$ cd data/global/
[kingbase@postgres global]$ pwd
/home/kingbase/data/global
[kingbase@postgres global]$ ls -l
总用量 664
-rw------- 1 kingbase kingbase 0 11月 10 17:06 8059
-rw------- 1 kingbase kingbase 0 11月 10 17:06 8074
-rw------- 1 kingbase kingbase 8192 11月 10 17:06 8076
-rw------- 1 kingbase kingbase 8192 11月 10 19:02 sys_control
-rw------- 1 kingbase kingbase 512 11月 10 17:06 sys_filenode.map
-rw------- 1 kingbase kingbase 34324 11月 10 19:02 sys_internal.init
[kingbase@postgres global]$
[kingbase@postgres ~]$ sys_ctl -D data stop
waiting for server to shut down...... done
server stopped
[kingbase@postgres ~]$ ps -ef|grep kingbase
root 812 1194 0 14:56 ? 00:00:00 sshd: kingbase [priv]
kingbase 817 812 0 14:56 ? 00:00:00 sshd: kingbase@pts/2
kingbase 818 817 0 14:56 pts/2 00:00:00 -bash
root 9502 9452 0 17:37 pts/0 00:00:00 su - kingbase
kingbase 9503 9502 0 17:37 pts/0 00:00:00 -bash
root 12554 1194 0 18:35 ? 00:00:00 sshd: kingbase [priv]
kingbase 12559 12554 0 18:35 ? 00:00:00 sshd: kingbase@pts/3
kingbase 12560 12559 0 18:35 pts/3 00:00:00 -bash
kingbase 14062 12560 0 19:02 pts/3 00:00:00 ksql -Usystem -dtest
kingbase 14303 9503 0 19:06 pts/0 00:00:00 ps -ef
kingbase 14304 9503 0 19:06 pts/0 00:00:00 grep --color=auto kingbase
模拟control文件损坏
[kingbase@postgres global]$ pwd
/home/kingbase/data/global
[kingbase@postgres global]$ ls -l sys_control
-rw------- 1 kingbase kingbase 8192 11月 10 19:02 sys_control
[kingbase@postgres global]$ rm -rf sys_control
[kingbase@postgres global]$
无法启动
[kingbase@postgres ~]$ sys_ctl -D data start
waiting for server to start....kingbase: could not find the database system
Expected to find it in the directory "/home/kingbase/data",
but could not open file "/home/kingbase/data/global/sys_control": No such file or directory
stopped waiting
sys_ctl: could not start server
Examine the log output.
[kingbase@postgres ~]$
再次启动
[kingbase@postgres ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 11:09:18.017 GMT [14432] FATAL: could not read file "/home/kingbase/data/global/sys_control": read 0 of 368
stopped waiting
sys_ctl: could not start server
Examine the log output.
使用 sys_resetwal恢复
sys_resetwal 使用说明
[kingbase@postgres ~]$ sys_resetwal --help
sys_resetwal 重置一个Kingbase数据库集簇的预写式日志.
用法:
sys_resetwal [选项]... 数据目录
选项:
-c, --commit-timestamp-ids=XID,XID
设置提交时间可以检索到的最老的和最新的事务ID
(0意味着没有变化)
[-D, --kingbase-data=]DATADIR data directory
-e, --epoch=XIDEPOCH 设置下一个事务ID的epoch
-f, --force 强制更新完成
-g, --dbmode set database mode
-l, --next-wal-file=WALFILE 设置新的WAL最小起始位置
-m, --multixact-ids=MXID,MXID 设置下一个和最旧的多事务ID
-n, --dry-run 不更新，只显示将要执行的操作
-o, --next-oid=OID 设置下一个OID
-O, --multixact-offset=OFFSET 设置下一个多事务偏移量
-V, --version 输出版本信息，然后退出
-x, --next-transaction-id=XID 设置下一个事务ID
--wal-segsize=SIZE WAL段的大小（兆字节）
-?, --help 显示本帮助，然后退出
Report bugs to <[email protected]>.
设置next-wal-file
-l, –next-wal-file=WALFILE
设置新的WAL最小起始位置，这个值可以从data/sys_wal目录下去看最后一个WAL文件，这个文件的id+1即可
[kingbase@postgres sys_wal]$ pwd
/home/kingbase/data/sys_wal
[kingbase@postgres sys_wal]$ ls -l |wc -l
42
[kingbase@postgres sys_wal]$ ll
总用量 655360
-rw------- 1 kingbase kingbase 16777216 11月 10 19:03 000000010000000000000029
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000002F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000030
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000031
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000032
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000033
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000034
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000035
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000036
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000037
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000038
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000039
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 00000001000000000000003F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000040
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000041
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000042
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000043
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000044
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000045
-rw------- 1 kingbase kingbase 16777216 11月 10 18:25 000000010000000000000046
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000047
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000048
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000049
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004A
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004B
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004C
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004D
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004E
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 00000001000000000000004F
-rw------- 1 kingbase kingbase 16777216 11月 10 18:26 000000010000000000000050
drwx------ 2 kingbase kingbase 6 11月 10 17:06 archive_status
[kingbase@postgres sys_wal]$
000000010000000000000050 + 1 000000010000000000000051
-l 000000010000000000000051
设置next-transaction
-x, –next-transaction-id=XID,设置下一个事务ID，这个值可以从sys_xact目录下的文件中查询
[kingbase@postgres sys_xact]$ pwd
/home/kingbase/data/sys_xact
[kingbase@postgres sys_xact]$ ll
总用量 256
-rw------- 1 kingbase kingbase 262144 11月 10 18:37 0000
[kingbase@postgres sys_xact]$
只有一个0000，那么下一个XID+1就是0001，* 1048576
0000 + 1 = 0001 * 1048576 = 1048576 转换为16进制就是 0x000100000 实际后面直接加5个0就行了
-x 0×000100000
multixact-ids设置
-m, –multixact-ids=MXID1,MXID2，设置下一个和最旧的多事务ID
MXID1和MXID2，都可以从DATA/sys_multixact/offsets目录下获得。
MXID1的值,首先找到最大值 +1 再乘以 65536 (0×10000,相当于后面加4个0)作为MXID1的值
MXID2的值,找到最小的值，后面加4个0，作为MXID2的值
/home/kingbase/data/sys_multixact/offsets
[kingbase@postgres offsets]$ ll
总用量 8
-rw------- 1 kingbase kingbase 8192 11月 10 17:11 0000
[kingbase@postgres offsets]$
MXID1=0000+1 * 65536 = 0x00010000
MXID2=0x00000000
-m 0x00010000,0x00000000
multixact-offset设置下一个事务偏移量
-O, –multixact-offset=OFFSET,通过DATA/sys_multixact/members目录下文件计算
找到最大值，+1，乘以 52352 (0xCC80)
[kingbase@postgres members]$ pwd
/home/kingbase/data/sys_multixact/members
[kingbase@postgres members]$ ll
总用量 8
-rw------- 1 kingbase kingbase 8192 11月 10 17:06 0000
[kingbase@postgres members]$
-O 0xCC80
最终命令为：
sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 -D /home/kingbase/data
[kingbase@postgres ~]$ sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 -D /home/kingbase/data
sys_resetwal: error: oldest multitransaction ID (-m) must not be 0
Latest checkpoint's oldestActiveXID: 1022
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
实际环境不可能是0.调整后处理
sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000001 -O 0xCC80 -D /home/kingbase/data
确认输出信息无误
[kingbase@postgres ~]$ sys_resetwal -l 000000010000000000000051 -x 0x000100000 -m 0x00010000,0x00000001 -O 0xCC80 -D /home/kingbase/data
sys_resetwal: warning: sys_control exists but is broken or wrong version; ignoring it
Guessed sys_control values:
pg_control version number: 1201
Catalog version number: 202208021
Database system identifier: 7164349438480498050
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID: 0:3
Latest checkpoint's NextOID: 12000
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 3
Latest checkpoint's oldestXID's DB: 0
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 0
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1988
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
database mode: 0
auth method mode: 0
Values to be changed:
First log segment after reset: 000000010000000000000051
NextMultiXactId: 65536
OldestMultiXid: 1
OldestMulti's DB: 0
NextMultiOffset: 52352
NextXID: 1048576
OldestXID: 2296015872
OldestXID's DB: 0
If these values seem acceptable, use -f to force reset.
[kingbase@postgres ~]$
确认信息没问题后加 -f 参数创建新的sys_control
[kingbase@postgres global]$ pwd
/home/kingbase/data/global
[kingbase@postgres global]$ ls -l sys_control
-rw------- 1 kingbase kingbase 8192 11月 10 19:38 sys_control
[kingbase@postgres global]$
创建成功
启动数据库
[kingbase@postgres ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 19:39:44.626 CST [16022] FATAL: shared_preload_libraries cannot contains libXXX_parser when pg-mode
2022-11-10 19:39:44.626 CST [16022] LOG: database system is shut down
stopped waiting
sys_ctl: could not start server
Examine the log output.
先注释掉shared_preload_libraries参数载启动
[kingbase@postgres data]$ vi kingbase.conf
[kingbase@postgres data]$ cd ..
[kingbase@postgres ~]$ sys_ctl -D data start
waiting for server to start....2022-11-10 19:42:41.220 CST [16253] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-11-10 19:42:41.220 CST [16253] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-11-10 19:42:41.220 CST [16253] LOG: listening on IPv6 address "::", port 54321
2022-11-10 19:42:41.224 CST [16253] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-11-10 19:42:41.249 CST [16253] LOG: redirecting log output to logging collector process
2022-11-10 19:42:41.249 CST [16253] HINT: Future log output will appear in directory "sys_log".
done
server started
[kingbase@postgres ~]$
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
WARNING:License file will expire in 15 days.
test=# select * from t;
bl | var
----+-----------
1 | 12313
0 | 12231313
0 | 122se1
1 | 1sdas2se1
(4 rows)
test=#