pg_stat_subscription_stats: Catching Logical Replication Errors Before Lag Alerts
The reporting subscriber was twenty-six hours behind, but the publisher swore replication was fine — and it was, because it was happily streaming WAL nobody could apply. One duplicate key had killed the apply worker on Tuesday, and the only counter that knew was pg_stat_subscription_stats.
The first symptom was a finance complaint: the reporting replica’s dashboards looked "about a day stale." They were twenty-six hours stale. The confusing part was that every publisher-side check came back green — pg_stat_replication showed the subscription streaming, state streaming, no write lag worth the name. And that was all true: the publisher was diligently shipping changes into a subscription whose apply worker had died on Tuesday at 14:03 with ERROR: duplicate key value violates unique constraint, SQLSTATE 23505, retried every few seconds since, and failed the same way every time. Logical replication was up. Logical replication was also completely stopped. Both facts were in the catalogs; we were just looking at the wrong one.
The right one is pg_stat_subscription_stats, added in PostgreSQL 15, and this post is the monitoring design we built around it: what it counts, why one bad row stops everything, how to skip the poison transaction, and the alert that would have paged us on Tuesday at 14:04.
What do the subscriber-side statistics actually show?
They show error counts per subscription, which is the signal the publisher cannot have. pg_stat_subscription_stats carries one row per subscription with apply_error_count, sync_error_count, and a stats_reset timestamp — the counters are cumulative since the reset, so the alertable event is a delta, not an absolute value. Pair it with the older pg_stat_subscription view, which shows the live workers: one row per worker with its PID, the LSNs it has received and applied, and relid distinguishing table-sync workers from the main apply worker, whose relid is NULL. The incident pattern reads off these two views in seconds: apply worker present and busy, apply_error_count greater than zero and climbing with every retry, applied LSN frozen while received LSN advances. That last pair is the tell — the subscriber is receiving fine and applying nothing, which means the failure is data, not transport, and no amount of network debugging will find it.
SELECT s.subname,
st.apply_error_count,
st.sync_error_count,
st.stats_reset,
s.pid,
s.relid, -- NULL means the main apply worker
s.received_lsn,
s.latest_end_lsn,
now() - s.latest_end_time AS idle_for
FROM pg_stat_subscription s
JOIN pg_stat_subscription_stats st USING (subid);Two housekeeping notes from running this in anger. The stats view only exists on PostgreSQL 15 and later — on 14 and older the same diagnosis lives only in the subscriber log, which is one more argument for upgrading subscription targets early. And stats_reset moves when statistics are reset cluster-wide, so trend the counters in your monitoring rather than trusting a snapshot; a subscription that reset yesterday and already shows errors is a subscription in trouble right now.
Why does one duplicate key stop an entire subscription?
Because the apply worker is single-threaded and strict: it replays transactions in commit order, and when one fails, it errors out, gets relaunched by the subscription machinery, replays the same transaction, and fails the same way — forever, until a human intervenes. There is no skip-on-error mode, and that strictness is correct: silently dropping a conflicting transaction would leave the subscriber diverged in a way nobody could trust. The usual suspects for the conflict, in our order of frequency: someone wrote directly to the subscriber (a manual "quick fix" insert that later collides with the replicated insert), a NOT VALID constraint or trigger on the subscriber that the publisher does not have, and sequence drift after any manual data surgery. Our Tuesday case was the first — an engineer had backfilled one row by hand on the subscriber to unblock a report, and the publisher’s own insert of that primary key arrived twenty minutes later. The full menagerie of conflict shapes is catalogued in the logical replication conflicts notes; the point here is that whatever the shape, the symptom is always the same frozen apply LSN with a climbing error counter.
The blast radius extends back to the publisher, which is why "the subscriber is broken" is never just the subscriber’s problem. A stalled subscriber holds its replication slot at the last applied position, and the publisher retains WAL for that slot — twenty-six hours of retained WAL in our case, growing at our full write rate. The disk-side mechanics of that are exactly the replication-slot retention story, and the publisher-side watch items from the replication monitoring guide apply unchanged; the fix has to happen before the retained WAL becomes the second incident.
How do you skip the poison transaction without resnapshotting?
You find the failing transaction’s finish LSN in the subscriber log, tell the subscription to skip it, and let apply resume from the next transaction — PostgreSQL 15 added ALTER SUBSCRIPTION … SKIP (lsn = …) for exactly this, so the days of pg_replication_origin surgery are mostly behind us. The log line you want is the CONTEXT of the apply error, which names the replication origin, the relation, and finished at followed by the LSN. Feed that LSN to the skip, and the apply worker will refuse to replay that one transaction and continue with everything after it:
-- LSN comes from the subscriber log CONTEXT line:
-- ... in transaction 93177 finished at 0/14C0378
ALTER SUBSCRIPTION reporting_sub SKIP (lsn = '0/14C0378');
-- Watch apply resume: latest_end_lsn should start moving
SELECT subname, latest_end_lsn, now() - latest_end_time AS idle_for
FROM pg_stat_subscription
WHERE subname = 'reporting_sub';Skipping is a divergence decision, so treat it like one: write down what was skipped and repair the data deliberately. In our case the skipped transaction was the publisher’s insert of the row the engineer had already created by hand — the subscriber already had the row, the skip made it consistent, and the repair was a Slack message, not a backfill. When the skipped transaction carries data the subscriber genuinely needs, apply it by hand after resolution, and then fix the process that made the subscriber writable in the first place. If the conflicts are structural rather than accidental — a sync phase that errors on every table, say — resync or resnapshot that table instead of skipping your way through a thousand transactions, and mind the decoding-side costs described in the logical decoding performance notes while the catch-up runs.
What does the alert look like so Tuesday never repeats?
Two alerts, one for errors and one for staleness, because they catch different failures. The error alert is a delta on pg_stat_subscription_stats: apply_error_count or sync_error_count increasing over any five-minute window pages, because an incrementing counter means the apply loop is failing right now. The staleness alert is on now() – latest_end_time per subscription, with a threshold above your normal burst lag but far below twenty-six hours — ours pages at fifteen minutes of no applied transactions during business hours, warn-only overnight. Both queries run against the subscriber, which is the part our original monitoring got wrong: we had publisher-side lag dashboards and assumed the subscriber’s view was redundant. It is not redundant; it is the only side that can see apply failures at all. The publisher can tell you the WAL left the building. Only the subscriber can tell you nobody was home.
Watching subscriptions with MonPG
Replication health is a two-sided story — publisher-side slot retention and WAL growth, subscriber-side apply errors and staleness — and the incident that opens this post happened because only one side was on a screen. MonPG monitors PostgreSQL in production today, and its PostgreSQL monitoring tracks replication lag, WAL retention, and database health on one timeline, so a stalled apply worker shows up next to the publisher’s growing slot instead of as a finance complaint a day later. Wire the error counters into the alert path, keep the skip procedure in the runbook, and the next duplicate key is a five-minute fix.