KingbasES V9备份恢复案例---执行物理备份恢复用户权限
案例说明：
测试在KingbaseES V8R6/V9版本，执行物理备份的用户的最小权限。
适用版本：
KingbaseES V8R6/V9
一、创建用户并授权
1、授权用户
Tips： 必须在kingbase数据库下授权！
如下所示，创建普通用户，并授权访问相应的函数：
# 创建普通用户
prod=# create user rman_u with password 'beijing';
CREATE ROLE
test=# \du+ rman_u
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
rman_u | | {} |
# 对相应的函数授权访问
[kingbase@node208 bin]$ ./ksql -U system kingbase
Type "help" for help.
kingbase=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO rman_u ;
GRANT
kingbase=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO rman_u ;
GRANT
kingbase=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(label text, fast boolean,exclusive boolean )
kingbase-# TO rman_u ;
GRANT
kingbase=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup() TO rman_u ;
GRANT
kingbase=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(exclusive boolean,
kingbase(# wait_for_archive boolean,lsn OUT pg_lsn, labelfile OUT text, spcmapfile OUT text) TO rman_u ;
GRANT
2、创建用户免密访问
[kingbase@node208 bin]$ ./sys_encpwd -H \* -P \* -D \* -U rman_u -W 'beijing'
[kingbase@node208 bin]$ cat ~/.encpwd
*:*:*:esrep:S2luZ2Jhc2VoYTExMA==
*:*:*:system:MTIzNDU2
*:*:*:rman_u:YmVpamluZw==
3、测试用户对函数的访问
[kingbase@node208 bin]$ ./ksql -U rman_u kingbase
Type "help" for help.
kingbase=> select pg_catalog.pg_create_restore_point('sys_rman Archive Check')::text;
pg_create_restore_point
-------------------------
0/1600BA48
(1 row)
二、执行备份初始化
1、备份配置文件
如下所示，采用single模式，备份使用普通用户：
[kingbase@node208 bin]$ cat sys_backup.conf |grep -v ^#|grep -v ^$
_target_db_style="single"
_one_db_ip="192.168.1.208"
_repo_ip="192.168.1.208"
_stanza_name="kingbase"
_os_user_name="kingbase"
_repo_path="/home/kingbase/kbbr1_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
_band_width=0
_os_ip_cmd="/sbin/ip"
_os_rm_cmd="/bin/rm"
_os_sed_cmd="/bin/sed"
_os_grep_cmd="/bin/grep"
_os_base64_cmd="/bin/base64"
_single_data_dir="/home/kingbase/db/v9/data"
_single_bin_dir="/home/kingbase/cluster/v9/kingbase/bin"
_single_db_user="rman_u"
_single_db_port="54321"
.......
如下所示，single模式备份：
2、执行初始化
如下所示，备份初始化成功：
[kingbase@node208 bin]$ ./sys_backup.sh init
# pre-condition: check the non-archived WAL files
# generate single sys_rman.conf...DONE
# update single archive_command with sys_rman.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 OK.
'sys_backup.sh start' should be executed when need back-rest feature.
3、查看备份信息
[kingbase@node208 bin]$ /opt/Kingbase/ES/V9/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase info
WARN: set process-max 4 is too large, auto set to CPU core count 1
stanza: kingbase
status: ok
cipher: none
db (current)
wal archive min/max (V009R001C002B0014): 000000080000000000000016/000000080000000000000018
full backup: 20241205-160816F
timestamp start/stop: 2024-12-05 16:08:16 / 2024-12-05 16:08:22
wal start/stop: 000000080000000000000018 / 000000080000000000000018
database size: 104.8MB, database backup size: 104.8MB
repo1: backup set size: 104.8MB, backup size: 104.8MB
三、执行PITR恢复测试
1、模拟数据丢失
prod=# select count(*) from t1;
count
-------
200
(1 row)
prod=# select now();
now
-------------------------------
2024-12-05 16:12:25.599684+08
(1 row)
# 模拟用户误操作
prod=# delete from t1 where id >101;
DELETE 99
prod=# select count(*) from t1;
count
-------
101
(1 row)
2、执行物理备份恢复
1）备份数据目录
[kingbase@node208 v9]$ mv data data.bk
2）关闭数据库服务及执行备份restore
如下所示，备份用户为普通用户：
[kingbase@node208 v9]$ /opt/Kingbase/ES/V9/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase --target="2024-12-05 16:12:25" --type=time restore
2024-12-05 16:16:45.472 P00 INFO: restore command begin 2.27: --band-width=0 --cmd-ssh=/home/kingbase/cluster/v9/kingbase/bin/sys_securecmd --config=/home/kingbase/kbbr1_repo/sys_rman.conf --exec-id=190062-9b37075d --kb1-path=/home/kingbase/db/v9/data --link-all --log-level-console=info --log-level-file=info --log-path=/home/kingbase/cluster/v9/kingbase/log --log-subprocess --non-archived-space=1024 --process-max=4 --repo1-path=/home/kingbase/kbbr1_repo --stanza=kingbase --target="2024-12-05 16:12:25" --type=time
WARN: set process-max 4 is too large, auto set to CPU core count 1
2024-12-05 16:16:45.497 P00 INFO: repo1: restore backup set 20241205-160816F, recovery will start at 2024-12-05 16:08:16
2024-12-05 16:16:45.596 P00 INFO: Restore Process: FILE: 1 / 2806 0% SZIE: 16777216 bytes / 109842336 bytes 16MB / 104.8MB 15%
........
2024-12-05 16:16:47.962 P00 INFO: write updated /home/kingbase/db/v9/data/kingbase.auto.conf
2024-12-05 16:16:47.967 P00 INFO: restore global/sys_control (performed last to ensure aborted restores cannot be started)
2024-12-05 16:16:47.968 P00 INFO: restore size = 104.8MB, file total = 2806
2024-12-05 16:16:47.969 P00 INFO: restore command end: completed successfully (2502ms)
3）查看restore后kingbase.auto.conf配置
[kingbase@node208 v9]$ cat /home/kingbase/db/v9/data/kingbase.auto.conf
# Recovery settings generated by sys_rman restore on 2024-12-05 16:16:47
restore_command = '/opt/Kingbase/ES/V9/KESRealPro/V009R001C002B0014/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --lock-path=/home/kingbase/kbbr1_repo --stanza=kingbase archive-get %f "%p"'
recovery_target_time = '2024-12-05 16:12:25'
4）启动数据库服务执行recovery
[kingbase@node208 bin]$ ./sys_ctl start -D /home/kingbase/db/v9/data
waiting for server to start....2024-12-05 16:18:06.480 CST [190606] LOG: sepapower extension initialized
........
done
# 查看sys_log日志
2024-12-05 16:18:06.865 CST [190609] LOG: restored log file "000000080000000000000018" from archive
2024-12-05 16:18:06.939 CST [190609] LOG: redo starts at 0/18000028
2024-12-05 16:18:06.939 CST [190609] LOG: redo wal segment count 1
2024-12-05 16:18:06.941 CST [190609] LOG: consistent recovery state reached at 0/180000F8
2024-12-05 16:18:06.941 CST [190606] LOG: database system is ready to accept read only connections
2024-12-05 16:18:06.948 P00 INFO: archive-get command begin 2.27: [000000080000000000000019, sys_wal/RECOVERYXLOG] --archive-timeout=600 --band-width=0 --cmd-ssh=/home/kingbase/cluster/v9/kingbase/bin/sys_securecmd --config=/home/kingbase/kbbr1_repo/sys_rman.conf --exec-id=190621-7f02c787 --kb1-path=/home/kingbase/db/v9/data --lock-path=/home/kingbase/kbbr1_repo --log-level-console=info --log-level-file=info --log-path=/home/kingbase/cluster/v9/kingbase/log --log-subprocess --process-max=4 --repo1-path=/home/kingbase/kbbr1_repo --stanza=kingbase
WARN: set process-max 4 is too large, auto set to CPU core count 1
2024-12-05 16:18:06.968 P00 INFO: found 000000080000000000000019 in the repo1: 12-1 archive
2024-12-05 16:18:06.968 P00 INFO: archive-get command end: completed successfully (23ms)
2024-12-05 16:18:06.968 CST [190609] LOG: restored log file "000000080000000000000019" from archive
2024-12-05 16:18:07.000 CST [190609] LOG: process: 1/1
2024-12-05 16:18:07.000 CST [190609] LOG: recovery stopping before commit of transaction 1147, time 2024-12-05 16:12:37.543667+08
2024-12-05 16:18:07.000 CST [190609] LOG: recovery has paused
2024-12-05 16:18:07.000 CST [190609] HINT: Execute sys_wal_replay_resume() to continue.
5）查看数据恢复
如下所示，数据库数据恢复完成：
[kingbase@node208 bin]$ ./ksql -U system prod
Type "help" for help.
prod=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+--------
public | sys_stat_statements | view | system
public | sys_stat_statements_all | view | system
public | t1 | table | system
(3 rows)
prod=# select count(*) from t1;
count
-------
200
(1 row)
---此时，数据库处于read-only模式。
# 完成数据库恢复
prod=# select sys_wal_replay_resume();
sys_wal_replay_resume
-----------------------
(1 row)
注释kingbase.auto.conf中的恢复项并重启数据库服务：
四、测试增量备份
1、执行事务操作
prod=# insert into t1 values (generate_series(201,300),'usr'||generate_series(201,300));
INSERT 0 100
prod=# select count(*) from t1;
count
-------
300
(1 row)
2、执行增量备份
如下所示，增量备份完成：
[kingbase@node208 bin]$ /opt/Kingbase/ES/V9/Server/bin/sys_rman --config=/home/kingbase/kbbr1_repo/sys_rman.conf --stanza=kingbase info
WARN: set process-max 4 is too large, auto set to CPU core count 1
stanza: kingbase
status: ok
cipher: none
db (current)
wal archive min/max (V009R001C002B0014): 000000080000000000000016/00000009000000000000001D
full backup: 20241205-160816F
timestamp start/stop: 2024-12-05 16:08:16 / 2024-12-05 16:08:22
wal start/stop: 000000080000000000000018 / 000000080000000000000018
database size: 104.8MB, database backup size: 104.8MB
repo1: backup set size: 104.8MB, backup size: 104.8MB
incr backup: 20241205-160816F_20241205-163400I
timestamp start/stop: 2024-12-05 16:34:00 / 2024-12-05 16:34:02
wal start/stop: 00000009000000000000001D / 00000009000000000000001D
database size: 104.8MB, database backup size: 17MB
repo1: backup set size: 104.8MB, backup size: 17MB
backup reference list: 20241205-160816F
五、总结
对于生产环境，在限制管理员用户用于物理备份操作时 ，可以考虑创建专门的普通用户用于物理备份，以上测试没有经过官方测试发布，在生产环境使用，需要严格测试后，再应用。
六、附件案例故障
1、在test或其他自定义库授权
如下所示，在test或其他自定义库授权：
prod=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO rman_u ;
GRANT
prod=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO rman_u ;
GRANT
prod=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(label text, fast boolean,exclusive boolean )
prod-# TO rman_u ;
GRANT
prod=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup() TO rman_u ;
GRANT
prod=# GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(exclusive boolean,
prod(# wait_for_archive boolean,lsn OUT pg_lsn, labelfile OUT text, spcmapfile OUT text) TO rman_u ;
2、测试在数据库下访问函数
如下所示，在test和prod库都能正常访问函数：
[kingbase@node208 bin]$ ./ksql -U rman_u test -p 54321
Type "help" for help.
test=> select pg_catalog.pg_create_restore_point('sys_rman Archive Check')::text;
pg_create_restore_point
-------------------------
0/1E00E3B0
(1 row)
test=> \c prod
You are now connected to database "prod" as userName "rman_u".
prod=> select pg_catalog.pg_create_restore_point('sys_rman Archive Check')::text;
pg_create_restore_point
-------------------------
0/1E00FE00
(1 row)
3、初始化失败
如下所示，初始化失败，提示缺失访问函数的权限：
4、问题解决
如下所示，必须在kingbase数据库下授权访问函数：
