How to convert mysql to postgresql?

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.

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

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.