Thread: Converting a Simple Database from MySQL to PostgreSQL in 40 hours?
Hi,
My internal customer has a new project and is considering using MySQL. Knowing that I'm a Postgres person, the customer is open to considering Postgres at a later date if the cost to transition is less than 40 hours. The database will probably be relatively small and simple (still in the planning stage). I personally think it's not possible to convert even a simple database from MySQL to Postgres in less than 40 hours.
Has anyone done the conversion before? If so, what do you think?
Please advise.
Mary
On Thu, Jun 24, 2010 at 03:41:08PM -0700, Wang, Mary Y wrote: > Hi, > > My internal customer has a new project and is considering using > MySQL. Knowing that I'm a Postgres person, the customer is open to > considering Postgres at a later date if the cost to transition is > less than 40 hours. The database will probably be relatively small > and simple (still in the planning stage). I personally think it's > not possible to convert even a simple database from MySQL to > Postgres in less than 40 hours. Has anyone done the conversion > before? If so, what do you think? This depends on a great many factors including: * The size of the data * What it's currently stored on for MySQL (a single slow spindle with a couple of TB on it may take time) * What it will be stored on for PostgreSQL * What MySQL idioms won't translate directly to PostgreSQL Generally, getting the schema and data moved over are the first two steps in a much longer process, wherein all the apps use the database as an active database rather than a passive one, the latter being all MySQL really allows. Hope this helps :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Thursday 24 June 2010 3:41:08 pm Wang, Mary Y wrote: > Hi, > > My internal customer has a new project and is considering using MySQL. > Knowing that I'm a Postgres person, the customer is open to considering > Postgres at a later date if the cost to transition is less than 40 hours. > The database will probably be relatively small and simple (still in the > planning stage). I personally think it's not possible to convert even a > simple database from MySQL to Postgres in less than 40 hours. Has anyone > done the conversion before? If so, what do you think? > > > Please advise. > Mary In addition to what David said, you might want to see about getting involved in the planning stage. I could see creating a shadow Postgres database that is kept close to the MySQL one via some scripts. Would make the transition less hectic. Would also give the developers some thing to work with as they went along. -- Adrian Klaver adrian.klaver@gmail.com
Heyho!
On Friday 25 June 2010 00.41:08 Wang, Mary Y wrote:
> I personally think it's not possible to convert even a simple database
> from MySQL to Postgres in less than 40 hours.
The problem is not the database, the problem is the application.
Converting the database takes a few hours at most (plus the actual import
time if it's a large db) and there are migration scripts available.
The time consuming part, and also the part where it's impossible to tell you
how long it takes because you haven't said anything about it, is the
application. If you take advantage of many MySQL specific features,
solutions will have to be implemented that work in PostgreSQL. If you had
performance problems in MySQL, whatever optimization you did was probably
specific to MySQL and may well make matters worse in pg (not because pg is
generally slower or faster or whatever, but because it's an entirely
different db and performance tuning needs to take into account how the db
works..) Etc, etc.
I converted an application from Oracle to support both Oracle and postgres.
The database itself was done after the first day and a half, including
extending the scripts that create the db so I could specify that it should
use Oracle or Postgres.
Our queries were all very basic, so I could convert the application in
another two days mostly by looking for stuff like NVL() function and similar
Oracle specific things and replacing them with SQL standard syntax that
works in both cases. The tricky bit was then testing all parts of the
application so that all queries would actually have been executed at least
once. After ca. 2 weeks I was confident that I had covered "everything"
(you always forget at least one case, don't you...), and now we're happily
using the application with PostgreSQL by default and with Oracle if we need
to use it to interface with another application at the customer's
installation.
Conclusion: your '40 hours' are realistic for a very simple *application*.
40 hours (manpower, not load time) is much too long if you only talk about
converting the database itself. 40 hourse also become much too short if the
application is not trivial. And the really difficult part is, as always,
testing that you didn't forget a corner case (MySQL and the rest of the SQL
world differ quite a bit in behaviour involving NULL values vs. empty
strings and IIRC by default MySQL allows you to use implicit type casts in
many ways. No idea about date/time handling, but I'd expect some work there
as well (In the Oracle -> pg it wasn't that much work in the end, but I had
to read up quite a bit on how exactly the Oracle date/time types behave so I
could be sure it's ok.)
You may want to read the recent discussion about "database agnostic"
programming on this list.
cheers
-- vbi
(Creepy. My signature generator becomes increasingly context aware -
getting a quote like that when I'm posting in an MySQL/PostgreSQL thread...
;-)
--
Every religion is about absolute belief in its own superiority and the
divine right to impose its version of truth upon others.
-- Pervez Amir Ali Hoodbhoy, Prospect Magazine Feb 2002