8

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

1 Answers1

6

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.

Daniel
  • 27,718
  • 20
  • 89
  • 133
  • 1
    What *many* non-standard extensions are you referring to? In my experience MySQL is a lot more non-standard than PostgreSQL –  Feb 13 '12 at 19:39
  • Look through http://www.postgresql.org/docs/9.1/interactive/sql-commands.html, and see the Compatibility notes in each section. – Daniel Feb 14 '12 at 10:46
  • 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. – Alkanshel Dec 02 '15 at 19:31
  • 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. – Alkanshel Dec 02 '15 at 19:47
  • 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. – Alkanshel Dec 02 '15 at 20:28
  • Please note that citext is a extension to postgresql and not part of the PostgreSQL core functionality. – Daniel Dec 03 '15 at 09:36