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