Migrate database from Postgres to MySQL [closed]

Question

I have changed database from postgresql to mysql, but I don't know how to transfer data from one to other.

Does anyone know any command by which I can copy/migrate my database from postgresql to mysql?

Glasser

Pivotal also released a tool: github.com/pivotal-cf/pg2mysql (same name as the script below - different features though). – Dramatics

Answer 1

Faced the problem a few years ago, and used pg2mysql

EDIT 21 july 2019 : this fork seems a better option (patches, light maintenance)

Ranunculus

In my case it has not worked well in converting the COPY commands that are produced by exporting from postgres. – Piddle

@PeterDeWeese Using --inserts option dump data as INSERT commands. – Forsworn

script has syntax errors everywhere (MySQL 5.7 on Ubuntu 12.04). Works about as well as following these instructions – Narcisanarcissism

If you're using php 7 you'll get a Parse error: syntax error, unexpected '&' in pg2mysql.inc.php on line 133 fix it either by removing the & (pass by reference in function calls) for all errors in the source or easily run php5 pg2mysql_cli.php <inputfilename.sql> <outputfilename.sql> [engine] in the command line if available – Motteo

Unfortunately this trick will fail on large DBs. I use a soft called "PostgreSQL to MySQL" (convert-in.com/pgs2sql.htm) for years myself. It works like a charm, even on huge databases, but comes at a cost: $49. Otherwise answer by @CarstenSchmitz below is the best solution IMO. – Queue

This sucks, all kinds of syntax errors when converting postgres to msyql. – Rosariorosarium

Answer 2

You can use the MySQL Workbench which can import from a variety of database types, including Postgres.

Mascot

Sadly it does not work in macOS Sierra as of now. – Trooper

But available only for mysql workbench commercial users. – Annemarie

@caras That's not true. MySQL workbench import works in the standard version just fine. – Mascot

Here is the direct HowTo link to Migrate PostgreSQL databases to MySQL using the MySQL Workbench Migration Wizard: mysqlworkbench.org/2012/11/… – Queue

Trying to install on MAC, the new version would not work since my MAC was not updated to 10.4, older version will not start installing, will not even give you a message on why it's not starting, it's terrible, use Dbeaver instead, I got it working in 5 mins. – Rosariorosarium

On Windows with Workbench 8.0.20, it was just too buggy. DBeaver is much better and more reliable. – Hsiuhsu

Answer 3

Dbeaver is the perfect solution for painless migration, column to column mapping, character encoding and also open source.

Prepositive

Definitely a nice tool! Nothing "painless" about that migration, though.. at least not in my case. The column mapping alone took too much manual work since the automatic types it chooses aren't very good... – Azelea

So... how is this done in DBeaver? Specifically, copying the table data structures, and separately, copying the data. Can you explain the process in your answer? For copying the data, I can only find how to do 1 table at a time, which is really inefficient. – Carmelitacarmelite

Dbeaver is great, installed easily on MAC, super-intuitive and prompted me to install the mysql/postgres drivers. Works flawlessly! – Rosariorosarium