Kingbase-KDTS数据迁移mysql_to_kingbase

一、启动kingbase服务

cd /opt/Kingbase/ES/V8/Server/bin/

启动 ./sys_ctl start -D /opt/Kingbase/ES/V8/data/

停止 ./sys_ctl stop -D /opt/Kingbase/ES/V8/data/

 

二、启动kingbase自带的DTS数据同步服务

cd  /opt/Kingbase/ES/V8/ClientTools/guitools/KDts/KDTS-WEB/bin/

启动命令
./startup.sh
停止命令
./shutdown.sh

 三、登录UI配置

　　http://192.168.12.104:54523  此处IP需要根据自己情况调整

 配置源数据库

 配置目标数据库

 配置迁移任务

 这里需要注意！

 迁移完成后查看

 

 

 查看系统日志

 

四、登录kingbase-Cli 查询kingbase中是否有Schema信息了

cd /opt/Kingbase/ES/V8/Server/bin

查询kingbase数据库中是否有表数据了

 

 五、常用sql语句

1.查询库中的schema：

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
  pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  AND n.nspname <> 'sys'
  AND n.nspname <> 'sys_catalog'
ORDER BY 1;

　　

2.查询库

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

　

3.查询库中某个特定Schema的表

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' 
       THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' WHEN 'g' THEN 'global index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','s','')
      AND n.nspname !~ '^pg_toast'
  AND n.nspname OPERATOR(pg_catalog.~) '^(repmgr)$' COLLATE pg_catalog.default
ORDER BY 1,2;

　　

六、UI显示连接数已经达到最大值的解决办法

可通过修改kingbase.conf文件中的max_connections值来增加服务器的后台并发处理数。

max_connections = 500

注：max_connections的值需小于license支持的并发数。