数据迁移 —— 从 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 工具

2、其他

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

数据迁移 —— 从 PostgreSQL 到 MySQL

上一篇:net core天马行空系列:降低net core门槛,数据库操作和http访问仅需写接口,实现类由框架动态生成


下一篇:Docker之Tomcat容器连接mysql容器