54

I have changed database from to , 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 to ?

Improve this question
1
  • Pivotal also released a tool: github.com/pivotal-cf/pg2mysql (same name as the script below - different features though). Commented Aug 22, 2019 at 20:40

3 Answers 3

Reset to default
49

Faced the problem a few years ago, and used pg2mysql

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

In my case it has not worked well in converting the COPY commands that are produced by exporting from postgres.
@PeterDeWeese Using --inserts option dump data as INSERT commands.
script has syntax errors everywhere (MySQL 5.7 on Ubuntu 12.04). Works about as well as following these instructions
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
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.
|
19

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

Improve this answer

6 Comments

Sadly it does not work in macOS Sierra as of now.
But available only for mysql workbench commercial users.
@caras That's not true. MySQL workbench import works in the standard version just fine.
Here is the direct HowTo link to Migrate PostgreSQL databases to MySQL using the MySQL Workbench Migration Wizard: mysqlworkbench.org/2012/11/…
On Windows with Workbench 8.0.20, it was just too buggy. DBeaver is much better and more reliable.
|
5

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

Improve this answer

3 Comments

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...
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.
Dbeaver is great, installed easily on MAC, super-intuitive and prompted me to install the mysql/postgres drivers. Works flawlessly!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.