MYSQL to POSTGRESQL via SED

Question

I am looking for a way to migrate my backup from a Mysql database to Postgresql. The simplest way I found was using sed upon backup, but it hasn’t worked.

It was like this:

1 - performing a backup

2 - executing the command sed "s/\\\'/\'\'/g" bkp-bancodedados.sql

3 - importing the backup in postgresql with the command psql databasename < bkp-bancodedados.sql

As reported on this link

However, when trying to import the backup does not work due to syntax errors. I believe that sed is not being executed correctly, or else I should sub-quote, I don’t know. Can someone help me, please?

One of the mistakes is this:

ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `destinatario_mensagem` VALUES (147799,52350,NUL...

Answer 1

You can use the mysqldump with the option --compatible.

mysqldump --compatible=postgresql dbname > export.sql

Then you need to escape the quotes from the file generated with the command sed. Use the following command to escape the quotes in the file export sql. and save the file new_export.sql with the amendments

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

(Note that in the code you are running, the output of the command sed is not being saved to the file and this way you are trying to import the same file generated by mysqldump).

Next you can import the new file generated by sed using the psql.

psql databasename < novo_export.sql