# Custom postgres_exporter queries (Alloy prometheus.exporter.postgres custom_queries_config_path).
# Metric name = <key>_<column>.
#
# pg_replication_lag_seconds / pg_replication_is_replica replace the upstream "replication" collector (disabled in
# config.alloy). The upstream formula reports now() - pg_last_xact_replay_timestamp() whenever receive LSN != replay LSN;
# right after a replica restart the receive LSN sits at the segment start below the replay LSN until new WAL arrives,
# so on an idle primary the lag grows without bound (observed 2026-10-08: 116 s -> 281 s, back to 0 after one
# transaction). Treating receive <= replay as "fully applied" removes that artifact; real lag (replay paused, slow
# apply under load) still shows because receive runs ahead of replay.
pg_replication:
  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 AS lag_seconds, CASE WHEN pg_is_in_recovery() THEN 1 ELSE 0 END AS is_replica"
  master: true
  metrics:
    - lag_seconds:
        usage: "GAUGE"
        description: "Replication lag behind the primary in seconds (0 when every received byte is replayed)"
    - is_replica:
        usage: "GAUGE"
        description: "1 when the server is in recovery (replica)"
# pg_is_wal_replay_paused (used by the DbReplicationLag rule, design 4.3): not in any upstream collector.
pg_is_wal_replay:
  query: "SELECT CASE WHEN pg_is_in_recovery() AND pg_is_wal_replay_paused() THEN 1 ELSE 0 END AS paused"
  master: true
  metrics:
    - paused:
        usage: "GAUGE"
        description: "1 when this server is a replica and WAL replay is paused"