MySql 到 PostgreSql 的迁移

Question

我的PostgreSQL安装在Windows上。我该如何将数据从MySQL数据库迁移到PostgreSQL？ 我已经阅读了很多文章，但是都没有帮助 :(

谢谢。

我的步骤：

1. 使用mysql dump:

   mysqldump -h 192.168.0.222 --port 3307 -u root -p --compatible=postgresql synchronizer > c:\dump.sql
   

2. 创建pgsql数据库同步器

3. 导入转储：

psql -h 192.168.0.100 -d synchronizer -U postgres -f C:\dump.sql

4. 输出：

psql:C:/dump.sql:17: NOTICE:  table "Db_audit" does not exist, skipping
DROP TABLE
psql:C:/dump.sql:30: ERROR:  syntax error at or near "("
СТРОКА 2:  "id" int(11) NOT NULL,
           ^
psql:C:/dump.sql:37: ERROR:  syntax error at or near ""Db_audit""
СТРОКА 1:LOCK TABLES "Db_audit" WRITE;
             ^
psql:C:/dump.sql:39: ERROR:  relation "Db_audit" does not exist
СТРОКА 1:INSERT INTO "Db_audit" VALUES (4068,4036,4,1,32,'2010-02-04 ...
             ^
psql:C:/dump.sql:40: ERROR:  relation "Db_audit" does not exist
СТРОКА 1:INSERT INTO "Db_audit" VALUES (19730,2673,2,2,44,'2010-11-23...
             ^
psql:C:/dump.sql:42: ERROR:  syntax error at or near "UNLOCK"
СТРОКА 1:UNLOCK TABLES;
 ^
psql:C:/dump.sql:48: NOTICE:  table "ZHNVLS" does not exist, skipping
DROP TABLE
psql:C:/dump.sql:68: ERROR:  syntax error at or near "("
СТРОКА 2:  "id" int(10) unsigned NOT NULL,
           ^
psql:C:/dump.sql:75: ERROR:  syntax error at or near ""ZHNVLS""
СТРОКА 1:LOCK TABLES "ZHNVLS" WRITE;
             ^
psql:C:/dump.sql:77: WARNING:  nonstandard use of escape in a string literal
СТРОКА 1:...???????? ??? ???????','10','4607064820115','0','','??????-??...
                                                      ^
ПОДСКАЗКА:  Use the escape string syntax for escapes, e.g., E'\r\n'.
Cancel request sent
psql:C:/dump.sql:77: WARNING:  nonstandard use of escape in a string literal
СТРОКА 1:...??????????? ????????','10','4602784001189','0','','???????? ...

- Andrew Kalashnikov

---

你能否将MySQL中的数据备份到CSV或类似的SQL文件中？如果可以，那么你应该能够创建数据库，然后使用pgadmin恢复脚本。 - Daniel Casserly

Answer 1

我对MySQL到Postgresql的迁移经历并不愉快，所以我同意Daniel的建议，使用CSV文件。

在我的情况下，我手动重新创建了模式，并使用mysqldump和pg_restore逐个导入所有表。

因此，虽然这种转储/恢复可能适用于数据，但模式很可能无法工作。我没有尝试过任何商业解决方案，所以看看其他人说什么并祝你好运！

更新：我查看了该过程留下的代码，以下是我实际执行的方式。

我的PostgreSQL数据库中有一些不同的模式，因此某些表被合并了，某些表被拆分了。这就是为什么直接导入不是一个选项，我的情况可能比你描述的要复杂，这个解决方案可能有些过度。

对于PG数据库中的每个表，我编写了一个查询，从MySQL数据库中选择相关数据。如果表在两个数据库中基本上相同，并且没有连接，那么可以像这样简单：

select * from mysql_table_name

然后我将此查询的结果导出到XML，为此需要像这样运行它：

echo "select * from mysql_table_name" | mysql [CONNECTION PARAMETERS] -X --default-character-set=utf8 > mysql_table_name.xml

这将创建一个简单的XML文件，具有以下结构：

<resultset statement="select * from mysql_table_name">
  <row>
    <field name="some_field">field_value</field>
    ...
  </row>
  ...
</resultset>

然后，我编写了一个脚本，针对 XML 文件中的每个行元素生成 INSERT 语句。将要插入数据的表的名称作为命令行参数传递给此脚本。Python 脚本，如果需要的话。

这些 SQL 语句被写入文件，然后像这样提供给 psql：

psql [CONNECTION PARAMETERS] -f FILENAME -1

在XML -> SQL转换中唯一的技巧就是要识别数字并取消引号。

总之，MySQL可以生成查询结果为XML，您可以使用它。

- Maxim Sloyko

---

maksymko，我已经重新创建了模式。您能否描述一下使用mysqldump和pg_restore逐步复制表中数据的方法？ - Andrew Kalashnikov

Answer 2

这比较复杂。这里有很多相关文档：http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL。你可以在那里找到转换脚本。

- Peter Eisentraut

Answer 3

在我比较简单的情况下（30个表，10000条记录），我使用了一个Perl脚本：http://pgfoundry.org/frs/?group_id=1000198。它通过mysql转储文件并生成pg转储文件，但存在以下问题。

我正在导入到Heroku，所以我使用了他们的pgbackups插件，这几乎没有任何问题。

需要注意的问题：

1. 布尔数据类型。MySQL将它们存储为0和1。PostGreSQL将它们存储为t和f。请注意不要将布尔值迁移为整数。 2. 自增ID。您可能会发现您的ID从1开始重新计数。您将收到此类错误："duplicate key value violates unique constraint ..."。这很容易修复，但要注意。

- superluminary

Answer 4

我用py-mysql2pgsql将一个大型MySQL数据库转换为Postgres。它处理大多数情况都很好，但是由于我的特定需求，我不得不对其进行了一些补丁。

默认情况下，它从MySQL读取数据并写入Postgres。但是，您可以要求它将模式和/或数据写入文件以便在加载到Postgres之前进行检查。 https://pypi.python.org/pypi/py-mysql2pgsql

- Anand Chitipothu

Answer 5

你可以使用https://github.com/mihailShumilov/mysql2postgresql。这是一个用PHP编写的转换器。

- GodSon

Answer 6

还有一个非常好的（fork of a）Python转换器，由GitLab创建者维护：

https://github.com/gitlabhq/mysql-postgresql-converter

原始的分支对于这个项目来说已经过时了。对我来说，使用这个脚本一切都运行得非常完美。

- panmari

Answer 7

这里有一个项目，可以通过几个命令将您当前的MySQL数据库迁移到Postgresql，包括索引和外键。它还允许定义名称、索引和列类型解析，以便您可以覆盖默认行为。

https://github.com/ggarri/mysql2psql

我希望这对于任何有兴趣将其当前项目迁移到PG的人都能有所帮助，在我们的情况下，我们获得了约20％的性能提升。

- Gabriel G.

Answer 8

使用一些自动化迁移程序会更好。 即使您熟悉所有的技巧，手动完成每个步骤可能需要很长时间，特别是当您的数据库很大时。

尝试从MySQL到PostgreSQL。

这个工具功能丰富且易于使用。 它映射数据类型，精确地迁移约束、索引、主键和外键，就像它们在您的MySQL数据库中一样。 在幕后，它使用PostgreSQL COPY，因此数据传输非常快速。

- AnatolyUss

---

我尝试了这个，但是出现了错误 "FromMySqlToPostgreSql" - 数据库迁移工具 版权所有2015年Anatoly Khaytovich [e-mail] -- 迁移开始... -- 脚本已终止。 - user3441799

错误原因： -- FromMySqlToPostgreSql::createSchema -- 无法创建新模式... -- PDOException代码：0 -- 文件：/Users/admin/Downloads/FromMySqlToPostgreSql-master/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php -- 行：300 -- 消息：找不到驱动程序 - user3441799

看起来你没有安装PDO驱动程序...请安装： MySQL：sudo apt-get install php5-mysql 或者 PostgreSQL：sudo apt-get install php5-pgsql - AnatolyUss