将数据从 PostgreSQL 迁移至 MatrixOne

将数据从 PostgreSQL 迁移至 MatrixOne

本篇文档将指导你如何将数据从 PostgreSQL 迁移至 MatrixOne。

PostgreSQL 是目前全世界最先进的开源关系型数据库之一，拥有者极其完备的数据类型、对象类型、SQL 模型以及其他功能，在企业、教育科研等诸多领域有着重要的地位。根据 PostgreSQL 的数据量大小，本文依然推荐使用在线与离线两种模式进行迁移。

数据类型差异

MatrixOne 与 PostgreSQL 自带的数据类型存在着较多的差异，这些差异有些可以通过其他类型来替换，有些则暂时无法支持。同时，PostgreSQL 支持三层逻辑结构：Database（数据库） - Schema（模式） - Table（表），即你可以在一个数据库内创建多个模式，每个模式内可以包含多个表，这样的层级结构允许更好地组织和管理数据；而 MatrixOne 仅支持两层逻辑结构：Database（数据库） - Table（表）。在 MatrixOne 中，直接在数据库中创建表，所以数据在迁移过程中也会存在些许不同。

数据类型差异具体列表如下：

PostgreSQL	MatrixOne
serial	通过自增列替换
money	使用 decimal 替换
bytea	使用 binary 或 varbinary 替换
geometric	暂不支持
network adress	使用 char 或 varchar 替换
bit string	暂不支持
text search	暂不支持
xml	暂不支持
array	暂不支持
composite	暂不支持
range	暂不支持
domain	暂不支持
object identifier	暂不支持
pg_lsn	暂不支持
pseudo	暂不支持

在线迁移

本章节将指导你使用第三方工具 DBeaver，将数据从 PostgreSQL 迁移至 MatrixOne。

通过 DBeaver，将源端的数据按批次获取，再将数据以 INSERT 的方式，插入到目标库。如果在迁移过程中报错 heap 空间不足，请尝试调整每个批次获取并插入数据的规模。

适用场景：数据量较小（建议小于 1GB），对迁移的速度不敏感的场景。
安装 DBeaver 的跳板机推荐配置：内存 16GB 以上。

准备工作

带图形界面的跳板机：可连接 PostgreSQL 源端，也可连接 MatrixOne 目标端。
数据迁移工具：在跳板机上下载 DBeaver。
DDL 转译工具：在跳板机上下载 pg2mysql，注意跳板机上需要拥有 python 环境。

第一步：迁移表结构

在这里以 TPCH 数据集为例，将 TPCH 数据集的 8 张表从 PostgreSQL 迁移至 MatrixOne。

打开 DBeaver，从 PostgreSQL 中选择待迁移的表，鼠标右键点击后选择生成 SQL > DDL 点击复制，先将这段 SQL 复制到一个文本编辑器内，给文本编辑器命名为 pg_ddl.sql，保存到跳板机本地。

使用 pg2mysql 转译工具将 pg_ddl.sql 文件转换为 MySQL 格式的 DDL：**

a. 首先，运行下面的命令，将 pg_ddl.sql 文件中的 bpchar 替换为 char：

```
# Linux 系统执行的命令如下：
sed -i 's/bpchar/char/g' pg_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/bpchar/char/g' pg_ddl.sql
```

b. 接下来，使用 pg2mysql 转译工具将 pg_ddl.sql 文件转换为 MySQL 格式，并将输出保存到 mysql_ddl.sql 文件中：

```
# Linux 和 MacOS 系统执行的命令均如下：
./pg2mysql.pl < pg_ddl.sql > mysql_ddl.sql
```

c. 转换后的 DDL 会保留原始的 Postgresql schema 名称。如果有必要，你可以执行以下命令，将 schema 名称替换为 MySQL 中的数据库名称：

```
# Linux 系统执行的命令如下：
sed -i 's/{schema_name}/{database_name}/g' mysql_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/{schema_name}/{database_name}/g' mysql_ddl.sql
```

d. 最后，你可能需要统一将 mysql_ddl.sql 文件中的 numeric 替换为 decimal，可以通过以下命令实现：

```
# Linux 系统执行的命令如下：
sed -i 's/numeric/decimal/g' mysql_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/numeric/decimal/g' mysql_ddl.sql
```

连接 MatrixOne，并在 MatrixOne 中创建新的数据库和表：
```
create database tpch;
use tpch;
source '/YOUR_PATH/mysql_ddl.sql'
```

第二步：迁移数据

打开 DBeaver，从 PostgreSQL 中选择待迁移的表，鼠标右键点击后选择导出数据：
在转化目标 > 导出目标窗口选择数据库，点击下一步；在表映射窗口选择目标容器，目标容器选择 MatrixOne 的数据库 tpch：
在抽取设置和数据加载设置窗口，设置选择抽取和插入的数量，为了触发 MatrixOne 的直接写 S3 策略，建议填写 5000：
完成设置后，DBeaver 开始对数据进行迁移，在完成后 DBeaver 将会提示迁移成功。

第三步：检查数据

完成迁移之后，可以采用如下方式检查数据：

通过 select count(*) from <table_name> 来确认源库与目标库的数据量是否一致。
通过相关的查询对比结果，你也可以参见完成 TPCH 测试查询示例，进行结果对比。

离线迁移

本章节将指导你通过离线文件导入到 MatrixOne。

适用场景：数据量较大（大于 1GB），对迁移的速度较为敏感的场景。

准备工作

带图形界面的跳板机：可连接 PostgreSQL 源端，也可连接 MatrixOne 目标端的。
数据迁移工具：下载 DBeaver 到跳板机。
DDL 转译工具：在跳板机上下载 pg2mysql。
在 PostgreSQL 服务器端安装 pgdump。如果你不熟悉如何使用 mysqldump，可参见 pgdump 教程

第一步：迁移表结构

打开 DBeaver，从 PostgreSQL 中选择待迁移的表，鼠标右键点击后选择生成 SQL > DDL 点击复制，先将这段 SQL 复制到一个文本编辑器内，给文本编辑器命名为 pg_ddl.sql，保存到跳板机本地。

使用 pg2mysql 转译工具将 pg_ddl.sql 文件转换为 MySQL 格式的 DDL：**

a. 首先，运行下面的命令，将 pg_ddl.sql 文件中的 bpchar 替换为 char：

```
# Linux 系统执行的命令如下：
sed -i 's/bpchar/char/g' pg_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/bpchar/char/g' pg_ddl.sql
```

b. 接下来，使用 pg2mysql 转译工具将 pg_ddl.sql 文件转换为 MySQL 格式，并将输出保存到 mysql_ddl.sql 文件中：

```
# Linux 和 MacOS 系统执行的命令均如下：
./pg2mysql.pl < pg_ddl.sql > mysql_ddl.sql
```

c. 转换后的 DDL 会保留原始的 Postgresql schema 名称。如果有必要，你可以执行以下命令，将 schema 名称替换为 MySQL 中的数据库名称：

```
# Linux 系统执行的命令如下：
sed -i 's/{schema_name}/{database_name}/g' mysql_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/{schema_name}/{database_name}/g' mysql_ddl.sql
```

d. 最后，你可能需要统一将 mysql_ddl.sql 文件中的 numeric 替换为 decimal，可以通过以下命令实现：

```
# Linux 系统执行的命令如下：
sed -i 's/numeric/decimal/g' mysql_ddl.sql
# MacOS 系统则使用的命令如下：
sed -i '' 's/numeric/decimal/g' mysql_ddl.sql
```

连接 MatrixOne，并在 MatrixOne 中创建新的数据库和表：
```
create database tpch;
use tpch;
source '/YOUR_PATH/mysql_ddl.sql'
```

第二步：迁移数据

在 MatrixOne 中，有两种数据迁移方式可供选择：INSERT 和 LOAD DATA。当数据量大于 1GB 时，首先推荐使用 LOAD DATA，其次可以选择使用 INSERT。

LOAD DATA

在 PostgreSQL 数据库命令行环境下将 PostgreSQL 的数据表导出为 CSV 格式：

postgres=# \c tpch;
postgres=# COPY tpch.nation TO '/{filepath}/nation.tbl' DELIMITER '|';
postgres=# COPY tpch.region TO '/{filepath}/region.tbl' DELIMITER '|';
postgres=# COPY tpch.customer TO '/{filepath}/customer.tbl' DELIMITER '|';
postgres=# COPY tpch.part TO '/{filepath}/part.tbl' DELIMITER '|';
postgres=# COPY tpch.supplier TO '/{filepath}/supplier.tbl' DELIMITER '|';
postgres=# COPY tpch.partsupp TO '/{filepath}/partsupp.tbl' DELIMITER '|';
postgres=# COPY tpch.lineitem TO '/{filepath}/lineitem.tbl' DELIMITER '|';
postgres=# COPY tpch.orders TO '/{filepath}/orders.tbl' DELIMITER '|';

连接到 MatrixOne，将导出的 CSV 数据导入至 MatrixOne：

mysql> load data infile '/{filepath}/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';

更多关于 LOAD DATA 的操作示例，参见批量导入。

INSERT

INSERT 语句需要使用 pgdump 先将逻辑语句导出，再导入到 MatrixOne：

使用 pgdump 导出数据，为了确保插入时触发 MatrixOne 的直接写 S3，建议批量插入尽量大，net_buffer_length 这个参数尽量在 10mb 起步：
```
$ pg_dump -U postgres --no-acl --no-owner --inserts --rows-per-insert 5000  --format p --data-only --schema=tpch tpch -f pg_data.sql
```
在 MatrixOne 端，执行该 SQL 文件，期间会有报错信息，但是不影响数据的插入：
```
source '/YOUR_PATH/pg_data.sql'
```

更多关于 INSERT 的操作示例，参见插入数据。

第三步：检查数据

完成迁移之后，可以采用如下方式检查数据：

通过 select count(*) from <table_name> 来确认源库与目标库的数据量是否一致。
通过相关的查询对比结果，你也可以参见完成 TPCH 测试查询示例，进行结果对比。

参考示例

如果你是新手，想尝试迁移小数据量的数据，可参见使用 source 命令批量导入数据。