migrate off oracle data to postgres

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
bvo
Reply | Threaded
Open this post in threaded view
|

migrate off oracle data to postgres

bvo
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
Reply | Threaded
Open this post in threaded view
|

Re: migrate off oracle data to postgres

Virendra Kumar
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 <[hidden email]> 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
[hidden email]



bvo
Reply | Threaded
Open this post in threaded view
|

Re: migrate off oracle data to postgres

bvo
In reply to this post by bvo
@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 <[hidden email]> wrote:


I am sure you might be referring this 

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 <[hidden email]>
Sent: 31 March 2020 16:51
To: Pgsql-admin <[hidden email]>; Pgsql-general <[hidden email]>; PgAdmin Support List <[hidden email]>; JC JC <[hidden email]>
Subject: Re: migrate off oracle data to postgres
 
I have tables dddata 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 <[hidden email]> 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 <[hidden email]>
Sent: 31 March 2020 15:15
To: Pgsql-admin <[hidden email]>; Pgsql-general <[hidden email]>; PgAdmin Support List <[hidden email]>
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