How a risk-sequenced PostgreSQL logical replication migration, a purpose-built change-capture table, and a monitoring script keep source and target in sync as the project moves toward cutover.

Key Takeaways

• Production data is moving from Patroni-managed PostgreSQL clusters to clusters managed by EDB Failover Manager (EFM), using PostgreSQL logical replication and sequenced by risk: the parent table first, then the child tables that reference it through foreign keys.
• We identified a silent data-loss race specific to a child table: an UPDATE that replicates before its row’s own backfill INSERT has landed on the target is dropped by the apply worker and never retried. The fix: a trigger-based capture table plus a one-time, batched reconciliation pass after backfill.
• We replaced exact SELECT COUNT(*) with pg_class.reltuples in the recurring sanity-check monitor, keeping every check cheap enough to run every 10 to 15 minutes without competing with the backfill’s own I/O. MIN/MAX(“ID”) stays exact, since an index-backed lookup costs nothing regardless of table size.
• We set drift tolerances as a percentage of the source table with a small absolute floor, after testing showed that a 500-row floor was generous enough to hide a genuine 50% drop in row count on a modest-sized table.

The Starting Point

The client, a payments platform, needs to move off a Patroni-managed PostgreSQL cluster onto EFM-managed clusters while avoiding the two outcomes it fears most: extended downtime and a silently incomplete cutover. The approved runbook uses PostgreSQL logical replication rather than a dump-and-restore cutover, because it keeps the target continuously caught up with the source while the switchover is prepared.

At the time of writing, the migration is in its first phase. Tables are being moved in order of risk: the parent table is being backfilled to the target first, with the child tables to follow.

Designing Around a Silent Data-Loss Race

Most tables were straightforward candidates for standard logical replication: create a publication, create a subscription with (copy=false), backfill historical data, then let the subscription keep the two sides in sync. One of the child tables was an exception, for a reason worth spelling out because it isn’t obvious from the PostgreSQL documentation alone.

During a large historical backfill, a row can be updated on the source, and that UPDATE can reach the target before the backfill’s COPY has inserted the row there. When the subscription’s apply worker receives that UPDATE and finds no matching row, it logs a warning and moves on. It does not retry. The backfill’s later INSERT carries the row’s value as of the backfill’s SELECT snapshot. That value is usually correct, but any UPDATE committed on the source after the snapshot can still arrive first and be lost permanently.

The fix was a purpose-built capture mechanism rather than a workaround bolted onto replication itself: a trigger on the affected child table that upserts a row into an audit table whenever a tracked column actually changes.

The function and trigger that populate audit_table, created on the source:

CREATE OR REPLACE FUNCTION track_updates() RETURNS trigger AS $$ 
BEGIN
 INSERT INTO "audit_table"("ID", "Information", "child_table_Timestamp")
 VALUES (NEW."ID", to_jsonb(NEW), NEW."Timestamp")
 ON CONFLICT ("ID") DO UPDATE SET
  "Information" = EXCLUDED."Information",
  "child_table_Timestamp" = EXCLUDED."child_table_Timestamp",
  "update_at" = now();
 RETURN NEW; 
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_capture_child_table_update
AFTER UPDATE ON “child_table”
FOR EACH ROW
WHEN (OLD.”Information” IS DISTINCT FROM NEW.”Information”
OR OLD.”Timestamp” IS DISTINCT FROM NEW.”Timestamp”)
EXECUTE FUNCTION track_updates();

The audit table itself replicates normally, since it is an insert-only audit trail. Once the child_table backfill is confirmed complete, a single reconciliation pass replays audit_table against child_table in batches, correcting only the rows whose latest captured value doesn’t already match the target.

The reconciliation statement, executed on the target one time window at a time:

UPDATE "child_table" ct
SET "Information" = aud."Information"->'Information',
"Timestamp" = aud."child_table_Timestamp"
FROM "audit_table" aud
WHERE ct."ID" = aud."ID"
AND ct."Timestamp" IS DISTINCT FROM aud."child_table_Timestamp"
AND aud."update_at" BETWEEN :batch_start_ts AND :batch_end_ts;

Monitoring PostgreSQL Logical Replication Without Slowing It Down

Any migration like this needs a way to check, continuously, that both the old system and the new one agree on how much data they hold. The obvious way to do that is to just count every single row in both databases and compare the totals. The problem is that “just count everything” (a full SELECT COUNT(*)) is expensive when a table has billions of rows. Running it every 10 to 15 minutes for the months a migration lasts would compete for the same disk I/O the backfill needs, slowing everything down.

So instead of counting every row, the monitor uses a shortcut: it asks Postgres for its internal row estimate (pg_class.reltuples), which the database already maintains for its own purposes. That estimate is nearly free to check, no matter how big the table gets, because Postgres reads a stored statistic instead of scanning the table.

The lowest and highest ID values (MIN and MAX on the indexed “ID” column), on the other hand, can be checked exactly at no extra cost. The index works like a table of contents that lets you jump straight to the first and last entries without reading the whole book.

Why the estimate needs some wiggle room

That built-in estimate only refreshes periodically, not the instant something changes. So two databases can be perfectly in sync and still show slightly different counts, just because one side’s estimate hasn’t refreshed yet. To avoid false alarms, the check allows a small tolerance instead of demanding an exact match. The tolerance is sized as a percentage of the source table, with a minimum floor so very small tables aren’t judged too harshly. Testing showed how easily that floor can be set wrong. A 500-row floor, which looked harmless, was generous enough to hide a genuine 50% drop in row count on a modest-sized table. That floor is now kept small and deliberate: just large enough to absorb harmless noise, never so large that it could hide a real, meaningful gap in the data.

A quick check isn’t the same as proof

The estimate-based check cheaply catches large, obvious problems. Its speed and its imprecision come from the same source: it is an estimate. It was never designed to confirm that a specific batch that had just been copied landed correctly. For that, a second, exact check runs alongside it: after each batch is copied, an exact COUNT(*) limited to that batch runs on both sides to confirm the numbers are identical. No estimate or tolerance is needed. When the same slice is counted on both sides, the two numbers must match.

Two different processes, moving at two different speeds

This migration doesn’t move data over in just one way. New writes on the source reach the target in near real time through the logical replication subscription. Meanwhile, the backfill copies historical data separately, in large batches, much more slowly. Both processes are writing into the same new database at the same time, but at very different speeds.

That’s why asking “what’s the newest record on the target?” is the wrong question. The subscription makes that number look caught up almost immediately, even while the backfill is still far from finished. A single measure blends two processes moving at different speeds, so it can’t show which one is behind.

The check instead treats the two processes as two separate questions. There is a clear dividing line: a specific point in the data that separates everything the subscription is responsible for from everything the backfill is responsible for. By checking each side of that line on its own, the monitor can say precisely which process a discrepancy belongs to, and catch a real problem the moment it happens, rather than blending two signals into one.