3

I am in the process of migrating an app to another server, and will need to migrate the database contents from MariaDB to Postgresql.

Most documentation indicates that the following format should suffice to dump the table:

mysqldump -u root -p --compatible=postgresql db > db.sql (and once with --default-character-set=utf8)

I'm getting a number of syntax errors when I go to reimport the database in Postgres; something that did not occur with MySQL databases previously, so I suspect it has to do with MariaDB. I ran into a similar issue when migrating this same database from MariaDB to another MySQL server previously.

I checked out some tools recommended by the Postgres documentation but to no avail.

To experiment further, I dumped with --compatible=mysql and then imported to a MySQL server in order to re-dump it with Postgres set, and added:

SET standard_conforming_strings = 'off'; SET backslash_quote = 'on';

to the beginning of the dump file before importing it in postgres, which gave me some success until hitting another syntax error deeper into the dumpfile (at this point, like 2498 lines in).

Is there a more reliable method of converting a MariaDB database to a postgres-compatible dump?

Improve this question
1
  • mysqldump will only work for extremely basic data, and generally only for data-only dumps. Data type representations differ, rules about valid timestamps differ, etc. You'll want an ETL tool for any kind of automated or non-trivial work. Look at Pentaho Kettle, Talend Studio, CloverETL, etc. Commented Oct 5, 2014 at 5:42

2 Answers 2

Reset to default
1

I highly recommend using a tool for this and not mysqldump

In the past I have used

https://github.com/philipsoutham/py-mysql2pgsql

with great success

Improve this answer
3
  • Thanks; That was the one I didn't try, but it looks like I'm hitting errors with that one as well: `Traceback (most recent call last): paste.ubuntu.com/8497302 Commented Oct 5, 2014 at 4:00
  • I managed to use this with success once I cleaned up the database (used --compatible=mysql, imported it to a MySQL server), and then ran this against the MySQL server. I'm not sure why there was so much difficulty with MariaDB, but it worked. Thanks! Commented Oct 5, 2014 at 16:01
  • glad it worked for you Commented Oct 6, 2014 at 2:57
0

I believe (having done several successful migrations) that pgloader is the de facto best way to do this. You don't convert a dumpfile from mysql so much as connect to the source database (or a source database file) so that the loader can query it for all the things it needs then iterate over copying the data over.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.