Search code examples
mysqldatabasepostgresqldatabase-restoredatabase-dump

Convert a PostgreSQL dump into a MySQL dump

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.

Solution

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