MySql 到 PostgreSql 的迁移

2022-01-23 00:00:00 postgresql migration dump mysql

我的 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 转储:

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

    相关文章