PostgreSQL: Database Import from MySQL dump - syntax error

Question

I have a backup MySQL file, and I'm trying to import that to my PostgreSQL database.

/Users/bheng/Desktop/database_backups/2016-06-10-local.sql

I'm trying to do it via a command line:

psql -d db-local -U root -f ~/Desktop/database_backups/2016-06-10-local.sql

I kept getting

I even try to log in to Postgres and run this it import fine

\i /Users/bheng/Desktop/database_backups/2016-06-10-local.sql

but the same result happens.

Did I do something wrong ? How do I stop/prevent this ?

Answer 1

For any still looking for answers;
The error has a mysql "back-tick" in it (`). Mysql uses "back-ticks" to keep identifiers like table names safe. Postgres uses a double quotes (").

You can not just take a dump of one sql database vendor and import on another. There are syntax, foreign key, index, data escaping, and etc. issues to deal with.

If you are lucky and you have a simple database, you can get away with using something like "sed" to replace syntax and encoding issues.

For example, you could have replaced "back-ticks" with double quotes to stop the error you are geeting.

sed -i 's/`/"/g' /path/to/sql_script

However, I am sure this will just reveal the next issue. It takes time to migrate database vendors. You will probably end up using "sed" or something like it. ODBC and/or JDBC can not handle all of the flat out bad data and unusual cases you may encounter.

Answer 2

What worked for me was pgloader. It's very fast + open source under the PostgreSQL License.

The first detail at the top of the website actually mentions migrating from MySQL to PostgreSQL in one command. Good luck!

Link to the Github repo