agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedoracle to postgres migration
30+ messages / 22 participants
[nested] [flat]
* oracle to postgres migration
@ 2011-09-10 11:23 Karuna Karpe <karuna.karpe@os3infotech.com>
0 siblings, 0 replies; 30+ messages in thread
From: Karuna Karpe @ 2011-09-10 11:23 UTC (permalink / raw)
To: pgsql-admin
Hello,
Please I want to know how to use 'MERGE INTO' statement in oracle
database to postgresql, while migrating oracle to postgresql.
Please give me solution.
Regards,
Karuna karpe.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Oracle to postgres migration
@ 2017-12-26 00:30 Azimuddin Mohammed <azimeiu@gmail.com>
0 siblings, 3 replies; 30+ messages in thread
From: Azimuddin Mohammed @ 2017-12-26 00:30 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin
Hello,
Can anyone guide me through the steps for migration from oracle to postgres
with config changes required, keeping in mind that neither I am a oracle
DBA not postgres admin
--
Regards,
Azim
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2017-12-26 01:51 John Scalia <jayknowsunix@gmail.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
2 siblings, 0 replies; 30+ messages in thread
From: John Scalia @ 2017-12-26 01:51 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin
Hi,
May I first suggest that you first look at some of EnterpriseDB’s technical literature. They have some documents which list what their products add to PostgreSQL to make it more like Oracle. If your firm isn’t doing this just to get out of licensing fees, they would probably be a good option for you. The basics of a migration is to look at the Oracle schema in order to determine which data types you’ll need to convert. Then, you’ll need to check which stored procedures you already have, and then what you’ll need to turn into PostgreSQL functions. Offhand, I’d say this isn’t difficult, but it may get very involved.
—
Jay
Sent from my iPad
> On Dec 25, 2017, at 7:30 PM, Azimuddin Mohammed <azimeiu@gmail.com> wrote:
>
> Hello,
> Can anyone guide me through the steps for migration from oracle to postgres with config changes required, keeping in mind that neither I am a oracle DBA not postgres admin
>
> --
>
> Regards,
> Azim
>
>
> Virus-free. www.avast.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2017-12-26 09:36 Vasilis Ventirozos <v.ventirozos@gmail.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
2 siblings, 1 reply; 30+ messages in thread
From: Vasilis Ventirozos @ 2017-12-26 09:36 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: pgsql-admin
I'd start with this first : https://ora2pg.darold.net <https://ora2pg.darold.net/;
> On 26 Dec 2017, at 02:30, Azimuddin Mohammed <azimeiu@gmail.com> wrote:
>
> Hello,
> Can anyone guide me through the steps for migration from oracle to postgres with config changes required, keeping in mind that neither I am a oracle DBA not postgres admin
>
> --
>
> Regards,
> Azim
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...; Virus-free. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...; <x-msg://28/#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2017-12-26 12:32 Timo Myyrä <timo.myyra@bittivirhe.fi>
parent: Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Timo Myyrä @ 2017-12-26 12:32 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Tue, Dec 26, 2017, at 11:36, Vasilis Ventirozos wrote:
> I'd start with this first : https://ora2pg.darold.net
>
>> On 26 Dec 2017, at 02:30, Azimuddin Mohammed
>> <azimeiu@gmail.com> wrote:>>
>> Hello,
>> Can anyone guide me through the steps for migration from oracle to
>> postgres with config changes required, keeping in mind that neither I
>> am a oracle DBA not postgres admin>>
>> --
>>
>> Regards,
>> Azim
>>
>>
>> Virus-free. www.avast.com[1]
I second the ora2pg tool. Currently working on oracle to postgresql
migration with it and ora2pg helps a lot.But you can't get ready recipe for migration. There's ton of stuff to
consider from trivial rewriting NVL queries to use COALESCE to more
bigger stuff like lack of synonyms in postgresql. All depends on your
current Oracle database.
Timo
Links:
1. https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2017-12-26 13:14 Timo Myyrä <timo.myyra@bittivirhe.fi>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
2 siblings, 0 replies; 30+ messages in thread
From: Timo Myyrä @ 2017-12-26 13:14 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin
On Tue, Dec 26, 2017, at 02:30, Azimuddin Mohammed wrote:
> Hello,
> Can anyone guide me through the steps for migration from oracle to
> postgres with config changes required, keeping in mind that neither I
> am a oracle DBA not postgres admin>
> --
>
> Regards,
> Azim
>
>
> Virus-free. www.avast.com[1]
You should read this through before starting the migration process.
https://ora2pg.darold.net/slides/ora2pg_the_hard_way.pdf
Timo
Links:
1. https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...
^ permalink raw reply [nested|flat] 30+ messages in thread
* Oracle to Postgres migration
@ 2023-12-20 03:35 bimal maity <bimal.af2020@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: bimal maity @ 2023-12-20 03:35 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I have below query used in Oracle but while migrating to Postgres this code
is not supported in Postgres.
Could you please tell me how to resolve this?
SELECT p.id_po, p.line_number,
replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C,
regexp_replace(p.protocol_number,'([[:cntrl:]])','', 'g'))ORDER BY 1), ','
) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_number
,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C,
regexp_replace(p.protocol_status,'([[:cntrl:]])','', 'g'))ORDER BY 1), ','
) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_status
,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C,
regexp_replace(p.protocol_approver,'([[:cntrl:]])','', 'g'))ORDER BY 1),
',' ) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_approver
,max(p.protocol_date) AS protocol_date
,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C,
regexp_replace(p.protocol_nota,'([[:cntrl:]])','', 'g'))ORDER BY 1), ',' )
,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_nota
,sum(coalesce(p.protocol_value,0)) protocol_value
FROM podl_extended_protocol p
where upper(p.protocol_status) not in
('REJEITADO','ELIMINADO')
group by p.id_po, p.line_number
Thanks,
Bimal
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres migration
@ 2023-12-22 09:49 Ilya Kosmodemiansky <ik@dataegret.com>
parent: bimal maity <bimal.af2020@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Ilya Kosmodemiansky @ 2023-12-22 09:49 UTC (permalink / raw)
To: bimal maity <bimal.af2020@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Bimal,
On Fri, Dec 22, 2023 at 10:28 AM bimal maity <bimal.af2020@gmail.com> wrote:
> I have below query used in Oracle but while migrating to Postgres this code is not supported in Postgres.
I didn't try your query, but I guess it complains about RTRIM, because
it should accept text as an argument. If it is a case, you can try to dig in
direction of something like this:
> SELECT p.id_po, p.line_number, replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C, regexp_replace(p.protocol_number,'([[:cntrl:]])','', 'g'))ORDER BY 1)::text
(with explicit type casting)
best regards,
Ilya
--
Ilya Kosmodemiansky
CEO, Founder
Data Egret GmbH
Your remote PostgreSQL DBA team
T.: +49 6821 919 3297
ik@dataegret.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres migration
@ 2023-12-22 12:25 Thomas Kellerer <spam_eater@gmx.net>
parent: bimal maity <bimal.af2020@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Thomas Kellerer @ 2023-12-22 12:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
bimal maity schrieb am 20.12.2023 um 04:35:
> Hi,
>
> I have below query used in Oracle but while migrating to Postgres this code is not supported in Postgres.
> Could you please tell me how to resolve this?
>
> SELECT p.id_po, p.line_number, replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C, regexp_replace(p.protocol_number,'([[:cntrl:]])','', 'g'))ORDER BY 1), ',' ) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_number
> ,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C, regexp_replace(p.protocol_status,'([[:cntrl:]])','', 'g'))ORDER BY 1), ',' ) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_status
> ,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C, regexp_replace(p.protocol_approver,'([[:cntrl:]])','', 'g'))ORDER BY 1), ',' ) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_approver
> ,max(p.protocol_date) AS protocol_date
> ,replace(replace(replace(RTRIM(XMLAGG(XMLELEMENT(name C, regexp_replace(p.protocol_nota,'([[:cntrl:]])','', 'g'))ORDER BY 1), ',' ) ,'</C><C>','/'),'<C>',''),'</C>','') AS protocol_nota
> ,sum(coalesce(p.protocol_value,0)) protocol_value
> FROM podl_extended_protocol p
> where upper(p.protocol_status) not in ('REJEITADO','ELIMINADO')
> group by p.id_po, p.line_number
What exactly does it do? I have often seen the hack using xmlagg/xmlelement/regexp_replace to do some kind of poor man's unnest/string_agg.
If you tell us, what exactly the goal is, I am confident there is a better solution in Postgres.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Oracle to Postgres Migration
@ 2024-02-01 10:50 Kalyani Maity <bimal.af2020@gmail.com>
0 siblings, 3 replies; 30+ messages in thread
From: Kalyani Maity @ 2024-02-01 10:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I am doing an oracle to postgres migration.
I have one scenario where one synonym created as below in oracle DB:
create synonym 'schema1.procedure1' for 'schema2.procedure1'
procedure1 only exist in schema2.
I have migrated both schema 1 and schema 2 in postgres.
How to create this synonym in postgres.
Thanks.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres Migration
@ 2024-02-01 12:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Kalyani Maity <bimal.af2020@gmail.com>
2 siblings, 1 reply; 30+ messages in thread
From: Laurenz Albe @ 2024-02-01 12:38 UTC (permalink / raw)
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org
On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.
You don't. Instead, you set "search_path" to include both schemas.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres Migration
@ 2024-02-01 13:35 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Kalyani Maity <bimal.af2020@gmail.com>
2 siblings, 0 replies; 30+ messages in thread
From: MichaelDBA @ 2024-02-01 13:35 UTC (permalink / raw)
To: Kalyani Maity <bimal.af2020@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
In general, you convert Oracle synonyms to PG views in the public schema.
Kalyani Maity wrote on 2/1/2024 5:50 AM:
> Hi,
> I am doing an oracle to postgres migration.
>
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.
>
> Thanks.
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../a8bb6f89-dee3-630c-ff6f-602c21d886a3@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 30+ messages in thread
* Oracle to Postgres Migration
@ 2024-02-01 15:52 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 30+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-02-01 15:52 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I disagree a little with this. Setting search_path to fix non schema qualified SQL, is not a Best Practice.
You CAN do this and it will work, but it CAN cause trouble if your database has things of the same name. (again a not Best Practice).
My personal opinion on this, is to correct your SQL to include the schema qualified syntax (schema.whatever.) This way you are always 100% sure of what you are doing.
Just my $0.02
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Oracle to Postgres Migration
On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.
You don't. Instead, you set "search_path" to include both schemas.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres Migration
@ 2024-02-01 16:03 M Sarwar <sarwarmd02@outlook.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 30+ messages in thread
From: M Sarwar @ 2024-02-01 16:03 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I have worked on Federal, State and commercial projects and this is how any database object is referenced.
Not prefixing schema name along with the database object name will lead to several chaos situation in my opinion too.
Thanks,
Sarwar
________________________________
From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Sent: Thursday, February 1, 2024 10:52 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Oracle to Postgres Migration
I disagree a little with this. Setting search_path to fix non schema qualified SQL, is not a Best Practice.
You CAN do this and it will work, but it CAN cause trouble if your database has things of the same name. (again a not Best Practice).
My personal opinion on this, is to correct your SQL to include the schema qualified syntax (schema.whatever.) This way you are always 100% sure of what you are doing.
Just my $0.02
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Oracle to Postgres Migration
On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.
You don't. Instead, you set "search_path" to include both schemas.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres Migration
@ 2024-02-01 16:32 M Sarwar <sarwarmd02@outlook.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 30+ messages in thread
From: M Sarwar @ 2024-02-01 16:32 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Synonym can be used if the object has high frequency of usage in the application.
Admin/ Developer need to ensure that there is no conflict in using the naming convention.
If you suspect a fraction of naming convention conflict, that needs to be prefixed by a schema name and that should not be used as a synonym.
There are my thoughts.
Thanks,
Sarwar
________________________________
From: M Sarwar <sarwarmd02@outlook.com>
Sent: Thursday, February 1, 2024 11:03 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Oracle to Postgres Migration
I have worked on Federal, State and commercial projects and this is how any database object is referenced.
Not prefixing schema name along with the database object name will lead to several chaos situation in my opinion too.
Thanks,
Sarwar
________________________________
From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Sent: Thursday, February 1, 2024 10:52 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>; Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Oracle to Postgres Migration
I disagree a little with this. Setting search_path to fix non schema qualified SQL, is not a Best Practice.
You CAN do this and it will work, but it CAN cause trouble if your database has things of the same name. (again a not Best Practice).
My personal opinion on this, is to correct your SQL to include the schema qualified syntax (schema.whatever.) This way you are always 100% sure of what you are doing.
Just my $0.02
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Oracle to Postgres Migration
On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.
You don't. Instead, you set "search_path" to include both schemas.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to Postgres Migration
@ 2024-02-01 16:59 M Sarwar <sarwarmd02@outlook.com>
parent: Kalyani Maity <bimal.af2020@gmail.com>
2 siblings, 0 replies; 30+ messages in thread
From: M Sarwar @ 2024-02-01 16:59 UTC (permalink / raw)
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Kalyani,
If you want to refer newly migrated procedure, procedure1 which is now existing in the schema, scheam2, your solution is correct.
create synonym 'schema1.procedure1' for 'schema2.procedure1'
Thanks,
________________________________
From: Kalyani Maity <bimal.af2020@gmail.com>
Sent: Thursday, February 1, 2024 5:50 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Oracle to Postgres Migration
Hi,
I am doing an oracle to postgres migration.
I have one scenario where one synonym created as below in oracle DB:
create synonym 'schema1.procedure1' for 'schema2.procedure1'
procedure1 only exist in schema2.
I have migrated both schema 1 and schema 2 in postgres.
How to create this synonym in postgres.
Thanks.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Oracle to postgres migration
@ 2025-01-27 09:12 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Rajesh Kumar @ 2025-01-27 09:12 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:15 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Kashif Zeeshan @ 2025-01-27 09:15 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 <rajeshkumar.dba09@gmail.com>
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] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:21 Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Raphael Salguero Aragón @ 2025-01-27 09:21 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; +Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 <kashi.zeeshan@gmail.com> 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 <rajeshkumar.dba09@gmail.com>
> 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] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:22 Julien Rouhaud <rjuju123@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Julien Rouhaud @ 2025-01-27 09:22 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:30 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Rajesh Kumar @ 2025-01-27 09:30 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:33 Avinash Vallarapu <avinash.vallarapu@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Avinash Vallarapu @ 2025-01-27 09:33 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
On Mon, Jan 27, 2025 at 3:01 PM 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
>
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, <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.
>>
>
--
Regards,
Avinash Vallarapu
CEO
HexaCluster (www.hexacluster.ai)
Try our new Database Migration Service: www.hexarocket.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 09:43 manish yadav <manishy174@yahoo.co.in>
parent: Avinash Vallarapu <avinash.vallarapu@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: manish yadav @ 2025-01-27 09:43 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; Avinash Vallarapu <avinash.vallarapu@gmail.com>
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 <avinash.vallarapu@gmail.com> wrote:
Hi,
On Mon, Jan 27, 2025 at 3:01 PM 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
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, <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.
>>
>>
>
--
Regards,
Avinash Vallarapu
CEO
HexaCluster (www.hexacluster.ai)
Try our new Database Migration Service: www.hexarocket.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 10:09 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Ron Johnson @ 2025-01-27 10:09 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 10:11 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Rajesh Kumar @ 2025-01-27 10:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 10:13 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Rajesh Kumar @ 2025-01-27 10:13 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
With regards to lo, is there any difficulty if we have rowsize > 1gb
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!
>>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Oracle to postgres migration
@ 2025-01-27 13:07 Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Raphael Salguero Aragón @ 2025-01-27 13:07 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
>>>
>>
^ permalink raw reply [nested|flat] 30+ messages in thread
* RE: [EXT] Re: Oracle to postgres migration
@ 2025-01-27 18:28 Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
parent: Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
0 siblings, 1 reply; 30+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2025-01-27 18:28 UTC (permalink / raw)
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>
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 <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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<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] 30+ messages in thread
* RE: [EXT] Re: Oracle to postgres migration
@ 2025-01-27 19:07 Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
parent: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
0 siblings, 1 reply; 30+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2025-01-27 19:07 UTC (permalink / raw)
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>
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 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 <raphael.salguero@enterprisedb.com<mailto:raphael.salguero@enterprisedb.com>>
Sent: Monday, January 27, 2025 7:08 AM
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com<mailto:rajeshkumar.dba09@gmail.com>>
Cc: Ron Johnson <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>>; Pgsql-admin <pgsql-admin@lists.postgresql.org<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<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] 30+ messages in thread
* Re: [EXT] Re: Oracle to postgres migration
@ 2025-01-28 16:05 Sam Stearns <sam.stearns@dat.com>
parent: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
0 siblings, 0 replies; 30+ messages in thread
From: Sam Stearns @ 2025-01-28 16:05 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>; +Cc: Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; Peter Garza <peter.garza@dat.com>; Henry Ashu <henry.ashu@dat.com>
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) <
kamfook.wong@thomsonreuters.com> 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 <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 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 <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
> <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, <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
> <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] 30+ messages in thread
end of thread, other threads:[~2025-01-28 16:05 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-10 11:23 oracle to postgres migration Karuna Karpe <karuna.karpe@os3infotech.com>
2017-12-26 00:30 Oracle to postgres migration Azimuddin Mohammed <azimeiu@gmail.com>
2017-12-26 01:51 ` Re: Oracle to postgres migration John Scalia <jayknowsunix@gmail.com>
2017-12-26 09:36 ` Re: Oracle to postgres migration Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-12-26 12:32 ` Re: Oracle to postgres migration Timo Myyrä <timo.myyra@bittivirhe.fi>
2017-12-26 13:14 ` Re: Oracle to postgres migration Timo Myyrä <timo.myyra@bittivirhe.fi>
2023-12-20 03:35 Oracle to Postgres migration bimal maity <bimal.af2020@gmail.com>
2023-12-22 09:49 ` Re: Oracle to Postgres migration Ilya Kosmodemiansky <ik@dataegret.com>
2023-12-22 12:25 ` Re: Oracle to Postgres migration Thomas Kellerer <spam_eater@gmx.net>
2024-02-01 10:50 Oracle to Postgres Migration Kalyani Maity <bimal.af2020@gmail.com>
2024-02-01 12:38 ` Re: Oracle to Postgres Migration Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-01 15:52 ` Oracle to Postgres Migration Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2024-02-01 16:03 ` Re: Oracle to Postgres Migration M Sarwar <sarwarmd02@outlook.com>
2024-02-01 16:32 ` Re: Oracle to Postgres Migration M Sarwar <sarwarmd02@outlook.com>
2024-02-01 13:35 ` Re: Oracle to Postgres Migration MichaelDBA <MichaelDBA@sqlexec.com>
2024-02-01 16:59 ` Re: Oracle to Postgres Migration M Sarwar <sarwarmd02@outlook.com>
2025-01-27 09:12 Oracle to postgres migration Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2025-01-27 09:15 ` Re: Oracle to postgres migration Kashif Zeeshan <kashi.zeeshan@gmail.com>
2025-01-27 09:21 ` Re: Oracle to postgres migration Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
2025-01-27 09:22 ` Re: Oracle to postgres migration Julien Rouhaud <rjuju123@gmail.com>
2025-01-27 09:30 ` Re: Oracle to postgres migration Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2025-01-27 09:33 ` Re: Oracle to postgres migration Avinash Vallarapu <avinash.vallarapu@gmail.com>
2025-01-27 09:43 ` Re: Oracle to postgres migration manish yadav <manishy174@yahoo.co.in>
2025-01-27 10:09 ` Re: Oracle to postgres migration Ron Johnson <ronljohnsonjr@gmail.com>
2025-01-27 10:11 ` Re: Oracle to postgres migration Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2025-01-27 10:13 ` Re: Oracle to postgres migration Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2025-01-27 13:07 ` Re: Oracle to postgres migration Raphael Salguero Aragón <raphael.salguero@enterprisedb.com>
2025-01-27 18:28 ` RE: [EXT] Re: Oracle to postgres migration Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
2025-01-27 19:07 ` RE: [EXT] Re: Oracle to postgres migration Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
2025-01-28 16:05 ` Re: [EXT] Re: Oracle to postgres migration Sam Stearns <sam.stearns@dat.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox