随笔- 320 文章- 0 评论- 5 阅读- 34799

KingbaseES V8R3备份恢复案例---sys_rman_v6执行物理备份

案例说明：
在KingbaseES V8R3的最新版本中使用了sys_rman_v6执行备份，本案例介绍如何使用sys_rman_v6执行物理备份。
适用版本：
KingbaseES V8R3

系统架构：

test=# show pool_nodes;
 node_id |   hostname    | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_
delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------
------
 0       | 192.168.1.201 | 54321 | up     | 0.500000  | primary | 2          | false             | 0
 1       | 192.168.1.202 | 54321 | up     | 0.500000  | standby | 0          | true              | 0
(2 rows)

一、初始化配置
1、启动归档（all nodes）

[kingbase@node201 bin]$  cat ../data/kingbase.conf |grep archive_
archive_mode = on             # enables archiving; off, on, or always

2、配置sys_backup.conf
如下所示，配置sys_backup.conf，在集群主节点执行备份：

[kingbase@node201 bin]$ cat sys_backup.conf|grep -v ^#|grep -v ^$
_target_db_style="cluster"
_one_db_ip="127.0.0.1"
_repo_ip="127.0.0.1"
_stanza_name="kingbase"
_os_user_name="kingbase"
_repo_path="/home/kingbase/kbbr3_repo"
_repo_retention_full_count=5
_crond_full_days=7
_crond_diff_days=0
_crond_incr_days=1
_crond_full_hour=2
_crond_diff_hour=3
_crond_incr_hour=4
_os_ip_cmd="/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
_single_data_dir="/home/kingbase/ES/V8_single/data"
_single_bin_dir="/home/kingbase/ES/V8_single/Server/bin"
_single_db_user="system"
_single_db_port="54321"
_kb_pass="S0lOR0JBU0VBRE1JTg=="

3、创建pg_show_all_settings()函数
如下图所示，默认V8R3不存在pg_show_all_settings()函数，在执行sys_backup.sh init时，系统调用sys_rman_v6执行备份，出现以下故障：

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 FUNC

二、执行sys_backup.sh init

[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.

在执行sys_backup.sh init后，归档参数被修改：

[kingbase@node201 bin]$ cat ../data/kingbase.conf |grep archive_
archive_mode=on
archive_command='/home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config /home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase archive-push %p'

# 查看archive_command

test=# show archive_command ;
                                                           archive_command
---------------------------------------------------------------------------------------------------------------
 /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config /home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=ki
ngbase archive-push %p
(1 row)

sys_rman_v6.conf文件：

[kingbase@node201 bin]$ cat ~/kbbr3_repo/sys_rman_v6.conf
# Genarate by script at 20240410192459, should not change manually
[kingbase]
kb1-path=/home/kingbase/cluster/R3HA/db/data
kb1-port=54321
kb1-user=SUPERMANAGER_V8ADMIN
kb1-pass=S0lOR0JBU0VBRE1JTg==
kb2-path=/home/kingbase/cluster/R3HA/db/data
kb2-port=54321
kb2-user=SUPERMANAGER_V8ADMIN
kb2-pass=S0lOR0JBU0VBRE1JTg==
kb2-host=192.168.1.202
kb2-host-user=kingbase

[global]
repo1-path=/home/kingbase/kbbr3_repo
repo1-retention-full=5
log-path=/tmp/
log-level-file=info
log-level-console=info
log-subprocess=y
process-max=4
#### default gz, support: gz none
compress-type=gz
compress-level=3

三、启动备份

1、启动备份（创建备份crond任务）

[kingbase@node201 bin]$ ./sys_backup.sh start
Enable some sys_rman_v6 in crontab-daemon
Set full-backup in 7 days
Set incr-backup in 1 days
0 2 */7 * * kingbase /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase --archive-copy --type=full backup >>/tmp/sys_rman_v6_backup_full.log 2>&1
0 4 */1 * * kingbase /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase --archive-copy --type=incr backup >>/tmp/sys_rman_v6_backup_incr.log 2>&1

# 查看备份计划任务
[kingbase@node201 bin]$ cat /etc/cron.d/KINGBASECRON
......
0 2 */7 * * kingbase /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase --archive-copy --type=full backup >>/tmp/sys_rman_v6_backup_full.log 2>&1
0 4 */1 * * kingbase /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase --archive-copy --type=incr backup >>/tmp/sys_rman_v6_backup_incr.log 2>&1

2、执行备份

[kingbase@node201 bin]$ /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase --archive-copy --type=full backup
2024-04-10 19:26:50.994 P00   INFO: backup command begin 2.27: --archive-copy --compress-level=3 --compress-type=gz --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 --process-max=4 --repo1-path=/home/kingbase/kbbr3_repo --repo1-retention-full=5 --stanza=kingbase --type=full
2024-04-10 19:26:53.142 P00   INFO: Read from ControlFile catalog_version is 201608131
.......
2024-04-10 19:27:01.236 P00   INFO: backup stop archive = 00000005000000010000002C, lsn = 1/2C000130
2024-04-10 19:27:01.241 P00   INFO: check archive for segment(s) 00000005000000010000002C:00000005000000010000002C
2024-04-10 19:27:01.296 P00   INFO: new backup label = 20240410-192653F
2024-04-10 19:27:01.377 P00   INFO: backup command end: completed successfully (10386ms)
2024-04-10 19:27:01.377 P00   INFO: expire command begin 2.27: --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --log-level-console=info --log-level-file=info --log-path=/tmp --log-subprocess --kb2-host=192.168.1.202 --kb2-host-user=kingbase --repo1-path=/home/kingbase/kbbr3_repo --repo1-retention-full=5 --stanza=kingbase
2024-04-10 19:27:01.898 P00   INFO: expire command end: completed successfully (521ms)

3、查看备份

[kingbase@node201 bin]$ /home/kingbase/cluster/R3HA/db/bin/sys_rman_v6 --config=/home/kingbase/kbbr3_repo/sys_rman_v6.conf --stanza=kingbase info
stanza: kingbase
    status: ok
    cipher: none

    db (current)
        wal archive min/max (V008R003C002B0370-1): 000000050000000100000028/00000005000000010000002C

        full backup: 20240410-192509F
            timestamp start/stop: 2024-04-10 19:25:09 / 2024-04-10 19:25:16
            wal start/stop: 00000005000000010000002A / 00000005000000010000002A
            database size: 107.1MB, backup size: 107.1MB
            repository size: 13.6MB, repository backup size: 13.6MB

        full backup: 20240410-192653F
            timestamp start/stop: 2024-04-10 19:26:53 / 2024-04-10 19:27:01
            wal start/stop: 00000005000000010000002C / 00000005000000010000002C
            database size: 107.1MB, backup size: 107.1MB
            repository size: 13.6MB, repository backup size: 13.6MB

四、初始化故障案例

如下备份日志所示，在执行视图查询时无权限：

[kingbase@node201 bin]$ cat /tmp/sys_rman_v6_check.log
2024-04-10 17:57:21.233 P00   INFO: check 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
2024-04-10 17:57:23.355 P00   INFO: Read from ControlFile catalog_version is 201608131
2024-04-10 17:57:23.356 P00   INFO: switch wal not performed because this is a standby
2024-04-10 17:57:23.357 P00   INFO: Read from ControlFile catalog_version is 201608131
ERROR: [057]: unable to execute query 'select sys_catalog.sys_create_restore_point('sys_rman_v6 Archive Check')::text': ERROR:  permission denied for function SYS_CREATE_RESTORE_POINT
2024-04-10 17:57:23.558 P00   INFO: check command end: aborted with exception [057]

system用户执行视图查询：
如下所示，system执行视图查询成功。

test=# select sys_catalog.sys_create_restore_point('sys_rman_v6 Archive Check')::text;
 sys_create_restore_point
--------------------------
 1/210002C0
(1 row)

supermanager_v8admin执行视图查询：（备份用户为supermanager_v8admin）
如下所示，supermanager_v8admin查询失败，无权限：

KINGBASEADMIN[kingbase@node201 bin]$ ./ksql -U supermanager_v8admin -W KINGBASEADMIN test
ksql (V008R003C002B0370)
Type "help" for help.

test=# select sys_catalog.sys_create_restore_point('sys_rman_v6 Archive Check')::text;
ERROR:  permission denied for function SYS_CREATE_RESTORE_POINT

经排查发现系统启用了restricted DBA:
如下图所示，系统启用restricted DBA后，导致supermanager_v8admin无权限访问视图：

关闭restricted DBA后，问题解决。

[kingbase@node201 bin]$ ./ksql -U supermanager_v8admin -W KINGBASEADMIN test
ksql (V008R003C002B0370)
Type "help" for help.

test=# select sys_catalog.sys_create_restore_point('sys_rman_v6 Archive Check')::text;
 sys_create_restore_point
--------------------------
 1/22000100
(1 row)

五、总结
在KingbaseES V8R3版本启用了sys_rman_v6执行物理备份，但存在一定的bug，需要先解决bug后，再执行备份初始化及启动备份。