MYSQL to POSTGRESQL via SED

Question

MYSQL to POSTGRESQL via SED

Navigation

#1 by (1 votes)

2

I'm looking for a way to migrate my backup from a MySQL database to Postgresql. The simplest way I found was by using sed on top of the backup, but it did not work.

It was as follows:

1 - Performing a backup

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

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

As informed on this link

But when trying to import the backup does not work, due to syntax errors. I believe that the sed is not running properly, or else I should replace the quotes, I do not know. Can someone help me, please?

One of the errors is as follows:

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

mysql sql postgresql migrations

asked by anonymous 13.10.2016 / 18:39

1 answer

How to list multiple columns using Inner Join and left join? [duplicate] How do I import a .sql file into an android bank?

score 1 · Answer 1

You can use mysqldump with the --compatible option.

mysqldump --compatible=postgresql dbname > export.sql

Then you need to escape the quotation marks of the generated file with the sed command. Use the following command to escape the quotes in the export.sql file and save the new_export.sql file with the changes

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

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

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

psql databasename < novo_export.sql