

MySQL to PostgreSQL converter - mihailshumilov
https://github.com/mihailShumilov/mysql2postgresql

======
jeltz
I would recommend using pgloader ([http://pgloader.io/](http://pgloader.io/))
instead. It is simple to use in the default case, but can be configured with
rules for how data types should be converted if necessary. It can also clean
up weird timestamps like 0000-00-00 00:00:00.

~~~
adamferguson
Upvote for pgloader. Moving data from SQL Server into Postgres with PGLoader
has been fairly seamless and the maintainer is very responsive on Github.

~~~
louthy
There doesn't seem to be any mention of SQL Server on the site? I am in the
process of researching a move to Postgres from SQL Server, so if you have any
other info on how you achieved this that'd be very much appreciated.

 _EDIT_ I see it now. Still, anything that you think useful would be great :)

------
bjacobel
No foreign key support is kind of a deal-breaker. Hard to believe anyone who
is actually in the position of needing this would have a schema so simple that
it contains no foreign keys.

~~~
warbiscuit
Hard to believe mysql doesn't include that data in one of it's dump formats.

~~~
morgo
It is supported, just not in XML dump format.

~~~
warbiscuit
Ah. That's a relief, I was a little worried. But that makes it strange that
someone, knowing of this limitation, would choose to start with the xml dump
format.

~~~
barking
I think the fact that this form of dump doesn't contain foreign keys is even
more extraordinary.

What's the point of having a dump format that you can't use to restore your
database properly?

Does it have a health warning advising that it's not a proper dump?

~~~
maaaats
Well, I've had issues with pg_dump's standard format. It can't be directly
used to restore a db, one must manually set it to dump a .bak or something
else.

~~~
jeltz
Not sure what you refer to here. All pg_dump formats should work for restoring
a database. The only flaw in pg_dump that I know of is the mess if you want to
dump an entire cluster of databases but still have one file per database.

------
stephen
We were getting really close to converting our RDS instance from MySQL -> PG,
but now with Amazon Aurora being the spiffy super-scalable/etc. RDS engine,
we'll probably end up moving to that when/if needed.

Which is a mixed blessing; Aurora sounds great, but I was looking forward to
getting back into PG after years of RDS being MySQL-only.

------
codexon
I find the more difficult issue to be rewriting bulk "insert ignore" and "on
duplicate key" queries.

~~~
tiglionabbit
Yeah, seems like postgres is the only database that doesn't support those
things. I mean, I use postgres, but I curse the lack of this feature
regularly.

~~~
jaytaylor
Which other databases support "insert ignore"?

~~~
mh-
while not that specific syntax, _upserts_ are widely supported. e.g.
[http://en.wikipedia.org/wiki/Merge_%28SQL%29](http://en.wikipedia.org/wiki/Merge_%28SQL%29)

I think PostgreSQL is relatively alone in lacking it.

~~~
driax
I have tried to read into why PostgresSQL doesn't yet (they are working it)
have upserts. Several times I have come across people discussing that Merge
which is much more powerful the simple upserts, also doesn't really mandate
the nature of upserts in most databases.

Simply a user might expect that upserts always succeed with either an update
or insert, and never return an error (except when the consistency model is set
high enough).

The problem is that many implementations of merge doesn't provide that
guarantee, unbeknownst to many users. Or they are simpler databases such as
sqlite (which doesn't provide the same multi-user/transaction performance).
Postgres, as they are well known for, want to implement it properly. They know
that application developers does not expect that they have to resubmit failed
upsert. Aside: they are also working on audit features, which presents a
number of implementations difficulties that upserts also does. So some time or
later, probably not to far away, we will see upserts in postgres. Proper
atomic, performant, nice upserts.

------
caioariede
[https://github.com/philipsoutham/py-
mysql2pgsql](https://github.com/philipsoutham/py-mysql2pgsql)

Works very well for me.

I think the only limitation is related to spatial data. I'm not aware of
anything else.

------
pornel
A big problem I've encountered was MySQL-specific functions used in client
code.

Fortunately there was a project that reimplemented many of these functions in
Postgres:

[https://github.com/pornel/mysqlcompat](https://github.com/pornel/mysqlcompat)

~~~
chx
There was but the last commit is from 2011?

~~~
pornel
That's when I've migrated ;)

The code is even older, but fortunately the most common MySQL functions
haven't changed in maybe a decade.

------
mihailshumilov
Thanks for Your comments It's really very important for me I think that next
version of converter will be supported raw dump format

------
putna
[https://github.com/lanyrd/mysql-postgresql-
converter](https://github.com/lanyrd/mysql-postgresql-converter)

does the job for me

------
pgib
When I saw this was a PHP project:
[https://dl.dropboxusercontent.com/u/31250/gifs/no-
thanks.gif](https://dl.dropboxusercontent.com/u/31250/gifs/no-thanks.gif)

~~~
sarciszewski
Really? What does the language it's written in have to do with anything at all
here?

~~~
jeltz
Here it actually matters a bit. The pgloader project was rewritten in Common
Lisp from the original Python version to speed up processing the data. You
want to be able to migrate terabyte databases without too much downtime.

