数据迁移丨借助 AI 从 PostgreSQL 到 GreatSQL

数据迁移丨借助 AI 从 PostgreSQL 到 GreatSQL

本文将介绍如何从 PostgreSQL 到 GreatSQL 的数据迁移，并运用 AI 协助迁移更加方便。迁移的方式有很多，比方：

• pg_dump：导出SQL文件，修改后导入 GreatSQL 数据库。
  
• COPY：导出txt文本文件，导入 GreatSQL 数据库。
  
• pg2mysql：从 PostgreSQL 迁移到 MySQL/GreatSQL 工具。
  
• GreatDTS：商业的异构数据库迁移工具。
  

本文将介绍 pg_dump 和 COPY 两种方法迁移。
PostgreSQL 和 GreatSQL 区别

PostgreSQL

PostgreSQL是一个开源的对象关系型数据库管理系统（ORDBMS）。它的特色是强调扩展性、数据完备性和高级特性。PostgreSQL由社区维护和开发，具有出色的可定制性，可以适应各种不同的应用场景。它支持复杂的数据类型、JSON 数据存储、空间数据处理惩罚和全文搜索等特性。
GreatSQL

GreatSQL 数据库是一款 开源免费 数据库，可在普通硬件上满足金融级应用场景，具有 高可用、高性能、高兼容、高安全 等特性，可作为 MySQL 或 Percona Server for MySQL 的理想可选更换。
详细区别

对比项目GreatSQLPostgreSQL许可证采用 GPLv2 协议基于 PostgreSQL 许可下，是一种类似于 BSD 或 MIT 的自由开源许可对象层次结构4级（实例、数据库、表、列）5级（实例、数据库、模式、表、列）ACID事物支持支持安全性支持 RBAC、逻辑备份加密、CLONE 备份加密、审计、表空间国密加密、敏感数据脱敏支持 RBAC、行级安全 (RLS)JSON支持（但和PG语法不同）支持（但和GreatSQL语法不同）复制Binlog 进行逻辑复制WAL 进行物理复制巨细写敏感默认不敏感（默认不区分巨细写）默认巨细写敏感（默认区分巨细写）参数值引号使用双引号”“使用单引号‘’数据类型支持（但和PG语法不同）支持（但和GreatSQL语法不同）SQL语法支持（但和PG语法不同）支持（但和GreatSQL语法不同）函数支持（但和PG语法不同）支持（但和GreatSQL语法不同）表和索引支持（但和PG语法不同）支持（但和GreatSQL语法不同）自增AUTO_INCREMENTSMALLSERIAL、SERIAL、SERIAL解释#--.........

在迁移过程中，要注意两款数据库产物的差别。

迁移优势

迁移到 GreatSQL 有以下优势：

• 高可用
  

针对 MGR 进行了大量改进和提升工作，支持 地理标签、仲裁节点、读写动态 VIP、快速单主模式、智能选主 等特性，并针对 流控算法、事务认证队列清理算法、节点到场&退出机制、recovery机制 等多个 MGR 底层工作机制算法进行深度优化，进一步提升优化了 MGR 的高可用保障及性能稳定性。

• 高性能
  

相对 MySQL 及 Percona Server For MySQL 的性能体现更稳定优异，支持 Rapid 引擎、事务无锁化、并行LOAD DATA、异步删除大表、线程池、非壅闭式DDL、NUMA 亲和调度优化 等特性，在 TPC-C 测试中相对 MySQL 性能提升超过 30%，在 TPC-H 测试中的性能体现是 MySQL 的十几倍甚至上百倍。

• 高兼容
  

GreatSQL 实现 100% 完全兼容 MySQL 及 Percona Server For MySQL 用法，支持大多数常见 Oracle 用法，包括 数据类型兼容、函数兼容、SQL 语法兼容、存储程序兼容 等众多兼容扩展用法。

• 高安全
  

GreatSQL 支持逻辑备份加密、CLONE 备份加密、审计、表空间国密加密、敏感数据脱敏等多个安全提升特性，进一步保障业务数据安全，更实用于金融级应用场景。
迁移准备

业务需求分析

评估哪些业务需要迁移，以及迁移的影响。先明白迁移的范围，需要知道哪些业务系统和服务会受到影响，可以根据优先级进行迁移。相识数据库直接交互的应用程序、服务、脚本等，分析这些依靠关系，有助于制定迁移计划，和减少对业务的影响。同时也要评估迁移带来的风险，比如数据丢失、数据同步延迟、业务中断等。
兼容评估

评估 PostgreSQL 和 GreatSQL 之间的兼容性，包括语法、功能、数据类型、索引等。PostgreSQL 和 GreatSQL 在 SQL 语法和功能上存在一些差别，应特殊注意。

在迁移之前，肯定要先相识 PostgreSQL 和 GreatSQL 之间的区别：

• PostgreSQL：https://postgresql.p2hp.com/index.html
  
• GreatSQL：https://greatsql.cn/
  

备份和规复

在迁移前确保 PostgreSQL 数据库的备份和规复机制完善。比方做一次全量备份，在迁移之前，首先辈行完备的数据库备份（比方使用 pg_dump），以确保在迁移过程中遇到问题时可以快速规复。可以选择基于文件系统的快照备份或基于逻辑备份的 pg_dump，并将备份数据存储在安全位置。
测试情况搭建

安装 PostgreSQL 并生成测试数据

PostgreSQL 版本为 15.8

1. $ psql --version
2. psql (PostgreSQL) 15.8 (Debian 15.8-0+deb12u1)

复制代码

迁移库 pg_to_greatsql 库下的 users 表

1. pg_to_greatsql-# \d Users
2.                                           数据表 "public.users"
3.       栏位       |            类型             | 校对规则 |  可空的  |               预设               
4. -----------------+-----------------------------+----------+----------+-----------------------------------
5. id              | integer                     |          | not null | nextval('users_id_seq'::regclass)
6. username        | character varying(255)      |          | not null |
7. password        | character varying(255)      |          | not null |
8. id_card         | character varying(255)      |          | not null |
9. email           | character varying(255)      |          | not null |
10. phone           | character varying(20)       |          |          |
11. address         | character varying(255)      |          |          |
12. job_title       | character varying(255)      |          |          |
13. education_level | character varying(255)      |          |          |
14. salary          | numeric(10,2)               |          |          |
15. hire_date       | date                        |          |          |
16. leave_date      | date                        |          |          |
17. remarks         | text                        |          |          |
18. status          | character varying(255)      |          |          |
19. created_at      | timestamp without time zone |          |          |
20. created_by      | character varying(255)      |          |          | 'system'::character varying
21. 索引：
22.     "users_pkey" PRIMARY KEY, btree (id)
23.     "users_email_key" UNIQUE CONSTRAINT, btree (email)
24.     "users_username_key" UNIQUE CONSTRAINT, btree (username)

复制代码

该 user 表的数据量为 1010000 行

1. pg_to_greatsql=# SELECT COUNT(*) FROM users;
2.   count  
3. ---------
4. 1010000
5. (1 行记录)

复制代码

安装 GreatSQL 数据库

推荐安装 GreatSQL 最新版本

• https://greatsql.cn/docs/8.0.32-26/4-install-guide/1-install-prepare.html
  

迁移到 GreatSQL 数据库

表结构迁移

此时可以使用 AI 来帮助迁移，比方使用 ChatGPT 将 PostgreSQL 数据库表结构转换为 GreatSQL 数据库的表结构。

AI 生成完成后，需要自行检查下是否正确！

• 自增字段：id 字段使用 AUTO_INCREMENT 取代 nextval
  
• 数据类型：PostgreSQL 的 character varying 对应 GreatSQL 的 VARCHAR，numeric 对应 DECIMAL，text 对应 TEXT，timestamp without time zone 对应 TIMESTAMP。
  
• 默认值：对于 created_at 字段，使用 DEFAULT CURRENT_TIMESTAMP 设置默认值，created_by 字段的默认值保持不变。
  
• 字符集：推荐使用 utf8mb4 字符集，以支持更广泛的字符
  

在 GreatSQL 中创建对应库，并执行由 AI 生成的 SQL 建表语句：

1. -- 创建 pg_to_greatsql 库
2. greatsql> CREATE DATABASE pg_to_greatsql;
3. -- 创建 users 表
4. greatsql> CREATE TABLE users (
5.     id INT NOT NULL AUTO_INCREMENT,
6.     username VARCHAR(255) NOT NULL UNIQUE,
7.     password VARCHAR(255) NOT NULL,
8.     id_card VARCHAR(255) NOT NULL,
9.     email VARCHAR(255) NOT NULL UNIQUE,
10.     phone VARCHAR(20),
11.     address VARCHAR(255),
12.     job_title VARCHAR(255),
13.     education_level VARCHAR(255),
14.     salary DECIMAL(10, 2),
15.     hire_date DATE,
16.     leave_date DATE,
17.     remarks TEXT,
18.     status VARCHAR(255),
19.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
20.     created_by VARCHAR(255) DEFAULT 'system',
21.     PRIMARY KEY (id)
22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

数据迁移

方法一：pg_dump 备份

在 PostgreSQL 执行pg_dump备份

1. $ pg_dump --data-only --inserts --column-inserts -U postgres -d pg_to_greatsql > ./pg_to_greatsql.sql

复制代码

• --data-only：只导出数据，不包括数据库对象的定义（如表结构、索引等）。
  
• --inserts：以 SQL INSERT语句的形式导出数据，而不是默认的自定义格式。如许生成的备份文件更易于阅读和编辑。
  
• --column-inserts：使用带有列名的 INSERT语句形式，即INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...);这种方式在处理惩罚包含特殊字符的数据时可能更稳定，并且可以更精确地控制插入的列。
  

此时会生成 pg_to_greatsql.sql 文件：

1. $ ls -lh
2. 总计 474M
3. drwxr-xr-x 3 postgres postgres 4.0K  7月23日 10:49 15
4. -rw-r--r-- 1 postgres postgres 474M 10月21日 15:36 pg_to_greatsql.sql

复制代码

去除无用信息
此时还不能将这份 SQL 文件直接导入到 GreatSQL 中。由于上面有介绍，两款数据库的对象层次结构不同。打开 pg_to_greatsql.sql 文件：

1. -- 语句中有 public. 需要去除
2. INSERT INTO public.users (id, username, password, id_card, email, phone, address, job_title, education_level, salary, hire_date, leave_date, remarks, status, created_at, created_by) VALUES (1010000, '527d66e0a6cdb128d44fc45', '10cccade4c7c35d553cd23e48b5facd1', '435078200404227108', 'b8a5b05af990ff4bdc9ccdc@qq.com', '18059437765', '讗慹簪瞠珒鸚鼜瘔狹覰', 'C++', '博士', 23592.00, '2020-07-04', '2020-12-29', '0e3801d3e64be7c38d93cb5', '离职', '2023-06-20 22:42:39', 'system');

复制代码

可以看到 INSERT 语句表前面有 public. 关键词，需要将这个关键词去掉：

1. $ sed 's/INSERT INTO public\./INSERT INTO /g' pg_to_greatsql.sql > modified_pg_to_greatsql.sql

复制代码

同时另有一些关于 PostgreSQL 参数的设置，需要去掉：

1. # 这些要去掉，否则导入不了 GreatSQL
2. SET statement_timeout = 0;
3. SET lock_timeout = 0;
4. SET idle_in_transaction_session_timeout = 0;
5. SET client_encoding = 'UTF8';
6. SET standard_conforming_strings = on;
7. SELECT pg_catalog.set_config('search_path', '', false);
8. SET check_function_bodies = false;
9. SET xmloption = content;
10. SET client_min_messages = warning;
11. SET row_security = off;

复制代码

my.cnf 参数可以选择 GreatSQL 推荐的参数模板设置：

• https://greatsql.cn/docs/8.0.32-26/3-quick-start/3-4-quick-start-with-cnf.html
  

当然有些参数，比方例子中的 lock_timeout在 PostgreSQL 中是代表锁超时，在 GreatSQL 中锁超时参数是 lock_wait_timeout，如有需要可自行查找对应在 GreatSQL 的参数。

pg_dump 导入 GreatSQL

接下来就可以直接将这份 SQL 文件导入到 GreatSQL 数据库中：

1. $ mysql -u root -p pg_to_greatsql < modified_pg_to_greatsql.sql
3. greatsql> SELECT COUNT(*) FROM pg_to_greatsql.users;
4. +----------+
5. | count(*) |
6. +----------+
7. |  1010000 |
8. +----------+
9. 1 row in set (1.06 sec)

复制代码

数据量如果很大，该方法导入会特殊慢

方法二：COPY 导出数据

使用 INSERT 的方法导入，会比较慢，此时可以用 COPY 的方法导出表数据，在配合 GreatSQL 的并行 Load Data 特性，可以使迁移更加迅速。
使用 COPY 导出数据：

1. pg_to_greatsql=# COPY users TO '/var/lib/postgresql/output_file.txt' WITH (FORMAT TEXT);
2. COPY 1010000

复制代码

COPY 导入 GreatSQL

使用 GreatSQL 中的并行 Load Data 特性：

• https://greatsql.cn/docs/8.0.32-26/5-enhance/5-1-highperf-parallel-load.html
  

1. greatsql> LOAD /*+ SET_VAR(gdb_parallel_load = ON) SET_VAR(gdb_parallel_load_chunk_size = 65536) SET_VAR(gdb_parallel_load_workers = 16) */ DATA INFILE '/var/lib/mysql/output_file.txt' INTO TABLE pg_to_greatsql.users;
2. Query OK, 1010000 rows affected (4 min 25.87 sec)
3. Records: 1010000  Deleted: 0  Skipped: 0  Warnings: 0
5. greatsql> SELECT COUNT(*) FROM pg_to_greatsql.users;
6. +----------+
7. | count(*) |
8. +----------+
9. |  1010000 |
10. +----------+
11. 1 row in set (1.04 sec)

复制代码

到此迁移完成，下篇将介绍使用 pg2mysql 工具迁移

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。