Populate MySQL database from postgresql dump file

Question

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.

Answer 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 ...

Answer 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.