News for hackers. Stuff that matters.
Re:MySQL to PostgreSQL converter by jeltz on Saturday February 28, 2015 @10:13AM (#9123848)
I would recommend using pgloader (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.
Re:MySQL to PostgreSQL converter by adamferguson on Saturday February 28, 2015 @01:07PM (#9124448)
Upvote for pgloader. Moving data from SQL Server into Postgres with PGLoader has been fairly seamless and the maintainer is very responsive on Github.
Re:MySQL to PostgreSQL converter by louthy on Saturday February 28, 2015 @04:59PM (#9125207)
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 :)

Re:MySQL to PostgreSQL converter by jeltz on Saturday February 28, 2015 @09:59PM (#9125876)
Nice to hear that it works so well already. It is a very new feature which was sponsored by the company I work for only a couple of months ago. We are not even done migrating our database yet (we have to convert stored procedures and the queries in the application too).
Re:MySQL to PostgreSQL converter by scrollaway on Saturday February 28, 2015 @01:03PM (#9124436)
Seconded. Used pgloader to turn a massive mysql database into postgres. Turned out great.
Re:MySQL to PostgreSQL converter by anacleto on Saturday February 28, 2015 @04:39PM (#9125161)
+1 pgloader.
Re:MySQL to PostgreSQL converter by bjacobel on Saturday February 28, 2015 @09:34AM (#9123737)
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.
Re:MySQL to PostgreSQL converter by maligree on Saturday February 28, 2015 @04:09PM (#9125074)
> so simple

or complex

Re:MySQL to PostgreSQL converter by warbiscuit on Saturday February 28, 2015 @09:42AM (#9123755)
Hard to believe mysql doesn't include that data in one of it's dump formats.
Re:MySQL to PostgreSQL converter by morgo on Saturday February 28, 2015 @09:45AM (#9123761)
It is supported, just not in XML dump format.
Re:MySQL to PostgreSQL converter by warbiscuit on Saturday February 28, 2015 @09:55AM (#9123793)
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.
Re:MySQL to PostgreSQL converter by barking on Saturday February 28, 2015 @10:10AM (#9123836)
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?

Re:MySQL to PostgreSQL converter by maaaats on Saturday February 28, 2015 @07:54PM (#9125634)
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.
Re:MySQL to PostgreSQL converter by jeltz on Saturday February 28, 2015 @09:56PM (#9125869)
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.
Re:MySQL to PostgreSQL converter by danneu on Saturday February 28, 2015 @12:16PM (#9124293)
It contains the foreign key data, but not the constraint.

You get this:

    <field Field="course_id" Type="int(10)" Null="NO" Key="MUL"/>
But not this:

    CONSTRAINT `fk_courses_offered_class_details`
    FOREIGN KEY (`course_id`)
    REFERENCES `courses_offered` (`id`)
Re:MySQL to PostgreSQL converter by chris_wot on Saturday February 28, 2015 @10:03AM (#9123823)
How?
Re:MySQL to PostgreSQL converter by stephen on Saturday February 28, 2015 @11:27AM (#9124102)
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.

Re:MySQL to PostgreSQL converter by codexon on Saturday February 28, 2015 @03:01PM (#9124829)
I find the more difficult issue to be rewriting bulk "insert ignore" and "on duplicate key" queries.
Re:MySQL to PostgreSQL converter by tiglionabbit on Saturday February 28, 2015 @03:21PM (#9124893)
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.
Re:MySQL to PostgreSQL converter by jaytaylor on Saturday February 28, 2015 @04:39PM (#9125159)
Which other databases support "insert ignore"?
Re:MySQL to PostgreSQL converter by mh- on Saturday February 28, 2015 @07:20PM (#9125565)
while not that specific syntax, upserts are widely supported. e.g. http://en.wikipedia.org/wiki/Merge_%28SQL%29

I think PostgreSQL is relatively alone in lacking it.

Re:MySQL to PostgreSQL converter by driax on Saturday February 28, 2015 @08:26PM (#9125681)
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.

Re:MySQL to PostgreSQL converter by jaytaylor on Saturday February 28, 2015 @07:31PM (#9125590)
got it, thanks mh-!
Re:MySQL to PostgreSQL converter by rpedela on Saturday February 28, 2015 @08:27PM (#9125684)
It is looking like some form of upsert will make it into 9.5, but not confirmed yet.
Re:MySQL to PostgreSQL converter by caioariede on Saturday February 28, 2015 @10:19AM (#9123870)
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.

Re:MySQL to PostgreSQL converter by pornel on Saturday February 28, 2015 @11:47AM (#9124179)
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

Re:MySQL to PostgreSQL converter by chx on Saturday February 28, 2015 @11:53AM (#9124202)
There was but the last commit is from 2011?
Re:MySQL to PostgreSQL converter by pornel on Saturday February 28, 2015 @11:54AM (#9124208)
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.

Re:MySQL to PostgreSQL converter by zymhan on Saturday February 28, 2015 @12:11PM (#9124276)
Fork it!
Re:MySQL to PostgreSQL converter by mihailshumilov on Saturday February 28, 2015 @02:29PM (#9124711)
Thanks for Your comments It's really very important for me I think that next version of converter will be supported raw dump format
Re:MySQL to PostgreSQL converter by putna on Saturday February 28, 2015 @10:15AM (#9123851)
Re:MySQL to PostgreSQL converter by pgib on Saturday February 28, 2015 @11:38AM (#9124142)
Re:MySQL to PostgreSQL converter by pmelendez on Saturday February 28, 2015 @11:46AM (#9124172)
So... If I give you some binaries.. would you even care?
Re:MySQL to PostgreSQL converter by sarciszewski on Saturday February 28, 2015 @11:40AM (#9124147)
Really? What does the language it's written in have to do with anything at all here?
Re:MySQL to PostgreSQL converter by jeltz on Saturday February 28, 2015 @11:57AM (#9124219)
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.
Re:MySQL to PostgreSQL converter by olefoo on Saturday February 28, 2015 @12:49PM (#9124376)
Just because a few very disciplined people can make something clean and functional in a language does not mean it's a strength of the language. PHP is great as a template language, but when it comes to manipulating data it's somewhat louche approach to data types is a liability.