Thread

1. Converting a Simple Database from MySQL to PostgreSQL in 40 hours?

   Wang, Mary Y <mary.y.wang@boeing.com> — 2010-06-24T22:41:08Z

   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
   
   
   
   

2. Re: Converting a Simple Database from MySQL to PostgreSQL in 40 hours?

   David Fetter <david@fetter.org> — 2010-06-24T23:02:40Z

   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
   
   

3. Re: Converting a Simple Database from MySQL to PostgreSQL in 40 hours?

   Adrian Klaver <adrian.klaver@gmail.com> — 2010-06-25T00:11:58Z

   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
   
   

4. Re: Converting a Simple Database from MySQL to PostgreSQL in 40 hours?

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-25T05:25:23Z

   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