mysqldump compatible mode postgresql is not working

Question

I need to convert a mysql database to postgres. Just for testing, I installed a local mysql database and created a simple test database with one table. Now I wan't to make a SQL dump with the option --compatible=postgresql:

mysqldump test --compatible=postgresql -uroot > ~/Documents/testdump.sql

But I always get the following error message:

Invalid mode to --compatible: postgresql

I'm using OSX and installed mysql using homebrew

the mysql version is: stable 8.0.12 (bottled)

I also tried it with the Docker-container and the newest version 8.0.12 but it also gives me the same error message. I need the compatible mode so I can use the dump with an python script to convert it to postgresql.

Edit:

I downgraded to 5.7 and it's now working - I'm still wondering why it's not working with the new version.

Answer 1

If you check the documentation for MySQL 8, you will find this:

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The only permitted value for this option is ansi, which has the same meaning as the corresponding option for setting the server SQL mode.

So setting the value of name to postgresql won't work.

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_compatible

For 5.7 the following values are possible, which is the reason that a downgrade enabled you to use the desired value:

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options.