MySql到PostgreSql的迁移 [英] MySql to PostgreSql migration

问题描述

我的PostgreSQL安装在Windows上.如何将数据从MySQL数据库迁移到PostgreSQL? 我读了无数的大腿草.没有任何帮助:(

My PostgreSQL is installed on Windows. How can I migrate data from MySQL database to PostgreSQL? I've read tons of aricles. Nothing helps :(

谢谢.

我的动作:

1. mysql dump:

1. mysql dump:

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

在pgsql上创建数据库同步器

create db synchronizer at pgsql

导入转储:

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

输出:

output:

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','','???????? ...

推荐答案

我在MySQL上的经验-> Postgresql迁移并不十分令人愉快，因此我不得不赞同Daniel对CSV文件的建议.

My experience with MySQL -> Postgresql migration wasn't really pleasant, so I'd have to second Daniel's suggestion about CSV files.

在我的情况下，我手动创建了模式 ，然后使用mysqldump和pg_restore一对一地导入了所有表.

In my case, I recreated the schema by hands and then imported all tables, one-by-one, using mysqldump and pg_restore.

因此，尽管转储/恢复 可以用于数据，但您很可能对模式不走运.我还没有尝试任何商业解决方案，所以请看看别人怎么说，祝您好运！

So, while this dump/restore may work for the data, you are most likely out of luck with schema. I haven't tried any commercial solutions, so see what other people say and... good luck!

更新:我查看了留下的代码，这是我实际的操作方式.

UPDATE: I looked at the code the process left behind and here is how I actually did it.

我的PostgreSQL数据库中有一个略有不同的模式，因此一些表被联接，一些表被拆分.这就是为什么不能选择直接导入的原因，而我的案子可能比您描述的要复杂得多，并且此解决方案可能会过大.

I had a little different schema in my PostgreSQL db, so some tables were joined, some were split. This is why straightforward import was not an option and my case is probably more complex than what you describe and this solution may be an overkill.

对于PG数据库中的每个表，我编写了一个查询，该查询从MySQL数据库中选择相关数据.如果两个数据库中的表基本相同，并且没有连接，则可以如此简单

For each table in PG database I wrote a query that selects the relevant data from MySQL database. In case the table is basically the same in both databases, and there are no joins it can be as simple as this

select * from mysql_table_name

然后，我将查询结果导出到XML，要执行此操作，您需要像这样运行它:

Then I exported results of this query to XML, to do this you need to run it like this:

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

这将创建具有以下结构的简单XML文件:

This will create a simple XML file with the following structure:

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

然后，我编写了一个脚本，该脚本为该XML文件中的每个行元素生成INSERT语句.该脚本的命令行参数给出了表名(在该表中插入数据). Python脚本，以备不时之需.

Then, I wrote a script, that produces INSERT statement for each row element in this XML file. The name of the table, where to insert the data was given as a command line parameter to this script. Python script, in case you need it.

将这些sql语句写入文件，然后将其馈送到psql，如下所示:

These sql statements were written to a file, and then fed to psql like this:

psql [CONNECTION PARAMETERS] -f FILENAME -1

XML唯一的窍门-> SQL转换是识别数字，然后取消对数字的引用.

The only trick there was in XML -> SQL transformation is to recognize numbers, and unquote them.

总结一下:mysql可以将查询结果生成为XML，您可以使用它.

To sum it up: mysql can produce query results as XML and you can use it.

这篇关于MySql到PostgreSql的迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！