Populate MySQL database from postgresql dump file

mysql database postgresql pg-dump
Nikhil Sharma picture Nikhil Sharma · May 5, 2012 · Viewed 32.1k times · Source

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

aleroot picture aleroot · May 5, 2012

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

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How do you write a case insensitive query for both MySQL and Postgres?

I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can't handle that. …

Read More
Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)

I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite. Here is what I have so far, but I'm afraid it's not done and I need some people with …

Read More