0

I have an Oracle schema that I am transferring to PostgreSQL with ogr2ogr. This particular table has 5 columns with number(22) each. There are some 300M rows. In under 10 minutes I can transfer this table between two oracle servers over the network. ogr2ogr took a day to transfer 10M records and will take a month to transfer the whole table.

I suspect the problem is on the receiving end. Data is transferred to AWS PgSQL with 4 Gb ram. How to debug the bottleneck and speed up the transfer?

3
  • There is a foreign data wrapper for Oracle maybe that works better
    – user1822
    Jul 26, 2019 at 15:02
  • Does ogr2ogr create the indexes before it loads the data?
    – jjanes
    Jul 27, 2019 at 12:13
  • does ogr2ogr use single row inserts, multi-row inserts, or streaming to copy ... from stdin ? thousand-row inserts are about a hundred times faster than single row inserts, but streaming is about 10 times faster than that.
    – Jasen
    Apr 14, 2022 at 1:56

1 Answer 1

1

It might be worth pushing a subset to a file and loading it with copy, just to get a sense of the throughput. Copy is...legendary....for high-speed bulk loading into an empty table.

https://www.postgresql.org/docs/current/sql-copy.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.