Architectural Patterns for Zero-Downtime Legacy Migrations

The Problem

Many enterprises have a shared database at the centre of their estate: one large relational database that dozens of applications, reports and batch jobs read from and write to. Moving it to a new platform, whether a cloud-managed service, a different engine or a split into service-owned stores, is often the hardest part of a modernisation programme.

The traditional approach is a maintenance window: stop the applications, dump the data, restore it on the target, validate, restart. For a few gigabytes that’s fine. For multi-terabyte databases the copy alone can take hours or days, and for businesses that trade 24/7 a window that long is either unacceptable or impossible to get approved.

The goal is to move live, constantly changing data to a new database with no lost or duplicated transactions, and with a write interruption measured in seconds rather than hours. Honestly, “zero downtime” for a database almost always means near-zero write downtime. Even AWS’s managed RDS Blue/Green Deployments say switchover downtime is usually under a minute. Plan for a short, rehearsed write pause rather than promising none.

The Architectural Solution

The standard pattern is snapshot plus log-based Change Data Capture (CDC), followed by a controlled cutover.

1. Snapshot and stream

A CDC connector reads the source database’s transaction log: the PostgreSQL write-ahead log (WAL) through logical decoding, the MySQL binlog, or Oracle redo logs through LogMiner. It takes a consistent initial snapshot and then streams every subsequent INSERT, UPDATE and DELETE in commit order. Debezium is the most widely used open-source implementation. It runs as Kafka Connect source connectors and writes change events to Kafka topics, and a sink (for example Debezium’s JDBC sink connector, or a custom consumer) applies them to the target.

Log-based CDC is preferred over trigger-based capture because it adds no extra writes to the source transactions and captures deletes reliably. It isn’t free, though: see the WAL retention risk below.

2. Validate continuously

While the target catches up and then tracks the source, run reconciliation: row counts per table, plus checksums over key ranges, compared between source and target. Managed tools such as AWS DMS data validation do this for you. With Debezium you build it. Also run the new application stack against the target in shadow mode (read-only, comparing responses) to catch semantic differences that row checksums can’t see.

3. Keep a single writer

At any moment, each table has exactly one system of record. CDC replicates from it to the other side. Avoid having the application write to both databases at once (“dual writes”) alongside CDC. Two independent writes can’t be made atomic, and failures between them cause silent divergence (see Confluent’s explanation of the dual-write problem). Application-level dual writes can work as a standalone strategy, as in Stripe’s online migrations pattern, but only with backfill and reconciliation and without CDC on the same tables.

4. Cut over, with a way back

1. Confirm replication lag is near zero and reconciliation is clean.
2. Pause writes on the source (application read-only mode or connection drain).
3. Wait until the connector has delivered every change up to the source’s current log position.
4. Advance sequences / identity columns on the target. PostgreSQL logical replication does not replicate sequence state.
5. Switch routing (connection strings, DNS with a low TTL, or the strangler routing layer) to the target and resume writes.
6. Start reverse CDC from target to source, so that fallback is a controlled cutover in the other direction rather than a restore from backup.

Architecture Diagram

Core Implementation

Below is a Debezium PostgreSQL source connector configuration, submitted to the Kafka Connect REST API (POST /connectors). Property names follow current Debezium releases. Older tutorials use database.server.name and database.history.*, which were replaced in Debezium 2.0. Schema-history settings are not used by the PostgreSQL connector.

{
  "name": "legacy-inventory-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "onprem-db.enterprise.internal",
    "database.port": "5432",
    "database.user": "debezium_cdc",
    "database.password": "${file:/opt/kafka/secrets/legacy-db.properties:password}",
    "database.dbname": "legacy_monolith",
    "topic.prefix": "legacy",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_inventory",
    "publication.name": "dbz_inventory",
    "publication.autocreate.mode": "filtered",
    "table.include.list": "public.products,public.stock_levels",
    "snapshot.mode": "initial",
    "decimal.handling.mode": "precise",
    "heartbeat.interval.ms": "10000",
    "tombstones.on.delete": "true"
  }
}

What each non-obvious setting does:

• database.password uses Kafka’s file config provider (enabled in the Connect worker with config.providers=file), so the secret isn’t stored in the connector config or returned by the REST API.
• topic.prefix namespaces topics: changes land in legacy.public.products and legacy.public.stock_levels.
• plugin.name: pgoutput uses PostgreSQL’s built-in logical decoding plugin (PostgreSQL 10+), so no server extension is needed. The source must run with wal_level = logical, which requires a restart. Schedule that early.
• publication.autocreate.mode: filtered creates a publication for only the included tables, which the Debezium PostgreSQL documentation recommends with pgoutput.
• table.include.list is a comma-separated list of regular expressions. Don’t put spaces after the commas.
• decimal.handling.mode: precise keeps NUMERIC values exact. The double option can silently lose precision on financial data.
• heartbeat.interval.ms lets the connector confirm its position regularly even when the captured tables are quiet, which stops WAL from piling up on a busy database (see below).

Tables need a primary key (or an explicit REPLICA IDENTITY) for updates and deletes to be keyed correctly downstream.

Edge Cases & Limitations

1. WAL retention can fill the source disk. A replication slot makes PostgreSQL keep all WAL the consumer hasn’t confirmed. If the connector or Kafka is down for hours, WAL grows without limit by default and can exhaust disk on the production primary. Monitor slot lag in pg_replication_slots, and consider setting max_slot_wal_keep_size (PostgreSQL 13+). The trade-off is that exceeding it invalidates the slot, which forces a re-snapshot.
2. Source load. Log-based capture is light on the transaction path, but the initial snapshot is a long scan that competes for I/O. For very large tables, use Debezium’s incremental (chunked) snapshots, which can run alongside streaming, and schedule them for off-peak hours.
3. DDL is not replicated. PostgreSQL logical replication does not carry schema changes. Freeze schema changes during the migration window, or apply additive changes to the target first, using the expand/contract approach of parallel change.
4. Heterogeneous engines. Oracle to PostgreSQL is not just a copy. Oracle DATE includes a time component, Oracle treats empty strings as NULL, and NUMBER without precision needs an explicit target type. Settle the type mapping and test it with production-like data before streaming. Data that is quietly wrong is harder to fix than a pipeline that fails loudly.
5. Network partitions between data centres. If the link to the cloud drops, lag grows. The connector resumes from its last confirmed position, but only if the WAL is still retained (see point 1). Size retention for your longest plausible outage.
6. Cutover rehearsal. The cutover runbook (pause writes, drain, sync sequences, switch, reverse CDC) should be rehearsed in a production-like environment and timed. The first time it runs should not be in production.
7. When not to use CDC: for small databases where a short, scheduled dump-and-restore window is acceptable. CDC adds Kafka, connectors, monitoring and on-call load, and that overhead is only worth it when the downtime would cost more.

Key Strategic Takeaways

• Log-based CDC over triggers or batch copies. Read the transaction log to capture every change in commit order, without adding writes to source transactions.
• One writer per table at all times. Replicate from the system of record. Never combine application dual writes with CDC on the same data.
• Prove convergence before cutover. Continuous row-count and checksum reconciliation, plus shadow reads, turn cutover from a leap of faith into a checklist.
• Plan for a short write pause, not zero. Pause, drain, fix sequences and switch. Measure the pause in rehearsal and tell the business the real number.
• Keep a way back. Reverse CDC from target to source keeps fallback possible without data loss until you formally decommission the legacy database.

---

Recommended Industry Reading & References

1. Debezium Documentation: PostgreSQL Connector. Configuration reference, snapshot modes, publications and WAL disk-space guidance.
2. Debezium Documentation: Architecture. How Debezium runs on Kafka Connect, as a server or embedded.
3. PostgreSQL Documentation: Logical Replication Restrictions. What isn’t replicated (DDL, sequences, large objects).
4. Stripe Engineering: Online migrations at scale. The four-phase dual-write, backfill and cutover approach for application-level migrations.
5. Confluent: Solving the Dual-Write Problem. Why independent writes to two systems diverge, and the outbox/CDC alternatives.
6. AWS Documentation: Amazon RDS Blue/Green Deployments. A managed take on staged database cutover, with documented switchover behaviour.
7. Martin Fowler: Strangler Fig Application. The application-level pattern that database migration usually supports.