数据迁移 —— 从 PostgreSQL 到 MySQL
一、背景
公司某个项目，本来生产环境一直用线上的 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 数据类型，两者的对应关系：
| 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）注释不要的语句
- 注释文件结尾处的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
