Convert and import sql file into postgresql

Question

I want to migrate database from mysql to postgresql. So, first did the mysqldump with this command

mysqldump -u root -p --compatible=postgresql --default-character-set=utf8  databasename > output.sql

Then i upload the output.sql to host and import the sql file using below command

psql -U root -d databasename -f /home/test/output.sql

But i got error when i try to import

ERROR:  syntax error at or near "KEY"   
ERROR:  syntax error at or near "UNLOCK"  
ERROR:  syntax error at or near "("       
invalid command \n        
invalid command \'></script>       
invalid command \";s:2:          
invalid command \',        
invalid command \']       
Query buffer reset (cleared).

The size of database that I dumped is 7.5gb.

tadman · Accepted Answer · 2017-09-19 07:05:08Z

3

The SQL used in MySQL and that used in Postgres are not one-to-one inter-compatible. You need a converter program for anything non-trivial.

From the documentation for --compatible:

This option does not guarantee compatibility with other servers.

In other words, it's only slightly more compatible.

There are commercial products like Navicat which can help do this for you automatically, or you can dump out all your MySQL data in a neutral format like XML, JSON or CSV and read it back in using some other tool.

answered Sep 19, 2017 at 7:05

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert and import sql file into postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related