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.

This really depends on the datatypes you're using, and whether you're using custom functions and triggers. In addition, you'll certainly have to port the code that uses those tables, possibly with difficulty. See dba.stackexchange.com/q/17132/2129 — Bruno
– Bruno, Commented May 5, 2012 at 13:08
@Bruno: We dont have any triggers or dependency on the database in the code. — Nikhil Sharma
– Nikhil Sharma, Commented May 5, 2012 at 13:13

aleroot · Accepted Answer · 2012-05-05 13:13:48Z

32

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

edited May 5, 2012 at 13:13

answered May 5, 2012 at 13:06

aleroot

73k32 gold badges182 silver badges220 bronze badges

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

1 Comment

Nikhil Sharma Over a year ago

I dont have neither the schema nor the data for MySql, although I only have my database populated.

Javed · Accepted Answer · 2017-03-02 10:58:33Z

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.

answered Mar 2, 2017 at 10:58

Javed

6,2875 gold badges52 silver badges77 bronze badges

3 Comments

Williams Over a year ago

Requires oracle login.

stapmoshun Over a year ago

link for postgresql driver is broken. just use chocolatey: choco install psqlodbc -y

aphoe Over a year ago

You can get the PostgreSQL ODBC driver here as well: postgresql.org/ftp/odbc/releases

Collectives™ on Stack Overflow

Populate MySQL database from postgresql dump file

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related