MySql to PostgreSql migration

My PostgreSQL is installed on Windows. How can I migrate data from MySQL database to PostgreSQ开发者_如何学PythonL? 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','','???????? ...
   

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.

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.

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

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

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

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

Issues to watch for

1. 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.
2. 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.

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.

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

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

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

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

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.

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.

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.