Overview
This is a typical engagement for a mid-size SaaS platform team that has run a schema conversion and a first pass at PL/SQL, and now faces the part that decides the outage: moving the data and switching the application. Several Oracle databases feed one product, some of them depend on each other, and the business will not accept an open-ended maintenance window.
Challenge
A converted schema says little about how long the load takes, whether the source can keep serving while data moves, or what happens if the switch fails. Ora2Pg documents its data migration as offline and requiring downtime, while change capture needs source-side prerequisites and a lag to watch. Databases linked to each other cannot all cut over in the same hour. Without a written rollback, the change board has nothing to approve.
Environment
Oracle databases on-premises and in a cloud account, a PostgreSQL target on virtual machines, and application services that connect to several databases at once.
Approach
The engagement treats data movement and cutover as their own design problem. Each database is classed by how long it can stop. The load is timed on production-sized data in rehearsal, so the downtime figure comes from a measurement. Databases are ordered by their dependencies, and the rollback is written and rehearsed with the cutover, not afterwards.
Solution
- Class each database by tolerable downtime and write-rate, and choose offline export and load or full load plus change data capture for each
- List the source-side prerequisites for change capture and the lag thresholds that gate a go decision
- Order the cutovers across databases that depend on each other through links, and plan the period when some sit on Oracle and some on PostgreSQL
- Rehearse load and cutover on production-sized copies, and record measured times for every step
- Define validation: row counts, checksums and application-level checks before and after the switch
- Write the rollback per database: the Oracle source kept untouched as the fallback, or changes replicated back, and which writes would be lost in each case
- Keep the PostgreSQL major version fixed during the window, so the platform move is the only change
Outcome
The engagement delivers a data-movement method per database, a cutover order across dependent databases, measured rehearsal timings, a validation plan, and a written rollback with go or no-go checkpoints that a change board can approve. The cutover date is set from rehearsal results rather than hope.
Technologies
PostgreSQLAWS DMSOra2Pg
Kafka
Kubernetes
RabbitMQ
Docker
NGINX
Trino
Apache Hive
