I am using Oracle for production, and need to migrate to Postgres with no downtime. I figured out that I can follow the 4-step? approach for the migration.
First, write changes to both databases, and read only from Oracle, until Postgres has all the previous data from Oracle.
When the data migration is complete, start reading from both Postgres and Oracle, and compare the transactions. We still use Oracle, but check the Postgres transactions and log for errors/inconsistencies with Oracle.
When Postgres is trusted, meaning makes no errors, replace reading from Oracle with Postgres, but keep writing the changes to both databases. If something snaps with Postgres, with no downtime we can switch back to Oracle
When we finally trust Postgres, and its error-free, we can drop Oracle and continue only with Postgres.
Now the question: how do I efficiently write/read to Postgres, without disrupting the main flow of the application? Do I just open two connections? I have a feeling that this would impact the flow of the application.
Also, what about the performance loss when copying data from Oracle to Postgres? I cane across ora2pg, but I am still not sure about it.
So far I have tried opening two connections to both Oracle and Postgres, I have a timer on Postgres write for N-ms, inside a try-catch statement. I am worried that this would impact the normal flow performance of the application. Same for writing changes to both databases.