Convert a PostgreSQL dump into a MySQL dump

mysql database postgresql database-restore database-dump
user1207329 picture user1207329 · Feb 13, 2012 · Viewed 21.9k times · Source

I have a Postgres dump file and I need to turn it into a MySQL dump so I can create a matching MySQL database. I am new to Postgres I have it installed on my Ubuntu server and I was going to see whether I could import and export SQL of some sort. I don't know how to do that.

Here is the command I tried:

pg_dump -Fc --no-acl --no-owner -h localhost -U dbuser testdb > b526.dump

This doesn't error but I don't think anything happened:

testdb=# \dt;
No relations found.
testdb=# \d
No relations found.
testdb=# \t
Showing only tuples.
testdb=# \d

Also, I would appreciate an easier way to turn this .dump into a MySQL dump.

Answer

Daniel picture Daniel · Feb 13, 2012

You can save the data of the PostgreSQL database in plain text format with -Fp (plain), but you will have to do some manual conversion afterwards, since PostgreSQL contains many non-standard extensions to the SQL language.

However, if you already converted the schema, a data dump should be mostly compatible.

I recommend to use a tool like SquirrelSQL. It supports conversions from one database to another.

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