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

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

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.

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.

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 

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 

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> 

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.

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

psql [CONNECTION PARAMETERS] -f FILENAME -1 

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

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

Answer 2

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.