pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedmigrate off oracle data to postgres
11+ messages / 7 participants
[nested] [flat]
* migrate off oracle data to postgres
@ 2020-03-31 14:15 Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 14:20 ` Re: migrate off oracle data to postgres John Wiencek <jwiencek3@comcast.net>
2020-03-31 14:35 ` Re: migrate off oracle data to postgres Jan Birk <jan.g.birk@gmail.com>
2020-03-31 14:58 ` Re: migrate off oracle data to postgres Thomas Kellerer <shammat@gmx.net>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
0 siblings, 4 replies; 11+ messages in thread
From: Pepe TD Vo @ 2020-03-31 14:15 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 14:20 ` John Wiencek <jwiencek3@comcast.net>
3 siblings, 0 replies; 11+ messages in thread
From: John Wiencek @ 2020-03-31 14:20 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
You should explore EnterprisDB’s Migration ToolKit (MTK).
Regards
John Wiencek
> On Mar 31, 2020, at 9:15 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> Hello,
>
> I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
>
>
> Bach-Nga
>
> No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 14:35 ` Jan Birk <jan.g.birk@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Jan Birk @ 2020-03-31 14:35 UTC (permalink / raw)
To: pgadmin-support@lists.postgresql.org
tir, 31 03 2020 kl. 14:15 +0000, skrev Pepe TD Vo:
> I have a huge data on Oracle, would you please suggest how to migrate all data
> off Oracle to Postgres? Do I need to export into csv and upload to postgres?
> Ora2pg is limited data from oracle to postgresql right?
How huge is huge?
We have used ora2pg for ~1tb with success
Best
/Jan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 14:58 ` Thomas Kellerer <shammat@gmx.net>
3 siblings, 0 replies; 11+ messages in thread
From: Thomas Kellerer @ 2020-03-31 14:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Pepe TD Vo schrieb am 31.03.2020 um 16:15:
> I have a huge data on Oracle, would you please suggest how to migrate
> all data off Oracle to Postgres? Do I need to export into csv and
> upload to postgres? Ora2pg is limited data from oracle to postgresql
> right?
Not sure what you mean here.
Ora2Pg can migrate the table definitions and copy data as well.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 15:46 ` JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
3 siblings, 1 reply; 11+ messages in thread
From: JC JC @ 2020-03-31 15:46 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; Pepe TD Vo <pepevo@yahoo.com>
In my view ora2pg can do most of the stuff you requested.
ora2pg can be used to generate the DDL from oracle to postgres based on attributes set in ora2pg conf file (for example TYPE TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring the data). No doubt you need to set remaining attributes based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if you like it to do Direct Data Streaming.
As of my limited experience I never encountered any issue on the limit on data being transferred, but definitely the load time vary depending on the environment.
Regards
jc
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 15:15
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Subject: migrate off oracle data to postgres
Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
@ 2020-03-31 15:51 ` Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:54 ` Re: migrate off oracle data to postgres Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-31 16:09 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
0 siblings, 2 replies; 11+ messages in thread
From: Pepe TD Vo @ 2020-03-31 15:51 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
I have tables ddl data definitions already. Only need to move the data over off Oracle to Postgres
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
#yiv3534479505 P {margin-top:0;margin-bottom:0;}In my view ora2pg can do most of the stuff you requested.ora2pg can be used to generate the DDL from oracle to postgres based on attributes set in ora2pg conf file (for example TYPE TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring the data). No doubt you need to set remaining attributes based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if you like it to do Direct Data Streaming.As of my limited experience I never encountered any issue on the limit on data being transferred, but definitely the load time vary depending on the environment.
Regardsjc
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 15:15
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Subject: migrate off oracle data to postgres Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 15:54 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-31 16:08 ` Re: migrate off oracle data to postgres Virendra Kumar <viru_7683@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2020-03-31 15:54 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
On 3/31/20 8:51 AM, Pepe TD Vo wrote:
> I have tables ddl data definitions already. Only need to move the data
> over off Oracle to Postgres
PostgreSQL Foreign Data Wrapper for Oracle:
http://laurenz.github.io/oracle_fdw/
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:54 ` Re: migrate off oracle data to postgres Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-31 16:08 ` Virendra Kumar <viru_7683@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Virendra Kumar @ 2020-03-31 16:08 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>; Adrian Klaver <adrian.klaver@aklaver.com>
FDW for oracle and ora2pg both are too slow compared to offload data in a file and load into PostgreSQL. When I was doing one of my migrations I found the best solution was to download data using sqlldr on to the database host where PostgreSQL instance is running and load it using COPY command.
Regards,
Virendra Kumar
On Tuesday, March 31, 2020, 8:55:17 AM PDT, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/31/20 8:51 AM, Pepe TD Vo wrote:
> I have tables ddl data definitions already. Only need to move the data
> over off Oracle to Postgres
PostgreSQL Foreign Data Wrapper for Oracle:
http://laurenz.github.io/oracle_fdw/
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 16:09 ` JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 16:45 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: JC JC @ 2020-03-31 16:09 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; Pepe TD Vo <pepevo@yahoo.com>
I am sure you might be referring this
https://ora2pg.darold.net/documentation.html
You need to create ora2pg conf file with TYPE COPY, and ALLOW <LIST OF TABLES separated by space> and remaining other attributes like
ORACLE_DSN
ORACLE_PWD
ORACLE_USER
similarly
PG_DSN
PG_USER
PG_PWD
etc
etc.
Cheers
jc
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 16:51
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
Subject: Re: migrate off oracle data to postgres
I have tables ddl data definitions already. Only need to move the data over off Oracle to Postgres
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
In my view ora2pg can do most of the stuff you requested.
ora2pg can be used to generate the DDL from oracle to postgres based on attributes set in ora2pg conf file (for example TYPE TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring the data). No doubt you need to set remaining attributes based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if you like it to do Direct Data Streaming.
As of my limited experience I never encountered any issue on the limit on data being transferred, but definitely the load time vary depending on the environment.
Regards
jc
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 15:15
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Subject: migrate off oracle data to postgres
Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 16:09 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
@ 2020-03-31 16:45 ` Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 17:00 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
0 siblings, 1 reply; 11+ messages in thread
From: Pepe TD Vo @ 2020-03-31 16:45 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
@JC - I do have ora2pg set up last year in test environment already and that what I suggested team using insert script to run in Postgres. And I am not sure how long it would take to load? 1T data. Though if there's a tool liked MySQL workbench to pull the oracle export data pump and import into MySQL.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 12:09:13 PM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
#yiv7970034777 P {margin-top:0;margin-bottom:0;}I am sure you might be referring this https://ora2pg.darold.net/documentation.html
You need to create ora2pg conf file with TYPE COPY, and ALLOW <LIST OF TABLES separated by space> and remaining other attributes like ORACLE_DSNORACLE_PWDORACLE_USER
similarly PG_DSNPG_USERPG_PWD etc etc.
Cheersjc
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 16:51
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
Subject: Re: migrate off oracle data to postgres I have tables ddl data definitions already. Only need to move the data over off Oracle to Postgres
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
In my view ora2pg can do most of the stuff you requested.ora2pg can be used to generate the DDL from oracle to postgres based on attributes set in ora2pg conf file (for example TYPE TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring the data). No doubt you need to set remaining attributes based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if you like it to do Direct Data Streaming.As of my limited experience I never encountered any issue on the limit on data being transferred, but definitely the load time vary depending on the environment.
Regardsjc
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 15:15
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Subject: migrate off oracle data to postgres Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: migrate off oracle data to postgres
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:46 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 16:09 ` Re: migrate off oracle data to postgres JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 16:45 ` Re: migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2020-03-31 17:00 ` JC JC <shekharjchandra@hotmail.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: JC JC @ 2020-03-31 17:00 UTC (permalink / raw)
To: pgsql-admin; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; Pepe TD Vo <pepevo@yahoo.com>
Though I am not very experienced in this, I am working on a migration project from Oracle to Postgres using ora2pg from around 5+ months.
The source data from which I migrate is of size 64 GB and I use AWS environment and run ora2pg in parallel by specifying the parameters in ora2pg utility. I migrate 3 schemas containing around 192 tables data and it takes around 2.5+ hrs (in normal windows environment other then AWS it use to take more than 9 hrs for the same size of data) and then I do some checks like count of source objects, and count of rows in the source as well as destination data. This is to check if the data is imported as required, Later I do lot of other stuff to validate the data.
I am sure 1TeraByte of data will take a long time, again it depends on the environment and system configuration it is running.
Not must idea on MySQL workbench tool, sorry.
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 17:45
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
Subject: Re: migrate off oracle data to postgres
@JC - I do have ora2pg set up last year in test environment already and that what I suggested team using insert script to run in Postgres. And I am not sure how long it would take to load? 1T data. Though if there's a tool liked MySQL workbench to pull the oracle export data pump and import into MySQL.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 12:09:13 PM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
I am sure you might be referring this
https://ora2pg.darold.net/documentation.html
You need to create ora2pg conf file with TYPE COPY, and ALLOW <LIST OF TABLES separated by space> and remaining other attributes like
ORACLE_DSN
ORACLE_PWD
ORACLE_USER
similarly
PG_DSN
PG_USER
PG_PWD
etc
etc.
Cheers
jc
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 16:51
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>; JC JC <shekharjchandra@hotmail.co.uk>
Subject: Re: migrate off oracle data to postgres
I have tables ddl data definitions already. Only need to move the data over off Oracle to Postgres
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, March 31, 2020, 11:46:58 AM EDT, JC JC <shekharjchandra@hotmail.co.uk> wrote:
In my view ora2pg can do most of the stuff you requested.
ora2pg can be used to generate the DDL from oracle to postgres based on attributes set in ora2pg conf file (for example TYPE TABLE, will allow getting oracle DDL in an output file, TYPE COPY will bring the data). No doubt you need to set remaining attributes based on the requirement.
Export to CSV is not required, as mentioned TYPE COPY will do a load of data if you like it to do Direct Data Streaming.
As of my limited experience I never encountered any issue on the limit on data being transferred, but definitely the load time vary depending on the environment.
Regards
jc
________________________________
From: Pepe TD Vo <pepevo@yahoo.com>
Sent: 31 March 2020 15:15
To: Pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-general <pgsql-general@postgresql.org>; PgAdmin Support List <pgadmin-support@lists.postgresql.org>
Subject: migrate off oracle data to postgres
Hello,
I have a huge data on Oracle, would you please suggest how to migrate all data off Oracle to Postgres? Do I need to export into csv and upload to postgres? Ora2pg is limited data from oracle to postgresql right?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2020-03-31 17:00 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-31 14:15 migrate off oracle data to postgres Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 14:20 ` John Wiencek <jwiencek3@comcast.net>
2020-03-31 14:35 ` Jan Birk <jan.g.birk@gmail.com>
2020-03-31 14:58 ` Thomas Kellerer <shammat@gmx.net>
2020-03-31 15:46 ` JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 15:51 ` Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 15:54 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-31 16:08 ` Virendra Kumar <viru_7683@yahoo.com>
2020-03-31 16:09 ` JC JC <shekharjchandra@hotmail.co.uk>
2020-03-31 16:45 ` Pepe TD Vo <pepevo@yahoo.com>
2020-03-31 17:00 ` JC JC <shekharjchandra@hotmail.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox