MySql 到 PostgreSql 的迁移

Question

提问by Andrew Kalashnikov

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

我的 PostgreSQL 安装在 Windows 上。如何将数据从 MySQL 数据库迁移到 PostgreSQL？我已经阅读了大量的文章。没有任何帮助:(

Thanks.

谢谢。

My actions:

我的行动：

mysql dump:

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

create db synchronizer at pgsql

import dump:

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 转储：

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

在 pgsql 创建数据库同步器

导入转储：

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

输出：

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

Answer 1

回答by Maxim Sloyko

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

我对 MySQL -> Postgresql 迁移的体验并不是很愉快，所以我不得不支持 Daniel 关于 CSV 文件的建议。

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

就我而言，我手动重新创建了架构，然后使用 mysqldump 和 pg_restore 一个一个地导入了所有表。

So, while this dump/restore maywork 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.

更新：我查看了该过程留下的代码，这是我实际操作的方式。

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.

我的 PostgreSQL 数据库中有一些不同的架构，所以有些表被加入，有些被拆分。这就是为什么直接导入不是一种选择，我的情况可能比你描述的更复杂，这个解决方案可能是一种矫枉过正。

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

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

select * from mysql_table_name

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

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

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

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

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

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

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.

然后，我编写了一个脚本，为这个 XML 文件中的每个行元素生成 INSERT 语句。表的名称（插入数据的位置）作为此脚本的命令行参数给出。Python 脚本，以防您需要它。

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

这些 sql 语句被写入一个文件，然后像这样输入 psql：

psql [CONNECTION PARAMETERS] -f FILENAME -1

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

XML -> SQL 转换中唯一的技巧是识别数字并取消引用它们。

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

总结一下：mysql可以将查询结果生成为XML，你可以使用它。

Answer 2

回答by Peter Eisentraut

It's a bit more complicated than that. There is plenty of documentation here:

它比那要复杂一些。这里有很多文档：

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

There, you'll also find conversion scripts.

在那里，您还可以找到转换脚本。

Answer 3

回答by superluminary

In my rather simple case (30 tables, 10000 records), I used a perl script:

在我相当简单的情况下（30 个表，10000 条记录），我使用了一个 perl 脚本：

http://pgfoundry.org/frs/?group_id=1000198

It chugged through the mysql dump file and produced a pg dump file, with the following issues.

它检查了 mysql 转储文件并生成了一个 pg 转储文件，但存在以下问题。

I was importing to Heroku so I used their pgbackups plugin which worked almost flawlessly.

我正在导入到 Heroku，所以我使用了他们的 pgbackups 插件，该插件几乎完美无缺。

Issues to watch for

需要注意的问题

Boolean data types. MySQL stores these as 0 and 1. PostGreSQL stores them as t and f. Watch that the booleans dont get migrated as integers.
Auto incrementing IDs. You may find your ids start counting again from 1. You'll get errors like this: "duplicate key value violates unique constraint ...". It's easy to fix, but watch out for it.

布尔数据类型。MySQL 将它们存储为 0 和 1。PostGreSQL 将它们存储为 t 和 f。注意布尔值不会作为整数迁移。
自动递增 ID。您可能会发现您的 id 从 1 开始重新计数。您会收到如下错误：“重复键值违反唯一约束...”。这很容易修复，但要小心。

Answer 4

回答by Anand Chitipothu

I've used py-mysql2pgsql for converting a big MySQL database into Postgres. It handles most cases very well. I had to patch it for couple of cases specific to my needs though.

我使用 py-mysql2pgsql 将大型 MySQL 数据库转换为 Postgres。它可以很好地处理大多数情况。不过，我不得不针对特定于我的需求的几种情况修补它。

https://pypi.python.org/pypi/py-mysql2pgsql

By default, it reads data from MySQL and writes to Postgres. But you can ask it to write the schema and/or data to a file for inspecting before loading into Postgres.

默认情况下，它从 MySQL 读取数据并写入 Postgres。但是您可以要求它在加载到 Postgres 之前将架构和/或数据写入文件以进行检查。

Answer 5

回答by GodSon

You can use https://github.com/mihailShumilov/mysql2postgresqlThis is wroted on PHP convertor

你可以使用https://github.com/mihailShumilov/mysql2postgresql这是写在 PHP 转换器上的

Answer 6

回答by panmari

There's also a very nice (fork of a) python converter that is maintained by the gitlab creators:

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

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

The original fork is for this project is stale. For me, everything worked perfectly using this script.

这个项目的原始 fork 是陈旧的。对我来说，使用此脚本一切正常。

Answer 7

回答by Gabriel G.

Here there is a project which migrates in couple commands your current MySQL database to Postgresql including indexes, and foreign keys. Also it allows to define name, indexes and column type parsings so you can overwrite default behavior.

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

https://github.com/ggarri/mysql2psql

I hope it could be useful for anyone of you who is interested in migrating his current project to PG, in our case we obtained around 20% performance increase.

我希望它对任何有兴趣将他当前的项目迁移到 PG 的人有用，在我们的例子中，我们获得了大约 20% 的性能提升。

Answer 8

回答by AnatolyUss

It is much better to use some program, that automates the process of migration. Even if you familiar with all gotchas, doing every step by hand may take a lot of time, especially when your db is "big".

最好使用一些程序来自动执行迁移过程。即使您熟悉所有问题，手动完成每一步也可能需要大量时间，尤其是当您的数据库“很大”时。

Try FromMySqlToPostgreSql.

尝试FromMySqlToPostgreSql。

This tool is feature-reach and easy to use. It maps data-types, migrates constraints, indexes, PKs and FKs exactly as they were in your MySQL db. Under the hood it uses PostgreSQL COPY, so data transfer is very fast.

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

MySql 到 PostgreSql 的迁移

提问by Andrew Kalashnikov

回答by Maxim Sloyko

回答by Peter Eisentraut

回答by superluminary

Issues to watch for

需要注意的问题

回答by Anand Chitipothu

回答by GodSon

回答by panmari

回答by Gabriel G.

回答by AnatolyUss

相关推荐

最近更新

标签

MySql 到 PostgreSql 的迁移

提问by Andrew Kalashnikov

回答by Maxim Sloyko

回答by Peter Eisentraut

回答by superluminary

Issues to watch for

需要注意的问题

回答by Anand Chitipothu

回答by GodSon

回答by panmari

回答by Gabriel G.

回答by AnatolyUss

相关推荐

MySQL - 在哪里或？

MySQL 中列名“order”的替代方法

MySQL Mysql查询基于两列动态转换行到列

MySQL 如何删除MySQL数据库中的所有表？

相关推荐

最近更新

标签