How to migrate a huge Oracle table to Postgres using oracle_fdw when primary key is composed of two fields?

Question

Currently migrating huge tables from Oracle to Postgres using oracle_fdw. Everything works fine on small tables that can even be transfered in a single query. But some tables are big (100GB - 2TB) so I must do it in multiple batches. For some of them the primary keys is made of two fields.

What is the best practice to migrate those tables in multiples batches ?

Answer 1

I see no problem in migrating a large table in a single batch. Different from Oracle, PostgreSQL has no limit on the size of a transaction. Besides, if you use smaller batches, you have to worry about data consistency.

But if you want to do it in batches, the easiest thing would be to have the first primary key column determine the batch. That way, you get a simple WHERE condition that can be pushed down to Oracle, and it can use an index scan on the primary key index.