Oracle Data Integrator (ODI) - Transfer data from Oracle to PostGreSQL Database

user12033760

We would like to transfer around 4 millions rows of data from Oracle Database to PostGreSQL using Oracle Database Integrator (ODI).

At the moment we have around 4 million rows on 600 columns (columns will potential increase in the future) in Oracle and when we transfer them in PostGreSQL it takes around 4 hours. For the transfer of the data we use JYTHON knowledge module in ODI.

4 hours is quite a long time and it is not an acceptable option for what we are doing. We would like someone to provide us with alternative options on how to move this amount of data, using ODI ONLY, from Oracle to PostgreSQL but with reduced time

Thank you

SH_INT

Not sure what the JYTHON knowledge module you are using in ODI does but generally the quickest way to copy large volumes of data into PostGreSQL is to use the copy command (note this requires a csv file as the source). There is a good article on PostGrSQL load performance here which shows how optimising the load of 32,500 records reduced the load time from just over 2 mins for a row by row insert to around 0.5 seconds when using the copy command. Take a look here:

https://hakibenita.com/fast-load-data-python-postgresql

All of the techniques could be implemented in ODI