从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');

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

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

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