Most reliable MariaDB to Postgres dump method

Question

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?

Solution

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

Comments

[Craig Ringer]

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.

[tony_perkis666]

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

[tony_perkis666]

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!

[Mike]

glad it worked for you