MySql to PostgreSql migration

Question

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

Thanks.

My actions:

1. mysql dump:

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

2. create db synchronizer at pgsql

3. import dump:

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

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

Answer 1

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.

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.

Answer 2

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.

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.