Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:

  • Free
  • Works under Linux
  • Simple to use and not buggy
  • You actually tried and confirm that works
  • Preferably, not written with Java or Ruby

I have tried different tools listed here, but none worked for me.

Thank you in advance.

share|improve this question
    
I am not PostgreSQL expert, still learning myself. I removed my answer. However, there is another question about using mysqldump with postgresql posted by someone else : dba.stackexchange.com/q/9792/877. Hope it helps !!! –  RolandoMySQLDBA Jan 3 '12 at 20:14
1  
What's wrong with tools written in Java? And it might help if you described what exactly was "not working" with all of them. –  a_horse_with_no_name Jan 3 '12 at 21:44
add comment

3 Answers 3

Migrating between two very different DBMS requires a lot more than just migrating the data. But migration of the data is usually the easiest part.

The way that I have tried, which is free and I can confirm that it works:

  • create a mysql schema only dump
  • Adjust the SQL using a text editor and a lot of search and replace
  • run the converted SQL in Postgres
  • Create a plain text dump from MySQL (e.g. CSV, or some other delimited format)
  • import the data using PostgreSQL's COPY command

Importing the data might actually be difficult if you relied on MySQL's behaviour to accept illegal data (like the 31st of February)

My guess is, that this is going to be quicker than searching for a tool, evaluating a bunch of them and then trying to understand the one that you chose. But it depends on what kind of "big" you are referring to. If big is several hundreds of tables this might not be feasible. If big only referred to the number of rows, then this is probably the quickest way to do it.

There are some tools out there that can dump a database schema in a DBMS independent (XML) format, like Liquibase, SchemaSpy or WbSchemaReport. With Liquibase probably being the easiest to use. The others will require some manual work writing/extending the XSLT to transform the generated XML.

If you are using triggers and stored procedures in MySQL, I don't believe there will be any automated tool that can translate them without requiring a major manual fixing afterwards - and then the generated procedures would probably not use any advanced features of the target DBMS.

share|improve this answer
add comment

Had to do it recently and since it would have taken too long to look for a tool and be able to do stuff with it, I did it per hand with vim and substitution-foo

  • replace INT NOT NULL AUTO_INCREMENT (or anything like this) with SERIAL
  • change everythig string related into a TEXT (no speed difference in using TEXT, VARCHAR, or similar)
  • get rid of those f***ing backticks `
  • no storage engines in Postgres, so remove ENGINE InnoDB (or whatever)
  • remove UNSIGNED
  • every BLOB field is BYTEA
  • strings use single quotes ' and nothing else
  • be carecul if you use string concatenation somewhere, starting from 9.1, Postgres support CONCAT as a fallback for non-standard string concatenation, before 9.1 it was done using 'string' || ' string'
  • re-write any procedural code...
  • take care of names that are reserved (e.g. table, name,...) as well as upper case names. They need to be double quoted "
  • Indexed should be pretty much the same since the syntax doesn't vary
  • For everything that I have forgotten, check out the wikibooks on this topic

Each of those bullets should be done with a single substitution.

I exported the schema and the data seperatly. Make sure that you use INSERTs instead of COPY. If there is no fance stuff going on, the data should require (almost) no clean up. Make sure everything is on UTF-8. With 2 seperate files, the schema gets more managable and you don't have a 1GB file to edit.

When loading the schema, you get pretty detailed information where an error is, so debugging should be pretty simple.

share|improve this answer
    
Hi, how convert BLOB field data of MySQL to BYTEA of PostgreSQL? I found if I dump the data of Mysql as insert sql, I can not load the data in PostgreSQL. –  francs Jan 8 '12 at 6:02
add comment

I am throwing this out there, having never tried it, but Tungesten Replicator may do what you want. It's primarily a replication tool, but I think it may have a bootstrapping process that may help you at least with the data piece.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.