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?

tony_perkis666
  • 243
  • 2
  • 12
  • 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. – Craig Ringer Oct 05 '14 at 05:42

2 Answers2

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

Mike
  • 21,910
  • 7
  • 55
  • 79
  • 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): http://paste.ubuntu.com/8497302/ – tony_perkis666 Oct 05 '14 at 04: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! – tony_perkis666 Oct 05 '14 at 16:01
  • glad it worked for you – Mike Oct 06 '14 at 02: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.

Caleb
  • 11,583
  • 4
  • 35
  • 49