Re: Move From Oracle DB to PostgreSQL DB

On 03/14/11 11:43 AM, gene(dot)poole(at)macys(dot)com wrote:
> Is there a recent tutorial, white paper, how to on move/migrate from
> Oracle to PostgreSQL? I can get the Oracle schema DDL by pointing our
> Data Modeling software at the Red Hat Satellite server and extracting
> the DDL. It will take that Oracle DDL and convert it to PostgreSQL,
> but only version 8.x, and we were planning on looking at the open
> source PostgreSQL 8. Are the differences between versions that great?

if it works with 8.4, it probably will work with 9.0.

in my experience, 8.3 to 8.4 was a more difficult transition due to
tightening of some previously automatic type casting.

the big sticker with client apps in 9.0 seems to be changes in the
default encoding in BYTEA binary data. this only effects a rather
small number of apps.

converting from oracle to postgres, IMHO, your biggest issues will be ...

A) if you're heavily dependent on pl/sql business logic, you'll be doing
a LOT of redesign/refactoring/recoding. We're moving a lot of our
formerly pl/sql business logic into a java middleware layer.

B) if your apps are very performance tuned and optimized for the way
oracle does transactional updates, you may find you'll need to rearchitect.