MTK迁移工具和手动迁移配合案例

发表于2025-12-15 23:09:40194次浏览7个评论

## PG库表准备

### 安装PG

- 官网： https://www.postgresql.org/
- Ubuntu 安装 PostgreSQL：

```shell
sudo apt-get update
sudo apt-get install postgresql postgresql-client
```

```shell
root@wei95:~# systemctl start postgresql
root@wei95:~# sudo -u postgres psql
\password postgres
# 输入新密码两次 123456
\q
```

### PG创建数据库

```shell
root@wei95:~# sudo -i -u postgres
postgres@wei95:~$ createdb -h localhost -p 5432 -U postgres testdb
Password: 123456
postgres@wei95:~$
```

### PG创建表

```shell
root@wei95:~# su - postgres
postgres@wei95:~$ psql
psql (14.20 (Ubuntu 14.20-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table test01(id int, c1 TIMESTAMP, c2 varchar(200));
CREATE TABLE
testdb=# insert into test01 values(1,'2025-12-12 00:00:00','正常时间');
INSERT 0 1
testdb=# insert into test01 values(2,'infinity','永不过期');
INSERT 0 1
testdb=# insert into test01 values(3,'-infinity','已过期');
INSERT 0 1
testdb=# insert into test01 values(1,'2025-12-12 01:00:00','正常时间');
INSERT 0 1
testdb=#
testdb=# select * from test01;
id | c1 | c2
----+---------------------+----------
1 | 2025-12-12 00:00:00 | 正常时间
2 | infinity | 永不过期
3 | -infinity | 已过期
1 | 2025-12-12 01:00:00 | 正常时间
(4 rows)

testdb=# create table test02(id int, c1 TIMESTAMP, c2 varchar(200));
CREATE TABLE
testdb=# insert into test02 values(1,'2025-12-12 01:00:00','正常时间');
INSERT 0 1
testdb=#
```

## GBase8s库表准备

### 安装GBase8s

简化安装可以参考这个： https://www.gbase.cn/community/post/6992

### 创建业务数据库：

```shell
# su - gbasedbt ； #用gbasedbt用户操作：
db_name=testdb
dbs_name=data8dbs3
dbaccess sysmaster <<EOF
create database ${db_name} in ${dbs_name} with log;
EOF

```

### 创建业务用户：

```shell
# 用root用户操作：
user_name=user01
user_passwd=(base)pwd95
useradd ${user_name} -m -d /home/${user_name} -s /bin/bash
echo "${user_passwd}" | passwd --stdin ${user_name}
cat /home/gbasedbt/.bash_profile > /home/${user_name}/.bash_profile

```

### 授权：

```shell
# su - gbasedbt ； #用gbasedbt用户操作：
db_name=testdb
user_name=user01
dbaccess ${db_name} <<EOF
grant dba to ${user_name};
EOF

```

### 业务用户连接数据库执行SQL语句示例：

```shell
# root 用户：
user_name=user01
su - ${user_name}
# user01 用户：
db_name=testdb
dbaccess $db_name <<!
    create table t1(id int);
    insert into t1 values(1);
    drop table t1;
!
```

## 迁移工具准备

### 下载MTK迁移工具

- 通过网盘分享的文件：GBaseMTKWeb_V1.0.1_P20151211_RHEL6_x86_64-20251211142652.tar
链接: https://pan.baidu.com/s/1xjFi-wJ2AxP21eG4mfDKYg?pwd=6789 提取码: 6789

### 解压并进入工具目录

```shell
root@wei95:/opt/mtk# pwd
/opt/mtk
root@wei95:/opt/mtk# ls -l
总计 344856
-rw-rw-r-- 1 wei95 wei95 353126400 12月 15 19:51 GBaseMTKWeb_V1.0.1_P20151211_RHEL6_x86_64-20251211142652.tar
root@wei95:/opt/mtk# tar xvf GBaseMTKWeb_V1.0.1_P20151211_RHEL6_x86_64-20251211142652.tar
root@wei95:/opt/mtk# mv GBaseMTKWeb_V1.0.1_P20151211_RHEL6_x86_64 mtk
root@wei95:/opt/mtk# cd mtk
```

### 启动迁移工具服务

```shell
root@wei95:/opt/mtk/mtk# ./mtkweb.sh start
migration_web.jar 开始后台启动,请在日志查看进度...
root@wei95:/opt/mtk/mtk# ls -l logs/
总计 12
-rw-r--r-- 1 root root 0 12月 15 20:09 error.log
-rw-r--r-- 1 root root 130 12月 15 20:09 progress.log
-rw-r--r-- 1 root root 7974 12月 15 20:09 system.log
root@wei95:/opt/mtk/mtk# ps -ef |grep -i mtk
root 5646 1 65 20:09 pts/3 00:00:25 ./jre/bin/java -jar -Xms2048m -Xmx2048m -DmysqlFrag=1 /opt/mtk/mtk/migration_web.jar
root 5729 5257 0 20:10 pts/3 00:00:00 grep --color=auto -i mtk
root@wei95:/opt/mtk/mtk#
```

## 使用网页迁移

- 直接打开你的ip的6855端口web网址，http://192.168.1.95:6855 ，使用图形化迁移。
- 如果服务器没有图形化界面，可以用内网的其他机器打开。
- 迁移工具使用jdbc连接数据库和迁移数据，内网中任意基础部署皆可。
- 帮助文档见：通过网盘分享的文件：GBaseMTKWeb使用指南_v1.0.1_2.pdf
链接: https://pan.baidu.com/s/1hJ0Hle_R8xjK7xbJI-tEbA?pwd=6789 提取码: 6789

## 使用命令行迁移

- 如果不想使用图形，只想使用命令行迁移，也可以。帮助文件见：通过网盘分享的文件：命令行迁移使用手册_v1.0.1.pdf
链接: https://pan.baidu.com/s/1RVkBa_l0SkvwgaWUS7_7EQ?pwd=6789 提取码: 6789

### 配置任务参数（数据源PG , 目标GBase8s）

```SHELL
root@wei95:/opt/mtk/mtk# cp jobjson/postgresql2gbase8s.json jobjson/pg_to_gbase8s.json
root@wei95:/opt/mtk/mtk# vim jobjson/pg_to_gbase8s.json #仅修改数据源部分如下
```

```json
root@wei95:/opt/mtk/mtk# cat jobjson/pg_to_gbase8s.json

{
"taskName":"postgresql迁移",
"reader": {
"dataBaseType": "postgresql",
"parameter": {
"ip": "127.0.0.1",
"port": "5432",
"username": "postgres",
"password": "123456",
"dbName": "testdb",
"schema":"public"
}
},
"writer": {
"dataBaseType": "gbase8s",
"parameter": {
"ip": "192.168.1.95",
"port": "9088",
"username": "user01",
"password": "(base)pwd95",
"instanceName": "ol_gbasedbt1210",
"dbName": "testdb",
"otherParamStr": "APPENDISAM=TRUE;"
}
},
"dataCountCompare": true,
"object": {
"tableObject": {
"choose": true,
"tableStructure": true,
"redoTable": true,
"tableContent": false,
"primaryKey": true,
"foreignKey": true,
"index": true,
"notnull": true,
"unique": true,
"check": true,
"defaultValue": true,
"tableData": true,
"deleteData": false,
"preDataNum": 0,
"table": []
},
"viewObject": {
"choose": false,
"list": []
},
"seqObject": {
"choose": false,
"list": []
},
"syncObject": {
"choose": false,
"list": []
},
"mviewObject": {
"choose": false,
"list": []
},
"procObject": {
"choose": false,
"list": []
},
"triggerObject": {
"choose": false,
"list": []
},
"jobObject": {
"choose": false,
"list": []
},
"functionObject": {
"choose": false,
"list": []
},
"packageObject": {
"choose": false,
"list": []
},
"userDefinedObject": {
"choose": false,
"list": []
},
"dealViewGrammar":false
}
}
root@wei95:/opt/mtk/mtk#
```

### 定义迁移任务

```shell
#前提1:
apt install curl
#前提2:
root@wei95:/opt/mtk/mtk# su - gbasedbt
gbasedbt@wei95:~$ dbaccess sysadmin <<!
grant dba to user01;
!
Database selected.
Permission granted.
Database closed.
gbasedbt@wei95:~$
gbasedbt@wei95:~$ exit
root@wei95:/opt/mtk/mtk#
```

```shell
root@wei95:/opt/mtk/mtk# ./create_task.sh jobjson/pg_to_gbase8s.json
```

### 查看任务列表

```shell
root@wei95:/opt/mtk/mtk# ./operate_task.sh allList
taskId= 6040f3f4adf94db4b3bc4878c63b3615,taskName= postgresql迁移,taskStatusStr= 未开始,createTime= 2025-12-15 21:29:15
root@wei95:/opt/mtk/mtk#
```

### 启动任务

```shell
root@wei95:/opt/mtk/mtk# ./operate_task.sh start 6040f3f4adf94db4b3bc4878c63b3615
启动任务命令已发起，可在当前目录执行tail -1000f ./logs/progress.log查看详情！
root@wei95:/opt/mtk/mtk#
```

### 查看任务执行结果

```shell
root@wei95:/opt/mtk/mtk# ./operate_task.sh allList
taskId= 6040f3f4adf94db4b3bc4878c63b3615,taskName= postgresql迁移,taskStatusStr= 已完成,createTime= 2025-12-15 21:29:15
root@wei95:/opt/mtk/mtk#
```

```shell
root@wei95:/opt/mtk/mtk# cat ./logs/progress.log |tail -8
2025-12-15 21:39:00 - 数据迁移任务[postgresql迁移]运行结束,共耗时0秒.
2025-12-15 21:39:00 - 其中，数据统计情况如下：任务[postgresql迁移]共迁移2张表，失败0张表，其中：因表结构原因写入数据失败表名为:[]，表数据失败表名为:["test01"]
2025-12-15 21:39:00 - 任务[postgresql迁移]源和目标端表的数据条数比对开始......
2025-12-15 21:39:00 - 共比对2张表,其中：比对通过1张表，比对不通过1张表。不通过的表比对情况为:(目标表数量为-1表示未知)
[testdb->testdb], 表[test01] : 4/-1
2025-12-15 21:39:00 - 任务postgresql迁移源和目标端数据量比对结束!
2025-12-15 21:39:00 - [taskId = 6040f3f4adf94db4b3bc4878c63b3615]开始关闭源库连接池...
2025-12-15 21:39:00 - [taskId = 6040f3f4adf94db4b3bc4878c63b3615]开始关闭目标库连接池...
root@wei95:/opt/mtk/mtk#

```

- 可以发现testdb的test01表迁移失败

### 查看任务详细报错

```shell
root@wei95:/opt/mtk/mtk# cat logs/error.log |tail -14
2025-12-15 21:39:00 ERROR cn.gbase.migration.core.worker.AbstractProcessWorker-{1}:484 - 表[test01]插入数据发生错误:
Error SQLCode : [-1261]!
Error message : Too many digits in the first field of datetime or interval.

java.sql.BatchUpdateException: Too many digits in the first field of datetime or interval.
at com.gbasedbt.jdbc.IfxPreparedStatement.executeBatch(IfxPreparedStatement.java:3224)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:551)
at cn.gbase.migration.core.dataBase.dbExcutor.TargetDataBaseExecutor.executeInsertSQLWithLog(TargetDataBaseExecutor.java:122)
at cn.gbase.migration.core.dataBase.dbExcutor.GBase8sDataBaseExecutor.executeInsertSQL(GBase8sDataBaseExecutor.java:114)
at cn.gbase.migration.core.worker.impl.UserDataWriteWorker.call(UserDataWriteWorker.java:218)
at cn.gbase.migration.core.worker.impl.UserDataWriteWorker.call(UserDataWriteWorker.java:48)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.lang.Thread.run(Thread.java:745)
2025-12-15 21:39:00 ERROR cn.gbase.migration.core.executor.TaskExecutorManager-{1}:268 - [#TableUserDataExecutor#] migration finished, return Failed!
root@wei95:/opt/mtk/mtk#

```

## 手动迁移test01表(处理1261报错)

### 单独迁移test01表的表结构

#### 配置任务参数（数据源PG , 目标GBase8s，单独迁移test01表）

```shell
root@wei95:/opt/mtk/mtk# cp jobjson/pg_to_gbase8s.json jobjson/test01.json
root@wei95:/opt/mtk/mtk# vim jobjson/test01.json #修改只迁移test01表
```

- 只迁移test01表

```shell
"table": []
修改为
"table": ["test01"]
```

- 只迁移表结构

```shell
"tableData": true,
修改为
"tableData": false,
```

#### 删除历史任务

```shell
root@wei95:/opt/mtk/mtk# ./operate_task.sh allList
taskId= 6040f3f4adf94db4b3bc4878c63b3615,taskName= postgresql迁移,taskStatusStr= 已完成,createTime= 2025-12-15 21:29:15
root@wei95:/opt/mtk/mtk# ./operate_task.sh delete 6040f3f4adf94db4b3bc4878c63b3615
删除任务成功删除任务命令已发起，可在当前目录执行tail -1000f ./logs/progress.log查看详情！
root@wei95:/opt/mtk/mtk# ./operate_task.sh allList

root@wei95:/opt/mtk/mtk#
```

#### 定义新迁移任务

```shell
root@wei95:/opt/mtk/mtk# ./create_task.sh jobjson/test01.json
创建任务命令已发起，可在当前目录执行tail -1000f ./logs/progress.log查看详情！
root@wei95:/opt/mtk/mtk# ./operate_task.sh allList
taskId= 5896670c21ed4017b8c24ae97fb1a5a8,taskName= postgresql迁移,taskStatusStr= 未开始,createTime= 2025-12-15 21:54:23
root@wei95:/opt/mtk/mtk#
```

#### 启动新定义的迁移任务

```shell
root@wei95:/opt/mtk/mtk# ./operate_task.sh start 5896670c21ed4017b8c24ae97fb1a5a8
启动任务命令已发起，可在当前目录执行tail -1000f ./logs/progress.log查看详情！
root@wei95:/opt/mtk/mtk#
```

#### 查看任务执行结果

```shell
root@wei95:/opt/mtk/mtk# cat ./logs/progress.log |tail -8
2025-12-15 22:00:33 - 数据迁移任务[postgresql迁移]运行结束,共耗时0秒.
2025-12-15 22:00:33 - 其中，数据统计情况如下：任务[postgresql迁移]共迁移1张表，失败0张表，其中：因表结构原因写入数据失败表名为:[]，表数据失败表名为:[]
2025-12-15 22:00:33 - 任务[postgresql迁移]源和目标端表的数据条数比对开始......
2025-12-15 22:00:33 - 共比对1张表,其中：比对通过0张表，比对不通过1张表。不通过的表比对情况为:(目标表数量为-1表示未知)
[testdb->testdb], 表[test01] : 0/-1
2025-12-15 22:00:33 - 任务postgresql迁移源和目标端数据量比对结束!
2025-12-15 22:00:33 - [taskId = 5896670c21ed4017b8c24ae97fb1a5a8]开始关闭源库连接池...
2025-12-15 22:00:33 - [taskId = 5896670c21ed4017b8c24ae97fb1a5a8]开始关闭目标库连接池...
root@wei95:/opt/mtk/mtk#
```

### 单独迁移test01表的表数据

- 用最大值和最小值替换infinity和-infinity

#### PG导出表数据

```sql
postgres@wei95:~$ psql -h localhost -p 5432 -U postgres testdb
Password for user postgres:
psql (14.20 (Ubuntu 14.20-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=# select * from test01;
id | c1 | c2
----+---------------------+------------
1 | 2025-12-12 00:00:00 | 正常时间
2 | infinity | 永不过期
3 | -infinity | 已过期
1 | 2025-12-12 01:00:00 | 正常时间
1 | 2025-12-12 01:00:00 | 正,常,时间
(5 rows)

testdb=# COPY (
SELECT
id,
CASE
WHEN c1 = 'infinity'::timestamp THEN '9999-12-31 23:59:59'::timestamp
WHEN c1 = '-infinity'::timestamp THEN '0001-01-01 00:00:00'::timestamp
ELSE c1
END as c1,
c2
FROM test01
) TO '/tmp/export.csv'
WITH CSV
DELIMITER '|'
QUOTE '"'
ENCODING 'UTF8';
COPY 5
testdb=# \q
postgres@wei95:~$ cat /tmp/export.csv
1|2025-12-12 00:00:00|正常时间
2|9999-12-31 23:59:59|永不过期
3|0001-01-01 00:00:00|已过期
1|2025-12-12 01:00:00|正常时间
1|2025-12-12 01:00:00|正,常,时间
postgres@wei95:~$

```

#### GBase8s导入数据

```shell
user01@wei95:~$
user01@wei95:~$ dbaccess testdb -
Your evaluation license will expire on 2026-11-26 00:00:00

Database selected.

> select * from test01;

No rows found.

Elapsed time: 0.005 sec

> LOAD FROM '/tmp/export.csv' DELIMITER '|' INSERT INTO test01 ;

5 row(s) loaded.

Elapsed time: 0.008 sec