Convert a PostgreSQL dump into a MySQL dump [duplicate]

Question

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.

Hessite

See: #5909723 – Jepson

Many related questions. – Eustache

Answer 1

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.

Shevat

What many non-standard extensions are you referring to? In my experience MySQL is a lot more non-standard than PostgreSQL – Efik

Look through postgresql.org/docs/9.1/interactive/sql-commands.html, and see the Compatibility notes in each section. – Shevat

Can you provide a link as to how to do these conversions? I installed SQL Squirrel and set it up, but am having a hard time finding any info on converting a database. – Airtight

Edit: need to use the DBCopy plugin, it turns out. Right-click all tables in the schema, hit copy, go to the other session and paste into the tables section for your db. – Airtight

Sorry for triple comment, but the DBCopy plugin's migration functionality is incredibly fragile. It can't handle converting to / from postgres' citext datatype, among numerous other things. I kinda regret spending time on this. Hopefully there's a better tool for SquirrelSQL. – Airtight

Please note that citext is a extension to postgresql and not part of the PostgreSQL core functionality. – Shevat