Monitoring & Alerting

Wiring an off-the-shelf Prometheus, Grafana, and Alertmanager stack to logical-replication state turns silent WAL retention into a paged incident, and it is the observability discipline that keeps Logical Replication Setup & Management from ending in a full-disk outage on the primary. This guide specifies the metric model exported from pg_replication_slots, pg_stat_replication, and pg_stat_subscription, the dashboard panels that render it, and the alert thresholds — bytes retained against max_slot_wal_keep_size, active = false, sustained apply lag — that separate a controlled slot invalidation from a primary that stops accepting writes.

The failure this stack exists to catch has no query error and no degraded latency until the moment it is terminal. A consumer disconnects, its replication slot freezes restart_lsn, and the primary retains every WAL segment generated since — by unrelated writers — while continuing to accept traffic. Nothing surfaces in application logs; the only signal is pg_wal climbing toward the filesystem ceiling. A metrics pipeline that scrapes slot state every 15 seconds and alerts at a fraction of max_slot_wal_keep_size converts that runaway into a 20-minute warning with a clear owner. This guide covers the industry-standard components — postgres_exporter with a custom query file, Prometheus for storage and rule evaluation, Grafana for visualization, and Alertmanager for routing — as opposed to a purpose-built collector, which the async monitoring integration guide covers when you need scrape logic the exporter cannot express. The two are complementary: run the exporter for the common metric surface and the bespoke collector only where a metric needs custom transaction control or a scrape cadence below one second.

Prerequisites & Configuration Objects #

Four components sit between the database and an on-call phone, and each has a narrow contract. The exporter needs a least-privilege database role; Prometheus needs a scrape target and a rule file; Grafana needs Prometheus as a data source; Alertmanager needs a route. The database side is deliberately minimal — the exporter reads statistics views and nothing else.

The exporter connects as a dedicated role that holds pg_monitor and no more. That built-in role (PostgreSQL 10+) bundles pg_read_all_stats, pg_read_all_settings, and access to the replication views, so the exporter can read every slot, sender, and subscription without REPLICATION privilege or SELECT on any application table. The full reasoning for keeping the monitoring identity separate from the replication identity is in security boundaries and permissions.

-- Least-privilege role for postgres_exporter. No REPLICATION, no table access.
CREATE ROLE exporter LOGIN PASSWORD 'from-a-secret-store';
GRANT pg_monitor TO exporter;                 -- PG 10+: stats + settings + repl views

-- Fence any session the exporter opens so a wedged scrape cannot pin WAL.
ALTER ROLE exporter SET statement_timeout = '10s';
ALTER ROLE exporter SET idle_in_transaction_session_timeout = '15s';

On the server, one parameter is the denominator for the most important alert and therefore must be a known, finite number. max_slot_wal_keep_size caps how much WAL a lagging slot can pin before PostgreSQL invalidates it; every retained-bytes alert on this stack pages at a fraction of that value, so leaving it at the -1 default (unlimited) removes the reference point the alert is measured against.

-- Publisher: a finite cap gives the retained-bytes alert a denominator (PG 13+).
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';
ALTER SYSTEM SET track_commit_timestamp = on;   -- restart required; enables wall-clock apply lag
SELECT pg_reload_conf();

Step-by-Step Implementation #

The build order is bottom-up: get the exporter emitting the custom gauges, point Prometheus at it, then layer dashboards and alerts on the series that result.

1. Define the custom queries. The stock exporter build ships no replication-slot lag metrics, so supply a queries.yaml that turns pg_replication_slots into the gauges the whole pipeline depends on. Compute retained bytes with pg_wal_lsn_diff on the publisher so the exporter emits a plain byte count, not two LSNs a dashboard would have to subtract.

# queries.yaml — loaded via --extend.query-path
pg_replication_slot_lag:
  query: >
    SELECT slot_name,
           slot_type,
           active::int AS active,
           (wal_status = 'reserved')::int AS reserved,
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)         AS retained_bytes,
           pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS unconfirmed_bytes
    FROM pg_replication_slots
  metrics:
    - slot_name:      {usage: "LABEL",   description: "Replication slot name"}
    - slot_type:      {usage: "LABEL",   description: "physical or logical"}
    - active:         {usage: "GAUGE",   description: "1 if a consumer is attached"}
    - reserved:       {usage: "GAUGE",   description: "1 while wal_status = reserved"}
    - retained_bytes: {usage: "GAUGE",   description: "WAL bytes pinned behind restart_lsn"}
    - unconfirmed_bytes: {usage: "GAUGE", description: "bytes past confirmed_flush_lsn"}

2. Run the exporter with the query file. Point it at the publisher, load the custom queries, and expose the metrics endpoint. The deployment specifics — container vs systemd, DSN secret handling, the exact gauge semantics — are the subject of deploying postgres_exporter for replication metrics.

postgres_exporter \
  --extend.query-path=/etc/postgres_exporter/queries.yaml \
  --web.listen-address=:9187
# DATA_SOURCE_NAME sourced from the environment, never a flag:
#   postgresql://exporter@pub.internal:5432/app?sslmode=verify-full

3. Scrape it from Prometheus. Add the exporter as a target and load a rule file. Keep the scrape interval at 15 seconds — fast enough that a runaway slot pages within a couple of intervals, slow enough that a fleet of exporters does not add load.

# prometheus.yml
global:
  scrape_interval: 15s
  evaluation_interval: 15s
rule_files:
  - /etc/prometheus/rules/replication.yml
scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ['pg-primary.internal:9187']
        labels: {cluster: 'analytics-prod', role: 'primary'}
alerting:
  alertmanagers:
    - static_configs:
        - targets: ['alertmanager.internal:9093']

4. Draw the dashboards. In Grafana, add Prometheus as a data source and build panels from the exported series — retained bytes as a fraction of the cap, apply lag per slot, and a heartbeat panel so a dead exporter reads as unknown rather than healthy. Panel PromQL and threshold coloring are covered in building Grafana dashboards for replication lag.

5. Write the alert rules. Encode the failure modes as Prometheus rules with expr, a for clause to suppress flapping, and labels.severity for routing. The complete rule set — inactive slot, retained-bytes fraction, wal_status leaving reserved, sustained apply lag, and exporter absent — is authored in writing alert rules for slot and WAL thresholds.

Parameter Reference Table #

These are the settings that decide whether the pipeline is a safety net or a source of false confidence. Defaults are the component defaults; the behavior column is what the value means specifically for replication observability.

Diagnostic Queries #

These queries are the source of truth the exported gauges mirror; run them by hand when a dashboard disagrees with reality or an alert fires and you need the raw numbers. Each carries the threshold that should trip an alert. The arithmetic behind restart_lsn versus confirmed_flush_lsn — why one pins WAL and the other tracks consumer progress — is detailed in WAL stream mechanics.

-- Slot health and retained WAL, per consumer. This is what queries.yaml exports.
-- ALERT: retained > 50% of max_slot_wal_keep_size, active = false, or wal_status <> 'reserved'.
SELECT slot_name,
       active,
       wal_status,                                                   -- reserved | extended | unreserved | lost
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))         AS retained,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS unconfirmed
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Publisher send/flush/replay lag, per streaming connection.
-- ALERT: state <> 'streaming', or write_lag / flush_lag > 60 s sustained.
SELECT application_name, client_addr, state,
       write_lag, flush_lag, replay_lag,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS apply_backlog_bytes
FROM pg_stat_replication;

-- Subscriber apply-worker liveness and durable error counters.
-- ALERT: pid IS NULL for an expected worker, or apply_error_count increasing.
SELECT s.subname,
       ss.pid,                                     -- NULL pid = apply worker not running
       st.apply_error_count,
       st.sync_error_count
FROM pg_subscription s
LEFT JOIN pg_stat_subscription       ss ON ss.subname = s.subname
LEFT JOIN pg_stat_subscription_stats st ON st.subname = s.subname;   -- stats view: PG 15+

The single highest-priority signal is a logical slot with active = false and retained climbing — the direct precursor to disk exhaustion. Alert on the transition into unreserved; by the time wal_status reads lost, the slot is invalidated and the consumer must be re-seeded through a fresh subscription sync.

Failure Modes & Gotchas #

Stale-green dashboards after the exporter dies. Signature: every panel shows the last-known value, no alert fires, but replication has been broken for an hour. Root cause: the exporter crashed and Prometheus records the target as up == 0, but no rule watches that series. Remediation: make up == 0 for the postgres job a first-class alert, and build a heartbeat panel so a dead exporter renders as unknown, not healthy. Absence of data must page as loudly as a bad value.

The alert has no denominator. Signature: a retained-bytes alert fires constantly or never, because it compares against a hard-coded byte count that drifts from the real cap. Root cause: max_slot_wal_keep_size is -1 (unlimited) or was changed without updating the rule. Remediation: export the cap itself — scrape SHOW max_slot_wal_keep_size or read it from pg_settings — and write the alert as a fraction of the exported value so the two can never diverge.

Time-based lag reads zero on an idle system. Signature: write_lag and flush_lag are NULL or 00:00:00 and a dashboard shows replication as instant. Root cause: the pg_stat_replication lag columns are populated from feedback on actual traffic; with no writes there is nothing to measure. Remediation: make byte-based backlog (pg_wal_lsn_diff) the primary alert and treat time-based lag as a secondary, traffic-dependent view. Never build the only alert on a column that is legitimately zero at 3 a.m.

Wrong-node scraping after a failover. Signature: after a promotion the exporter reports zero slots and green health while the new primary silently retains WAL. Root cause: the DSN points at a fixed host that is now a standby, or (before PostgreSQL 17) the logical slots never existed on the promoted node. Remediation: point the exporter at the same service endpoint the consumers use, and have a rule assert that the expected slot names are present on whichever node is currently primary.

Cardinality blow-up from per-slot labels. Signature: Prometheus memory climbs and query latency degrades as slots churn. Root cause: short-lived slots — temporary backfill slots, or per-restart Debezium slots — each mint a new slot_name label series that persists in the TSDB. Remediation: use static, persistent slot names for production consumers, and drop or relabel ephemeral slot series at scrape time so the active set stays bounded.

Alerts fire but no one is paged. Signature: Prometheus shows an alert in the firing state on its rules page, yet no notification arrives. Root cause: an Alertmanager misconfiguration — a severity label the routing tree does not match, a silence left in place after a maintenance window, or an inhibition rule that suppresses the child alert. Remediation: test the route with amtool config routes test severity=critical before relying on it, expire silences deliberately rather than letting them outlive the work they covered, and add a dead-man’s-switch alert that fires constantly and pages if it ever stops — the canary that proves the whole delivery path, from rule evaluation to phone, is intact.

Integration Touchpoints #

This stack is the operational readout of every object the rest of the setup creates. The retained-bytes gauge measures the durable cursor reserved during initializing replication slots; the apply-lag series reflects the worker started by a subscription sync; and the byte thresholds you alert on enforce the retention policy chosen in replication slot types.

Where the exporter’s fixed query model is not enough — a metric that needs a specific transaction isolation, a sub-second cadence, or a computed value the SQL cannot express cheaply — the async monitoring integration guide builds a bespoke asyncpg collector that publishes to the same Prometheus. The two feed one dashboard set; the exporter handles the common surface and the collector fills the gaps. Downstream, once changes cross into the streaming layer, consumer-lag observability moves to the event routing and Kafka integration guide, where a Debezium connector exposes its own JMX metrics that reuse the exact slot this stack watches. Correlating pg_replication_slots.confirmed_flush_lsn against the connector’s committed offset localizes a stall to the database, the connector, or the broker.

Frequently Asked Questions #

Related guides #

• Deploying postgres_exporter for replication metrics — the least-privilege role, queries.yaml, and container/systemd deployment behind this pipeline.
• Building Grafana dashboards for replication lag — PromQL, per-slot template variables, and threshold coloring for the panels.
• Writing alert rules for slot and WAL thresholds — the Prometheus groups: rules that turn these thresholds into pages.
• Async monitoring integration — the complementary bespoke collector for metrics the exporter cannot express.
• Logical Replication Setup & Management — the parent guide this observability layer completes.