Engineering reference project

Migration you can actually verify

Gated migration to managed cloud with row reconciliation as the acceptance test, and log-based replication keeping two engines in agreement through a durable event backbone.

Discuss a similar build
Fortis engineering reference project. Built and operated by Fortis as a working reference implementation, not delivered under contract to a client. No customer, program or employer material appears here. Figures describe measured behaviour of this build only.
Type
Engineering reference build
Source engines
PostgreSQL and SQL Server
Pattern
Log-based capture to an event backbone
Acceptance test
Row counts reconciled

Executive overview

Two related pieces of engineering. Moving a database onto managed cloud and proving nothing was lost, then building the replication layer that keeps two different database engines in agreement through an event backbone rather than a batch window.

The migration half covers a managed PostgreSQL instance moved to a target platform under a gated sequence: establish the target version, capture evidence, dump while the source is live, record row counts before anything is deleted, verify the archive, restore, reconcile, and only then tear down in dependency order.

The replication half runs change data capture from PostgreSQL logical decoding and from SQL Server change tables into a single replicated event backbone, with schema compatibility enforced at publish time so a breaking change is rejected before a consumer sees it.

The problem

Modernization fails in two predictable places, and neither is the copy itself.

Problem

  • Verification. A migration that appears to succeed and quietly loses rows is worse than one that fails outright, because the failure surfaces weeks later in a report nobody can reconcile.
  • Keeping systems in agreement afterwards. Once a workload spans more than one engine, a nightly batch job means every downstream consumer sees stale data for most of the day, and a point-to-point integration per consumer becomes unmaintainable at the third consumer.

The requirement was a migration path with a verifiable acceptance test, and a replication architecture where adding a consumer costs nothing and a schema change cannot silently break one.

Engineering approach

For the migration, every step that could destroy information was placed behind a gate, and the acceptance test was defined before the work began: row counts per table taken from the source before deletion, reconciled against the target after restore.

For replication, log-based capture rather than query-based polling. Reading the write-ahead log or the change tables means the source database carries no additional query load, and every change is captured including deletes, which a polling query cannot see.

  • Change events are published once to a durable log and consumed independently, so a new consumer is a subscription rather than an integration project.
  • Three brokers at replication factor 3 with minimum in-sync replicas 2, so a single broker loss costs neither data nor availability.
  • Schema compatibility is checked when a producer publishes, so a breaking change fails at deploy time rather than being discovered by a consumer at runtime.
  • Both engines feed the same backbone, which is what makes the pattern heterogeneous rather than a PostgreSQL-only trick.
Architecture

Architecture

The replication topology first, then the migration sequence, because the two answer different questions.

Enlarge Change data capture topology across two source engines into a replicated event backbone
Figure 1Change data capture topology. Two engines, two capture mechanisms, one backbone. The operational consequence worth naming: a stalled connector holds a replication slot open and the source database retains write-ahead log until the disk fills. Slot monitoring is a requirement of this design, not an enhancement to it.
Enlarge Gated migration and validation sequence
Figure 2Migration and validation sequence. The three highlighted steps are gates. Each one exists because skipping it has a specific cost that is difficult to recover from.

Key technologies

Source engines
  • PostgreSQL, logical decoding
  • SQL Server, change tables
  • Write-ahead log based capture
Managed cloud
  • RDS for PostgreSQL
  • Final snapshot before teardown
  • Dependency-ordered decommission
Replication
  • Debezium capture connectors
  • Apache Kafka, KRaft mode
  • Independent consumer groups
Contract enforcement
  • Apicurio Registry
  • Compatibility checked at publish
  • Avro with schema references
Migration tooling
  • Custom-format compressed dump
  • Archive table-of-contents verification
  • Row count reconciliation
Automation
  • Ansible configuration management
  • Pipeline-gated deployment
  • Version-pinned components

Security and operational considerations

  • Capture accounts are scoped to capture. A replication account needs replication rights and nothing else. It is not a general-purpose application account.
  • Replication slots are a disk risk. If a connector stops and its slot remains, the source retains write-ahead log indefinitely. Monitoring slot lag is an operational control, and its absence is a capacity incident waiting for a quiet weekend.
  • The agent must be running for change tables to populate. On SQL Server, a service restart can leave the agent stopped, which means capture silently stops with no error anywhere. A restart-and-verify step exists because that failure is invisible by default.
  • Restore is forward-only across major versions. Establish the target version before dumping. Discovering a version mismatch after the source is gone is unrecoverable without standing the source back up.
  • Restart-sensitive statistics counters lie. A freshly restarted instance reports zero live rows per table, which looks exactly like an empty database. Row verification uses an actual count.
  • A clean console is not a clean bill. Unreleased addresses, orphaned volumes and idle serverless collections keep billing after a teardown looks complete. The invoice is the confirmation, not the resource list.
Decisions

The alternatives that were rejected, and what each one would have cost.

Judgement

Important engineering decisions

Log-based capture rather than query-based polling

Alternative rejected: scheduled batch extraction on a timestamp column

Polling puts read load on the source, misses deletes entirely, and depends on a reliable modified-timestamp that many schemas do not have. Reading the log captures every change including deletes and adds no query load. The cost is the replication slot, which is a real operational obligation and is documented as one.

One durable log, many independent consumers

Alternative rejected: point-to-point integration per consumer

Point-to-point works until the third consumer, then every schema change becomes a coordinated release across teams. Publishing once to a durable log means a new consumer is a subscription, and replay is available because the log retains history rather than discarding a message once delivered.

Schema compatibility enforced at publish

Alternative rejected: documenting the schema and trusting producers

A breaking schema change that reaches a consumer at runtime is discovered by an outage. Checking compatibility when the producer publishes moves that failure to deploy time, where it is a rejected merge request instead of an incident.

Replication factor 3 with minimum in-sync replicas 2

Alternative rejected: replication factor 1, or factor 3 with minimum in-sync replicas 1

Factor 1 means a broker loss is data loss. Factor 3 with a minimum of 1 accepts writes that only one broker has, which reintroduces the same exposure under failure. Requiring two in-sync replicas means an acknowledged write survives a broker loss, at the cost of rejecting writes when two brokers are down, which is the correct trade for a system of record.

Row counts as the acceptance test

Alternative rejected: trusting a successful restore exit code

A restore can succeed and be incomplete. Counts taken from the source before deletion and reconciled after restore are the only evidence that survives the teardown, which is why they are captured before anything is destroyed rather than after.

Demonstrated capabilities

Measured on this build. Nothing here is a client outcome or a contracted result.

  • Heterogeneous change data capture running concurrently from PostgreSQL logical decoding and SQL Server change tables into one replicated event backbone.
  • Schema compatibility enforcement rejecting a breaking change at publish, verified by attempting one.
  • Three-broker cluster at replication factor 3 with minimum in-sync replicas 2, surviving a broker loss without data loss or write rejection.
  • Managed cloud database migrated to a target platform with row counts reconciled exactly against pre-teardown figures.
  • Gated migration procedure documented as a runbook with real commands and no placeholders, including the two failure modes that produced the gates.
  • Dependency-ordered decommission with the resource classes that continue billing after teardown inventoried explicitly.

What this demonstrates

This project is the reference implementation behind two Fortis service lines.

Data Platform Engineering

Legacy database migration and the platform layer that makes the result durable, with a verifiable acceptance test rather than an assurance.

Distributed Systems

Event-driven replication with a durable log and replay, the pattern that holds integrity when a consumer or a broker is unavailable rather than assuming both are up.

Cloud Modernization

Migration into and out of managed cloud database services, with cost exposure at teardown treated as an engineering concern.

Operational reliability

The failure modes that are silent by default: stopped capture agents, retained write-ahead log, statistics counters that report an empty database, and resources that bill after they look gone.

All projects Start a conversation