Can i import sql dump in postgresql database

Question

I have mysql dump localhost.sql. But when i try to import this to postgresql db i got errors like this:

psql:localhost.sql:10: ERROR:  unrecognized configuration parameter "sql_mode"
psql:localhost.sql:11: ERROR:  unrecognized configuration parameter "autocommit"
START TRANSACTION
psql:localhost.sql:13: ERROR:  unrecognized configuration parameter "time_zone"
psql:localhost.sql:24: ERROR:  syntax error at or near "NOT"
LINE 1: CREATE DATABASE IF NOT EXISTS `peoplfv0_WPGHJ` DEFAULT CHARA...
                           ^
psql:localhost.sql:25: ERROR:  syntax error at or near "USE"
LINE 1: USE `peoplfv0_WPGHJ`;
        ^
psql:localhost.sql:39: ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `dl_affiliates` (
                     ^
psql:localhost.sql:47: ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `dl_affiliates` (`affiliates_id`, `affiliates_na...
                    ^
psql:localhost.sql:65: ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `dl_artist` (
                     ^
psql:localhost.sql:94: ERROR:  syntax error at or near "`"

Can i convert this sql dump to postgresql format to import data from mysql dump?

MySQL and Postgres use different syntax here and there. So you cannot directly use a dump from MySQL that is written in MySQL specific syntax, you need to transform it in Postgres syntax first. — sticky bit
– sticky bit, Commented Dec 24, 2019 at 15:53
i understand this, but how i can transform in Postgress syntax directly this dump — Bogdan Tsareyvich
– Bogdan Tsareyvich, Commented Dec 24, 2019 at 16:12

Andrea Annibali · Accepted Answer · 2019-12-24 18:02:36Z

1

You can try the --compatible option for mysqldump:

mysqldump --compatible=postgresql dbname > dbname.sql

According to MySQL documentation:

--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. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.10, “Server SQL Modes”.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

As the documentation says it does not guarantee full compatibility with Postgres syntax, but it helps a lot and sometimes could be the solution.

answered Dec 24, 2019 at 18:02

Andrea Annibali

3892 silver badges5 bronze badges

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

3 Comments

Bill Karwin Over a year ago

Unfortunately the dump output still has MySQL version comments like /*!40101 ... */ and those are hard-coded in mysqldump printf statements. There's no option to convert these to uncommented syntax. So one would have to edit the dump output anyway.

A. Scherbaum Over a year ago

That compatible option is worthless. It might change a few small details, but in the end it's still MySQL syntax.

Andrea Annibali Over a year ago

These comments (i.e. comments like /*!40101 ... */) are conditional-execution tokens, not real comments (it may look that way, but is not). So removing such comments could lead to incomplete restore on Postgres. In the documentation is reported: it does not guarantee compatibility, it only helps for a much closer compatibility.

Collectives™ on Stack Overflow

Can i import sql dump in postgresql database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related