Alerting on Cross-Shard Replication Lag with Prometheus

This walkthrough builds end-to-end lag alerting for a sharded PostgreSQL fleet — custom postgres_exporter queries, shard-labelled metrics, recording rules, two-tier alerts, and per-shard routing — implementing the design from replication lag & capacity alerting within the Partition Monitoring & Failover Automation section.

Prerequisites

Step 1 — Write the custom lag query file

postgres_exporter’s built-in metrics do not include LSN-diff byte lag or an idle-safe replay-time lag, so define both in a custom queries file. The primary-side query reads pg_stat_replication (one row per attached replica); the replica-side query computes its own staleness with the LSN-equality guard that prevents idle shards from reporting phantom lag. Deploy the same file to every host — each query no-ops harmlessly on the wrong role.

# /etc/postgres_exporter/lag_queries.yml
pg_replication_primary:
  query: |
    SELECT application_name AS replica,
           pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)::float8 AS lag_bytes,
           COALESCE(EXTRACT(EPOCH FROM replay_lag), 0)::float8        AS replay_lag_seconds,
           COALESCE(EXTRACT(EPOCH FROM flush_lag), 0)::float8         AS flush_lag_seconds
    FROM pg_stat_replication
  metrics:
    - replica:            { usage: "LABEL",  description: "Replica application_name" }
    - lag_bytes:          { usage: "GAUGE",  description: "WAL bytes not yet replayed" }
    - replay_lag_seconds: { usage: "GAUGE",  description: "Server-computed replay lag" }
    - flush_lag_seconds:  { usage: "GAUGE",  description: "Server-computed flush lag" }

pg_replication_replica:
  query: |
    SELECT CASE
             WHEN NOT pg_is_in_recovery() THEN 0
             WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
             ELSE GREATEST(0, EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()))
           END::float8 AS staleness_seconds
  metrics:
    - staleness_seconds: { usage: "GAUGE", description: "Idle-safe replica read staleness" }

Operational note: The CASE guard is the load-bearing part. Without the LSN-equality check, now() - pg_last_xact_replay_timestamp() climbs one second per second on any shard whose primary is momentarily idle, and the resulting false pages will train the on-call to ignore the alert that matters.

SRE tip: Keep this file in version control and deploy it via configuration management, not by hand. A shard whose exporter runs an older query file emits subtly different series — the fleet-wide max by (shard) in Step 4 then silently excludes it, which is invisible until the day that shard is the one lagging.

Step 2 — Run postgres_exporter on each shard

Run one exporter alongside each PostgreSQL instance (primary and replicas) as a systemd service. The shard identity is attached in Prometheus (Step 3), so the exporter itself stays identical everywhere — one unit file template for the entire fleet.

sudo tee /etc/systemd/system/postgres_exporter.service > /dev/null <<'EOF'
[Unit]
Description=Prometheus postgres_exporter
After=network-online.target postgresql.service

[Service]
User=postgres_exporter
EnvironmentFile=/etc/postgres_exporter/env
ExecStart=/usr/local/bin/postgres_exporter \
  --extend.query-path=/etc/postgres_exporter/lag_queries.yml \
  --web.listen-address=:9187
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

echo 'DATA_SOURCE_NAME=postgresql://monitor:CHANGE_ME@localhost:5432/postgres?sslmode=disable' \
  | sudo tee /etc/postgres_exporter/env > /dev/null
sudo chmod 600 /etc/postgres_exporter/env
sudo systemctl daemon-reload
sudo systemctl enable --now postgres_exporter

Operational note: The exporter connects over localhost, so a network partition between shards and the monitoring host takes out the scrape, not the measurement — Prometheus sees the target go down, which Step 5 turns into its own alert instead of a silent gap.

SRE tip: Grant the monitoring role pg_monitor and nothing more. It provides read access to pg_stat_replication and the recovery functions without superuser, and a leaked exporter DSN then exposes statistics rather than data.

Step 3 — Label every target with its shard in the scrape config

Attach shard and role labels at scrape time so every series from a host carries its fleet coordinates. Static configuration keeps the example self-contained; with service discovery you would derive the same labels via relabel_configs from instance tags instead.

# prometheus.yml (fragment)
global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - /etc/prometheus/rules/replication_lag.yml

scrape_configs:
  - job_name: postgres_shards
    static_configs:
      - targets: ["10.0.1.10:9187"]
        labels: { shard: "s01", role: "primary" }
      - targets: ["10.0.1.11:9187"]
        labels: { shard: "s01", role: "replica" }
      - targets: ["10.0.2.10:9187"]
        labels: { shard: "s02", role: "primary" }
      - targets: ["10.0.2.11:9187"]
        labels: { shard: "s02", role: "replica" }

alerting:
  alertmanagers:
    - static_configs:
        - targets: ["alertmanager:9093"]

Operational note: Never encode the shard in the metric name (pg_lag_s01_seconds). Labels let one alert expression, one dashboard query, and one routing tree cover the whole fleet; per-shard metric names require editing every rule each time you reshard.

SRE tip: Keep the shard label values zero-padded and stable (s01, not 1). Alertmanager regex routes and dashboard sorting both depend on lexical order, and renaming label values later breaks series continuity in long-range dashboards.

Step 4 — Add recording rules for max lag per shard

Failover safety is bounded by the worst replica in each shard, so precompute shard-level maxima. Alerts, dashboards, and any failover gate then read one authoritative series instead of re-deriving it with slightly different expressions.

# /etc/prometheus/rules/replication_lag.yml (part 1)
groups:
  - name: replication-lag-recording
    interval: 15s
    rules:
      - record: shard:replication_lag_seconds:max
        expr: >
          max by (shard) (
            pg_replication_primary_replay_lag_seconds
              or
            pg_replication_replica_staleness_seconds
          )
      - record: shard:replication_lag_bytes:max
        expr: max by (shard) (pg_replication_primary_lag_bytes)
      - record: shard:replication_replicas_connected:count
        expr: count by (shard) (pg_replication_primary_replay_lag_seconds)

Operational note: The or merges the primary-side and replica-side views, so the recorded series survives either vantage point disappearing — if the primary dies, the replica-side staleness gauge keeps the shard’s series alive exactly when you need it most.

SRE tip: Point everything downstream — Grafana panels, the failover orchestrator’s gate, capacity reports — at these recorded series. When alerting and promotion logic read different lag expressions, they eventually disagree during an incident, and that disagreement is how stale replicas get promoted.

Step 5 — Write the two-tier alert rules

Two severities with different thresholds and for: windows: a 30-second warning that tolerates brief bursts, and a five-minute critical that means the failover data-loss window is open. A third rule fires when a shard’s lag series disappears entirely — the failure mode that otherwise looks like health.

# /etc/prometheus/rules/replication_lag.yml (part 2)
  - name: replication-lag-alerts
    rules:
      - alert: ShardReplicationLagWarning
        expr: shard:replication_lag_seconds:max > 30
        for: 5m
        labels: { severity: warning }
        annotations:
          summary: "Shard {{ $labels.shard }} replication lag above 30s"
          description: "Worst replica on {{ $labels.shard }} is {{ $value | humanize }}s behind (5m sustained). Cross-shard reads touching this shard are stale."

      - alert: ShardReplicationLagCritical
        expr: shard:replication_lag_seconds:max > 300
        for: 2m
        labels: { severity: critical }
        annotations:
          summary: "Shard {{ $labels.shard }} lag exceeds failover RPO gate"
          description: "Worst replica on {{ $labels.shard }} is {{ $value | humanize }}s behind. Promotion of this replica would lose that write window; failover automation should be gated."

      - alert: ShardLagMetricsAbsent
        expr: >
          count by (shard) (shard:replication_lag_seconds:max) unless
          count by (shard) (shard:replication_lag_seconds:max offset 10m)
            or
          (shard:replication_lag_seconds:max offset 10m unless shard:replication_lag_seconds:max)
        for: 5m
        labels: { severity: critical }
        annotations:
          summary: "Shard {{ $labels.shard }} stopped reporting lag metrics"
          description: "Lag series for {{ $labels.shard }} vanished — exporter down, replica detached, or scrape blocked. Treat as a lag incident, not missing data."

Operational note: The for: windows do the burst-filtering: a bulk load that pushes lag to 45 seconds for three minutes never fires the warning, while a replay stall that holds 30+ seconds for five minutes does. Tighten for: before tightening thresholds when you need faster detection.

SRE tip: Keep the critical threshold at or below the promotion gate configured in your failover orchestration tooling (for Patroni, maximum_lag_on_failover). The page should always arrive before the orchestrator starts refusing to fail over — never the other way around.

Step 6 — Route and group alerts by shard in Alertmanager

Group notifications by shard so one degraded shard produces one grouped notification, and route shard ranges to their owning teams with a warning-severity branch that files tickets instead of paging.

# alertmanager.yml
route:
  receiver: dba-fallback
  group_by: ["alertname", "shard"]
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 4h
  routes:
    - matchers: ['severity = "warning"']
      receiver: dba-ticket-queue
    - matchers: ['shard =~ "s(0[1-9]|1[0-2])"']   # s01-s12: payments team
      receiver: payments-oncall
    - matchers: ['shard =~ "s(1[3-9]|2[0-4])"']   # s13-s24: marketplace team
      receiver: marketplace-oncall

receivers:
  - name: dba-fallback
    webhook_configs: [{ url: "http://oncall-bridge:8080/dba" }]
  - name: dba-ticket-queue
    webhook_configs: [{ url: "http://ticketer:8080/create" }]
  - name: payments-oncall
    webhook_configs: [{ url: "http://oncall-bridge:8080/payments" }]
  - name: marketplace-oncall
    webhook_configs: [{ url: "http://oncall-bridge:8080/marketplace" }]

Operational note: Route order matters — the warning matcher sits first so warnings from any shard become tickets, and only criticals fall through to the team pager routes. Reversing the order pages teams for every 31-second blip.

SRE tip: For planned bulk loads, create a silence scoped to shard="s07" (plus the alertname) rather than silencing the whole rule. A fleet-wide silence during one shard’s maintenance window is how a second shard’s genuine incident goes unseen.

Verification

Confirm the exporter emits the custom metrics on any shard host:

curl -s http://10.0.1.11:9187/metrics | grep -E '^pg_replication_(primary|replica)'

Expected output on a replica (primary hosts show the pg_replication_primary_* family instead):

pg_replication_replica_staleness_seconds 0.42

Validate the rule files before reloading Prometheus:

promtool check rules /etc/prometheus/rules/replication_lag.yml
promtool check config /etc/prometheus/prometheus.yml

Expected output:

Checking /etc/prometheus/rules/replication_lag.yml
  SUCCESS: 6 rules found

Finally, prove the pipeline end to end: pause replay on one replica with SELECT pg_wal_replay_pause();, watch shard:replication_lag_seconds:max{shard="s01"} climb in the Prometheus expression browser, confirm ShardReplicationLagWarning fires after its five-minute for: window and lands with the right team in Alertmanager, then resume with SELECT pg_wal_replay_resume(); and verify the alert resolves.

Failure mode table

Failure mode Root cause SRE mitigation
Idle shard pages with steadily climbing lag Replay-timestamp arithmetic without the LSN-equality guard; the last commit ages on a write-idle primary while the replica is fully caught up Use the Step 1 CASE guard; on shards that idle for hours, add a 1 Hz heartbeat write on the primary so replay timestamps stay fresh
Lag alert never fires although replication is broken Replica detached, so the primary-side pg_stat_replication row — and the metric — vanished; dashboards show “no data,” alerts see nothing above threshold Keep the ShardLagMetricsAbsent rule and the shard:replication_replicas_connected:count series; alert when connected replica count drops below the expected number per shard
One shard silently missing from every fleet aggregate Scrape target for that shard lost its shard label after a config edit, so max by (shard) excludes it Alert on count(count by (shard) (shard:replication_lag_seconds:max)) < 24 (expected shard count); lint scrape configs in CI with promtool check config

FAQ

Why does my lag metric climb forever on an idle shard?

Because now() - pg_last_xact_replay_timestamp() measures the age of the last replayed commit, and on a shard with no new writes that commit ages while the replica is perfectly caught up. The custom query in Step 1 guards against this by comparing pg_last_wal_receive_lsn() with pg_last_wal_replay_lsn() first and reporting zero when they are equal. If a shard can be idle for long stretches, add a once-per-second heartbeat write on the primary so there is always a fresh transaction to replay.

Can one postgres_exporter instance scrape all shards?

Yes — recent postgres_exporter versions support multi-target scraping where Prometheus passes the shard DSN as a target parameter, which reduces deployment footprint. The sidecar-per-shard pattern shown here is still preferable for lag monitoring: the exporter fails independently per shard, a network partition between a central exporter host and one shard cannot blank out that shard’s metrics unnoticed, and replica-side staleness queries run local to each replica. If you do centralize, keep one absent-metrics alert per shard so a missing target is treated as an incident.

Do recording rules delay alert firing?

Marginally. Recording rules are evaluated on the rule evaluation interval (15 seconds in this walkthrough), so an alert built on a recorded series sees data at most one evaluation interval older than the raw scrape. Against for: windows of two to five minutes, that latency is negligible — and the payoff is that the alert expression, the dashboard, and the failover gate all read the same shard-level series instead of three subtly different queries.

Back to Replication Lag Capacity Alerting