KingbaseES V8R3 集群运维系列 -- 备库数据迁移单实例库

案例说明：

在生产环境需要将集群中架构转换为单实例环境，本案例以备库转换为单实例库为案例，介绍了两种方案，一种在数据库数据量小的环境下采用 sys_dumpall 导出导入方式建立单实例库；另外一种是在数据量大的情况下，采用物理copy数据文件的方式迁移到单实例库。

适用版本:

KingbaseES V8R3

案例架构：

查看集群状态：

1、集群节点状态

TEST=# show pool_nodes;                   
 node_id |   hostname    | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.7.243 | 54321 | up     | 0.500000  | primary | 0          | false             | 0
 1       | 192.168.7.248 | 54321 | up     | 0.500000  | standby | 0          | true              | 0
(2 rows)

2）流复制状态

TEST=# select * from sys_stat_replication;
 PID  | USESYSID | USENAME | APPLICATION_NAME |  CLIENT_ADDR  | CLIENT_HOSTNAME | CLIENT_PORT |         BACKEND_START         | BACKEND_XMIN |   STATE   | SENT_LOCATION | WRITE_LOCATION | FLUSH_LOCATION | REPLAY_LOCATION | SYNC_PRIORITY | SYNC_STATE 
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------
 3393 |       10 | SYSTEM  | node2            | 192.168.7.248 |                 |        9968 | 2021-03-01 12:09:12.662830+08 |              | streaming | 0/B0CC040     | 0/B0CC040      | 0/B0CC040      | 0/B0CC040       |      2 | sync
(1 row)

案例一： sys_dumpall导出导入方式（用于数据量小的架构，在生产环境下，不能保证单实例库的数据和集群中的数据是一致的）。

1、集群测试数据

PROD=# create table t1 (id int ,name varchar(10));
CREATE TABLE
PROD=# insert into t1 values(generate_series(1,10000),'usr'||generate_series(1,10000));
INSERT 0 10000
PROD=# select count(*) from t1;
 COUNT 
-------
 10000
(1 row)

2、在备库主机创建新的实例

# 创建单实例库数据目录
[kingbase@node1 bin]$ mkdir -p /data/kingbase/v8r3c/data

# 初始化单实例库
[kingbase@node1 bin]$ pwd
/home/kingbase/cluster/ha/db/bin

[kingbase@node1 bin]$ ./initdb -U system -W 123456 -E utf8 --case-insensitive -D /data/kingbase/v8r3c/data
.....
Success. You can now start the database server using:

    ./sys_ctl -D /data/kingbase/v8r3c/data -l logfile start

3、从备库备份数据（sys_dumpall）

[kingbase@node1 bin]$ ./sys_dumpall -U SYSTEM -W 123456 > ~/db.sql
sys_dumpall: role name starting with "sys_" skipped (SYSSAO)
sys_dumpall: role name starting with "sys_" skipped (SYSSSO)
sys_dumpall: role name starting with "sys_" skipped (SYSTEM)

4、启动单实例数据库并导入数据

# 启动数据库服务
[kingbase@node1 bin]$ ./sys_ctl start -D /data/kingbase/v8r3c/data/
server starting
.......

[kingbase@node1 bin]$ netstat -an |grep 54323
tcp        0      0 0.0.0.0:54323           0.0.0.0:*               LISTEN     
tcp6       0      0 :::54323                :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     42092    /tmp/.s.KINGBASE.54323


# 连接数据库并导入数据
 
# 导入备库备份数据
test=# \i /home/kingbase/db.sql
SET
SET
SET
CREATE DATABASE
ksql:/home/kingbase/db.sql:25: ERROR:  database "SECURITY" already exists
REVOKE
GRANT
You are now connected to database "PROD" as user "system".
SET
SET
SET
SET
SET
SET
   set_config    
-----------------
 "$USER", PUBLIC
(1 row)
.......

5、查看单实例库数据

TEST=# \c PROD
You are now connected to database "PROD" as user "system".
PROD=# \d
                    List of relations
 Schema |             Name              | Type  | Owner  
--------+-------------------------------+-------+--------
 PUBLIC | pathman_cache_stats           | view  | system
 PUBLIC | pathman_concurrent_part_tasks | view  | system
 PUBLIC | pathman_config                | table | system
 PUBLIC | pathman_config_params         | table | system
 PUBLIC | pathman_partition_list        | view  | system
 PUBLIC | T1                            | table | system
(6 rows)

PROD=# select count(*) from t1;
 count 
-------
 10000
(1 row)

=== 从以上可以获知，集群中的数据已经导入到单实例库中，此时可以停止备库或删除集群===

案例2：物理拷贝方式（如果单实例库数据需要和集群数据一致，需要停止整个集群；如果不需要一致，只需要停止备库数据库服务即可）。

1、集群测试数据

PROD=# \d
                    List of relations
 Schema |             Name              | Type  | Owner  
--------+-------------------------------+-------+--------
 PUBLIC | PATHMAN_CACHE_STATS           | view  | SYSTEM
 PUBLIC | PATHMAN_CONCURRENT_PART_TASKS | view  | SYSTEM
 PUBLIC | PATHMAN_CONFIG                | table | SYSTEM
 PUBLIC | PATHMAN_CONFIG_PARAMS         | table | SYSTEM
 PUBLIC | PATHMAN_PARTITION_LIST        | view  | SYSTEM
 PUBLIC | T1                            | table | SYSTEM
 PUBLIC | T2                            | table | SYSTEM
 PUBLIC | T3                            | table | SYSTEM
(8 rows)

PROD=# select count(*) from t1;
 COUNT 
-------
 10000
(1 row)
.......

2、备库生成检查点

PROD=# checkpoint;
CHECKPOINT

3、停止备库cron任务和数据库服务

[root@node1 ~]# cat /etc/cron.d/KINGBASECRON 
.......
#*/1 * * * * root  /home/kingbase/cluster/ha/db/bin/network_rewind.sh
#*/1 * * * * root  /home/kingbase/cluster/ha/kingbasecluster/bin/restartcluster.sh

# 停止备库数据库服务
[kingbase@node1 bin]$ ./sys_ctl stop -D ../data
waiting for server to shut down.... done
server stopped

4、建立单实例库

# 创建单实例库数据目录
[kingbase@node1 bin]$ mkdir -p /data/kingbase/v8r3c/data

# 初始化单实例库
Tips：
   单实例库的数据库模式、大小写支持、字符集等要和原集群库一致。

[kingbase@node1 bin]$ ./initdb -U system -W 123456 -E utf8 --case-insensitive -D /data/kingbase/v8r3c/data
The files belonging to this database system will be owned by user "kingbase".
This user must also own the server process.
......

5、拷贝备库数据到单实例库

[kingbase@node1 v8r3c]$ mv data data.bk
[kingbase@node1 v8r3c]$ mkdir data

# 拷贝备库数据到单实例库
[kingbase@node1 data]$ cp -var * /data/kingbase/v8r3c/data/
‘backup_label.old’ -> ‘/data/kingbase/v8r3c/data/backup_label.old’
‘base/1/1255’ -> ‘/data/kingbase/v8r3c/data/base/1/1255’
‘base/1/1255_fsm’ -> ‘/data/kingbase/v8r3c/data/base/1/1255_fsm’
‘base/1/1247’ -> ‘/data/kingbase/v8r3c/data/base/1/1247’
‘base/1/1247_fsm’ -> ‘/data/kingbase/v8r3c/data/base/1/1247_fsm’
.......

6、修改data下的recovery.conf文件
[kingbase@node1 data]$ mv recovery.conf recovery.conf.bk

7、启动单实例库数据库服务

[kingbase@node1 bin]$ ./sys_ctl start -D /data/kingbase/v8r3c/data
server starting
[kingbase@node1 bin]$ LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "/home/kingbase/cluster/ha/db/data/sys_log".

[kingbase@node1 bin]$ ps -ef |grep kingbase

kingbase 21482     1  1 14:08 pts/1    00:00:00 /home/kingbase/cluster/ha/db/bin/kingbase -D /data/kingbase/v8r3c/data
kingbase 21484 21482  0 14:08 ?        00:00:00 kingbase: logger process   
kingbase 21487 21482  0 14:08 ?        00:00:00 kingbase: checkpointer process   
kingbase 21488 21482  0 14:08 ?        00:00:00 kingbase: writer process   
kingbase 21489 21482  0 14:08 ?        00:00:00 kingbase: wal writer process   
kingbase 21490 21482  0 14:08 ?        00:00:00 kingbase: autovacuum launcher process   
kingbase 21491 21482  0 14:08 ?        00:00:00 kingbase: archiver process   
kingbase 21492 21482  0 14:08 ?        00:00:00 kingbase: stats collector process   
kingbase 21493 21482  0 14:08 ?        00:00:00 kingbase: bgworker: syslogical supervisor   
......

8、验证和查看单实例库数据

PROD=# \d
                    List of relations
 Schema |             Name              | Type  | Owner  
--------+-------------------------------+-------+--------
 PUBLIC | PATHMAN_CACHE_STATS           | view  | SYSTEM
 PUBLIC | PATHMAN_CONCURRENT_PART_TASKS | view  | SYSTEM
 PUBLIC | PATHMAN_CONFIG                | table | SYSTEM
 PUBLIC | PATHMAN_CONFIG_PARAMS         | table | SYSTEM
 PUBLIC | PATHMAN_PARTITION_LIST        | view  | SYSTEM
 PUBLIC | T1                            | table | SYSTEM
 PUBLIC | T2                            | table | SYSTEM
 PUBLIC | T3                            | table | SYSTEM
(8 rows)

PROD=# select count(*) from t1;
 COUNT 
-------
 10000
(1 row)

=== 从以上获知，备库的数据已经同步到单实例库===

总结：

   1、本案例是在原集群架构外，另外创建单实例，完成从集群到单实例库的转换，也可以直接在原集群上转换，不再赘述。
   2、转换为单实例库后，可以按照生产要求 ，删除或保留原集群架构。
   3、另外集群主库转换为单实例库，方法基本和此案例一致。