Import MySQL dump to PostgreSQL database

How can I import an "xxxx.sql" dump from MySQL to a PostgreSQL database?

Solution 1:

This question is a little old but a few days ago I was dealing with this situation and found pgloader.io.

This is by far the easiest way of doing it, you need to install it, and then run a simple lisp script (script.lisp) with the following 3 lines:

/* content of the script.lisp */
LOAD DATABASE
FROM mysql://dbuser@localhost/dbname
INTO postgresql://dbuser@localhost/dbname;


/*run this in the terminal*/
pgloader script.lisp

And after that your postgresql DB will have all of the information that you had in your MySQL SB.

On a side note, make sure you compile pgloader since at the time of this post, the installer has a bug. (version 3.2.0)

Solution 2:

Don't expect that to work without editing. Maybe a lot of editing.

mysqldump has a compatibility argument, --compatible=name, where "name" can be "oracle" or "postgresql", but that doesn't guarantee compatibility. I think server settings like ANSI_QUOTES have some effect, too.

You'll get more useful help here if you include the complete command you used to create the dump, along with any error messages you got instead of saying just "Nothing worked for me."

Solution 3:

Mac OS X

brew update && brew install pgloader

pgloader mysql://user@host/db_name postgresql://user@host/db_name

Solution 4:

For those Googlers who are in 2015+.
I've wasted all day on this and would like to sum things up.

I've tried all the solutions described at this article by Alexandru Cotioras (which is full of despair). Of all the solutions mentioned there only one worked for me.

— lanyrd/mysql-postgresql-converter @ github.com (Python)

But this alone won't do. When you'll be importing your new converted dump file:

# \i ~/Downloads/mysql-postgresql-converter-master/dump.psql 

PostgreSQL will tell you about messed types from MySQL:

psql:/Users/jibiel/Downloads/mysql-postgresql-converter-master/dump.psql:381: ERROR:  type "mediumint" does not exist
LINE 2:     "group_id" mediumint(8)  NOT NULL DEFAULT '0',

So you'll have to fix those types manually as per this table.

In short it is:

tinyint(2) -> smallint  
mediumint(7) -> integer
# etc.

You can use regex and any cool editor to get it done.

MacVim + Substitute:

:%s!tinyint(\w\+)!smallint!g
:%s!mediumint(\w\+)!integer!g

Solution 5:

You could potentially export to CSV from MySQL and then import CSV into PostgreSQL.