views:

3287

answers:

7

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.

Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improvements/losses, major hurdles in converting SQL and stored procedures, etc.

Edit: Just to clarify to those who have asked why we don't like MySQL's licensing. We are developing a commercial product which (currently) depends on MySQL as a database back-end. Their license states we need to pay them a percentage of our list price per installation, and not a flat fee. As a startup, this is less than appealing.

A: 

I don't have any migration tips for you, but I have to question your reason for moving. Unless you want to make changes to the database engine yourself, the GPL shouldn't be that big of a hassle.

That said, I love Postgres and would recommend it to anyone.

Neall
Actually, the client libraries are GPL as well, not just the server. If your program is linking against libmysqlclient.so, it either needs to be open source or you need to buy a commercial MySQL licence.So this affects you if your using MySQL from e.g. C but not if your using it from a PHP script.
skoob
It also affects you using MySQL from REALbasic as the only driver is GPL.
Andy Dent
A: 

Sorry for not providing a real answer, but I'm curious.
Which parts of the license bother you?

John Smithers
Licensing for MySQL has become something of a nightmare. I've spent several hours trying to make sense out of their licensing scheme. The short version of the story is that they have a specific provision that requires payment if you distribute software using MySQL (OEM $400 - $700 I think).
idontwanttortfm
This should be a comment under the original question.
jpartogi
Welcome to StackOverflow, jpartogi :-) Look at the time stamp. There was no commenting feature when I posted this.
John Smithers
+11  A: 

Steve, I had to migrate my old application the way around, that is PgSQL->MySQL. I must say, you should consider yourself lucky ;-) Common gotchas are:

  • SQL is actually pretty close to language standard, so you may suffer from MySQL's dialect you already know
  • MySQL quietly truncates varchars that exceed max length, whereas Pg complains - quick workaround is to have these columns as 'text' instead of 'varchar' and use triggers to truncate long lines
  • double quotes are used instead of reverse apostrophes
  • boolean fields are compared using IS and IS NOT operators, however MySQL-compatible INT(1) with = and <> is still possible
  • there is no REPLACE, use DELETE/INSERT combo
  • Pg is pretty strict on enforcing foreign keys integrity, so don't forget to use ON DELETE CASCADE on references
  • if you use PHP with PDO, remember to pass a parameter to lastInsertId() method - it should be sequence name, which is created usually this way: [tablename][primarykeyname]seq

I hope that helps at least a bit. Have lots of fun playing with Postgres!

Michał Rudnicki
+8  A: 

I have done a similar conversion, but for different reasons. It was because we needed better ACID support, and the ability to have web users see the same data they could via other DB tools (one ID for both).

Here are the things that bit us:

  1. MySQL does not enforce constraints as strictly as PostgreSQL.
  2. There are different date handling routines. These will need to be manually converted.
  3. Any code that does not expect ACID compliance may be an issue.

That said, once it was in place and tested, it was much nicer. With correct locking for safety reasons and heavy concurrent use, PostgreSQL performed better than MySQL. On the things where locking was not needed (read only) the performance was not quite as good, but it was still faster than the network card, so it was not an issue.

Tips:

  • The automated scripts in the contrib directory are a good starting point for your conversion, but will need to be touched a little usually.
  • I would highly recommend that you use the serializable isolation level as a default.
  • The pg_autodoc tool is good to really see your data structures and help find any relationships you forgot to define and enforce.
Grant Johnson
+4  A: 

There are some good technical articles on this subject here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

+1  A: 

Replicating maybe an issue you. MySQL supports it out of the box.

l_39217_l
+2  A: 

We did a move from a MySQL3 to PosgreSQL 8.2 than 8.3. Postgresql has the basic of SQL and a lot more so if your MYSQL do not use fancy MySql stuff you will be ok.

From my experience, our MySql database (version 3) doesn't have Foreign Key... PostGreSql let you have so we had to change that... and it was a good thing and we found some mistake.

The other thing that we had to change was the coding (C#) connector that wasn't the same in Mysql. The MySql one was more stable than the PostGresql one. We still have few problems with the PostGresql one.

Hope that help you.

Daok
"Postgresql", "PostGreSql", "PostGresql" => "PostgreSQL" ;-)
Endlessdeath