#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102

    Migrating from MySQL 5.0 to PostgreSQL db


    Hello everyone,

    Currently my company is a x86 Sun/Solaris 10 on Sun servers and using MySQL 5.0 db.

    Well the owner/boss has decide after 25 yrs we will no more be a SUN shop. I am new to this company and having 17+ yrs as a DBA ...where I have mostly been a Oracle DBA from (7x to 11.2.0.3 rel), and have also supported MySQL, PostgreSQL, and SQL server 2005 dbs as well. So I was hired to redesign and move them into a new environment in the Linux world.

    I believe our direction is to be a x86_64 Linux shop and the flavor of Linux of Red Hat 6 with Red Hat Suite as well on HP Blades, and although we will be upgrading all of our current customers from MySQL 5.0 to 5.1 then on to 5.5, but I am seriously considering moving us to PostgreSQL for all new customer. To me there is alot very much simular between Oracle and PostgreSQL dbs. Oh I know Oracle has a ton of bells and whistles, but you end up paying for them in support and license fees to Oracle corp as well.

    So my real question here is how difficult or hard is it to move from MySQL to PostgreSQL dbs. BTW if we do do this we will get support from a company called enterprisedb . I like having support for the RDBMS as well as for the OS and hardware. I would like to hear anyone's feeling and thoughts on this issue and anything else I/we should also consider in this direction.

    Thank you in advance!!!
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,301
    Rep Power
    7170
    If your current application is written using mysql queries then migrating would be a pretty major undertaking from a coding perspective. The query languages are similar, but there are just enough differences between them to virtually guarantee that many of your old queries will not work directly in postgres. A large part of that depends on what database abstraction layer your application code uses (if any).
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    You might want to start here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Hi there,

    To answer your question, they have developed a pretty basic code for the database so that it could be used in any RDBMS that the customer chooses. I for one am pushing that we push to leverage the RDBMS that we use more completely in the future.

IMN logo majestic logo threadwatch logo seochat tools logo