KingbaseES V8R3备份恢复案例---sys_rman_v6备份调用pg_show_all_settings()故障

案例说明：
在KingbaseES V8R3的最新版本中使用了sys_rman_v6执行备份，但在备份过程中出现调用pg_show_all_settings()函数错误。

适用版本：
KingbaseES V8R3

一、问题现象：
如下所示，执行sys_backup.sh init出现以下故障：

[kingbase@node201 bin]$ ./sys_backup.sh init
The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established.
ECDSA key fingerprint is 23:6c:a7:c8:ef:a3:e4:68:9e:6d:9b:94:fb:c4:9c:fd.
.......
# update all node: sys_rman_v6.conf and archive_command with sys_rman_v6.archive-push...
# update all node: sys_rman_v6.conf and archive_command with sys_rman_v6.archive-push...DONE
# create stanza and check...(maybe 60+ seconds)
ERROR: create stanza failed, check log file /tmp/sys_rman_v6_stanza-create.log
[kingbase@node201 bin]$ cat /tmp/sys_rman_v6_stanza-create.log
2024-04-10 17:16:08.403 P00   INFO: stanza-create command begin 2.27: --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --kb1-pass=S0lOR0JBU0VBRE1JTg== --kb2-pass=S0lOR0JBU0VBRE1JTg== --log-level-console=info --log-level-file=info --log-path=/tmp --log-subprocess --kb2-host=192.168.1.202 --kb2-host-user=kingbase --kb1-path=/home/kingbase/cluster/R3HA/db/data --kb2-path=/home/kingbase/cluster/R3HA/db/data --kb1-port=54321 --kb2-port=54321 --kb1-user=SUPERMANAGER_V8ADMIN --kb2-user=SUPERMANAGER_V8ADMIN --repo1-path=/home/kingbase/kbbr3_repo --stanza=kingbase
WARN: unable to check kb-1: [DbQueryError] unable to execute query 'SELECT context FROM pg_show_all_settings() WHERE name = 'client_idle_timeout'': ERROR:  function pg_show_all_settings() does not exist
      LINE 1: SELECT context FROM pg_show_all_settings() WHERE name = 'cli...
                                  ^
      HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
WARN: unable to check kb-2: [DbQueryError] raised from remote-0 protocol on '192.168.1.202': unable to execute query 'SELECT context FROM pg_show_all_settings() WHERE name = 'client_idle_timeout'': ERROR:  function pg_show_all_settings() does not exist
      LINE 1: SELECT context FROM pg_show_all_settings() WHERE name = 'cli...
                                  ^
      HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR: [056]: unable to find primary cluster - cannot proceed
2024-04-10 17:16:09.828 P00   INFO: stanza-create command end: aborted with exception [056]

如下图所示，在执行sys_backup.sh init时，系统调用sys_rman_v6执行备份，出现以下故障：

二、问题分析

查询pg_show_all_settings()函数：
如下所示，当前数据库为V8R3版本，不存在pg_show_all_settings()函数，对应的函数为sys_show_all_settings()：

test=# select proname from sys_proc where proname like '%show_all_settings%';
        proname
-----------------------
 SYS_SHOW_ALL_SETTINGS
(1 rows)

执行sys_rman_v6备份时，系统调用pg_show_all_settings()函数，而V8R3版本无此函数，只有sys_show_all_settings()函数。

三、问题解决

创建pg_show_all_settings()函数：

1、查看sys_show_all_settings()函数定义

test=# \sf+ sys_show_all_settings()
    CREATE OR REPLACE INTERNAL FUNCTION SYS_CATALOG.SYS_SHOW_ALL_SETTINGS(OUT NAME TEXT, OUT SETTING TEXT, OUT UNIT TEXT, OUT CATEGORY TEXT, OUT SHORT_DESC TEXT, OUT EXTRA_DESC TEXT, OUT CONTEXT TEXT, OUT VARTYPE TEXT, OUT SOURCE TEXT, OUT MIN_VAL TEXT, OUT MAX_VAL TEXT, OUT ENUMVALS TEXT[], OUT BOOT_VAL TEXT, OUT RESET_VAL TEXT, OUT SOURCEFILE TEXT, OUT SOURCELINE INTEGER, OUT PENDING_RESTART BOOLEAN)
     RETURNS SETOF RECORD
     LANGUAGE INTERNAL
     STABLE PARALLEL SAFE STRICT
   AS $function$show_all_settings$function$

2、创建pg_show_all_settings()函数

test=# \set SQLTERM /
test-# CREATE OR REPLACE INTERNAL FUNCTION SYS_CATALOG.PG_SHOW_ALL_SETTINGS(OUT NAME TEXT, OUT SETTING TEXT, OUT UNIT TEXT, OUT CATEGORY TEXT, OUT SHORT_DESC TEXT, OUT EXTRA_DESC TEXT, OUT CONTEXT TEXT, OUT VARTYPE TEXT, OUT SOURCE TEXT, OUT MIN_VAL TEXT, OUT MAX_VAL TEXT, OUT ENUMVALS TEXT[], OUT BOOT_VAL TEXT, OUT RESET_VAL TEXT, OUT SOURCEFILE TEXT, OUT SOURCELINE INTEGER, OUT PENDING_RESTART BOOLEAN)
test-#          RETURNS SETOF RECORD
test-#          LANGUAGE INTERNAL
test-#          STABLE PARALLEL SAFE STRICT
test-#        AS $function$show_all_settings$function$
test-# /
test-# \set SQLTERM ;
CREATE INTERNAL FUNCTION

3、执行sys_backup.sh备份
如下所示，执行sys_rman_v6初始化及备份成功。

[kingbase@node201 bin]$ ./sys_backup.sh init
# generate local sys_rman_v6.conf...DONE
# update all node: sys_rman_v6.conf and archive_command with sys_rman_v6.archive-push...
# update all node: sys_rman_v6.conf and archive_command with sys_rman_v6.archive-push...DONE
# create stanza and check...(maybe 60+ seconds)
# create stanza and check...DONE
# initial first full backup...(maybe several minutes)
# initial first full backup...DONE
# Initial sys_rman_v6 OK.
'sys_backup.sh start' should be executed when need back-rest feature.