Postgres Pro
Downloads
Home   >   mailing lists

Thread: Oracle to PostgreSQL Migration - Need Information

Oracle to PostgreSQL Migration - Need Information

From
Tim Clotworthy
Date:
Hello, 
 
I have a customer that is about to undertake a migration of an Oracle 11g 
database to PostgreSQL 9.x (exact version to be determined). I am talking 
not only of the migration of schemas and data, but also of a substantial 
codebase of Pl/SQL stored procedures, as well as many triggers. 
 
I don't think they know yet what they are up against. Everything I have 
read is that this is a very substantial effort. At this stage, they would 
be particularly interested in realistic and practical information on how to 
estimate the effort required as well as any best-practices or guidance on 
transition strategies. 
 
I have found official documentation on the PostgreSQL site for porting 
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
there success will require that they are well prepared realistically 
understanding the scope of the effor they are asbout to undertake. 
 
Thanks for any response! 

Re: Oracle to PostgreSQL Migration - Need Information

From
Tim Clarke
Date:
This is almost exactly what we did around 8 years ago; obviously the version numbers have changed. The reason we chose Postgres was the enormous similarity between the two languages plus the overwhelming ROI on the migration; my CEO had a spontaneous nosebleed when the Oracle licensing costs were revealed one year.

Migration requires some thought, investigation, a clear plan and testing - but I'm probably preaching to the converted.

We did it, we've never looked back and have had at least 8 long very happy, stable and productive years and are looking forward to many more (raises a glass to the developers and maintainers).

Tim Clarke
On 08/07/15 20:24, Tim Clotworthy wrote:
Hello, 
 
I have a customer that is about to undertake a migration of an Oracle 11g 
database to PostgreSQL 9.x (exact version to be determined). I am talking 
not only of the migration of schemas and data, but also of a substantial 
codebase of Pl/SQL stored procedures, as well as many triggers. 
 
I don't think they know yet what they are up against. Everything I have 
read is that this is a very substantial effort. At this stage, they would 
be particularly interested in realistic and practical information on how to 
estimate the effort required as well as any best-practices or guidance on 
transition strategies. 
 
I have found official documentation on the PostgreSQL site for porting 
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
there success will require that they are well prepared realistically 
understanding the scope of the effor they are asbout to undertake. 
 
Thanks for any response! 

Re: Oracle to PostgreSQL Migration - Need Information

From
John McKown
Date:
On Wed, Jul 8, 2015 at 2:24 PM, Tim Clotworthy <tclotworthy@bluestonelogic.com> wrote:
Hello, 
 
I have a customer that is about to undertake a migration of an Oracle 11g 
database to PostgreSQL 9.x (exact version to be determined). I am talking 
not only of the migration of schemas and data, but also of a substantial 
codebase of Pl/SQL stored procedures, as well as many triggers. 
 
I don't think they know yet what they are up against. Everything I have 
read is that this is a very substantial effort. At this stage, they would 
be particularly interested in realistic and practical information on how to 
estimate the effort required as well as any best-practices or guidance on 
transition strategies. 
 
I have found official documentation on the PostgreSQL site for porting 
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
there success will require that they are well prepared realistically 
understanding the scope of the effor they are asbout to undertake. 
 
Thanks for any response! 

​Why are they converting? 

Would EnterpriseDB (a commercial version of PostgreSQL which has extensions to make it a "drop in" replacement for Oracle) be a possibility? 
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
​


--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: Oracle to PostgreSQL Migration - Need Information

From
dinesh kumar
Date:
Hi,

We recently done the similar migration for one of our customer. We used all opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which took minimal downtime with CDC{Change Data Capture} approach. Also, we used Ora2Pg tool to migrate the DB objects with some manual syntax modifications.

Regards,
Dinesh

On Wed, Jul 8, 2015 at 12:24 PM, Tim Clotworthy <tclotworthy@bluestonelogic.com> wrote:
Hello, 
 
I have a customer that is about to undertake a migration of an Oracle 11g 
database to PostgreSQL 9.x (exact version to be determined). I am talking 
not only of the migration of schemas and data, but also of a substantial 
codebase of Pl/SQL stored procedures, as well as many triggers. 
 
I don't think they know yet what they are up against. Everything I have 
read is that this is a very substantial effort. At this stage, they would 
be particularly interested in realistic and practical information on how to 
estimate the effort required as well as any best-practices or guidance on 
transition strategies. 
 
I have found official documentation on the PostgreSQL site for porting 
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
there success will require that they are well prepared realistically 
understanding the scope of the effor they are asbout to undertake. 
 
Thanks for any response! 

Re: Oracle to PostgreSQL Migration - Need Information

From
John R Pierce
Date:
On 7/8/2015 1:16 PM, dinesh kumar wrote:
> We recently done the similar migration for one of our customer. We
> used all opensource tools to achieve this migration process.
>
> We used Pentaho Data Integration tool for doing Online DB migration,
> which took minimal downtime with CDC{Change Data Capture} approach.
> Also, we used Ora2Pg tool to migrate the DB objects with some manual
> syntax modifications.
>

thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?



--
john r pierce, recycling bits in santa cruz

Re: Oracle to PostgreSQL Migration - Need Information

From
CS DBA
Date:

On 07/08/2015 02:20 PM, John R Pierce wrote:
> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>> We recently done the similar migration for one of our customer. We
>> used all opensource tools to achieve this migration process.
>>
>> We used Pentaho Data Integration tool for doing Online DB migration,
>> which took minimal downtime with CDC{Change Data Capture} approach.
>> Also, we used Ora2Pg tool to migrate the DB objects with some manual
>> syntax modifications.
>>
>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he
> mentioned ?
>
>
>
Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully

Re: Oracle to PostgreSQL Migration - Need Information

From
dinesh kumar
Date:
On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce <pierce@hogranch.com> wrote:
On 7/8/2015 1:16 PM, dinesh kumar wrote:
We recently done the similar migration for one of our customer. We used all opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which took minimal downtime with CDC{Change Data Capture} approach. Also, we used Ora2Pg tool to migrate the DB objects with some manual syntax modifications.


 thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?


Yeah, we need to rewrite the business logic if there are any un-supported features like autonomous transactions, packages, nested procedures, e.t.c.

Regards,
Dinesh



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Oracle to PostgreSQL Migration - Need Information

From
"Joshua D. Drake"
Date:
On 07/08/2015 12:47 PM, John McKown wrote:

>
> ​Why are they converting?
>
> Would EnterpriseDB (a commercial version of PostgreSQL which has
> extensions to make it a "drop in" replacement for Oracle) be a possibility?
> http://www.enterprisedb.com/solutions/oracle-compatibility-technology

Because EDB is expensive. Why go from one closed source solution to
another when you can go to the best Open Source database and forgo all
of that?

Yes, EDB has some nice tools (no denying that) but porting from Oracle
to PostgreSQL proper is not difficult in the least (although time
consuming).

Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

Re: Oracle to PostgreSQL Migration - Need Information

From
Melvin Davidson
Date:
I would start by looking at how many databases, schemas, tables and views are involved. Then look at how many individual Oracle functions need to be converted to plpgsql. You also need to investigate if there are any custom data types. I do not have the formula, but I am sure there is a general time factor involved in converting x databases, y schemas and z tables. You did not mention what hardware is involved, but I am also sure there is a time factor involved in copying / converting data from Oracle to PostgreSQL, and you need to consider how much data you need to move and the order, as there are probably foreign keys involved also.

You might want to do a small test to see how long it takes to dump 10k rows of data from 1 tOracle table and load to PostgreSQL. That will at least give you a general idea of how long it will take to move all data.

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 07/08/2015 12:47 PM, John McKown wrote:


​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology

Because EDB is expensive. Why go from one closed source solution to another when you can go to the best Open Source database and forgo all of that?

Yes, EDB has some nice tools (no denying that) but porting from Oracle to PostgreSQL proper is not difficult in the least (although time consuming).

Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Oracle to PostgreSQL Migration - Need Information

From
William Dunn
Date:
On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 07/08/2015 12:47 PM, John McKown wrote:


​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology

Because EDB is expensive. Why go from one closed source solution to another when you can go to the best Open Source database and forgo all of that?

Yes, EDB has some nice tools (no denying that) but porting from Oracle to PostgreSQL proper is not difficult in the least (although time consuming).

I would suggest refrain from dismissing EnterpriseDB's PostgreSQL Advanced Server like that. It is not free like the community version of Postgres but the cost pays for developer time spent adding the additional features which make it capable of being a drop-in replacement of Oracle. For an organization migrating off of Oracle paying for that would make a lot of sense because of the developer time (and cost) saved by porting to that rather than the additional effort of migration to community Postgres. It also has the additional benefit of providing all the programming features that their app developers have become used to when working with Oracle.

The EnterpriseDB developers are some of the most active contributors to the community version of Postgres and the advancements made in the community version are included in EnterpriseDB Advanced Server as well.

Will J. Dunn

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 07/08/2015 12:47 PM, John McKown wrote:


​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology

 Because EDB is expensive. Why go from one closed source solution to another when you can go to the best Open Source database and forgo all of that?

Yes, EDB has some nice tools (no denying that) but porting from Oracle to PostgreSQL proper is not difficult in the least (although time consuming).

Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Oracle to PostgreSQL Migration - Need Information

From
Julien Rouhaud
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Le 08/07/2015 22:25, CS DBA a écrit :
>
>
> On 07/08/2015 02:20 PM, John R Pierce wrote:
>> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>>> We recently done the similar migration for one of our customer.
>>> We used all opensource tools to achieve this migration
>>> process.
>>>
>>> We used Pentaho Data Integration tool for doing Online DB
>>> migration, which took minimal downtime with CDC{Change Data
>>> Capture} approach. Also, we used Ora2Pg tool to migrate the DB
>>> objects with some manual syntax modifications.
>>>
>>
>> thats the easy part.
>>
>> now what about the massive code base of pl/sql and triggers he
>> mentioned ?
>>
>>
>>
> Have you considered using ora2pg? http://ora2pg.darold.net/
>
> We've done several client migrations with it, quite successfully
>
>
>

ora2pg is definitely the tool you need.  It can give you a total
migration estimated time (in man-day unit), with a call like

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --estimate_cost

You can check a sample report here: http://ora2pg.darold.net/report.html

- --
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (GNU/Linux)

iQEcBAEBAgAGBQJVnimjAAoJELGaJ8vfEpOqTKAH/0+uGEXUmGm6tfagpJqU7kB+
2c+jooW/MKokDcgifvmUTy+fKb8iDoF8CUffActFyX5YyrCFfb4Bjw9P6wuJfF6S
WXhzWXQ//AFiApqNPknfHWnYeqe4jJlLq2fHN7qCQvItEWuKFiHpWcEi1zVBPnMm
e6NLxePm0WzjpigbwhT2X0Ziena8CxxdencPQvO81clsR8Fgtq4B//6KQ3GEsIL2
aUtj4k+wBCmRywiMgiSYiQzLUXUp2HWOp6qLLYpwifr4BgY2X+CQNSHlAK2KXecf
fQ+rm3tyo5QEtMxHQPO/NUsl+zSIllZjPYG1Wa81RwsQpWKhNNB/+reDTSgC5ws=
=9pb/
-----END PGP SIGNATURE-----