KingbaeES V9集群运维案例之---透明加密表空间切换测试
案例说明：
在主备复制集群环境，配置透明加密后，集群switchover和failover切换测试。
测试数据库版本：
prod=# select version();
version
--------------------------------------------------------------------------------------------------------------
--------
KingbaseES V009R001C002B0014 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28),
64-bit
(1 row)
一、集群状态信息
[kingbase@node208 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 7 | | host=192.168.1.208 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
2 | node2 | standby | running | node1 | default | 100 | 6 | 0 bytes | host=192.168.1.209 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
二、配置extension（all nodes）
[kingbase@node208 bin]$ cat ../data/kingbase.conf |grep sysencrypt
shared_preload_libraries = 'repmgr, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, kdb_ora_expr, sepapower, dblink, sys_kwr, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze, src_restrict,sysencrypt'
# 主库执行
prod=# create extension sysencrypt;
CREATE EXTENSION
三、创建透明加密表空间及测试数据
prod=# CREATE TABLESPACE tsp1 LOCATION '/tmp/tsp' with (encryption = true);
CREATE TABLESPACE
prod=# CREATE TABLE test1(ID INT, NAME VARCHAR(100)) TABLESPACE tsp1;
CREATE TABLE
prod=# insert into test1 values(generate_series(1,100),'usr'||generate_series(1,100));
INSERT 0 100
prod=# select count(*) from test1;
count
-------
100
(1 row)
prod=# \d+ test1
Table "public.test1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+------------
-
id | integer | | | | plain | |
name | character varying(100 char) | | | | extended | |
Tablespace: "tsp1"
Access method: heap
prod=# alter table test1 add constraint pk_id primary key(id);
ALTER TABLE
prod=# \d+ test1;
Table "public.test1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+------------
-
id | integer | | not null | | plain | |
name | character varying(100 char) | | | | extended | |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
Tablespace: "tsp1"
Access method: heap
四、查看表空间存储信息
prod=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Descript
ion
-------------+--------+----------+-------------------+---------------------------------+------------+---------
----
sys_default | system | | | | 103 MB |
sys_global | system | | | | 735 kB |
sysaudit | system | | | | 28 kB |
tsp1 | system | /tmp/tsp | | {encryption=true,encmethod=sm4} | 8252 bytes |
(4 rows)
# 自定义表空间系统下存储
lrwxrwxrwx 1 kingbase kingbase 8 Dec 26 10:36 32938 -> /tmp/tsp
[kingbase@node208 bin]$ ls -lh /tmp/tsp/
total 0
drwx------ 3 kingbase kingbase 60 Dec 26 10:37 SYS_12_202404121
[kingbase@node208 bin]$ ls -lh /tmp/tsp/SYS_12_202404121/
total 0
drwx------ 2 kingbase kingbase 60 Dec 26 10:37 16475
五、switchover切换测试
1、备库创建表空间存储目录
[kingbase@node209 bin]$ mkdir -p /tmp/tsp/
2、执行switchover切换
[kingbase@node209 bin]$ ./repmgr standby switchover -h 192.168.1.208 -U esrep -d esrep
# 切换后集群状态
[kingbase@node209 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | node1 | standby | running | node2 | default | 100 | 5 | 0 bytes | host=192.168.1.208 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
2 | node2 | primary | * running | | default | 100 | 6 | | host=192.168.1.209 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
3、切换后表空间存储信息
[kingbase@node209 bin]$ ls -lh /tmp/tsp/
total 0
drwx------ 3 kingbase kingbase 60 Dec 26 10:37 SYS_12_202404121
[kingbase@node209 bin]$ ls -lh /tmp/tsp/SYS_12_202404121/
total 0
drwx------ 2 kingbase kingbase 60 Dec 26 10:37 16475
4、透明加密数据信息
如下所示，透明加密的对象可以正常访问：
prod=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(100 char) | | |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
Tablespace: "tsp1"
prod=# select count(*) from test1;
count
-------
100
(1 row)
prod=# select * from test1 limit 10;
id | name
----+-------
1 | usr1
2 | usr2
3 | usr3
4 | usr4
5 | usr5
6 | usr6
7 | usr7
8 | usr8
9 | usr9
10 | usr10
(10 rows)
prod=# delete from test1 where id=100;
DELETE 1
prod=# select count(*) from test1;
count
-------
99
(1 row)
六、执行failover切换测试
# 关闭主库数据库服务
[kingbase@node209 bin]$ ./sys_ctl stop -D ../data
# failover切换后集群状态
[kingbase@node208 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 7 | | host=192.168.1.208 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
2 | node2 | standby | running | node1 | default | 100 | 6 | 0 bytes | host=192.168.1.209 user=esrep dbname=esrep port=54325 connect_timeout=10 keepalives=1 keepalives_idle=2 keepalives_interval=2 keepalives_count=3 tcp_user_timeout=9000
如下所示，切换完成后，透明加密对象数据可以正常访问：
prod=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(100 char) | | |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
Tablespace: "tsp1"
prod=# select count(*) from test1;
count
-------
99
(1 row)
prod=# select * from test1 limit 10,20;
id | name
----+-------
11 | usr11
12 | usr12
13 | usr13
14 | usr14
15 | usr15
16 | usr16
17 | usr17
18 | usr18
19 | usr19
20 | usr20
21 | usr21
22 | usr22
23 | usr23
24 | usr24
25 | usr25
26 | usr26
27 | usr27
28 | usr28
29 | usr29
30 | usr30
(20 rows)
七、总结
对于KingbaseES 存储透明加密，在主备流复制的集群环境里，均支持switchover及failover切换。
