Deploying postgres_exporter for Replication Metrics

Deploying postgres_exporter for replication observability means giving it a read-only pg_monitor role and a queries.yaml that turns pg_replication_slots and pg_stat_replication into labeled Prometheus gauges, and getting that right is what lets the Monitoring & Alerting pipeline page on a stuck slot before pg_wal fills the disk. The stock exporter ships no slot-lag metrics, so a deployment that skips the custom query file produces a green dashboard that is blind to the one failure that takes a primary offline.

This guide covers the exporter side specifically: the least-privilege role, the exact custom queries and the semantics of each gauge they emit, the --extend.query-path wiring, and running the process as a container or a systemd unit with the DSN sourced from a secret. It stops where Prometheus begins — dashboards and alert rules are separate concerns built on the series defined here.

Gauge Semantics #

The custom query file is the contract between PostgreSQL and every downstream panel and alert. Each column marked GAUGE becomes a time series; each column marked LABEL becomes a dimension on it. The table below fixes what each emitted gauge means, so a dashboard author and an alert author read the same number the same way. Compute byte counts with pg_wal_lsn_diff on the publisher rather than shipping raw LSNs, because a Prometheus expression cannot subtract two pg_lsn values.

Two of these carry a subtlety worth stating outright. active flips to 0 the instant a consumer disconnects, but retained_bytes keeps climbing only while writers are busy — so a low-traffic database can show active = 0 for minutes before the byte count moves, which is exactly why both gauges must exist rather than one standing in for the other. And reserved is a boolean flattening of the four-valued wal_status; if you need to distinguish extended from lost on a dashboard, emit wal_status as a label instead of collapsing it. The underlying cursor mechanics that make restart_lsn freeze are covered in the replication slot types reference.

Export the cap alongside the slot gauges rather than hard-coding it downstream. A separate one-row query reads max_slot_wal_keep_size from pg_settings and emits it as slot_wal_keep_bytes, so a Grafana panel or alert rule can divide retained_bytes by a live value that tracks any ALTER SYSTEM change. Hard-coding the cap in a PromQL expression means every retention change silently invalidates the threshold — the fraction reads against a number that no longer matches the server.

# Second query block in queries.yaml — exports the cap as its own gauge.
pg_slot_wal_keep:
  query: >
    SELECT (setting::bigint * 1024) AS slot_wal_keep_bytes
    FROM pg_settings WHERE name = 'max_slot_wal_keep_size'
  metrics:
    - slot_wal_keep_bytes: {usage: "GAUGE", description: "max_slot_wal_keep_size in bytes"}

Diagnostic Patterns #

Before wiring the exporter into Prometheus, confirm the gauges it will emit match reality. Run the custom query by hand as the exporter role — if it errors or returns nothing, the exporter will silently expose an empty series, which is worse than no metric because it reads as healthy.

-- Run as the exporter role to prove pg_monitor grants enough and the SQL is valid.
SELECT slot_name,
       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
WHERE slot_type = 'logical';
-- Expect one row per logical slot. Zero rows = no slots yet, not a permission error.

Then scrape the exporter’s endpoint directly and grep for the custom metrics. If they are absent, --extend.query-path did not load — usually a YAML indentation error or a path the container cannot see.

# Confirm the custom gauges are actually exposed before Prometheus depends on them.
curl -s localhost:9187/metrics | grep -E 'pg_replication_slot_lag_(retained_bytes|active|reserved)'
# ALERT-worthy if empty: the query file failed to load and every panel will read stale-green.

Finally, verify the exporter role holds exactly pg_monitor and nothing broader — an over-grant here is a common security regression, and the correct boundary is spelled out in security boundaries and permissions.

-- The exporter must NOT hold REPLICATION or table SELECT. pg_monitor is sufficient.
SELECT r.rolname, r.rolsuper, r.rolreplication,
       pg_has_role('exporter', 'pg_monitor', 'MEMBER') AS has_pg_monitor
FROM pg_roles r WHERE r.rolname = 'exporter';

Safe Deployment Sequence #

Roll the exporter out so a misconfiguration fails visibly and is one command from revert.

1. Create the least-privilege role. Grant pg_monitor and fence its sessions so a hung scrape cannot pin WAL or hold a snapshot open.

CREATE ROLE exporter LOGIN PASSWORD 'from-a-secret-store';
GRANT pg_monitor TO exporter;                       -- PG 10+
ALTER ROLE exporter SET statement_timeout = '10s';
ALTER ROLE exporter SET idle_in_transaction_session_timeout = '15s';

2. Stage the query file. Place queries.yaml where the process can read it and validate the YAML before starting the exporter, so a parse error surfaces now rather than as a missing metric later.

# /etc/postgres_exporter/queries.yaml
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"}
    - slot_type:         {usage: "LABEL"}
    - 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"}

3. Run the process with the DSN from a secret. Never pass the password as a flag — it would leak into ps and shell history. Source DATA_SOURCE_NAME from the environment. As a container:

docker run -d --name pg-exporter -p 9187:9187 \
  -e DATA_SOURCE_NAME='postgresql://exporter@pub.internal:5432/app?sslmode=verify-full' \
  -e PG_EXPORTER_EXTEND_QUERY_PATH=/etc/pg/queries.yaml \
  -v /etc/postgres_exporter/queries.yaml:/etc/pg/queries.yaml:ro \
  quay.io/prometheuscommunity/postgres-exporter

Or as a systemd unit, with the DSN kept in a mode-0600 EnvironmentFile:

# /etc/systemd/system/postgres_exporter.service
[Service]
EnvironmentFile=/etc/postgres_exporter/env      # DATA_SOURCE_NAME=... , chmod 600
ExecStart=/usr/local/bin/postgres_exporter \
  --extend.query-path=/etc/postgres_exporter/queries.yaml \
  --web.listen-address=:9187
User=postgres_exporter
Restart=on-failure

4. Register the scrape target and verify. Add the exporter to Prometheus, reload, and confirm the target is up with the custom series present.

# prometheus.yml — scrape_configs entry
- job_name: postgres
  scrape_interval: 15s
  static_configs:
    - targets: ['pg-primary.internal:9187']
      labels: {cluster: 'analytics-prod', role: 'primary'}

One exporter can watch several databases on the same server by scraping additional connection strings, but keep the deployment topology explicit: one exporter process per PostgreSQL host, co-located so a network partition between them shows up as up == 0 rather than distorted lag numbers. Running a single central exporter that reaches across the network to many hosts couples every database’s monitoring to one process, and its death blinds the whole fleet at once. Always require TLS on the connection — sslmode=verify-full with a pinned CA — because the exporter authenticates with a real password and reads statistics that reveal write patterns; a sslmode=disable DSN leaks both across the wire.

Revert is a stop and a target removal: systemctl stop postgres_exporter (or docker rm -f pg-exporter) and delete the scrape entry. Because the exporter holds no state and only reads, removing it never affects the replication stream — the slot and its restart_lsn are untouched.

Pipeline Integration #

The exporter is one input to a broader observability picture, and it deliberately does not do everything. Its flat query model runs each SQL statement on every scrape with no cross-scrape state, which is ideal for slot and sender gauges but wrong for anything needing a specific transaction isolation, a computed rate, or a cadence below one second. Those belong in the bespoke asyncpg collector described in async monitoring integration, which publishes to the same Prometheus so both feed one dashboard set — run the exporter for the common surface and the collector only for the gaps, never duplicating a gauge across the two.

For a Python ETL team, the exporter also removes a temptation: do not add slot-lag scraping into the consumer process itself. A consumer that queries pg_replication_slots on its own hot path competes with the change stream and can open an idle in transaction session that pins WAL — the exact failure it was meant to detect. Keep observation off the data path in a separate process, and have the consumer export only what it uniquely knows, such as its last applied LSN and batch latency, joining them to the exporter’s slot series in Grafana by slot name. On failover, point the exporter at the same service endpoint the consumers resolve so it always scrapes the current primary; a fixed hostname will silently scrape a demoted standby and report zero slots.

Authoritative references #

• PostgreSQL: pg_replication_slots — the columns (restart_lsn, confirmed_flush_lsn, wal_status, active) the custom query reads.
• PostgreSQL: The Statistics Collector — pg_stat_replication — the sender lag columns exported as gauges.
• PostgreSQL: Predefined Roles (pg_monitor) — the least-privilege grant the exporter role relies on.

Related #

• Building Grafana dashboards for replication lag — turn these gauges into panels with per-slot template variables.
• Writing alert rules for slot and WAL thresholds — author Prometheus rules against the series defined here.
• Monitoring & Alerting — the parent guide this exporter deployment feeds.