Reputation: 33240

Is there a simple tool to convert mysql to postgresql syntax?

I've tried the tools listed here, some with more success than others, but none gave me valid postgres syntax I could use (tinyint errors etc.)

Upvotes: 29

Answers (10)

vog

Reputation: 25597

There's a mysqldump --compatible option which makes it output "more compatible" PostgreSQL code:

mysqldump --compatible=postgresql ...

But that doesn't work too well.

From the 5.7 docs:

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options.

...

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible.

Instead, please see the mysql-to-postgres tool as described in Linus Oleander's answer.

Upvotes: 21

Simon Epskamp

Reputation: 9966

As of 2023, I've found that NMIG is nice up-to-date tool that directly converts from live mysql to postgres databases. It works better than pgloader for me, which cannot connect to mysql 8 by default.

Usage: see readme, or:

git clone https://github.com/AnatolyUss/NMIG nmig
cd nmig
npm ci
npm run build
# Now edit config/config.json
npm run start

Upvotes: 4

Paul Rougieux

Reputation: 11379

Install pgloader on Debian or Ubuntu:

sudo apt install pgloader

sudo su postgres
createdb -O user db_migrated

Transfer data from the mysql database to postgresql

pgloader mysql://user@localhost/db postgresql:///db_migrated

Check also Dimitri Fontaine's rewrite of pgloader from python to common lisp so that he could implement real threading.

Installation on other platforms

To install pgloader on Windows, you can use the Windows Subsystem for Linux.
To install pgloader on Mac, you can use: brew install --HEAD pgloader.

Upvotes: 8

R.Sehdev

Reputation: 455

Try this one , it works like charm !!

http://www.sqlines.com/online

Upvotes: 21

Cees Timmerman

Reputation: 19644

This page lists the syntax differences, but a simple working query converter i haven't found yet. Using an ORM package instead of raw SQL could prevent these issues.

I'm currently hacking up a converter for a legacy codebase:

function mysql2pgsql($mysql){
    return preg_replace("/limit (\d+), *(\d+)/i", "limit $1 offset $2", preg_replace("/as '([^']+)'/i", 'as "$1"', $mysql)); // Note: limit needs order
}

For CREATE statements, SQLines converts most of them online. I still had to edit the mysqldump afterwards, though:

"mediumtext" -> "text", "^LOCK.*" -> "", "^UNLOCK.*" -> "", "`" -> '"', "'" -> "''" in 'data', "0000-00-00" -> "2000-01-01", deduplicate constraint names, " CHARACTER SET utf8 " -> " ".
"int(10)" -> "int" was missed in the last table, so pass that part of the mysqldump through http://www.sqlines.com/online again.

Upvotes: 0

Michał Powaga

Reputation: 23173

I've used py-mysql2pgsql. After installation it needs only simple configuration file in yml format (source, destination), e.g.:

# if a socket is specified we will use that
# if tcp is chosen you can use compression
mysql:
 hostname: localhost
 port: 3306
 socket: /tmp/mysql.sock
 username: mysql2psql
 password:
 database: mysql2psql_test
 compress: false
destination:
 # if file is given, output goes to file, else postgres
 file:
 postgres:
  hostname: localhost
  port: 5432
  username: mysql2psql
  password:
  database: mysql2psql_test

Usage:

> py-mysql2pgsql -h
usage: py-mysql2pgsql [-h] [-v] [-f FILE]

Tool for migrating/converting data from mysql to postgresql.

optional arguments:
  -h, --help            show this help message and exit
  -v, --verbose         Show progress of data migration.
  -f FILE, --file FILE  Location of configuration file (default:
                        mysql2pgsql.yml). If none exists at that path,
                        one will be created for you.

More on its home page https://github.com/philipsoutham/py-mysql2pgsql.

Upvotes: 1

Linus Oleander

Reputation: 18127

After some time on Google I found this post.

Install the mysql2psql gem using [sudo] gem install mysql2psql.
Create a config file by running mysql2psql. You'll see an error but a mysql2psql.yml file should have been created.
Edit mysql2psql.yml
Run mysql2psql again to migrate you data.

Tip: Set force_truncate to true in your mysql2psql.yml config file if you want the postgresql database to be cleared before migrating your data.

Upvotes: 4