Plenty of banks still plan core database upgrades around a Saturday night "maintenance window." That habit made sense when branches closed at six. Now a card issuer in Tokyo clears payments while London sleeps, and one hour with the ledger frozen means declined cards at checkout and settlements slipping to the next day. Application code can be rolled back in a minute. Lost transactions can't. So database migration is still the riskiest part of modernization.
The Night Window Doesn't Exist Anymore
Pretty much every DBA who once had to move a live Oracle ledger offline has a war story about it. Fourteen terabytes to export. A 10 Gbps link that somehow behaved like a 1 Gbps one at three in the morning. And the restore, which finished about forty minutes after the Hong Kong desk opened.
So things changed. These days engineering teams planning big core systems modernization programs tend to pair bidirectional data replication with automated schema conversion, mostly so that transactions don't have to stop for even a minute. The legacy database stays in charge. The new one runs next to it, fills up with data and sits through weeks of real traffic before anyone trusts it with a single payment.
So what breaks if a team still goes the offline way? Things nobody wrote down in the runbook:
-
the interest accrual job that started at 23:40 and can't be stopped halfway
-
fraud scoring and regulatory feeds, which suddenly point at a database that isn't there
-
rollback, which offline means restoring last night's backup and losing every write made since
-
a COBOL batch on the mainframe that still reads a VSAM file everyone thought was retired years ago
Where CDC Comes In
Behind almost all of these projects sits Change Data Capture (CDC). Old idea, honestly, but it still does the job. Nobody polls the tables. Every serious database keeps a log anyway, like redo in Oracle or the WAL in PostgreSQL. CDC reads those logs and sends out every committed change as its own event.
What About Triggers?
Some older projects still capture changes with triggers. On a quiet table, they're fine. Month-end is a different story. The busiest table gets hit hard, each insert waits a couple of milliseconds longer, and with a few thousand writes a second that delay gets very noticeable. With log-based CDC, the source doesn't really feel any of it, since the log gets read after the commit.
The Tooling
-
Debezium is free and open source. A lot of teams start here because it plugs into Kafka Connect and already has connectors for Oracle, SQL Server, PostgreSQL, MySQL and Db2.
-
Oracle GoldenGate has been around for ages. Pricey, sure, but its conflict handling for two-way replication has been used in banks for years.
-
AWS Database Migration Service (DMS). If a company is already on AWS, this is usually the first thing it tries, along with the schema conversion feature for the PL/SQL.
-
Apache Kafka sits between source and target. When the target falls behind, events just pile up in a topic and wait.
-
With Db2 for z/OS or IMS on the mainframe side, it's mostly IBM InfoSphere Data Replication or Precisely Connect.
Numbers, then. Lag on a tuned Oracle-to-Aurora PostgreSQL link through Kafka sits below 45 ms on a normal day, at least while nothing heavy is running. The nightly batch pushes that up to a few seconds. That's expected. Alerts usually go to a Slack channel at 500 ms, and someone gets paged at 5 seconds.
Ordering is the other trap. Kafka keeps events in order only inside one partition. Key the topic by account ID, and a debit will never arrive before the credit it depends on. Use random keys, and good luck explaining the balances to the auditors.
Strangler Fig, Applied to Data
Martin Fowler took the name from a fig that grows on a host tree and slowly replaces it. In software, you wrap the old system and then empty it out, feature after feature. For databases, the idea holds up only when the split follows business domains. Splitting by table count because a migration tool happens to sort them that way rarely ends well.
What Moves First
Customer profiles and product catalogs are the easy wins. Lots of reads, few tricky transactions. Ledger and payments wait until the end. Those are the riskiest parts, and they tend to hold a 4,000-line stored procedure from 2009 that no one wants to open. Requests get routed through an API gateway, a service mesh or a shared data access library, whichever the team already has.
Reads Go Over Before Writes
Here's a very common middle stage. Oracle still takes every write, CDC copies the changes into PostgreSQL, and read traffic like statements, dashboards and search moves to the new database. On a typical core system, reads are 70–90% of the load. Moving them alone can noticeably cut CPU on the old hardware and, sometimes, the license bill.
This is where eventual consistency starts to matter. A transfer goes through, the customer refreshes the app 80 ms later and may still see the old number. For a monthly statement page, who cares? On an overdraft check, everybody does, so those reads stay on the primary until the very end.
Dual Writes Look Easy. They Aren't.
The obvious first idea is to have the application write into both databases at once, and a lot of teams go there. Then they learn that two commits in two different engines aren't one transaction. Oracle accepts the row, PostgreSQL times out, and now the systems disagree without anyone being told. Two-phase commit fixes this on paper. In production, across Oracle and PostgreSQL? Not many architects would put their name on that.
There's a cleaner trick when two destinations really can't be avoided: the transactional outbox. The app writes the event into an outbox table in the same local transaction as the business data, and Debezium picks it up from the log later.
Shadow Writes: Believe One, Check the Other
The safer route is the shadow approach. Legacy stays the source of truth. The new database receives the same writes asynchronously, and you compare its results but never show them to customers. GitHub's Scientist library made this "run both, trust one" idea popular for code, and data works the same way.
Reconciliation jobs usually look at:
-
row counts per partition, refreshed every few minutes
-
hashes of the columns that matter most, like balance, status and currency
-
daily totals per account type
-
full-row diffs on a sample from the busiest tables
Zero mismatches during a quiet week proves very little. Zero through a full month-end close, with every batch job and manual adjustment, proves a lot.
Keeping a Way Back
Why replicate from the new system back into the old one? Rollback, basically. After cutover, writes go to PostgreSQL or CockroachDB, and reverse replication keeps Oracle current. A bug shows up on day three? Traffic switches back in minutes. Without that path, it's a weekend-long restore and an awkward call with the regulator.
Getting it right is hard, though. Two problems come up on almost every project:
-
Replication loops. A change copied from A to B must not get captured again and shipped back to A. GoldenGate filters out transactions made by its own replication user, while Debezium setups usually tag the origin or filter on transaction metadata.
-
Write conflicts. If two sides write to the same row, one change gets thrown away. Last-writer-wins by timestamp is fine for notification settings. For an account balance it's a real problem, which is why most teams keep only one side writable at a time and treat the reverse flow as a safety net.
Schema Evolution makes things messier. A new NOT NULL column on the target side, for example, will break reverse replication if the legacy table doesn't get the same default. The usual way around it is expand-and-contract. The new column is added and backfilled, readers move over, and the old column stays in place for weeks before anyone drops it.
Where the Data Lands
The target shapes the whole plan just as much as the source does:
-
PostgreSQL, managed or not, is where most Oracle workloads end up. Orafce takes care of quite a few Oracle functions that would otherwise need rewriting.
-
With AWS Aurora PostgreSQL, the data sits in six copies spread over three Availability Zones. A failover takes under 30 seconds in most cases, and for most banks that's enough.
-
CockroachDB is the pick when a bank runs in several regions and needs Distributed ACID Transactions across all of them. Serializable isolation is the default there. Stored procedures are where it gets harder, and teams often move a lot of PL/SQL logic into the application.
-
YugabyteDB also speaks PostgreSQL, and teams that want active-active usually end up testing its xCluster replication.
Conversion Gotchas
Automated converters get most of the DDL right. The edge cases are what quietly break data:
-
An Oracle DATE column holds the time as well, down to the second. After a careless mapping to a PostgreSQL date, only the day survives.
-
Empty strings are NULL in Oracle but not in PostgreSQL. So the same IS NULL filter, or the same unique constraint, can return different results on each side.
-
NUMBER with no precision becomes NUMERIC by default. It works, but on hot keys BIGINT would do the job much faster.
-
SQL Server DATETIME rounds to about 3 ms, PostgreSQL timestamp stores microseconds, and checksums won't match until values are normalized.
Cutover Day
Ideally, cutover day is dull. That's the whole point. Large integrators that handle mainframe and Oracle exits, DXC Technology, Kyndryl and Infosys among them, usually tie the switch to measurable criteria instead of a date picked in a steering committee:
-
sync lag under a fixed threshold for a sustained stretch, say below 100 ms for 72 hours
-
no reconciliation mismatches through at least one month-end close
-
writes moved with a feature flag, tenant by tenant or region by region, starting at 1–5% of traffic
-
a write freeze of a few seconds, just enough to drain the CDC queue and confirm the final SCN or LSN on both sides
-
reverse replication running before the first production write reaches the new primary
So, is a three-hour Friday night outage worth the risk? Hardly. A 15-second write pause hidden behind client retries is a much easier sell.
What It Comes Down To
There's no single trick behind a zero-downtime database migration. CDC moves the data. Strangler-style slicing keeps the scope manageable. Shadow writes prove the new system is correct, and reverse replication leaves the door open. Skip one, and the risk simply moves elsewhere. Teams that get through these projects without incidents tend to share one habit: they treat the old database as fully live production right up until the day it's switched off.
