The view that shows a subscriber looping

A failure that restarts itself

Logical replication is built to survive. When the process applying changes on the subscriber hits something it cannot handle, it logs the problem and exits, and the launcher starts a replacement a few seconds later. The replacement picks up from the same position, meets the same change, and exits again.

From outside, this is nearly invisible. The subscription still exists and is still enabled. The replication slot on the publisher still exists and still has a position. A connection count taken at the right moment shows a worker connected. What is happening is a loop that makes no progress, and the evidence for it before PostgreSQL 15 was in one place only, which was the subscriber’s log file.

Relying on a log file for this has the problems relying on a log file always has. Nobody reads it until something is already wrong, the relevant line is one among thousands, and any alerting built on it is a regular expression that goes quiet when a message is reworded.

PostgreSQL 15 added counters. The Logical Replication section of the PostgreSQL 15 release notes records the new view and the function that clears it.

What 14 could be asked

There was a view about subscriptions on 14, and it is worth looking at to see what it does and does not cover.

SELECT subid, subname, pid IS NOT NULL AS worker_connected, received_lsn, latest_end_time
FROM pg_stat_subscription;

 subid | subname | worker_connected | received_lsn | latest_end_time 
-------+---------+------------------+--------------+-----------------
(0 rows)

Empty, because this server has no subscriptions yet. What matters about that view is not whether it has rows but what its columns are: a worker process, a position received, a time. Every one of them describes progress, and none of them describes failure.

The counters are not there at all:

SELECT subname, apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

ERROR:  relation "pg_stat_subscription_stats" does not exist
LINE 1: ...subname, apply_error_count, sync_error_count FROM pg_stat_su...
                                                             ^

The new view on 15

SELECT subid, subname, apply_error_count, sync_error_count, stats_reset FROM pg_stat_subscription_stats;

 subid | subname | apply_error_count | sync_error_count | stats_reset 
-------+---------+-------------------+------------------+-------------
(0 rows)

Also empty, for the honest reason that there are no subscriptions on this server yet. Create one and it appears. A subscription created without connecting is a real subscription, which is convenient here because it means the view can be demonstrated without a second server.

CREATE SUBSCRIPTION billing_sub
  CONNECTION 'host=127.0.0.1 port=1 dbname=absent'
  PUBLICATION billing_pub
  WITH (connect = false);

CREATE SUBSCRIPTION

SELECT subname, apply_error_count, sync_error_count, stats_reset FROM pg_stat_subscription_stats;

   subname   | apply_error_count | sync_error_count | stats_reset 
-------------+-------------------+------------------+-------------
 billing_sub |                 0 |                0 | 
(1 row)

Two counters that exist because failures exist, rather than because progress does. That is the structural difference from the older view, and it is why a check built on this one can distinguish a subscription that is stuck from one that is merely quiet.

The older view does list the same subscription on 14, which makes the gap easy to miss. What it lists is the absence of progress, not the presence of a problem:

SELECT subname, pid IS NULL AS no_worker_attached, received_lsn, latest_end_time
FROM pg_stat_subscription;

   subname   | no_worker_attached | received_lsn | latest_end_time 
-------------+--------------------+--------------+-----------------
 billing_sub | t                  |              | 
(1 row)

A row, and nothing in it. No worker, no position, no time. That is exactly what a subscription whose apply worker is crashing in a loop looks like on 14 as well, and it is indistinguishable from a subscription that is simply idle between restarts. The counters are what separate the two.

Two counters, two different failures

They are not variations on one theme and confusing them wastes time.

The apply counter counts failures in the ongoing stream of changes. The usual causes are a constraint on the subscriber that the publisher does not have, a row that is not there to update because a previous change was skipped, or a type that does not survive the round trip. Every one of those is a data problem and none of them resolves by waiting.

The synchronisation counter counts failures during the initial copy of a table, which happens when a table is first added to a subscription. These fail for different reasons: a missing table, a permission the subscription owner does not have, or a copy that ran into a lock it could not get. A subscription can be applying changes perfectly for nine tables while the tenth fails its initial copy in a loop, and only the second counter moves.

The number to watch is the rate, not the value. A subscription that has accumulated a few errors over a year is a subscription that met a few bad rows. A subscription whose counter rises every minute is the loop this page is about, and the gap between those two readings is the whole reason a rate matters.

Getting from a counter to a cause

The counters say that something failed and how often. They do not say what, which stays in the log, and there is no column carrying the last error message. The practical sequence is that the counter tells you which subscription and when, and the log tells you why, which is a much narrower search than reading the log speculatively.

The other tool the same release gave subscribers is the ability to skip a specific transaction that cannot be applied, which is the escape hatch for a loop caused by one bad change rather than by a systematic mismatch. Reaching for it needs care: skipping a transaction discards it, the subscriber is then missing data the publisher has, and nothing afterwards will tell you that. The counter is what gives you the information to decide, and the decision is still yours.

On the publisher side, the slot is the other half of the picture. A subscriber looping is a subscriber not advancing, and a slot that is not advancing pins write-ahead log on the publisher until storage runs out. The slot statistics view added in PostgreSQL 14 is where that pressure shows up first, and watching one without the other is how a subscriber problem becomes a publisher outage.

Building a check on it

The counters only rise, which shapes what a useful check looks like more than anything else about them.

A threshold on the total is the wrong check. Every subscription that has run for a year has met something it could not apply, and a total that crosses a line tells you only that the subscription is old. What matters is whether the counter is rising now, which means a rate over a window short enough to catch a loop and long enough not to fire on a single bad row.

A rate needs two samples, and two samples need the collector to have taken both. This is the failure that catches people: an alert expressed as a rate evaluates against nothing when the collector stops, and evaluating against nothing is not the same as evaluating to zero. Most alerting systems treat the absent case as quiet. A subscriber looping and a collector that has died therefore look the same, and the second is more likely.

So the check has two halves. The rate over the error counters, and a liveness signal that proves the reading was taken. The cheapest liveness signal for replication is the position of the slot on the publisher, because a slot whose position has not moved in an hour is a fact about the database rather than about the monitoring.

Two further refinements are worth the effort on a fleet of any size.

• Alert per subscription, not per cluster. A cluster with six subscriptions where one is looping is a completely different situation from one where all six are, and a summed counter cannot tell them apart.
• Separate the two counters in the alert. An initial synchronisation failing repeatedly is usually a permission or a schema problem that resolves with one change, while an apply failure is usually a data problem that needs a decision about the row.

The last point is about noise. A subscription that is genuinely broken will keep the counter rising for as long as nobody acts, so an alert on it will keep firing. Building in an acknowledgement, or alerting on the transition rather than the state, is what stops it being the alert everybody mutes.

The flaw this view shipped with

The clearing function that arrived alongside the view does not behave as expected on 15. On a subscription that has never reported anything, the call succeeds, the timestamp that records it stays empty, and nothing indicates that the reset did not happen. That was fixed in the following major, and until a cluster gets there, a runbook step that clears these counters before a change should read the timestamp back rather than trust the call.

Cleaning up

A subscription whose publisher does not exist cannot be dropped directly, because the drop tries to remove a slot on the other end.

ALTER SUBSCRIPTION billing_sub DISABLE;
ALTER SUBSCRIPTION billing_sub SET (slot_name = NONE);
DROP SUBSCRIPTION billing_sub;

ALTER SUBSCRIPTION
ALTER SUBSCRIPTION
DROP SUBSCRIPTION

This is the sequence worth having in a runbook before you need it, which is generally at the point where a publisher has been decommissioned and its subscribers are still pointing at it.

What it costs

Nothing. Two counters per subscription, maintained in shared memory, with no setting to enable and no extension involved. The view assembles on request from the subscription catalog, so its cost scales with the number of subscriptions, which on any real fleet is a number you could count on your fingers.

The cost that is worth thinking about is the alerting one. These counters only increase, so an alert has to be written on a rate over a window, and a rate alert evaluates against nothing when the collector stops. A subscriber looping and a collector that has died produce the same absence, and distinguishing them needs a second signal, which is usually the age of the slot’s position on the publisher.