public inbox for [email protected]help / color / mirror / Atom feed
Oracle to postgres migration 14+ messages / 9 participants [nested] [flat]
* Oracle to postgres migration @ 2025-01-27 09:12 Rajesh Kumar <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Rajesh Kumar @ 2025-01-27 09:12 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 ? A little help is appreciated ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:15 Kashif Zeeshan <[email protected]> parent: Rajesh Kumar <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Kashif Zeeshan @ 2025-01-27 09:15 UTC (permalink / raw) To: Rajesh Kumar <[email protected]>; +Cc: Pgsql-admin <[email protected]> Hi Rajesh You can use EDB's Migration ToolKit (MTK) and following is the link to the documentation. https://www.enterprisedb.com/docs/migration_toolkit/latest/ On Mon, Jan 27, 2025 at 2:12 PM Rajesh Kumar <[email protected]> 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. > The time depends on the size of the data needed to migrate. Thanks Kashif Zeeshan > > Where do I start with? Anybody has any documentation related to ora2pg > migration ? > > A little help is appreciated > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:21 Raphael Salguero Aragón <[email protected]> parent: Kashif Zeeshan <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Raphael Salguero Aragón @ 2025-01-27 09:21 UTC (permalink / raw) To: Kashif Zeeshan <[email protected]>; +Cc: Rajesh Kumar <[email protected]>; Pgsql-admin <[email protected]> Hi Rajesh, ora2pg is a good starting point to get an overview about the complexity. But the effort for manual conversion also depends on your experiences and skills. That’s something you can adjust with the cost factors using ora2pg. Data migration is a different aspect. That depends on the db size, your object types and the migration method. I would suggest to get started with ora2pg first. Best regards Raphael Kashif Zeeshan <[email protected]> schrieb am Mo. 27. Jan. 2025 um 10:15: > Hi Rajesh > > You can use EDB's Migration ToolKit (MTK) and following is the link to the > documentation. > > https://www.enterprisedb.com/docs/migration_toolkit/latest/ > > > On Mon, Jan 27, 2025 at 2:12 PM Rajesh Kumar <[email protected]> > 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. >> > > The time depends on the size of the data needed to migrate. > > Thanks > Kashif Zeeshan > > >> >> Where do I start with? Anybody has any documentation related to ora2pg >> migration ? >> >> A little help is appreciated >> > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:22 Julien Rouhaud <[email protected]> parent: Rajesh Kumar <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Julien Rouhaud @ 2025-01-27 09:22 UTC (permalink / raw) To: Rajesh Kumar <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:30 Rajesh Kumar <[email protected]> parent: Julien Rouhaud <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Rajesh Kumar @ 2025-01-27 09:30 UTC (permalink / raw) To: Julien Rouhaud <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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, <[email protected]> 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. > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:33 Avinash Vallarapu <[email protected]> parent: Rajesh Kumar <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Avinash Vallarapu @ 2025-01-27 09:33 UTC (permalink / raw) To: Rajesh Kumar <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Pgsql-admin <[email protected]> Hi, On Mon, Jan 27, 2025 at 3:01 PM Rajesh Kumar <[email protected]> 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 > You can also use the Ora2Pg AI Chatbot, so that you can get responses if you are stuck while using Ora2Pg. https://ora2pgsupport.hexacluster.ai/ > > On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <[email protected]> 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. >> > -- Regards, Avinash Vallarapu CEO HexaCluster (www.hexacluster.ai) Try our new Database Migration Service: www.hexarocket.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 09:43 manish yadav <[email protected]> parent: Avinash Vallarapu <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: manish yadav @ 2025-01-27 09:43 UTC (permalink / raw) To: Rajesh Kumar <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Pgsql-admin <[email protected]>; Avinash Vallarapu <[email protected]> You may try EDB Migration portal (https://migration.enterprisedb.com/) for migration assessment which is freely available. EDB MTK to be used for data migration which is under subscription plan. Thanks and Regards, Manish Yadav On Monday 27 January, 2025 at 03:04:00 PM IST, Avinash Vallarapu <[email protected]> wrote: Hi, On Mon, Jan 27, 2025 at 3:01 PM Rajesh Kumar <[email protected]> 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 You can also use the Ora2Pg AI Chatbot, so that you can get responses if you are stuck while using Ora2Pg. https://ora2pgsupport.hexacluster.ai/ > > On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <[email protected]> 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. >> >> > -- Regards, Avinash Vallarapu CEO HexaCluster (www.hexacluster.ai) Try our new Database Migration Service: www.hexarocket.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 10:09 Ron Johnson <[email protected]> parent: Rajesh Kumar <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Ron Johnson @ 2025-01-27 10:09 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 <[email protected]> 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, <[email protected]> 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! ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 10:11 Rajesh Kumar <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Rajesh Kumar @ 2025-01-27 10:11 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> Thank you all. As mush as more info is always appreciated by dearest admins On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]> 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 <[email protected]> > 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, <[email protected]> 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! > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 10:13 Rajesh Kumar <[email protected]> parent: Rajesh Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Rajesh Kumar @ 2025-01-27 10:13 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> With regards to lo, is there any difficulty if we have rowsize > 1gb On Mon, 27 Jan 2025, 15:41 Rajesh Kumar, <[email protected]> wrote: > Thank you all. As mush as more info is always appreciated by dearest > admins > > On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]> 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 <[email protected]> >> 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, <[email protected]> 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! >> > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Oracle to postgres migration @ 2025-01-27 13:07 Raphael Salguero Aragón <[email protected]> parent: Rajesh Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Raphael Salguero Aragón @ 2025-01-27 13:07 UTC (permalink / raw) To: Rajesh Kumar <[email protected]>; +Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]> Hi Rajesh Rajesh Kumar <[email protected]> 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, <[email protected]> > wrote: > >> Thank you all. As mush as more info is always appreciated by dearest >> admins >> >> On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]> 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 < >>> [email protected]> 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, <[email protected]> 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! >>> >> ^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [EXT] Re: Oracle to postgres migration @ 2025-01-27 18:28 Wong, Kam Fook (TR Technology) <[email protected]> parent: Raphael Salguero Aragón <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Wong, Kam Fook (TR Technology) @ 2025-01-27 18:28 UTC (permalink / raw) To: Raphael Salguero Aragón <[email protected]>; Rajesh Kumar <[email protected]>; +Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]> 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 3rd 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 3rd 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 3rd 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 <[email protected]> Sent: Monday, January 27, 2025 7:08 AM To: Rajesh Kumar <[email protected]> Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]> Subject: [EXT] Re: Oracle to postgres migration External Email: Use caution with links and attachments. Hi Rajesh Rajesh Kumar <[email protected]<mailto:[email protected]>> 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, <[email protected]<mailto:[email protected]>> wrote: Thank you all. As mush as more info is always appreciated by dearest admins On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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, <[email protected]<mailto:[email protected]>> 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<https://urldefense.com/v3/...;. 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! ^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [EXT] Re: Oracle to postgres migration @ 2025-01-27 19:07 Wong, Kam Fook (TR Technology) <[email protected]> parent: Wong, Kam Fook (TR Technology) <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Wong, Kam Fook (TR Technology) @ 2025-01-27 19:07 UTC (permalink / raw) To: Raphael Salguero Aragón <[email protected]>; Rajesh Kumar <[email protected]>; +Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]> 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 <[email protected]>; Rajesh Kumar <[email protected]> Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]> 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 3rd 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 3rd 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 3rd 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 <[email protected]<mailto:[email protected]>> Sent: Monday, January 27, 2025 7:08 AM To: Rajesh Kumar <[email protected]<mailto:[email protected]>> Cc: Ron Johnson <[email protected]<mailto:[email protected]>>; Pgsql-admin <[email protected]<mailto:[email protected]>> Subject: [EXT] Re: Oracle to postgres migration External Email: Use caution with links and attachments. Hi Rajesh Rajesh Kumar <[email protected]<mailto:[email protected]>> 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, <[email protected]<mailto:[email protected]>> wrote: Thank you all. As mush as more info is always appreciated by dearest admins On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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, <[email protected]<mailto:[email protected]>> 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<https://urldefense.com/v3/...;. 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! ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [EXT] Re: Oracle to postgres migration @ 2025-01-28 16:05 Sam Stearns <[email protected]> parent: Wong, Kam Fook (TR Technology) <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Sam Stearns @ 2025-01-28 16:05 UTC (permalink / raw) To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: Raphael Salguero Aragón <[email protected]>; Rajesh Kumar <[email protected]>; Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]> We're in the middle of a migration, also. That's a great overview, Kam. Thank you. We've got schema and data migrated using Ora2pg. We're now looking at using HexaRocket to keep Postgres in sync with Oracle. Do you have any advice on HexaRocket or other sync tools? Thanks, Sam On Mon, Jan 27, 2025 at 11:07 AM Wong, Kam Fook (TR Technology) < [email protected]> wrote: > 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 <[email protected]>; Rajesh > Kumar <[email protected]> > *Cc:* Ron Johnson <[email protected]>; Pgsql-admin < > [email protected]> > *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 3rd 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 3rd 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 3rd 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 <[email protected]> > *Sent:* Monday, January 27, 2025 7:08 AM > *To:* Rajesh Kumar <[email protected]> > *Cc:* Ron Johnson <[email protected]>; Pgsql-admin < > [email protected]> > *Subject:* [EXT] Re: Oracle to postgres migration > > > > *External Email:* Use caution with links and attachments. > > > > Hi Rajesh > > > > Rajesh Kumar <[email protected]> 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 > <https://urldefense.com/v3/__https:/www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-work...; > > > > Best regards > > Raphael > > > > > > On Mon, 27 Jan 2025, 15:41 Rajesh Kumar, <[email protected]> > wrote: > > Thank you all. As mush as more info is always appreciated by dearest > admins > > > > On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email protected]> 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 <[email protected]> > 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, <[email protected]> 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 > <https://urldefense.com/v3/__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! > > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-01-28 16:05 UTC | newest] Thread overview: 14+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-27 09:12 Oracle to postgres migration Rajesh Kumar <[email protected]> 2025-01-27 09:15 ` Kashif Zeeshan <[email protected]> 2025-01-27 09:21 ` Raphael Salguero Aragón <[email protected]> 2025-01-27 09:22 ` Julien Rouhaud <[email protected]> 2025-01-27 09:30 ` Rajesh Kumar <[email protected]> 2025-01-27 09:33 ` Avinash Vallarapu <[email protected]> 2025-01-27 09:43 ` manish yadav <[email protected]> 2025-01-27 10:09 ` Ron Johnson <[email protected]> 2025-01-27 10:11 ` Rajesh Kumar <[email protected]> 2025-01-27 10:13 ` Rajesh Kumar <[email protected]> 2025-01-27 13:07 ` Raphael Salguero Aragón <[email protected]> 2025-01-27 18:28 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-27 19:07 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-28 16:05 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox