Prometheus Metrics for Replica Health and Lag

You cannot route around a lagging replica you cannot see. Every fallback rule, every lag-gated read decision, and every capacity plan in a read-replica deployment depends on one thing: a trustworthy, low-latency time series for how far behind each replica is and whether it is even alive. This guide, part of the broader work on monitoring and observability for read replicas, covers how to build that time series with Prometheus — instrumenting PostgreSQL and MySQL replicas, the PgBouncer poolers in front of them, and the recording rules and PromQL that turn raw scrapes into signals you can alert and route on.

The failure that motivates all of it is subtle: a replica reports zero lag, dashboards are green, and yet users are reading data that is minutes stale. That happens because the naive lag metric is derived from a clock that stops advancing on an idle primary, and because a dead exporter serves its last good sample until it goes stale. Getting replica observability right means instrumenting the right metrics, not just the convenient ones — and knowing which ones lie.

Concept Definition and Scope

Replica health monitoring with Prometheus has three moving parts, and conflating them is the source of most bad dashboards.

The exporter is a process that connects to a database (or pooler), runs a fixed set of queries, and translates the results into the Prometheus text exposition format on an HTTP /metrics endpoint. postgres_exporter speaks to PostgreSQL, mysqld_exporter to MySQL, and pgbouncer_exporter to PgBouncer’s virtual admin database. The exporter holds no history; it reports the instantaneous value at scrape time.

The Prometheus server scrapes each exporter on a fixed interval, stamps every sample with the scrape timestamp and the target’s labels, and stores the series in its TSDB. It is the component that turns instantaneous readings into a time series you can compute rates and quantiles over.

Recording rules and alerting rules run inside Prometheus on the evaluation interval. Recording rules pre-compute expensive expressions into new series; alerting rules evaluate a PromQL condition and fire when it holds for a for: duration.

The scope of this page is the metric layer only: what to expose, how to scrape it, and how to shape it into signals. The two deep-dive guides below take individual pieces further — the exact postgres_exporter custom queries for replication lag and monitoring PgBouncer pool saturation. Dashboarding those metrics in Grafana and wiring Alertmanager routes are covered separately under the parent section.

Mechanism Deep-Dive

The scrape topology

The single most important design decision is where the exporters run. Co-locate each exporter with the node it observes: postgres_exporter on each replica host, pgbouncer_exporter on each pooler host. This binds the exporter’s reachability to the node’s reachability, so when the host or database dies, the exporter’s up metric goes to 0 and you get an unambiguous signal. A single central exporter reaching out to the whole fleet cannot tell a dead database from a dead network path, and its own failure blinds you to every node simultaneously.

The metrics that actually matter

For a PostgreSQL replica, four series carry the load. pg_last_wal_replay_lsn and its receive-side counterpart give you the WAL position, and the byte difference against the primary’s pg_current_wal_lsn is your queue depth — how much data is in flight but not yet applied. pg_last_xact_replay_timestamp gives a wall-clock timestamp of the last replayed transaction, and now() minus that value is the intuitive “seconds behind” number. The critical caveat: on an idle primary, no transactions replay, so that timestamp stops advancing and the derived lag climbs even though the replica is perfectly caught up. The fix is a heartbeat table — a row on the primary updated every second — whose replayed timestamp on the replica gives true wall-clock lag regardless of organic write activity. The exact query definitions live in the postgres_exporter custom queries guide.

For MySQL, mysqld_exporter exposes mysql_slave_status_seconds_behind_source (older builds label it _seconds_behind_master), read from SHOW REPLICA STATUS. It has the same idle-primary weakness plus a worse one: it reports NULL — surfaced as the exporter dropping the series or emitting a sentinel — whenever the SQL apply thread is stopped, so absent() handling is mandatory.

For PgBouncer, pgbouncer_exporter scrapes SHOW POOLS, SHOW STATS, and SHOW LISTS, exposing pgbouncer_pools_client_waiting_connections, pgbouncer_pools_server_active_connections, and pgbouncer_pools_client_maxwait_seconds. Those three tell you whether the pool in front of your replicas is saturated long before the database itself shows stress.

Annotated scrape configuration

# prometheus.yml — scrape the replica fleet
global:
  scrape_interval: 5s          # short interval: lag changes fast under load
  scrape_timeout: 4s           # must be < scrape_interval or scrapes overlap
  evaluation_interval: 15s     # how often recording/alerting rules run

scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ['prim-1:9187']
        labels: {role: primary, cluster: orders, az: use1-az1}
      - targets: ['repl-a:9187']
        labels: {role: replica, cluster: orders, az: use1-az1}
      - targets: ['repl-b:9187']
        labels: {role: replica, cluster: orders, az: use1-az2}
    metric_relabel_configs:
      # Drop a noisy high-cardinality series at ingest
      - source_labels: [__name__]
        regex: 'pg_stat_activity_.*_by_query'
        action: drop

  - job_name: pgbouncer
    static_configs:
      - targets: ['pgb-1:9127']
        labels: {role: pooler, cluster: orders, az: use1-az1}

Every target carries role, cluster, and az labels so a single PromQL expression can slice the whole fleet. Those labels are low-cardinality and fixed — the opposite of the query-text or client-IP labels that blow up a TSDB.

Trade-Off Comparison Table

The pattern is consistent: the convenient built-in metrics all freeze on an idle primary, and only the heartbeat delta plus the up metric give you a signal you can safely route and alert on. Use the byte-lag and timestamp metrics for texture; anchor your alerts on the heartbeat and liveness.

Configuration Runbook

PostgreSQL — load custom lag queries

Point postgres_exporter at a query file and connect it with a least-privilege role. The full queries.yaml and the heartbeat-table DDL are in the custom queries deep-dive; the wiring is:

# systemd unit fragment / container args
DATA_SOURCE_NAME='postgresql://exporter@localhost:5432/postgres?sslmode=disable'
postgres_exporter \
  --extend.query-path=/etc/postgres_exporter/queries.yaml \
  --web.listen-address=:9187

-- Least-privilege role for the exporter (run on primary; replicates to replicas)
CREATE USER exporter;
GRANT pg_monitor TO exporter;          -- read stat views without superuser

MySQL — enable the replica status collector

# my.cnf for the exporter's own account, or pass via DATA_SOURCE_NAME
[client]
user=exporter
host=127.0.0.1

mysqld_exporter \
  --collect.slave_status \
  --web.listen-address=:9104

-- MySQL exporter grants
CREATE USER 'exporter'@'localhost' IDENTIFIED BY '***' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

PgBouncer — scrape the admin database

# pgbouncer.ini — expose the virtual admin DB to the exporter's stats user
[pgbouncer]
stats_users = stats_collector

pgbouncer_exporter \
  --pgBouncer.connectionString="postgres://stats_collector@localhost:6432/pgbouncer?sslmode=disable"

Pool-saturation PromQL and alert thresholds are worked through in the PgBouncer saturation guide, and the pooler itself is configured per configuring PgBouncer for read-only connection pools.

Recording rules — pre-compute the expensive parts

Recording rules evaluate once per group and write a new series, so dashboards and alerts read a cheap pre-aggregated value instead of re-running a heavy expression per query.

# rules/replica_recording.yml
groups:
  - name: replica_lag_recording
    interval: 15s
    rules:
      # Wall-clock lag from the heartbeat table, per replica
      - record: replica:heartbeat_lag_seconds
        expr: time() - pg_replication_heartbeat_timestamp_seconds

      # WAL apply rate in bytes/sec, smoothed over 5m
      - record: replica:wal_apply_bytes:rate5m
        expr: rate(pg_last_wal_replay_lsn_bytes[5m])

      # Pool saturation ratio: active servers / pool size, per pool
      - record: pool:server_saturation_ratio
        expr: |
          pgbouncer_pools_server_active_connections
            / clamp_min(pgbouncer_pools_server_maxwait_connections + pgbouncer_pools_server_active_connections, 1)

Monitoring and Alerting Signals

Anchor alerts on signals that stay honest when the system is quiet or broken. The three that matter most:

# rules/replica_alerts.yml
groups:
  - name: replica_health
    rules:
      # 1. Exporter or node down — the guard against "zero lag" from a dead scrape
      - alert: ReplicaExporterDown
        expr: up{job="postgres", role="replica"} == 0
        for: 1m
        labels: {severity: critical}
        annotations:
          summary: "postgres_exporter on {{ $labels.instance }} is down — lag is UNKNOWN, not zero"

      # 2. True wall-clock lag from the heartbeat, immune to idle primary
      - alert: ReplicaHeartbeatLagHigh
        expr: replica:heartbeat_lag_seconds > 5
        for: 2m
        labels: {severity: warning}
        annotations:
          summary: "Replica {{ $labels.instance }} heartbeat lag > 5s"

      # 3. The metric itself vanished — SQL thread stopped or query errored
      - alert: ReplicaLagMetricAbsent
        expr: absent(replica:heartbeat_lag_seconds{cluster="orders"})
        for: 2m
        labels: {severity: critical}
        annotations:
          summary: "No lag metric for cluster orders — replication may be stopped"

The up == 0 alert is non-negotiable. Without it, a crashed exporter leaves stale samples that read as a healthy replica until they age out, and by then the routing layer has been sending reads to a dead node for minutes. Pair every lag alert with an absent() alert so a series that stops being reported is treated as a failure, not silence. These thresholds feed the same SLO logic described under detecting and handling replication lag in real-time.

Failure Modes and Recovery Steps

1. Zero lag on an idle primary

Symptom: pg_last_xact_replay_timestamp delta shows growing lag, or flatlines at zero, during a low-traffic window; it corrects the instant real writes resume.

Recovery:

1. Stop trusting the timestamp-delta metric for alerting.
2. Deploy the heartbeat table (a 1 Hz UPDATE on the primary) and switch alerts to replica:heartbeat_lag_seconds.
3. Keep the LSN-byte metric only for capacity texture, never for routing.

2. Dead exporter reported as healthy

Symptom: Dashboards show a stable, low lag value that never changes; the underlying replica is actually degraded or gone.

Recovery:

1. Confirm with up{instance="repl-b:9187"} == 0 in the Prometheus expression browser.
2. Ensure the ReplicaExporterDown alert exists and routes to a pager, not just a dashboard.
3. Set honor_timestamps defaults and rely on staleness handling — do not extend scrape_timeout past scrape_interval, which would mask flapping scrapes.

3. MySQL seconds_behind_source goes NULL

Symptom: The mysql_slave_status_seconds_behind_source series disappears; alerts stop firing because the condition can no longer be evaluated.

Recovery:

1. Add an absent() alert for the series so its disappearance pages.
2. On the replica, run SHOW REPLICA STATUS and check Last_SQL_Error and Replica_SQL_Running.
3. Resolve the apply-thread error, then confirm the series returns before silencing the alert.

4. Cardinality explosion collapses Prometheus

Symptom: Prometheus memory climbs, scrape durations rise, and query latency degrades after adding a new exporter or custom query.

Recovery:

1. Run topk(20, count by (__name__)({__name__=~".+"})) to find the offending metric name.
2. Add a metric_relabel_configs drop rule at the scrape for the high-cardinality series.
3. Audit custom queries for label values sourced from query text, client address, or LSN — replace them with fixed role/cluster/az labels.

Guides in This Section

The two pages below go deep on the pieces this overview introduced.

PostgreSQL lag queries

postgres_exporter Custom Queries for Replication Lag The exact queries.yaml to expose replay-LSN-byte lag and heartbeat-seconds lag, how to wire --extend.query-path, the alerting PromQL, and why pg_last_xact_replay_timestamp() returns NULL on an idle primary along with the heartbeat-table fix.

PgBouncer pool saturation

Monitoring PgBouncer Pool Saturation with Prometheus Scraping SHOW POOLS, SHOW STATS, and SHOW LISTS through pgbouncer_exporter, the key series (cl_waiting, sv_active, maxwait), PromQL for saturation ratio and wait latency, and the alert thresholds that catch backpressure before clients time out.

FAQ

Related

← Back to Monitoring & Observability for Read Replicas

• postgres_exporter Custom Queries for Replication Lag — the exact queries.yaml, the heartbeat-table fix, and the alerting PromQL for PostgreSQL replay lag.
• Monitoring PgBouncer Pool Saturation with Prometheus — scraping SHOW POOLS/STATS/LISTS and the saturation-ratio PromQL that catches pooler backpressure.
• Configuring PgBouncer for Read-Only Connection Pools — the pooler configuration whose metrics you scrape here.
• Detecting and Handling Replication Lag in Real-Time — how the lag signals built here feed SLO thresholds and circuit-breaker routing decisions.
• Monitoring & Observability for Read Replicas — the parent section covering Grafana dashboards, Alertmanager routing, and query-performance analysis across the fleet.