Monitoring a Node with the Spock Status Views

Spock keeps the state of a node in shared memory and exposes it through a set of views in the spock schema. Together they answer the questions an operator would otherwise take to the server log: is every subscription replicating, which workers are running, how far behind is each provider, how much WAL is a slot holding back, and what happened recently.

spock.node_report() runs all of them one after another and returns the node as a single text report; see The full report.

All views are scoped to the current database. spock.lag_tracker, spock.progress and the spock.channel_*_stats views remain available and feed the views above.

System status

spock.system_status is one row about the host and the PostgreSQL instance. It answers the questions that come before any replication question: is the disk full, is the machine overloaded, are connections exhausted, is a long or idle transaction holding back vacuum and the replication slots, is WAL archiving failing.

SELECT pg_size_pretty(data_disk_free_bytes) AS data_free,
       pg_size_pretty(wal_disk_free_bytes) AS wal_free,
       load_average_1min, connections, max_connections,
       longest_transaction, idle_in_transaction, deadlocks,
       archive_failed_count, last_archive_failure
  FROM spock.system_status;

Node status

spock.node_status always returns exactly one row, even before a node has been created.

SELECT node_name, extension_update_pending, subscriptions_replicating,
       replication_slots_active, workers_restart_pending,
       pg_size_pretty(max_retained_wal_bytes) AS retained_wal,
       max_replication_lag, last_error_event_time
  FROM spock.node_status;

Subscription status

spock.subscription_status is the first place to look when replication seems stuck.

SELECT subscription_name, status, worker_status, replication_lag,
       pg_size_pretty(replication_lag_bytes) AS lag, last_error_message
  FROM spock.subscription_status;

Subscription statistics

spock.subscription_stats has one row for every subscription with the counters collected by its apply and sync workers. Counters survive worker restarts and are reset with spock.reset_subscription_stats(subid), or for all subscriptions with no argument. Every counter has a matching last_* timestamp of its most recent change.

Workers

spock.worker_status lists the supervisor, the manager of this database, and every apply and sync worker, including slots of failed workers that are waiting to be restarted.

Replication slots

spock.slot_status is the provider side view: one row per spock replication slot of the current database, joined with the walsender that serves it.

SELECT slot_name, active, state, pg_size_pretty(retained_wal_bytes) AS retained,
       pg_size_pretty(pending_wal_bytes) AS pending, replay_lag
  FROM spock.slot_status;

Peers, replication sets and settings

spock.peer_status lists every node in spock.node with its interfaces (passwords hidden), whether it is the local node, and the subscription that brings its changes here with that subscription's status and lag. A node with several subscriptions has one row per subscription.

spock.replication_set_status lists the replication sets of the local node with their replicate flags, the number of tables and sequences in each, and how many local subscriptions include the set. Tables that are in no set at all are listed by spock.tables where set_name IS NULL.

spock.settings shows every spock.* parameter and the core parameters replication depends on (wal_level, max_worker_processes, max_replication_slots, max_wal_senders, max_slot_wal_keep_size, track_commit_timestamp, shared_preload_libraries, the walsender and walreceiver timeouts, synchronous commit settings and logical_decoding_work_mem), with unit, source and whether a restart is pending. Connection strings in parameters have their password hidden.

Events

spock.events is a ring of recent state changes kept in shared memory, oldest first. The number of events kept is set by spock.event_history_size. The history is cleared by a postmaster restart, and spock.reset_events() clears the events of the current database.

Event types:

Events are recorded when the reporting code runs, whether or not the surrounding transaction commits.

SELECT event_time, event_type, subscription_name, detail
  FROM spock.events
 WHERE severity <> 'info'
 ORDER BY event_id DESC
 LIMIT 20;

The full report

spock.node_report() returns the whole node as text, section by section: system status, installed extensions, settings, node status, peers, subscriptions, subscription statistics, workers, pending exceptions, replication slots, replication sets, tables in no replication set, table synchronization, lag by node pair, recent exceptions, recent conflict resolutions and recent events. Wide views are printed one row per block, lists as aligned tables. A section whose query fails, for example node_status for a role without EXECUTE on the functions behind it, prints one error line and the report continues.

SELECT * FROM spock.node_report();
SELECT * FROM spock.node_report(200);   -- list up to 200 events, exceptions and resolutions

In psql, \pset format unaligned and \o report.txt before the call produce a plain file that can be attached to a ticket. The report contains no passwords.

spock.report_section(title, query, expanded) is the formatter behind the report and can render any query the same way.

Common tasks

The recipes below cover the questions that used to require the server log. Run them in psql with expanded output (\x) when a row is wide.

Is the node healthy?

One row answers it. Anything other than zero in workers_restart_pending, a difference between subscriptions_enabled and subscriptions_replicating, or a last_error_event_time that is more recent than you expect deserves a look.

SELECT node_name, extension_update_pending,
       subscriptions_enabled, subscriptions_replicating,
       workers_restart_pending, apply_paused, readonly_mode,
       pg_size_pretty(max_retained_wal_bytes) AS retained_wal,
       max_replication_lag, last_error_event_time
  FROM spock.node_status;

Why is a subscription not replicating?

Start with the subscription row. status tells whether a worker runs at all, worker_status and worker_terminated_at show a failed worker waiting for its restart delay, and last_error_message is the error the worker last raised, without opening the log.

SELECT subscription_name, status, enabled, worker_status,
       worker_terminated_at, worker_restart_delay,
       in_exception_handling, last_apply_error, last_error_message
  FROM spock.subscription_status
 WHERE status <> 'replicating';

Then read the events of that subscription, newest first:

SELECT event_time, event_type, severity, detail
  FROM spock.events
 WHERE subscription_name = 'sub_n2_n1'
 ORDER BY event_id DESC
 LIMIT 20;

A subscription that was disabled by exception handling shows a subscription_disabled event with the detail disabled by exception handling and the origin LSN of the failing transaction in lsn; the row in spock.exception_log has the failing tuple.

Is a worker blocked or deadlocking?

A worker waiting for a lock shows wait_event_type = 'Lock' and the session holding the lock in blocked_by_pids and blocking_query. A deadlock or lock timeout aborts the transaction; the worker exits and is restarted, and the error is classified.

SELECT worker_type, subscription_name, wait_event_type, wait_event,
       blocked_by_pids, blocking_pid, blocking_xact_start, blocking_query
  FROM spock.worker_status
 WHERE blocked_by_pids IS NOT NULL;

SELECT subscription_name, deadlocks, lock_timeouts,
       constraint_violations, resource_errors,
       last_error_sqlstate, last_apply_error, last_error_message
  FROM spock.subscription_stats;

Is a worker restarting in a loop?

worker_starts and worker_failures climb together when the apply worker crashes on every attempt. The events show the interval and the reason. Sync workers have their own pair of counters, so a resync of many tables does not look like a restart loop here.

SELECT subscription_name, worker_starts, worker_failures,
       last_worker_failure, last_error_message
  FROM spock.subscription_stats
 WHERE worker_failures > 0;

SELECT event_time, event_type, detail
  FROM spock.events
 WHERE event_type IN ('worker_started', 'worker_failed', 'worker_error')
 ORDER BY event_id DESC
 LIMIT 30;

What changed overnight?

Everything that is not routine has a severity above info.

SELECT event_time, event_type, severity, subscription_name, detail
  FROM spock.events
 WHERE event_time > now() - interval '12 hours'
   AND severity <> 'info'
 ORDER BY event_id;

Enable and disable calls, interface switches, replication set changes, sub_skip_lsn, pause and resume, repair mode and extension upgrades are all info or warning events with the operator's backend pid in pid.

How far behind is each provider?

SELECT subscription_name, provider_node,
       pg_size_pretty(replication_lag_bytes) AS lag_bytes,
       replication_lag, remote_commit_ts, received_lsn, origin_lsn
  FROM spock.subscription_status
 ORDER BY replication_lag_bytes DESC NULLS LAST;

replication_lag_bytes is WAL the provider has written that this node has not applied yet. replication_lag is the time between the last applied commit on the provider and its apply here. Both come from spock.progress and are also available per node pair in spock.lag_tracker.

Is a slot holding back WAL on the provider?

Run this on the provider. A slot with active = false or a growing retained_wal_bytes points at a subscriber that is down or lagging.

SELECT slot_name, active, client_name, state,
       pg_size_pretty(retained_wal_bytes) AS retained,
       pg_size_pretty(pending_wal_bytes) AS pending,
       replay_lag, last_reply_time, wal_status
  FROM spock.slot_status
 ORDER BY retained_wal_bytes DESC;

Is the initial synchronization finished?

SELECT subscription_name, schema_name, table_name, sync_kind, sync_status
  FROM spock.table_sync_status
 WHERE sync_status NOT IN ('ready', 'synchronized');

The row without a table is the subscription itself. Sync workers in flight are listed in spock.worker_status with worker_type = 'sync' and the table they copy.

Did the extension get upgraded?

spock.node_status compares the loaded library with the installed extension. After a binary upgrade the manager updates the extension on its next start and records the change.

SELECT spock_version, extension_version, extension_update_pending
  FROM spock.node_status;

SELECT event_time, detail
  FROM spock.events
 WHERE event_type = 'extension_upgraded';

Starting a fresh measurement

Counters and events accumulate since the postmaster started. Reset them before a benchmark or after fixing an incident so that the next numbers are clean.

SELECT spock.reset_subscription_stats();   -- all subscriptions
SELECT spock.reset_subscription_stats(sub_id)
  FROM spock.subscription WHERE sub_name = 'sub_n2_n1';
SELECT spock.reset_events();

spock.reset_channel_stats() clears the per-table tuple counters separately.

Watching from outside

The views are plain SQL, so any monitoring agent can poll them. Poll spock.events by event_id to fetch only new rows, and treat a gap in ids as history that was dropped because spock.event_history_size was too small for the polling interval. spock.get_monitor_summary() reports how full the event ring and the counters table are. The read-only views and the functions they call are granted to pg_monitor; the role also needs USAGE on the schema:

GRANT pg_monitor TO monitoring_role;
GRANT USAGE ON SCHEMA spock TO monitoring_role;

For now, USAGE on the spock schema also allows calling spock's management functions. Grant it only to trusted roles, and treat every function that changes something as needing a superuser. Members of pg_monitor cannot reset counters or events: spock.reset_events() and spock.reset_subscription_stats() stay with the superuser. The node_report() sections on spock.tables, spock.lag_tracker, spock.exception_log and spock.resolutions need SELECT on those, which the grant does not include; without it each shows as one error line.

Underlying functions

The views are built on functions that return the raw shared memory contents. They are useful for tooling that wants unfiltered rows.