How to migrate data from mySQL to PostgreSQL?

I want to migrate the data source of my Java web application from oracle to either MySQL or PostgreSQL. What are the pros and cons for each migration ? Which one do you advice ? What is your experience?

• Answer:

  I'd go with PostgreSQL. AFAIK Postgres and Oracle are ANSI SQL compliant making it easy for you to almost completely transfer the code to Postgres with few hitches. Why don't you go ahead with deriving the database schema from oracle and fire the [code]CREATE TABLE[\code] queries into Postgres along with possibly possibly populating them with data (assuming you have data). You'll see two things: If there is some incompatibility with the SQL when creating tables If all okay, then populate the tables with data Transfer trigger/procs code to Postgres. Each of the above should tell you "what's wrong" and you may be able to fix it relatively easily. I also suggest keeping this page open while you're at it for reference: http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion