search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Performance penalty doing ora2pg migration in tables with lobs througth WAN #1735

Closed ayacopino closed 4 months ago

ayacopino commented 4 months ago

Hi Darold good morning.

We are using ora2pg to migrate from oracle to postgresql, oracle is in local network, and ora2pg and postgresql are in cloud. Doing a local migration, we have performance of 200 to 600 rows in tables with lobs. When doing the same migration throught WAN we have performance of 20rows/s with tables with lobs. Tables without lobs are not a problem.

1) Can you recommend is there a way to deal with network delay (60ms) with lobs in ora2pg?

2) I have tried ora_fdw but didn't work, I have the error that the source schema can't be found (I have oracle database with schema and tables with uppercase/lowercase combination (with quotes). Can ora_fdw can help with this?

3) Can't use primary key, we have a PK with multiple fields with chars. I have tried executing multiple ora2pg at same time, filtering data with date in a field, I have ora2pg of 20rows/sec but this is not enough (108 jobs per 3 tables, 1 job per 5 days, have table of 25 millions rows)

Thanks any feedback will be welcome, and thanks for the tool.

darold commented 4 months ago

Maybe you could have more performances by exporting data locally in a PostgreSQL database then generate a dump that can be imported to the cloud. There is no miracle, the bottleneck is the network, maybe a transfer of a single file of a compressed dump could be more performant.

ayacopino commented 4 months ago

Hi Darold, yes thanks for the feedback. Yes we are thinking to do that, create local files and then transfer files to the cloud. Thanks.

ayacopino commented 4 months ago

Could be the performance much better, if we put ora2pg near the source database (in LAN)? First tests we have done, it seems 10x faster, remote pg database in WAN and ora2pg near the oracle source database in same LAN network. Thanks.

darold commented 4 months ago

Right, you can also try to parallelize more the ora2pg export to WAN using the -P and -j options.