Thread: Move From Oracle DB to PostgreSQL DB

On Mon, 2011-03-14 at 14:43 -0400, gene.poole@macys.com wrote:

> We're running Red Hat Satellite Server and it's used to provision our
> servers (both physical and virtual).  It works great and we have no
> issues  with it  *except* It will only provision Red Hat.
>
> Why this question:
>
> We've been directed by our management to examine the use of other
> Linux  distributions, like SUSE and Oracle Enterprise Linux.
>
> The 'real' questions:
>
> Is there a recent tutorial, white paper, how to on move/migrate from
> Oracle to PostgreSQL?


Please see
http://spacewalk.redhat.com/

PostgreSQL port is on the way. and it supports a few more distros IIRC.

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

On Mon, Mar 14, 2011 at 02:43:17PM -0400, gene.poole@macys.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?

Not really, no.  You might also have a look at ora2pg.  I have used
it, and while it still has some sharp corners it works.

A


--
Andrew Sullivan
ajs@crankycanuck.ca

Gene,

* gene.poole@macys.com (gene.poole@macys.com) wrote:
> Is there a recent tutorial, white paper, how to on move/migrate from
> Oracle to PostgreSQL?

It's typically "not hard", but it depends on what you're doing w/
Oracle.  Specifically, things like stored procedures (PL/SQL) may
require updates, if you're using RAC under Oracle, etc.

> 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?

The differences are signifigant enough that I wouldn't start with an old
major version if you have the option.  I'd recommend looking at 9.0.3 to
start, or at least 8.4.7.  I've found that Ora2Pg works quite well for
getting the schema and data across.

    Thanks,

        Stephen

On 03/14/11 11:43 AM, gene.poole@macys.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.

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:
> On Mon, 2011-03-14 at 14:43 -0400, gene.poole@macys.com wrote:
>> [ wants to port Red Hat Satellite to Postgres ]

> Please see
> http://spacewalk.redhat.com/

Yeah.  I'm on the fringes of that port effort, and it is *not* trivial;
Satellite is umpteen thousand lines of code with an Oracle dependency
in about every tenth one.  Don't imagine that you should try to do it
yourself.  But Spacewalk is a fully open-source effort (there were some
legal/IP reasons why Satellite wasn't :-() and they could use more hands.
If you want that to go faster, please feel free to join in and help.

            regards, tom lane