KingbaseES V8R6集群维护案例之--单实例数据迁移到集群案例

案例说明：
生产环境是单实例，测试环境是集群，现需要将生产环境的数据迁移到集群中运行，本文档详细介绍了从单实例环境恢复数据到集群环境的操作步骤，可以作为生产环境迁移数据的参考。

适用版本： KingbaseES V8R6

本案例数据库版本(单实例和集群使用相同的版本)：

test=# select version();
                                                       version
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V008R006C005B0041 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)

原集群节点信息：

[kingbase@node101 bin]$ ./repmgr cluster show
 ID | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                 
----+---------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node101 | primary | * running |          | default  | 100      | 13       | host=192.168.1.101 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
 2  | node102 | standby |   running | node101  | default  | 100      | 13       | host=192.168.1.102 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

一、单实例环境迁移前主备

Tips：
1)将单实例环境数据迁移到集群，需要停止单实例数据库服务，根据data目录数据的大小， 要估算停机窗口时间。
2)在关闭单实例数据库前，建议手工创建检查点，如果wal日志比较大，建议备份后，清理wal日志，只需要保留最近一天的日志到最近检查点后即可。
3)需要跨主机将单实例data目录拷贝到集群的主备库节点，需 根据网络带宽和节点数，估算整个拷贝时间。

1、查看数据信息

prod1=# \l
                             List of databases
   Name    | Owner  | Encoding | Collate  |    Ctype    | Access privileges
-----------+--------+----------+----------+-------------+-------------------
 prod      | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 prod1     | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 security  | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 template0 | system | UTF8     | ci_x_icu | zh_CN.UTF-8 | =c/system        +
           |        |          |          |             | system=CTc/system
 template1 | system | UTF8     | ci_x_icu | zh_CN.UTF-8 | =c/system        +
           |        |          |          |             | system=CTc/system
 test      | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
(6 rows)
 
 
prod1=# select count(*) from t1;
 count
--------
 100000
(1 row)

2、关闭数据库服务

1)手工建立checkpoint
prod1=# select sys_switch_wal();
 sys_switch_wal
----------------
 0/3F74BD70
(1 row)
 
prod1=# checkpoint;
CHECKPOINT

2)正常关闭数据库服务

[kingbase@node101 bin]$ ./sys_ctl stop -D /data/kingbase/v8r6_041/data
waiting for server to shut down.... done
server stopped

二、迁移数据到集群

1、停止集群

[kingbase@node101 bin]$ ./sys_monitor.sh stop
2022-06-20 10:33:40 Ready to stop all DB ...
.....
2022-06-20 10:33:55 begin to stop DB on "[192.168.1.102]".
2022-06-20 10:33:57 DB on "[192.168.1.101]" stop success.
2022-06-20 10:33:57 Done.

2、将集群的data目录备份

[kingbase@node101 bin]$ cd ../
[kingbase@node101 kingbase]$ mv data data.bk

3、将单实例的data目录拷贝到集群的主备节点

[kingbase@node101 v8r6_041]$ scp -r data node101:/home/kingbase/cluster/R6HA/kha/kingbase
[kingbase@node101 v8r6_041]$ scp -r data node102:/home/kingbase/cluster/R6HA/kha/kingbase

4、集群所有备库创建standby.signal文件

[kingbase@node102 kingbase]$ cd data
[kingbase@node102 data]$ touch standby.signal

5、创建kingbase.auto.conf、kingbase.conf、es_rep.conf文件：(所有节点)

[kingbase@node101 data]$ mv kingbase.auto.conf kingbase.auto.conf.bk
 
# 将原集群的文件复制
[kingbase@node101 data]$ cp ../data.bk/kingbase.auto.conf ./
 
[kingbase@node101 data]$ cat kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
enable_upper_colname = 'on'
wal_retrieve_retry_interval = '5000'
primary_conninfo = 'user=system connect_timeout=10 host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 application_name=node101'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_1'
synchronous_standby_names = '1 (node102)'
 
[kingbase@node101 data]$ cp ../data.bk/kingbase.conf ./
[kingbase@node101 data]$ cp ../data.bk/es_rep.conf ./

三、创建和配置流复制Tips：
1)注意保证所有节点的数据库服务启动正常。
2)如果数据库服务启动失败，注意查看sys_log(尤其是备库)。
3)如果流复制失败，可以根据备库sys_log获取到备库无法流复制的原因(如本案例，是因为复制槽问题引起)。

1、sys_ctl启动主备库数据库服务

[kingbase@node101 bin]$ ./sys_ctl start -D ../data
waiting for server to start....2022-06-20 11:02:01.860 CST [30274] WARNING:  enable_upper_colname can only be 
.......
server started

2、查看流复制状态

test=# select * from sys_stat_replication;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state |
sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+-
---------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
 
# 如以上所示流复制创建失败，查看备库sys_log，因为复制槽原因，导致备库无法创建流复制。

如下图所示：

3、查看复制槽信息

test=# select * from sys_replication_slots;
  slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confi
rmed_flush_lsn
--------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+------
---------------
 slot_node102 |        | physical  |        |          | f         | f      |            |      |              | 0/370B4F68  |
 slot_node101 |        | physical  |        |          | f         | f      |            |      |              |             |
(2 rows)

4、重新创建复制槽

test=# select sys_drop_replication_slot('slot_node101');
 sys_drop_replication_slot
---------------------------
 
(1 row)
 
test=# select sys_drop_replication_slot('slot_node102');
 sys_drop_replication_slot
---------------------------
 
(1 row)
 
test=# select * from sys_replication_slots;
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
 
test=# select sys_create_physical_replication_slot('repmgr_slot_2');
 sys_create_physical_replication_slot
--------------------------------------
 (repmgr_slot_2,)
(1 row)
 
test=# select sys_create_physical_replication_slot('repmgr_slot_1');
 sys_create_physical_replication_slot
--------------------------------------
 (repmgr_slot_1,)
(1 row)
 
test=# select * from sys_replication_slots;
   slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 repmgr_slot_2 |        | physical  |        |          | f         | t      |      31655 |  915 |              | 0/420001E0  |
 repmgr_slot_1 |        | physical  |        |          | f         | f      |            |      |              |             |
(2 rows)
 
test=# select * from sys_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+----
 
 31655 |       10 | system  | node102          | 192.168.1.102 |                 |       36924 | 2022-06-20 11:07:38.664780+08 |          | streaming | 0/420001E0 | 0/420001E0 | 0/420001E0 | 0/420001E0 |           |           |            |             1 | sync       | 2022-06-20 11:08:05.571558+08
(1 row)
 
# 如上所示，重新创建正确的复制槽后，流复制恢复正常。

四、配置repmgr集群管理(主库完成)

1、创建esrep库和esrep用户：

# 根据原集群用户的密码，创建esrep用户
[kingbase@node102 data]$ cat ~/.encpwd
*:*:*:system:MTIzNDU2NzhhYg==
#*:*:*:system:MTIzNDU2Cg==
*:*:*:esrep:S2luZ2Jhc2VoYTExMA==
[kingbase@node102 data]$ echo 'S2luZ2Jhc2VoYTExMA=='|base64 -d
Kingbaseha110
 
test=# create database esrep;
CREATE DATABASE
test=# create user esrep with superuser password 'Kingbaseha110';
CREATE ROLE
 
# 根据原集群的密码，修改system用户密码
[kingbase@node101 bin]$ echo 'MTIzNDU2NzhhYg=='|base64 -d
12345678ab[kingbase@node./ksql -U system test
ksql (V8.0)
Type "help" for help.
 
test=# alter user system with password '12345678ab';
ALTER ROLE

2、创建repmgr extension

# 注意在kingbase.conf中支持repmgr extension
 
[kingbase@node101 bin]$ cat ../data/kingbase.conf |grep repmgr
........
shared_preload_libraries = 'repmgr,liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze'
 
#主库创建repmgr extension
test=# create extension repmgr;
CREATE EXTENSION

3、注册主备库到repmgr集群

1)注册primary节点

[kingbase@node101 bin]$ ./repmgr primary register --force
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
 
[kingbase@node101 bin]$ ./repmgr cluster show
 ID | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                 
----+---------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node101 | primary | * running |          | default  | 100      | 1        | host=192.168.1.101 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

2)注册standby节点

[kingbase@node102 bin]$ ./repmgr standby register --force
INFO: connecting to local node "node102" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1)
INFO: standby registration complete
NOTICE: standby node "node102" (ID: 2) successfully registered

3)查看集群节点状态

[kingbase@node102 bin]$ ./repmgr cluster show
 ID | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                 
----+---------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node101 | primary | * running |          | default  | 100      | 1        | host=192.168.1.101 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
 2  | node102 | standby |   running | node101  | default  | 100      | 1        | host=192.168.1.102 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

五、验证数据

1、重启集群

[kingbase@node101 bin]$ ./sys_monitor.sh restart
2022-06-20 11:26:21 Ready to stop all DB ...
......
2022-06-20 11:27:03 repmgrd on "[192.168.1.102]" start success.
 ID | Name    | Role    | Status    | Upstream | repmgrd | PID   | Paused? | Upstream last seen
----+---------+---------+-----------+----------+---------+-------+---------+--------------------
 1  | node101 | primary | * running |          | running | 5641  | no      | n/a
 2  | node102 | standby |   running | node101  | running | 22473 | no      | 0 second(s) ago
[2022-06-20 11:27:08] [NOTICE] redirecting logging output to "/home/kingbase/cluster/R6HA/kha/kingbase/log/kbha.log"
 
[2022-06-20 11:27:13] [NOTICE] redirecting logging output to "/home/kingbase/cluster/R6HA/kha/kingbase/log/kbha.log"
 
2022-06-20 11:27:19 Done.

2、查看repmgrd进程状态(所有节点)

[kingbase@node101 bin]$ ps -ef |grep repmgr
kingbase  5641     1  0 11:26 ?        00:00:00 /home/kingbase/cluster/R6HA/kha/kingbase/bin/repmgrd -d -v -f /home/kingbase/cluster/R6HA/kha/kingbase/bin/../etc/repmgr.conf
kingbase  5879     1  0 11:27 ?        00:00:00 /home/kingbase/cluster/R6HA/kha/kingbase/bin/kbha -A daemon -f /home/kingbase/cluster/R6HA/kha/kingbase/bin/../etc/repmgr.conf

3、验证迁移后数据

[kingbase@node101 bin]$ ./ksql -U system test
ksql (V8.0)
Type "help" for help.
 
test=# \l
                             List of databases
   Name    | Owner  | Encoding | Collate  |    Ctype    | Access privileges
-----------+--------+----------+----------+-------------+-------------------
 esrep     | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 prod      | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 prod1     | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 security  | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
 template0 | system | UTF8     | ci_x_icu | zh_CN.UTF-8 | =c/system        +
           |        |          |          |             | system=CTc/system
 template1 | system | UTF8     | ci_x_icu | zh_CN.UTF-8 | =c/system        +
           |        |          |          |             | system=CTc/system
 test      | system | UTF8     | ci_x_icu | zh_CN.UTF-8 |
(7 rows)
 
test=# \c prod1
You are now connected to database "prod1" as user "system".
prod1=# \d
               List of relations
 Schema |        Name         | Type  | Owner
--------+---------------------+-------+--------
 public | sys_stat_statements | view  | system
 public | t1                  | table | system
(2 rows)
 
prod1=# select count(*) from t1;
 count
--------
 100000
(1 row)
 
# 迁移后数据和迁移前数据一致，迁移成功。

六、总结

1、从单实例环境迁移数据到集群，如果需要保证数据一致，必须要将单实例及集群停库，对于生产环境，要考虑停机窗口。
 2、如果需要将原集群数据重新加载到新的集群，需要将原集群数据做逻辑备份，但是在导入时如果有重复数据需注意处理。(如将测试数据再导入到新的集群中，可能有许多数据会重复)。
 3、申请停机窗口，要考虑原单实例数据量的大小、主机间的网络带宽、集群节点数、集群配置时间、集群启动故障的处理时间等。