一、背景

公司某个项目，本来生产环境一直用线上的 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 用 ""

更多区别可参考：https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL

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

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

（3）时区转换

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

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

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

答：建议带时区。

带时区的好处：

• 省去你考虑不同时区的麻烦，数据库的时区（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）注释不要的语句

• 注释文件结尾处的update序列最新值 的 sql 语句（因为 mysql 没有 postgres 单独的 sequence 概念），如：SELECT pg_catalog.setval(‘public."PocketShopPower_id_seq"‘, 6640, true);

步骤3、在 MySQL 上执行 xxx.sql 语句

可以用 navicat 、命令行 都可。 略。

四、更多方案

1、GUI 工具

• 1、Navicat [Premium]（亲测无效，tools -> Data Synchronization 倒是可以用，但是报错，check 生成的 SQL 语句，发现根本没帮我做转换啊！）
• 2、MySQL Workbench（没试，可看：https://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/

2、其他

pg2mysql ： https://github.com/pivotal-cf/pg2mysql

数据迁移 —— 从 PostgreSQL 到 MySQL