Thread: Oracle to postgres migration

Hi,

On Mon, Jan 27, 2025 at 02:42:22PM +0530, Rajesh Kumar wrote:
> Hi team,
>
> I am trying to migrate from oracle to postgres.
>
> I have been asked to provide an estimation for effort days. Anybody has any
> document related to estimation? And steps.
>
> Where do I start with? Anybody has any documentation related to ora2pg
> migration ?

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment.

In general the ora2pg documentation is really good, you should find the answer
to all your questions there.

I migrated a 12TB Oracle db that was mostly LOB objects into an 8TB PG database.  LOBs loaded into bytea columns.

One thing which I did not do, but should have, was have ora2pg convert NUMBER(38,0) values to BIGINT.

We just used ora2pg to convert data; the app developer rewrote all of the stored procedures, functions, triggers, etc.

On Mon, Jan 27, 2025 at 4:31 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

Size is 300gb, have lob objects. I prefer ora2pg. Does EDB MTK costs?

Mostly I need to know what are all the things I need to ask oracle people to start withj

On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <rjuju123@gmail.com> wrote:

Hi,

On Mon, Jan 27, 2025 at 02:42:22PM +0530, Rajesh Kumar wrote:
> Hi team,
>
> I am trying to migrate from oracle to postgres.
>
> I have been asked to provide an estimation for effort days. Anybody has any
> document related to estimation? And steps.
>
> Where do I start with? Anybody has any documentation related to ora2pg
> migration ?

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment.

In general the ora2pg documentation is really good, you should find the answer
to all your questions there.

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

Thank you all. As mush as more info is always appreciated by dearest admins

On Mon, 27 Jan 2025, 15:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

I migrated a 12TB Oracle db that was mostly LOB objects into an 8TB PG database.  LOBs loaded into bytea columns.

One thing which I did not do, but should have, was have ora2pg convert NUMBER(38,0) values to BIGINT.

We just used ora2pg to convert data; the app developer rewrote all of the stored procedures, functions, triggers, etc.

On Mon, Jan 27, 2025 at 4:31 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

Size is 300gb, have lob objects. I prefer ora2pg. Does EDB MTK costs?

Mostly I need to know what are all the things I need to ask oracle people to start withj

On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <rjuju123@gmail.com> wrote:

Hi,

On Mon, Jan 27, 2025 at 02:42:22PM +0530, Rajesh Kumar wrote:
> Hi team,
>
> I am trying to migrate from oracle to postgres.
>
> I have been asked to provide an estimation for effort days. Anybody has any
> document related to estimation? And steps.
>
> Where do I start with? Anybody has any documentation related to ora2pg
> migration ?

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment.

In general the ora2pg documentation is really good, you should find the answer
to all your questions there.

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

Adding to the list: 14. Study up locking (better yet test it yourself and select * from pg_locks/pg_stat_activity) and commit/auto commit and the behaviors of app impact. 15. Study up autovacuum (vs Oracle stats gathering) and the various parameters

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

 

ZjQcmQRYFpfptBannerEnd

Adding to the list:

14.  Study up locking (better yet test it yourself and select * from pg_locks/pg_stat_activity) and commit/auto commit and the behaviors of app impact.
15.  Study up autovacuum (vs Oracle stats gathering) and the various parameters that trigger the autovaccum to run.  And you should consider set up monitoring the autvacuum/why it didn’t run/why it was out of your expectations. 

 

Thank you

Kam

From: Wong, Kam Fook (TR Technology)
Sent: Monday, January 27, 2025 12:29 PM
To: Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: RE: [EXT] Re: Oracle to postgres migration

 

Rajesh,

 

We have done probably 1 thousand plus of Oracle DB migration to Postgres (and we still have Oracle and SQL Servers).  But I don’t have the documentation to share – one I don’t have it.  Two, even if I have it I can’t share it due to company policy.  In a high level here are a few things to chew on (others please add and correct)

1.  Schema migration – you can find a 3^rd party tool.

2.  Data migration – same as above.  If you are replicating your data online/ongoing from Oracle to Postgres with zero production downtime, be ready for “a lot/extremely busy” challenges.  You need a team just for this around the clock (lobs, data conflict resolution, performance, cascade delete and etc)

3.  Querries/store proc/trigger migration – you can find a 3^rd party tool but you still need manual changes, tuning, and logic verification.  Plus Scale testing.

4.  Partition table migration – you should tackle this problem early on if you have daily partition pruning.

5.  Cron job/DBMS scheduler job – we use pg_con extension.

6. Infrastructure sizing – make sure you size them correctly.

7.  Parameters configuration in Postgres – you will learn and face the challenges (vs Oracle init/pfile).

8.  Query performance tuning – Same concept but you will burn to learn quickly.

9.  Oracle AWR is no longer available.  One to two years ago I wasn’t able to find a comparable product.  We hire a brilliant contractor/consultant to write our custom snap that runs continuously (and prunes off the aged data).  We also use 3^rd party db tools and those alone often time is not sufficient to troubleshoot a challenging problem. 
10. Optimizer – good luck.  Find some good articles and study them (swim or drown).  There a only a handful of stuff you can tweak (I am still learning but there are expert-level gurus via this Posting that can help you).  But you don’t have the 1099 trace anymore. 

11.  Query hint – Oracle has hundreds of hints that you can use – this is a lifetime learning for those in Oracle DB fields but Postgres query hint is very minimal.  And your hand it tight when there are production query performance issue. 

12.  Profile query – I am not sure about the open source Postgres.  We are still working with AWS Aurora Postgres internal development team to enhance their QPM product. 

13.  Query plan flipping – I can’t speak for open source Postgres.  But AWS Aurora Postgres finally track query plan id on 14.11 and above. 

14.  And more that I missed. 

 

Thank you

Kam

p/s:  We didn’t use pg_largeobjects. We use byteA.  We ran into issues with > ~ 500 MB (out of memory) and we ended up “chunking” them into multiple rows for any lob size that is bigger than > 500 MB.  (developer code changes).

 

From: Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
Sent: Monday, January 27, 2025 7:08 AM
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXT] Re: Oracle to postgres migration

 

External Email: Use caution with links and attachments.

 

Hi Rajesh

 

Rajesh Kumar <rajeshkumar.dba09@gmail.com> schrieb am Mo. 27. Jan. 2025 um 11:13:

With regards to lo, is there any difficulty if we have rowsize > 1gb

For most cases, I would recommend to migrate lobs > 1gb into pg_largeobjects. The way of accessing those lobs will change (also for the application)

This could be done with a bit of python scripting. I’m not sure if there is a option within ora2pg meanwhile.

 

Regarding the sizes in general, you can check out below article: 

https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained

 

Best regards

Raphael 

 

 

On Mon, 27 Jan 2025, 15:41 Rajesh Kumar, <rajeshkumar.dba09@gmail.com> wrote:

Thank you all. As mush as more info is always appreciated by dearest admins

 

On Mon, 27 Jan 2025, 15:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

I migrated a 12TB Oracle db that was mostly LOB objects into an 8TB PG database.  LOBs loaded into bytea columns.

One thing which I did not do, but should have, was have ora2pg convert NUMBER(38,0) values to BIGINT.

 

We just used ora2pg to convert data; the app developer rewrote all of the stored procedures, functions, triggers, etc.

 

On Mon, Jan 27, 2025 at 4:31 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

Size is 300gb, have lob objects. I prefer ora2pg. Does EDB MTK costs?

Mostly I need to know what are all the things I need to ask oracle people to start withj

 

On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <rjuju123@gmail.com> wrote:

Hi,

On Mon, Jan 27, 2025 at 02:42:22PM +0530, Rajesh Kumar wrote:
> Hi team,
>
> I am trying to migrate from oracle to postgres.
>
> I have been asked to provide an estimation for effort days. Anybody has any
> document related to estimation? And steps.
>
> Where do I start with? Anybody has any documentation related to ora2pg
> migration ?

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment.

In general the ora2pg documentation is really good, you should find the answer
to all your questions there.

 

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!