How to convert a MySQL database to PostgreSQL?

Question

Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.

Start here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL — , Mar 29 '12 at 06:37
This is easily one of the most useful questions on this entire site. — Evan Carroll, Mar 19 '17 at 18:36

score 16 · Answer 1 · answered Mar 29 '12 at 10:42

16

have you tried:

mysqldump --compatible=postgresql dbname > export.sql

then do this to escape quotes

sed "s/\\\'/\'\'/g" export.sql > export1.sql

may need some more massaging so look at mysqldump doco more closely.

answered Mar 29 '12 at 10:42

Nick Kavadias

505
2
9

2

I have tried the method given by you already but it is giving error when restoring in the postgresql – Mughil Mar 29 '12 at 11:58
2

@Mughil what is the error? – Janus Troelsen Jul 29 '12 at 00:04
4

5 years and nobody's fixed mysqldump yet. syntax error at or near "(" LINE 2: "id" int(10) NOT NULL, – Cees Timmerman Sep 22 '17 at 15:49
3

According to https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_compatible the only permitted value for --compatible is ansi – phunehehe Jun 04 '19 at 20:43

score 8 · Answer 2 · answered Nov 07 '15 at 12:10

Here are a few open source tools, that can be very helpful:

FromMySqlToPostgreSql is a feature-reach tool and very 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.
FromMySqlToPostgreSql written in PHP (>= 5.4).

pgloader is a data loading tool for PostgreSQL, using the COPY command. Its main advantage over just using

COPY

or

\copy

, and over using a Foreign Data Wrapper, is its transaction behaviour, where pgloader will keep a separate file of rejected data, but continue trying to copy good data in your database. The default PostgreSQL behaviour is transactional, which means that any erroneous line in the input data (file or remote database) will stop the entire bulk load for the table. pgloader also implements data reformatting, a typical example of that being the transformation of MySQL datestamps 0000-00-00 and 0000-00-00 00:00:00 to PostgreSQL

NULL

value (because our calendar never had a year zero).
pgloader written in LISP and distributed as source code, so you you need to compile it before using.

Hope it will help you...

pgloader is even recommended migration tool on postgre docs, so I'd say it's the best choice. I've used it with succes on a fairly small DB with 50+ tables, worked like a charm. — Capaj, Oct 03 '17 at 09:04

score 3 · Answer 3 · edited Nov 26 '15 at 15:17

You can use EnterpriseDB Migration Wizard, for example.

It can symply migrate OracleDB. But for Mysql, it has some limitations, for example, it not supports views, triggers, stored procedures.

For more, see EDB Migration Toolkit. Not sure, but it can be possible to migrate mysql->oracle->postgresql saving your stored procedures (in case they exists).

Grep this: Database and SQL Migration Tools

score 3 · Answer 4 · answered Jun 27 '12 at 12:43

3

There is actually a free migration tool that's quite easy to use, developed by EnterpriseDB themselves. It can be installed via the StackBuilder: Migration Toolkit

answered Jun 27 '12 at 12:43

uygar.raf

406
5
9

score 3 · Answer 5 · answered Aug 26 '12 at 18:09

I recently use a non-free tool from DBConvert to convert an access database to postgres, and found it well worth the money compared to the amount of time I wasted trying to do it reliably for free. The sell a similar tool for MySQL<->postgres, which I have not used, but may well be worth considering unless you are only interested in command line tools.

In case you are wondering I am not affiliated with them in any way :-)

toing_toing · Answer 6 · 2015-11-26T10:47:12.203

1

This is just to add to the existing answers, which helped me find this solution. I used FromMySqlToPostgreSql. All you need to do is to download and extract FromMySqlToPostgreSql, make a copy of the sample config file, set the db connection values in it, and run the index file with the following command:

php index.php config.xml

Simple yet effective!

edited Nov 26 '15 at 10:47

answered Nov 26 '15 at 10:40

toing_toing

301
3
9

Could you edit your post with a link to this tool, please? – András Váczi Nov 26 '15 at 10:45
Sorry, i forgot that. Added! – toing_toing Nov 26 '15 at 10:47
Ah, this is funny. Have you realized that the author of the tool has posted an answer, too? ;) Now I did. – András Váczi Nov 26 '15 at 10:51
I'm flattered! He hasn't described much about his own wonderful plugin though. – toing_toing Nov 26 '15 at 10:59

score 0 · Answer 7 · answered Mar 19 '17 at 18:32

There is an opensource tool which provides functionalities to convert a MySQL db into Postgresql db includin indexes, fks and data.

https://github.com/ggarri/mysql2psql

This tool allows also to define schema changes so you could perform some upgrades to your current schema in run time.

How to convert a MySQL database to PostgreSQL?

7 Answers7