数据迁移丨如何从 PostgreSQL 到 GreatSQL

🕗 发布于 2025-01-21 20:24 postgresql 数据库

数据迁移丨借助 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 的理想可选替换。

详细区别

对比项目	GreatSQL	PostgreSQL
许可证	采用 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_INCREMENT	SMALLSERIAL、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

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

迁移库 pg_to_greatsql 库下的 users 表

pg_to_greatsql-# \d Users
                                          数据表 "public.users"
      栏位       |            类型             | 校对规则 |  可空的  |               预设                
-----------------+-----------------------------+----------+----------+-----------------------------------
 id              | integer                     |          | not null | nextval('users_id_seq'::regclass)
 username        | character varying(255)      |          | not null | 
 password        | character varying(255)      |          | not null | 
 id_card         | character varying(255)      |          | not null | 
 email           | character varying(255)      |          | not null | 
 phone           | character varying(20)       |          |          | 
 address         | character varying(255)      |          |          | 
 job_title       | character varying(255)      |          |          | 
 education_level | character varying(255)      |          |          | 
 salary          | numeric(10,2)               |          |          | 
 hire_date       | date                        |          |          | 
 leave_date      | date                        |          |          | 
 remarks         | text                        |          |          | 
 status          | character varying(255)      |          |          | 
 created_at      | timestamp without time zone |          |          | 
 created_by      | character varying(255)      |          |          | 'system'::character varying
索引：
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

该 user 表的数据量为 1010000 行

pg_to_greatsql=# SELECT COUNT(*) FROM users;
  count  
---------
 1010000
(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 建表语句：

-- 创建 pg_to_greatsql 库
greatsql> CREATE DATABASE pg_to_greatsql;
-- 创建 users 表
greatsql> CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    id_card VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address VARCHAR(255),
    job_title VARCHAR(255),
    education_level VARCHAR(255),
    salary DECIMAL(10, 2),
    hire_date DATE,
    leave_date DATE,
    remarks TEXT,
    status VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255) DEFAULT 'system',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据迁移

方法一：pg_dump 备份

在 PostgreSQL 执行pg_dump备份

$ 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 文件：

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

去除无用信息

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

-- 语句中有 public. 需要去除
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. 关键词，需要将这个关键词去掉：

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

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

# 这些要去掉，否则导入不了 GreatSQL
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
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 数据库中：

$ mysql -u root -p pg_to_greatsql < modified_pg_to_greatsql.sql

greatsql> SELECT COUNT(*) FROM pg_to_greatsql.users;
+----------+
| count(*) |
+----------+
|  1010000 |
+----------+
1 row in set (1.06 sec)

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

方法二：COPY 导出数据

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

使用 COPY 导出数据：

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

COPY 导入 GreatSQL

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

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

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;
Query OK, 1010000 rows affected (4 min 25.87 sec)
Records: 1010000  Deleted: 0  Skipped: 0  Warnings: 0

greatsql> SELECT COUNT(*) FROM pg_to_greatsql.users;
+----------+
| count(*) |
+----------+
|  1010000 |
+----------+
1 row in set (1.04 sec)

到此迁移完成，下篇将介绍使用 pg2mysql 工具迁移:)
ry OK, 1010000 rows affected (4 min 25.87 sec)
Records: 1010000 Deleted: 0 Skipped: 0 Warnings: 0

greatsql> SELECT COUNT() FROM pg_to_greatsql.users;
±---------+
| count() |
±---------+
| 1010000 |
±---------+
1 row in set (1.04 sec)


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

原文地址：https://blog.csdn.net/GreatSQL2021/article/details/145255673

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Ant Design Vue 的 a-input-number 组件限制最小值和最大值
下一篇：MOS怎样选型，步骤详解

Android系统开发（十九）：无缝拉伸的艺术——9-Patch 可绘制对象详解
这些案例展示了 9-Patch 在不同场景中的实际应用。通过灵活定义拉伸和内容区域，9-Patch 帮助开发者轻松实现高质量的 UI 适配效果。在项目中，这种技术能显著提升界面美观度和用户体验，堪称
阅读更多2025-01-22
简识Redis 持久化相关的 “Everysec“ 策略
另外，Redis 还提供了 "No" 策略（由操作系统决定何时同步），但这种策略在数据安全性方面存在较大的风险，通常不建议在生产环境中使用。在 Redis 的持久化机制中，&quo
阅读更多2025-01-22
14 常用的负载均衡算法
像我之前做过的互联网广告平台，对请求响应时间要求非常高，为了解决南北方网络延迟的问题，在不同的区域部署了不同的IDC机房，请求通过域名访问后，会判断落到哪个IP响应时间短后续该ip的请求就会落到不同I
阅读更多2025-01-22
MySQL为什么使用B+树？B+树和B树的区别
MySQL为什么使用B+树？B+树和B树的区别
阅读更多2025-01-22
QT的d指针和p指针
QT的d指针和p指针
阅读更多2025-01-22
有了TiDB，是否还需要“散装”大数据组件？
虽然TiDB提供了强大的分布式数据处理能力，但在面对。
阅读更多2025-01-22
7、数组知识点汇总
7、数组知识点汇总
阅读更多2025-01-22
安装第三方库到指定目录出现问题
从你的输出来看，安装过程似乎在尝试更新文件时遇到了权限问题。这个文件通常用于记录安装过程中创建或更新的文件列表，以便于后续的卸载或其他操作。即使你之前设置了自定义的（如），仍然会被写入构建目录（在这个
阅读更多2025-01-22
【真机调试】前端开发：移动端特殊手机型号有问题，如何在电脑上进行调试？
edge浏览器谷歌浏览器（开tizi）希望我的分享能够帮助到更多的人，如果觉得我的分享有帮助的话，请大家一键三连支持一下哦~❤️原创不易，期待你的关注与支持~😊之后我会继续更新前端学习小知识，关注我不
阅读更多2025-01-22
bundletool来特定设备规范的json安装aab包
java -jar ./bundletool.jar build-apks --device-spec=j:/device-spec.json --bundle=app-dev-release.aab
阅读更多2025-01-22