This is a little left field… but we’ve migrated a lot of onprem oracle databases to Postgres (on aws rds) using aws database migration service - which has worked very well and supports cdc.
I do wonder if you did the above and then add a 2nd cdc out from Postgres rds to your onprem Postgres.
So onprem oracle -> rds Postgres -> onprem Postgres (all working simultaneously)
(It wouldn’t work onprem oracle to onprem Postgres without a aws cloud setup)
Just a random thought and fairly straightforward to setup.
On Fri, 2023-03-24 at 08:00 +0000, Inzamam Shafiq wrote:
> Thanks, oracle_fdw can be used, but it doesn't provide real time sync, we have to
> schedule jobs to insert data in actual schema from foreign tables, so some delay might be there.
In addition to the commercial tools that Christophe mentioned, and which might be
your best option, let me point out that ora_migrator
(https://github.com/cybertec-postgresql/ora_migrator), which is open source and based
on oracle_fdw, offers some degree of what you are looking for using a cursor-based soution.
Certainly net refined and certainly not feasible in cases with high transactional
workload, but perhaps it can be useful for you.
Yours,
Laurenz Albe
Sent from iPhone