Populate MySQL database from postgresql dump file

问题:

Actually I need to populate MySQL database from a SQL file which was generated by postgresql as

pg_dump dbname > myfile.sql

So, if I try to do like

mysql> source myfile.sql

This obviously does not work. Although it did populate 70% tables but I want to know is there a way to achieve it ?

From the source file of postgresql, can I source and populate my database of MySql.

回答1:

If you want to migrate the data and structure from postgres table to mysql equivalent, the easiest way is using a database conversion tool like : ESF Data Migration Toolkit or the opensource counterpart openDBCopy .

If you don't want or you can't use a migration tool, and you need to only migrate data, another simple way could be export data in CSV format from PostgreSQL and then import it in MySQL, so you can do it with some commands like :

ON Postgres (Export):

COPY (SELECT query) TO '/path to csv file -*.csv'; 

ON Mysql (Import):

load data infile 'path to csv file-*.csv' into table tablename fields terminated by ',' lines terminated by '\n' . 

If you anyway want to proceed with the dump tool(pg_dump) you can add this options to produce a dump file that MySQL can understand better :

-d --data-only --no-owner --no-acl --attribute-inserts --disable-dollar-quoting --no-tablespaces

Keep in mind that, depending on the structure of the source database, you could have to manipulate the dump file to allow MysQL to understand the generated dump file ...

回答2:

The best way to migrate without loosing data is to use Mysql Workbench migrations. This tutorial explains how to do the migrations. Few things not mentioned in the tutorial that I found to be important are below:

• Install latest postgres drivers to connect source postgres database, how to install this driver can be found in this tutoria:
• Download and install the postgres odbc drivers from here
• After installation you will see the drivers in "Open ODBC Administrator" 1. 2.
• Use "PostgreSQL Unicode(x64)" as the driver while setting up the source postgres database

Note: using "PostgreSQL ANSI(x64) " as driver gives error while migrations . May be because my data contains unicode characters.