August 6th, 2012, 08:38 AM
Migrating from MySQL 5.0 to PostgreSQL db
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 220.127.116.11 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!!!
August 7th, 2012, 09:28 AM
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).
August 7th, 2012, 10:02 AM
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.
Tips on how to ask better questions:
August 7th, 2012, 10:05 AM
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.