数据迁移 —— 从 PostgreSQL 到 MySQL

最新推荐文章于 2024-09-27 17:48:51 发布

John_like521

最新推荐文章于 2024-09-27 17:48:51 发布

阅读量3.3k

点赞数 2

文章标签： mysql postgresql

原文链接：https://www.cnblogs.com/xjnotxj/p/12776575.html

版权

一、背景

公司某个项目，本来生产环境一直用线上的 aliyun 的 PostgreSQL RDS 的，但是后来为了一些更高级的功能扩展，换成了 aliyun 的 MySQL RDS。于是需要进行数据库的迁移。

技术栈：

Node.js（Express）
React
PostgreSQL

二、结构迁移

我们的库比较简单，只有表，没有视图、函数、存储过程、触发器什么的。所以结构这块不用考虑太多。

我们 Node 应用用的是 Sequelize，需要改造的就一条：

把 model 定义里涉及 JSONB 的都改成 JSON。

其余的 Sequelize 都会帮忙抹平差异。

三、数据迁移#

数据库的结构迁移好了，接下来就是迁移数据了。

我们的库比较简单，只涉及表的数据。

步骤1、备份（backup）PostgreSQL

平常我们备份 pg 数据库的时候，都会加上 -Fc 参数，表示压缩。但因为这次要迁移到不同家的数据库产品，所以只能导出 SQL statement 的纯文本文件。

执行：

pg_dump --data-only --inserts --column-inserts -h xxx -U  xxx_production -d xxx_production > ./xxx_prod.sql

参数解释：

--data-only：只迁移数据，不迁移结构
--inserts：生成 SQL statement 的纯文本文件
--column-inserts：生成的 INSERT 语句，会带上列清单（即明确地指定具体列名）

结果：生成 xxx_prod.sql 文件。

步骤2、修改（上一步生成的） xxx.sql 文件

（1）remove schema

做法：public. -> 空

如：
INSERT INTO public."Gift" (id, name, sku, "bindCount", type, enabled, "createdAt", "updatedAt")
->
INSERT INTO "Gift" (id, name, sku, "bindCount", type, enabled, "createdAt", "updatedAt")

原因：PostgreSQL 有 schema，MySQL 无。

（2）修改引用系统标识符形式

做法：把涉及表名 + 字段名的引用，从原来的 "" 包裹变成 `` 包裹。

如：
INSERT INTO "Gift" (id, name, sku, "bindCount", type, enabled, "createdAt", "updatedAt")
->
INSERT INTO Gift (id, name, sku, bindCount, type, enabled, createdAt, updatedAt)

原因：如下扩展所述：

[拓展] PostgreSQL 和 MySQL 的一些常用写法的区别

引用系统标识符，PostgreSQL 用 `` 注释（ANSI标准），MySQL 用 ""
注释，PostgreSQL 用 -- （ANSI标准），MySQL 用 -- or #
引用值，PostgreSQL 用 '' 注释（ANSI标准），MySQL 用 ""

总结：PostgreSQL 更符合 ANSI标准，跨平台性更好。

这里推荐一个在线 web 的 pg 转 mysql sql 的工具：http://www.lightbox.ca/pg2mysql.php （这个工具功能有限，引用系统标识符的修改还是可以用的，当然你手动修改也可以）。

（3）时区转换

[拓展] PostgreSQL 和 MySQL 关于日期数据类型时区的区别#

① 关于 datetime 数据类型，两者的对应关系：

	PostgreSQL	MySQL
不带时区	timestamp	DateTime
带时区	timestamptz	Timestamp

问：用带时区的好，还是不带时区的好？

答：建议带时区。

带时区的好处：

省去你考虑不同时区的麻烦，数据库的时区（or 操作系统的时区）只要变化，带时区的时间都会自动调整变化到此时区下的值。
为了国际化和未来可拓展性的考虑

② 时区默认取决于谁？

PostgreSQL 默认取决于数据库的设置：

-- 查看时区
show timezone;   -- PRC

-- 设置时区
select * from pg_timezone_names;   -- 查看可供选择的时区列表
set timezone 'UTC';

MySQL 默认取决于操作系统的时区设置：

-- 查看时区
show variables like '%time_zone%';

-- 结果：
-- system_time_zone CST
-- time_zone SYSTEM

③ 如果是带时区的数据类型，存进去、取出来、显示的差异：

PostgreSQL：2020-04-25 17:00:00.22+08 （会贴心的把时区信息带上：+08）
MySQL：2020-04-25 17:00:00.22 （没有时区信息，如果想知道这个时间的时区是多少？需要查看 mysql 当前的时区）

上面的拓展介绍了差异，那么我们要怎么做？

因为我们用了 Sequelize，他的 DATE 类型，在 PostgreSQL 是带时区的 timestamptz 类型（形如 2020-04-25 17:00:00.22+08），而在 MySQL 是不带时区的 DateTime 类型（形如 2020-04-25 17:00:00.22），所以我们 INSERT 的时候，要把字符串里的 +08 remove 掉，所以：

做法：+08 -> 空

如：'2020-03-16 15:02:10.616+08' -> '2020-03-16 15:02:10.616'

注意：记得确保执行 sql 的时候， MySQL 的时区为 +08。

问：为什么 Sequelize 在 MySQL 不对应也是带时区的 Timestamp 类型呢？

答：我网上没有搜到相关解释。我自己猜测，应该是 Sequelize 考虑到 Timestamp 类型有 2038 问题，DateTime 类型数值范围更广，是最优的选择，用的人也多（我之前用 mysql 的时候，就习惯用 DateTime）

（4）注释不要的语句#