Lag is not drift: my replication monitor was green while the replica was wrong

I have a home-grown replication daemon that copies a handful of Postgres databases to a standby in a cloud region, so that if the house burns down the services come back somewhere else. It has a healthcheck. The healthcheck is careful — it doesn't just assert the process is alive, because a live process with a wedged SSH tunnel looks perfectly healthy to systemd. It asserts real per-table progress: key lag for append-only tables, refresh cadence for the ones that get fully recopied, recorded errors and row counts for the change-tracked ones.

It was green. It had been green for months. The standby was wrong in at least six different ways.

The thing that actually paged

The alert was a UnicodeEncodeError, which is not what you want to see out of a replication daemon:

FAIL  mem:public.memories: last push recorded UnicodeEncodeError('latin-1', ...)
FAIL  mem:memex.messages:  last push recorded UnicodeEncodeError('latin-1', ...)
FAIL  mem:memex.embeddings: ForeignKeyViolation(... Key (oid)=(120951) is not present ...)

That one was easy once I looked at the right thing. The daemon deliberately does not hardcode a client encoding — it asks each server what the database is and agrees with it:

cur.execute("SELECT pg_encoding_to_char(encoding) FROM pg_database "
            "WHERE datname = current_database()")
enc = cur.fetchone()[0]
c.set_client_encoding("SQL_ASCII" if enc == "SQL_ASCII" else "UTF8")

That is correct per connection and it was the whole problem. The night before, I'd converted the source database from SQL_ASCII to UTF8. The standby copy was still SQL_ASCII. So the local end negotiated UTF8 and handed back real Python strings with em dashes and emoji in them, the remote end negotiated SQL_ASCII — which this code maps to the latin-1 codec so that bytes round-trip — and latin-1 cannot encode anything above U+00FF. Every row with a character outside Latin-1 was unwritable. The foreign key violation was downstream noise: the child rows couldn't land because their parents couldn't.

The fix is boring. Rebuild the standby database as UTF8. Do it on the standby so you're not pushing a gigabyte through a tunnel:

PGCLIENTENCODING=SQL_ASCII pg_dump -Fp mydb > /tmp/mydb.sql
iconv -f UTF-8 -t UTF-8 /tmp/mydb.sql > /dev/null && echo "already valid UTF-8"

That second line is the one worth stealing. SQL_ASCII doesn't mean "ASCII", it means "no encoding, just bytes" — and if everything that ever wrote to it was writing UTF-8, the dump is already a valid UTF-8 file and the conversion is a rename plus a restore. If it isn't valid, you've just found out before you dropped anything.

The invariant I didn't have written down anywhere: both ends of a replicated pair must be converted in the same change. Per-connection correctness isn't enough when the two connections have to agree with each other.

The part that bothered me more

Five tables failing loudly is a good day. What I wanted to know was whether the rest of the replica was fine, so I hashed every row on both sides and joined on the key.

Twelve percent of one table's rows held different text.

None of it had ever tripped anything, and once I looked at the causes it was obvious why. Every case had the same shape: a writer edited history in place without touching the signal the replication watches.

A change-tracked table only ships a row when its change column advances. A scrubbing pass had rewritten ~2,200 rows' content and left updated_at alone. Invisible.
An append-only table is paged forward by primary key and never looks back at a key it has already passed. A backfill populated a column across 43,450 historical rows. Invisible.
Nineteen rows had never replicated at all — inserted with a timestamp older than the watermark at the time, so the cursor had already stepped past them.

The daemon isn't buggy in any of those cases. It is behaving exactly as designed. The rows are simply outside what it can perceive, which is precisely why the assertion cannot be made from the daemon's own bookkeeping.

The worst instance: three of the changed rows were redactions. A pass had replaced dead credentials with a placeholder on the primary. Those rows never replicated, so the replica still had the originals sitting in cleartext. A redaction that doesn't replicate isn't a redaction. That one gets a trigger:

CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  IF NEW.content IS DISTINCT FROM OLD.content
     AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
    NEW.updated_at := to_char(now() AT TIME ZONE 'UTC',
                              'YYYY-MM-DD"T"HH24:MI:SS.US"Z"');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The second condition matters: if the writer set the timestamp itself, leave it alone. The trigger is a floor, not a policy.

Mojibake, in the other direction

Two more databases turned out to be SQL_ASCII on the source and UTF8 on the standby — the same mismatch, mirrored. That direction doesn't raise anything. Bytes come out through the latin-1 remap as one Python character per byte, go into a UTF8 database, and get encoded again:

PRI: 'Verify sudo access — was this expected'
STD: 'Verify sudo access â\x80\x94 was this expected'

e2 80 94 became c3 a2 c2 80 c2 94. Every non-ASCII character in both databases, quietly double-encoded, for as long as the pair had existed. The write succeeds, so there is no error anywhere to find. You only see it if you compare the bytes.

Writing the check

So: sample rows per table, compare whole-row hashes across the two sides. Two things made the naive version useless.

Some columns are supposed to differ. One table has row_updated_at maintained by a trigger on each side, so the standby records when the row arrived, not when the source wrote it. Hashing the whole row flagged 220 of 300 sampled rows as differing on nothing at all. Resolve the column list from information_schema and subtract the ones the replica legitimately stamps itself.

A mismatch is not drift. Some of these tables get fully recopied every 15 minutes, and some of their rows — a weather feed, a "latest alerts" blob — are rewritten continuously upstream. Those rows differ at literally every instant you look. There is no quiet moment. A "does it match right now" test pages every night forever.

What separates the two cases isn't whether the row differs, it's whether the standby moved:

frozen = [k for k in still_differing
          if remote_now.get(k) == previous_run_remote.get(k)]

Carry each suspect's remote hash forward in the verdict file. Next run, a row that still differs but whose standby copy has advanced is lag — replication is flowing, you just caught it mid-stride. A row still holding the byte-identical hash it held a full run ago, while the source has moved on, is frozen. That one is drift, and that one fails.

Lag advances. Drift is stuck. You cannot tell them apart from one sample, which is the same lesson I keep relearning about anything that flaps.

Where it runs

The comparison takes about 25 seconds across thirty tables and a tunnel to another region, and my monitoring system gives every probe a 25-second transport budget. The tempting move is to widen the budget. Don't — that blunts every probe on the fleet to accommodate one heavy assertion.

Instead the audit runs from cron and writes a verdict file, and the probe reads the file in 40 milliseconds and asserts two things: that the verdict is green, and that it is fresh. The freshness half is the important half. Without it, the cron dying looks exactly like the cron passing.

What I'd do differently

Assert the artifact, not the machinery. I already believed that — it's why the healthcheck asserts replication progress instead of process liveness. I just hadn't followed it one step further. Lag, cadence and error counts are still machinery. They are what the daemon says about itself. The artifact is the bytes on the other end, and the only way to check the bytes is to read the bytes.

Six months of green is not evidence. It's the absence of a question.