C12 — pgEdge / Spock Cluster¶

Health of a multi-master pgEdge/Spock replication cluster specifically — node list consistency, subscription and worker status, apply lag, and conflict-resolution activity between peers. Every check in this category self-skips cleanly (via pghf.has_spock()) on a server that doesn't have the Spock extension installed, which is the common case — installing Spock is never required to use the rest of this framework.

25 checks, PGHF12-001 through PGHF12-025. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF12-001 — Node list consistency¶

A divergent spock.node membership list across cluster nodes means the nodes disagree about who's even in the cluster — a strong sign of incomplete initialization or a node-removal race condition that needs resolving before it causes replication inconsistencies.

How to fix

On each node, compare SELECT * FROM spock.node; to find exactly which node(s) disagree.
If a node was removed but not fully cleaned up, finish the removal on every remaining node:

SELECT spock.node_drop('removed_node_name');

If a node was added but didn't propagate everywhere, re-run spock.node_create()/spock.sub_create() on the node(s) missing it. Don't proceed with other Spock changes until every node agrees on membership — downstream replication-set and subscription state assumes a consistent node list.

PGHF12-002 — Spock subscriptions enabled¶

A disabled subscription halts replication on that path entirely — multi-master synchronization silently stops until an operator notices and re-enables it.

How to fix

SELECT spock.sub_enable('subscription_name');

Check why it was disabled before re-enabling (a deliberate maintenance pause vs. an error that auto-disabled it) — if it auto-disabled due to a repeated apply failure, fix the underlying conflict first (PGHF12-005) or it will likely fail again immediately.

PGHF12-003 — Spock worker status¶

A missing Spock worker process in pg_stat_activity means misconfiguration or an initialization failure that's blocking all replication on that node, not just degrading it.

How to fix

Check the server log for the worker's startup error — a missing spock.node registration (PGHF12-019), wal_level not set to logical (PGHF12-011), or a max_worker_processes/max_replication_slots ceiling already exhausted are the usual causes.
Fix the underlying cause, then restart replication for the affected subscription:

SELECT spock.sub_disable('the_sub'); SELECT spock.sub_enable('the_sub');

PGHF12-004 — Spock apply lag¶

Apply lag past a few minutes or a large byte gap points at a slow consumer, network issue, or stalled apply worker — left unaddressed it risks real data divergence between nodes in a multi-master setup.

How to fix

Check PGHF12-003 for a missing/crashed worker first — no worker means no progress at all, a different problem from a slow one.
If the worker is running but slow, check for conflicts (PGHF12-005/PGHF12-022) forcing retries, or a genuine throughput bottleneck (network, or the target table missing an index the apply process needs — see PGHF06-005).
For a large byte gap specifically, check PGHF12-024 for spill-to-disk, which both signals and worsens apply lag under sustained load.

PGHF12-005 — Spock exception log¶

A growing row count in spock.exception_log means replication conflicts are accumulating faster than they're being resolved — each one represents a change that failed to apply and needs manual intervention.

How to fix

Review each row in spock.exception_log to understand the conflict type (duplicate key, missing row, and similar).
Resolve each one manually — apply the equivalent change by hand on the lagging node, or explicitly discard it if it's superseded — then remove it from the exception log once handled.
If exceptions accumulate faster than they can be reviewed, install pg_cron (PGHF12-008) for scheduled cleanup of resolved entries, and look at PGHF12-022 for the application write pattern actually causing them.

PGHF12-006 — Spock resolutions¶

An elevated row count in spock.resolutions signals conflicts are being resolved frequently — usually a hint of a write hotspot or application logic that isn't accounting for multi-master concurrent writes.

How to fix

No config-level fix — automatic resolution is working as intended, but a high rate points at an application-level issue. Identify the specific table/row pattern in spock.resolutions and check whether the application can avoid the concurrent-write pattern causing it (e.g. routing writes for a given key to a consistent node, or using SELECT ... FOR UPDATE semantics where the workload allows it).

PGHF12-007 — Oldest unresolved spock exception age¶

An exception sitting unresolved for days rather than hours usually means it's been abandoned, not actively worked — stale exceptions are exactly how a systemic conflict pattern goes unnoticed until it's a bigger problem.

How to fix

See PGHF12-005's remediation for resolving the specific exception. If exceptions are routinely going unreviewed, that's a process gap — assign clear ownership for working the spock.exception_log queue on a defined cadence, not just when this check fires.

PGHF12-008 — pg_cron extension¶

Without pg_cron, cleaning up spock.exception_log and similar Spock housekeeping tables falls to manual effort — worth knowing whether that automation is in place before exception-log growth becomes an unmonitored problem.

How to fix

If automated housekeeping is desired for this deployment:

ALTER SYSTEM SET shared_preload_libraries = 'pg_cron,...';  -- append to existing list

pg_ctl restart

CREATE EXTENSION pg_cron;
SELECT cron.schedule('cleanup-spock-exceptions', '0 3 * * *', 'DELETE FROM spock.exception_log WHERE resolved AND remote_commit_ts < now() - interval ''30 days''');

If this deployment deliberately handles housekeeping another way (an external job scheduler), no action is needed — presence here is a choice, not a requirement.

PGHF12-009 — Spock WAL slots¶

An inactive Spock replication slot fails to consume WAL on the publisher side, causing unbounded WAL disk growth — the Spock-specific counterpart to PGHF09-011's generic inactive-logical-slot risk.

How to fix

See PGHF09-011/PGHF09-001's remediation — identify whether the subscriber is genuinely gone (drop the slot via spock.sub_drop() on the subscription that owns it) or just disconnected (fix the subscriber's connection instead of dropping the slot).

PGHF12-010 — hot_standby_feedback¶

Without hot_standby_feedback on, autovacuum on the primary can remove row versions a replica still needs, cancelling replica queries mid-execution — on a Spock cluster this manifests as replication failures, not just query cancellations.

How to fix

ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

Applies on the standby side of a physical-replication pair sitting alongside Spock nodes. This trades a small amount of primary-side bloat (vacuum waits slightly longer to reclaim rows the standby still needs) for eliminating query cancellations — usually a clearly worthwhile tradeoff.

PGHF12-011 — wal_level¶

Spock requires wal_level=logical for logical decoding to function at all — anything less is a critical misconfiguration that stops replication before it can even start.

How to fix

ALTER SYSTEM SET wal_level = 'logical';

Requires a full restart, and increases WAL volume somewhat (see PGHF14-007) — expected and necessary for Spock to function, not a tuning regression.

PGHF12-012 — Table parity¶

A schema mismatch between nodes breaks replication outright and risks silent data loss on whichever node is out of sync — this needs catching before the divergence compounds across more DDL changes.

How to fix

Diff the flagged table's definition across nodes (\d+ schema.table on each) to find the exact discrepancy.
Apply the missing DDL manually on whichever node is behind — Spock does not automatically replicate DDL by default, so every schema change needs to be applied to each node deliberately (via spock.replicate_ddl() going forward, or manually to reconcile the current gap).
Once schemas match, verify the table is still correctly in its replication set (PGHF12-016).

PGHF12-013 — Index parity¶

Differing index definitions across nodes mean identical queries perform unevenly depending on which node serves them — confusing to troubleshoot unless index parity is checked directly.

How to fix

Create the missing index(es) on whichever node lacks them, matching the definition on the nodes that already have it:

CREATE INDEX CONCURRENTLY ON schema.the_table (the_column);  -- match the existing node's exact definition

Going forward, apply index DDL changes to every node explicitly (via spock.replicate_ddl() or a documented per-node rollout process) — same discipline as PGHF12-012's table-schema parity.

PGHF12-014 — Sequence increment collision¶

A sequence with a plain increment of 1 on a multi-master cluster will eventually generate the same value on two different nodes — non-overlapping offset ranges per node are required, or duplicate keys corrupt the data outright.

How to fix

Reconfigure the flagged sequence to use per-node offset ranges instead of a plain increment of 1 — e.g. for a 3-node cluster, node N starts at N and increments by the node count:

ALTER SEQUENCE schema.the_sequence INCREMENT BY 3 RESTART WITH 1;   -- on node 1
ALTER SEQUENCE schema.the_sequence INCREMENT BY 3 RESTART WITH 2;   -- on node 2
ALTER SEQUENCE schema.the_sequence INCREMENT BY 3 RESTART WITH 3;   -- on node 3

Alternatively, switch the column to a UUID or a Spock-aware snowflake-style ID generator if sequence-per-node offsetting doesn't fit the application's needs. Do this before a real collision occurs — after one happens, it means duplicate-key data that needs manual reconciliation.

PGHF12-015 — Spock forward_origins¶

Missing forward_origins configuration on a multi-master subscription blocks change propagation across the wider cluster — changes may replicate one hop but never reach every node that needs them.

How to fix

Set forward_origins on the flagged subscription so changes originating elsewhere continue propagating past this node, not just locally-originated ones:

SELECT spock.sub_alter_interface('the_sub', 'all');  -- or the specific origin-forwarding option this Spock version exposes

Consult the Spock documentation for the exact forward_origins syntax for the installed Spock version, since it has changed across releases.

PGHF12-016 — Replication set membership¶

A table missing from every replication set isn't being replicated at all — easy to miss since the table still exists and functions locally, just silently out of sync with the rest of the cluster.

How to fix

SELECT spock.repset_add_table('default', 'schema.the_table');

After adding it, the table needs its initial data synced to other nodes too — spock.sync_event()/a fresh subscription resync, depending on the Spock version — an empty replication-set add does not retroactively copy existing rows.

PGHF12-017 — Spock sync state¶

An error state in spock.local_sync_status requires manual recovery — distinguishing that from a legitimate in-progress initial sync is what this check exists to surface quickly.

How to fix

Check spock.local_sync_status.sync_status — a value indicating an in-progress initial sync (s/d, syncing schema/data) is normal and just needs time; an error state needs intervention.
For a genuine error, check the server log for the sync worker's failure reason, then resync just the affected table:

SELECT spock.sub_resync_table('the_sub', 'schema.the_table');

PGHF12-018 — Row count sampling¶

A large row-count discrepancy for the same table across nodes is a direct, easy-to-understand symptom of replication lag or genuine data divergence — a sanity check that catches problems more targeted diagnostics might miss.

How to fix

If replication is actively lagging (PGHF12-004), a row-count gap may just be transient — recheck after lag clears.
If lag is minimal but the gap persists, this is genuine divergence — cross-check spock.exception_log (PGHF12-005) for a related unresolved conflict on this table around the time the gap appeared.
For confirmed divergence with no corresponding exception logged, a full table resync is the safest fix:

SELECT spock.sub_resync_table('the_sub', 'schema.the_table');

PGHF12-019 — Spock local node registration¶

A node not registered in spock.local_node cannot join the cluster or participate in replication at all — the most basic prerequisite check for whether Spock is even set up on this node.

How to fix

SELECT spock.node_create(node_name => 'this_node_name', dsn => 'host=... dbname=...');

Then create subscriptions to/from the other cluster nodes with spock.sub_create(). If this node was meant to already be part of the cluster, check for an initialization step that didn't complete rather than assuming it was never attempted.

PGHF12-020 — Spock lag tracker¶

A per-receiver snapshot from spock.lag_tracker surfaces a sudden lag increase on one specific link before it becomes a cluster-wide symptom — useful for isolating which node pair is under pressure.

How to fix

Use the flagged node pair to narrow investigation to that specific link — see PGHF12-004's remediation, scoped to the specific receiver this check names rather than the whole cluster.

PGHF12-021 — Spock queue depth¶

A deep or aging spock.queue means a subscriber isn't consuming messages fast enough, or at all — the queue is the buffer between "written on one node" and "applied on another," and a growing backlog is unreplicated data piling up.

How to fix

See PGHF12-003/PGHF12-004's remediation — check first whether the consuming worker is even running, then whether it's just slow (conflicts, missing indexes, network) versus stopped entirely.

PGHF12-022 — Total spock channel conflicts¶

A high per-subscription conflict count reveals application write patterns that aren't actually compatible with multi-master concurrency — the fix is usually in the application, not the cluster configuration.

How to fix

Same as PGHF12-006 — identify the specific table/channel with the highest conflict count and look at the application's write pattern against it. Common fixes: route writes for a given key consistently to one node, widen the conflict-resolution window, or add explicit conflict-handling logic in the application rather than relying on Spock's default resolution for a pattern that conflicts often.

PGHF12-023 — Worst spock replication progress lag¶

A large lag between a specific node pair in spock.progress signals a capacity or network bottleneck on that link specifically — worth knowing which pair, not just that the cluster overall is lagging.

How to fix

Investigate the specific node-pair link this check names — network latency/bandwidth between those two nodes specifically, or a capacity difference (the receiving node under-provisioned relative to write volume). See PGHF12-004 for the general apply-lag remediation, scoped to this pair.

PGHF12-024 — Worst logical slot spill size¶

Spill files accumulating in pg_replslot/ mean an apply worker has fallen far enough behind that decoded changes no longer fit in memory — a growing spill size is heading toward disk exhaustion if the underlying lag isn't addressed.

How to fix

Raise logical_decoding_work_mem (PGHF09-015) so more can be held in memory before spilling — this buys headroom but doesn't fix the root cause.
Fix the underlying apply lag (PGHF12-004) — spilling is a symptom of the consumer falling behind, not an independent problem.
Monitor disk space on the pg_replslot/ filesystem directly while working the fix — this is the most time-sensitive of the Spock checks if it's trending upward.

PGHF12-025 — Oldest unresolved spock exception age (retry/crash-loop)¶

An exception being retried continuously for hours without resolving is a crash-loop signature — the apply worker keeps failing on the same change instead of making progress, and replication has effectively stalled while looking superficially active.

How to fix

Identify the specific stuck transaction/change from spock.exception_log.
Manually resolve it (apply the equivalent change by hand, or explicitly skip it) rather than letting the worker keep retrying the same failure indefinitely:

SELECT spock.sub_alter_skip_lsn('the_sub', 'the_lsn_from_the_log');

Once the specific blocker is cleared, confirm the worker resumes making forward progress (PGHF12-004 lag should start decreasing) rather than immediately hitting the next conflict in the same pattern.

Continue to C13 — OS & Resource-Level Checks.