postgres_exporter Custom Queries for Replication Lag

Problem statement: postgres_exporter ships built-in replication metrics, but the lag number they produce goes to zero on an idle primary and gives you bytes when you need seconds — so you need custom queries.yaml definitions that expose both a byte-level apply backlog and a heartbeat-based wall-clock lag that stays honest around the clock.

Symptom Identification

You are reading this because one of these is happening:

  • The pg_last_xact_replay_timestamp delta on a replica climbs steadily overnight, then snaps back to near-zero the moment morning traffic resumes — classic idle-primary artifact, not real lag.
  • postgres_exporter emits no lag series at all on a freshly built replica, because pg_last_xact_replay_timestamp() is returning NULL and the exporter drops the row.
  • Your dashboards show “seconds behind” but capacity planning needs “bytes queued,” or vice versa, and the built-in collector gives you only one.
  • Lag alerts never fire during a genuine replication stall because the metric simply vanished instead of crossing a threshold.

Each of these traces back to the same root: the default replication metrics are derived from signals that are only meaningful while the primary is actively committing transactions.

Root Cause Analysis

pg_last_xact_replay_timestamp() returns the commit timestamp of the most recent transaction the replica has replayed. On a primary with no write activity, there is no new transaction to replay, so the timestamp does not advance. Compute now() - pg_last_xact_replay_timestamp() and you get a number that grows linearly with idle time — pure artifact. On a brand-new replica that has replayed nothing since startup, the function returns NULL outright, and postgres_exporter silently drops any metric row whose value is NULL. Either way, the convenient metric fails exactly when you need a trustworthy baseline.

The byte-level metrics do not have the idle problem, but they answer a different question. pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn() are monotonic WAL positions; their difference is the number of bytes the replica has received but not yet applied — the local apply backlog. That is the correct signal for detecting a replica that is receiving WAL faster than it can replay, but it says nothing about wall-clock staleness, because you cannot convert bytes to seconds without knowing the instantaneous apply rate.

The fix that resolves both problems is a heartbeat table: a single row on the primary whose timestamp column is updated once per second. Because the update is itself a committed transaction, it guarantees a continuous stream of WAL for the replica to replay, so pg_last_xact_replay_timestamp() always has a fresh value — and the difference between now() and the replayed heartbeat timestamp is true wall-clock lag, independent of organic write volume. This is the same heartbeat approach the replica health and lag overview recommends as the anchor for routing and SLO alerts.

Step-by-Step Resolution

Step 1: Create the heartbeat table and writer

Create a tiny table on the primary — it replicates to every replica automatically through the normal WAL stream.

sql
-- Run on the PRIMARY; replicates to all standbys
CREATE TABLE IF NOT EXISTS monitoring.replication_heartbeat (
    id         smallint PRIMARY KEY DEFAULT 1,
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    CONSTRAINT single_row CHECK (id = 1)
);
INSERT INTO monitoring.replication_heartbeat (id) VALUES (1)
  ON CONFLICT (id) DO NOTHING;

Run a writer that updates it once per second. A tiny cron-free loop under systemd is enough:

bash
# heartbeat-writer.sh — runs on the primary only
while true; do
  psql -qtAX -c "UPDATE monitoring.replication_heartbeat
                 SET updated_at = clock_timestamp() WHERE id = 1;"
  sleep 1
done

Inline verification: on a replica, SELECT now() - updated_at FROM monitoring.replication_heartbeat; should return a small, steady interval that does not grow during quiet periods.

Step 2: Write the custom queries.yaml

Define two metric sets. The first computes local apply backlog in bytes; the second computes heartbeat wall-clock lag. Note the guard for the replica-only functions so the same file is safe to load on a primary.

yaml
# /etc/postgres_exporter/queries.yaml
pg_replication_backlog:
  query: |
    SELECT
      CASE WHEN pg_is_in_recovery()
           THEN pg_wal_lsn_diff(pg_last_wal_receive_lsn(),
                                pg_last_wal_replay_lsn())
           ELSE 0 END AS apply_backlog_bytes
  master: true
  metrics:
    - apply_backlog_bytes:
        usage: "GAUGE"
        description: "Bytes received but not yet replayed on this standby"

pg_replication_heartbeat:
  query: |
    SELECT
      EXTRACT(EPOCH FROM updated_at) AS timestamp_seconds,
      EXTRACT(EPOCH FROM (now() - updated_at)) AS lag_seconds
    FROM monitoring.replication_heartbeat
    WHERE id = 1
  master: true
  metrics:
    - timestamp_seconds:
        usage: "GAUGE"
        description: "Replayed heartbeat commit timestamp (epoch seconds)"
    - lag_seconds:
        usage: "GAUGE"
        description: "Wall-clock replication lag from the heartbeat row"

Inline verification: python -c "import yaml,sys;yaml.safe_load(open('/etc/postgres_exporter/queries.yaml'))" must exit cleanly — a YAML typo makes the exporter start with zero custom metrics and no loud error.

Step 3: Wire --extend.query-path and the role

Grant the exporter a least-privilege role and point it at the query file.

sql
-- On the PRIMARY (replicates to standbys)
CREATE USER exporter;
GRANT pg_monitor TO exporter;
GRANT USAGE ON SCHEMA monitoring TO exporter;
GRANT SELECT ON monitoring.replication_heartbeat TO exporter;
bash
export DATA_SOURCE_NAME='postgresql://exporter@localhost:5432/postgres?sslmode=disable'
postgres_exporter \
  --extend.query-path=/etc/postgres_exporter/queries.yaml \
  --web.listen-address=:9187

Inline verification: the exporter log line Parsed queries from /etc/postgres_exporter/queries.yaml (or absence of a parse error) confirms the file loaded.

Step 4: Confirm the series on /metrics

bash
curl -s localhost:9187/metrics | grep -E 'apply_backlog_bytes|heartbeat.*lag_seconds'

You should see something like:

text
pg_replication_backlog_apply_backlog_bytes 1.34217728e+08
pg_replication_heartbeat_lag_seconds 0.412
pg_replication_heartbeat_timestamp_seconds 1.7519990e+09

Inline verification: pg_replication_heartbeat_lag_seconds should be a fraction of a second on a healthy replica and must be present even during a write-free window — that is the whole point of the heartbeat.

Step 5: Add alerting PromQL

yaml
# rules/pg_replication.yml
groups:
  - name: pg_replication_lag
    rules:
      - alert: PostgresReplicaLagHigh
        expr: pg_replication_heartbeat_lag_seconds{role="replica"} > 5
        for: 2m
        labels: {severity: warning}
        annotations:
          summary: "Replica {{ $labels.instance }} heartbeat lag {{ $value | humanizeDuration }}"

      - alert: PostgresReplicaApplyBacklogLarge
        expr: pg_replication_backlog_apply_backlog_bytes{role="replica"} > 5e8
        for: 3m
        labels: {severity: warning}
        annotations:
          summary: "Replica {{ $labels.instance }} apply backlog > 500 MB"

      - alert: PostgresReplicaLagMetricAbsent
        expr: absent(pg_replication_heartbeat_lag_seconds{role="replica"})
        for: 2m
        labels: {severity: critical}
        annotations:
          summary: "Heartbeat lag metric missing — replay may be stopped"

Inline verification: in the Prometheus expression browser, pg_replication_heartbeat_lag_seconds should return one series per replica; the absent() alert should be inactive while they are present.

Configuration Snippet

A minimal, complete queries.yaml you can drop in and extend:

yaml
# /etc/postgres_exporter/queries.yaml — replication lag essentials
pg_replication_backlog:
  query: |
    SELECT CASE WHEN pg_is_in_recovery()
                THEN pg_wal_lsn_diff(pg_last_wal_receive_lsn(),
                                     pg_last_wal_replay_lsn())
                ELSE 0 END AS apply_backlog_bytes
  master: true
  metrics:
    - apply_backlog_bytes: {usage: "GAUGE", description: "Bytes received but not replayed"}

pg_replication_heartbeat:
  query: |
    SELECT EXTRACT(EPOCH FROM (now() - updated_at)) AS lag_seconds,
           EXTRACT(EPOCH FROM updated_at)           AS timestamp_seconds
    FROM monitoring.replication_heartbeat WHERE id = 1
  master: true
  metrics:
    - lag_seconds:       {usage: "GAUGE", description: "Heartbeat wall-clock lag (s)"}
    - timestamp_seconds: {usage: "GAUGE", description: "Replayed heartbeat epoch (s)"}

Verification and Rollback

Confirm both metrics behave under real conditions before trusting them:

sql
-- On the replica, compare exporter output against a direct read
SELECT pg_is_in_recovery() AS is_replica,
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS backlog_bytes,
       EXTRACT(EPOCH FROM (now() - updated_at)) AS heartbeat_lag_s
FROM monitoring.replication_heartbeat WHERE id = 1;

Deliberately induce lag to prove the metric tracks it: run a heavy write on the primary (INSERT ... SELECT generate_series(...)), or pause replay with SELECT pg_wal_replay_pause(); on the replica, and watch both series climb.

To roll back cleanly if a bad query file breaks the exporter:

bash
# Remove the extend path and restart with built-ins only
systemctl edit --full postgres_exporter   # drop --extend.query-path
systemctl restart postgres_exporter
# Then resume replay if you paused it during testing
psql -h replica -c "SELECT pg_wal_replay_resume();"

The heartbeat table and writer are harmless to leave in place; if you must remove them, DROP TABLE monitoring.replication_heartbeat; on the primary and stop the writer unit.

Edge Cases and Gotchas

The exporter drops NULL-valued rows silently

If you skip the heartbeat and expose now() - pg_last_xact_replay_timestamp() directly, a fresh replica that has replayed nothing returns NULL, and postgres_exporter omits the row entirely rather than emitting a zero or an error. Your alert then has no series to evaluate and stays green. Always pair any timestamp-derived lag with an absent() alert, and prefer the heartbeat, which cannot go NULL once the row exists.

master: true is required, not a mistake

In postgres_exporter’s queries.yaml, the master: true flag means “run this query against the primary connection” — which, confusingly, is the exporter’s single configured connection, whether that node is a primary or a replica. Setting it makes the query run on every node the exporter is deployed to. Omitting it can cause the query to be skipped in multi-target setups. The pg_is_in_recovery() guard inside the query is what makes it safe on both roles.

Byte lag on a cascading standby

On a replica that streams from another replica rather than the primary, pg_last_wal_receive_lsn() reflects WAL received from its upstream standby, not from the primary. The local receive - replay backlog is still valid for that node’s apply queue, but end-to-end lag from the true primary must be measured with the heartbeat timestamp, which propagates through the whole chain. Do not attempt to compare a cascading node’s LSN against the primary’s pg_current_wal_lsn() — they are on the same WAL timeline but the receive position lags by every intermediate hop. This mirrors the cascading-replication caveats in configuring PgBouncer for read-only connection pools.

FAQ

Why does pg_last_xact_replay_timestamp() return NULL on an idle primary?

That function reports the commit timestamp of the last transaction the replica replayed. If the primary has committed nothing since the replica started or since the last replay, there is no such transaction and the function returns NULL. It also stops advancing during any write-free window, so the derived lag climbs even though the replica is fully caught up. A heartbeat table updated every second guarantees a steady stream of transactions to replay, so the metric always has a fresh timestamp.

Should I measure lag in bytes or seconds?

Use both, for different jobs. Byte lag from pg_last_wal_replay_lsn against the primary’s current LSN tells you how much data is queued in flight, which is the right signal for capacity and burst detection. Heartbeat-seconds lag tells you actual wall-clock staleness, which is what routing and SLO alerts should key on. Byte lag cannot be converted to seconds without knowing the apply rate, and seconds lag hides how large the backlog is.

Does querying the primary’s LSN from a replica work?

No. A replica cannot call pg_current_wal_lsn() because it is not the write source; it exposes pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn() instead. Compute byte lag as receive minus replay on the replica itself for the local apply backlog, or compute sent minus replay from pg_stat_replication on the primary for the end-to-end delta. Do not try to join series across nodes inside a single custom query.

← Back to Prometheus Metrics for Replica Health and Lag