从MySQL到PostgreSQL,数据迁移实录

关于MySQL和PostgreSQL的对比,可以见这个知乎问题

从长远的角度看,我应当关注自己技术知识的效用,选择稳定性、并发性和可靠性更强的技术栈,因此,我决定迁移到PostgreSQL中。

迁移并不是一件容易的事情,MySQL和PostgreSQL在SQL语法上有一定差异,需要将MySQL导出的SQL手动转换后,才能被PostgreSQL读取。

我遇到了一个叫Pgloader工具,声称可以快速转换两个数据库的数据。但我在实践中发现,这个工具已经过时了,不支持最新版的PostgreSQL14。并且,市面上各类数据库转换工具似乎都有问题。最终选择手动转换。

手动转换,主要有这么几个关键点:将`表名`转换为"表名"(在PG中,通过双引号表示表名,单引号表示字符串),将'字符串内容\n含转义符'转换为E'字符串内容\n含转义符'(前面要加上E,否则PG默认不转义),将0000-00-00 00:00:00替换为0001-01-01 00:00:00。我写了下面的JS代码,一键替换SQL:

sql = sql.replace(/`([a-z_0-9A-Z]+?)`/g, '"$1"'); //turn `column_name` to "column_name"
sql = sql.replace(/(?<=, )('.*?')(?=[,\)])/g, 'E$1'); //turn 'abc\nbala' to E'abc\nbala'
sql = sql.replace(/\\0/g, ''); //solve \x00 error
sql = sql.replace(/0000-00-00 00:00:00/g, '0001-01-01 00:00:00');

在转换时,你需要阅读如何使用mysqldumppg_restore。建议将每个表单独导出成一个SQL文件,一个表一个表的向PostgreSQL迁移。这样如果出现错误,方便快速定位和调试,导出进度也不会丢失。

另外,如果存有blob二进制数据,可以为mysqldump加上–hex-blob标识,避免将二进制数据直接写入到SQL文件中,影响编辑器对SQL文件进行编辑(导出的.SQL文件就是一个含有大量SQL语句的文本文件)。

欢迎来到Yari的网站:yar2001 » 从MySQL到PostgreSQL,数据迁移实录