OnCallReadyStart learning

Runtimes and data Lesson 27.58 · PostgreSQL Operations · 18 min read

Monitoring PostgreSQL

In plain words

A car dashboard does not wait for the engine to explode before telling you something is wrong: it shows the fuel level, the temperature, a warning light when the oil is low. Monitoring PostgreSQL is building that dashboard for the database: how full the seats are, how old the oldest open transaction is, whether the backup machine is still working, how far behind the copy is.

Alerts are the warning lights, and the good ones come on while there is still time to pull over.

Why this lesson exists

Every incident in this chapter had a number that would have warned you hours or days before the page: connections creeping toward max_connections, the age of the oldest transaction, dead tuples climbing, an archiver failing, a slot retaining WAL, replication lag. Monitoring PostgreSQL means turning those numbers into time series and alerts before the outage. This lesson wires postgres_exporter into Prometheus and writes the alert rules every PostgreSQL server should have.

What you need to know already: every view this chapter used (pg_stat_activity, pg_stat_replication, pg_replication_slots, pg_stat_archiver, pg_stat_user_tables), and what an alert is (Chapter 0). Prometheus comes in here only as far as this lab needs it.

Later (Ch 43 and Ch 44): Prometheus in depth - PromQL, recording rules, exporters, service discovery - and the alerting pipeline behind the rules (Alertmanager, routing, silences).

The words you need first

  • Prometheus - a monitoring server that scrapes: every few seconds it fetches a plain-text /metrics page over HTTP from each target listed in /etc/prometheus/prometheus.yml (one scrape job per kind of target) and stores every number on it as a time series with labels. You ask questions with its query language, PromQL, over the HTTP API on port 9090.
  • Exporter - a small program that reads a system's own numbers (here, PostgreSQL's statistics views) and serves them as a /metrics page for Prometheus to scrape.
  • Alert rule - a PromQL expression in a rule file. While it returns a result the alert is pending; once that has lasted the rule's for: duration it is firing. promtool check config and promtool check rules validate the files before you reload Prometheus.
  • postgres_exporter - the Prometheus exporter for PostgreSQL (prometheus-community): it connects to the server, runs catalog queries on every scrape and exposes the results on :9187/metrics.
  • pg_monitor - a built-in role that may read every statistics view and setting a monitoring tool needs, without being a superuser.
  • pg_up - 1 if the exporter could connect on its last scrape. The first alert.
  • Saturation - how full a fixed resource is: connections / max_connections, disk, XID age / 2^31. Alert on saturation trends, not only on "down".

The exporter

$ sudo apt install -y prometheus-postgres-exporter > /dev/null 2>&1
$ systemctl is-active prometheus-postgres-exporter
active
$ grep -v '^#' /etc/default/prometheus-postgres-exporter | grep -v '^$'
ARGS=""
DATA_SOURCE_NAME="user=postgres host=/run/postgresql dbname=postgres"
$ curl -s localhost:9187/metrics | grep -E '^pg_up|^pg_settings_max_connections'
pg_up 0
pg_settings_max_connections{server="localhost:5432"} 100

Out of the box it connects as postgres over the socket. Give it its own least-privilege role instead - a monitoring tool with superuser rights is a liability:

$ sudo -u postgres psql -c "create role postgres_exporter login password 'exp-lab-pw' in role pg_monitor"
CREATE ROLE
$ echo 'host    all             postgres_exporter 127.0.0.1/32            scram-sha-256' | sudo tee -a /etc/postgresql/18/main/pg_hba.conf > /dev/null
$ sudo systemctl reload postgresql@18-main
$ sudo sed -i 's#^DATA_SOURCE_NAME=.*#DATA_SOURCE_NAME="postgresql://postgres_exporter:exp-lab-pw@127.0.0.1:5432/postgres?sslmode=disable"#' /etc/default/prometheus-postgres-exporter
$ sudo systemctl restart prometheus-postgres-exporter
$ curl -s localhost:9187/metrics | grep -E '^pg_up|^pg_stat_activity_count' | head -n 5
pg_up 1
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="disabled",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="fastpath function call",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="idle",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="idle in transaction",usename="",wait_event="",wait_event_type=""} 0

(The password sits in /etc/default/... - keep that file 0640 root:prometheus or use the exporter's --config.file with credentials from a secret store.)

Into Prometheus

$ printf "\n  - job_name: 'postgres'\n    static_configs:\n      - targets: ['localhost:9187']\n        labels:\n          cluster: 'orders-db'\n" | sudo tee -a /etc/prometheus/prometheus.yml > /dev/null
$ promtool check config /etc/prometheus/prometheus.yml
Checking /etc/prometheus/prometheus.yml
  SUCCESS: 2 rule files found
 SUCCESS: /etc/prometheus/prometheus.yml is valid prometheus config file syntax

Checking /etc/prometheus/rules/node.yml
  SUCCESS: 1 rules found

Checking /etc/prometheus/rules/orders.yml
  SUCCESS: 1 rules found
$ sudo systemctl reload prometheus
$ curl -s 'localhost:9090/api/v1/query' --data-urlencode 'query=up{job="postgres"}' | jq -r '.data.result[] | "\(.metric.instance) up=\(.value[1])"'
localhost:9187 up=1

(--data-urlencode because a PromQL selector's {...} in a curl URL is curl's own globbing syntax: curl 'host/api/v1/query?query=up{job="x"}' sends a different query than you typed.)

The series that matter, and what each is for:

metricquestion it answers
pg_upcan we connect at all?
sum(pg_stat_activity_count) / pg_settings_max_connectionshow close are we to too many clients?
pg_stat_activity_count{state="idle in transaction"}the lock-and-bloat danger (lessons 6, 7)
pg_stat_activity_max_tx_durationthe oldest open transaction, in seconds - the horizon holder
pg_locks_count{mode="accessexclusivelock"}a migration or VACUUM FULL holding everything
pg_replication_lag_seconds (on a replica)how stale reads are, how much a failover loses
pg_replication_slot_slot_is_active, pg_replication_slots_pg_wal_lsn_diffa slot holding WAL for a gone replica (disk-full risk)
rate(pg_stat_archiver_failed_count[5m])the archive is broken (PITR is silently gone)
pg_stat_user_tables_n_dead_tupvacuum not keeping up
pg_database_size_bytesgrowth, for capacity planning
rate(pg_stat_database_xact_commit[5m]), rate(pg_stat_database_deadlocks[5m])throughput, application bugs

Plus, from node_exporter: free space on the data and WAL filesystems (node_filesystem_avail_bytes with predict_linear, which extends the trend to say when the disk fills), and the I/O of the data disk.

$ curl -s 'localhost:9090/api/v1/query' --data-urlencode 'query=sum(pg_stat_activity_count) / max(pg_settings_max_connections)' | jq -r '.data.result[0].value[1]'
0.01

Alert rules

A rule file that covers this chapter's incidents (thresholds are starting points - tune them to your traffic):

$ sudo tee /etc/prometheus/rules/postgres.yml > /dev/null <<'EOF'
groups:
  - name: postgres
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels: {severity: page}
        annotations:
          summary: "PostgreSQL {{ $labels.instance }} is not accepting connections"
      - alert: PostgresConnectionsNearLimit
        expr: sum by (instance) (pg_stat_activity_count) / max by (instance) (pg_settings_max_connections) > 0.8
        for: 5m
        labels: {severity: page}
        annotations:
          summary: "{{ $labels.instance }}: {{ $value | humanizePercentage }} of max_connections in use"
      - alert: PostgresLongTransaction
        expr: max by (instance) (pg_stat_activity_max_tx_duration) > 600
        for: 2m
        labels: {severity: ticket}
        annotations:
          summary: "a transaction on {{ $labels.instance }} has been open for {{ $value | humanizeDuration }}"
      - alert: PostgresReplicationSlotInactive
        expr: pg_replication_slot_slot_is_active == 0
        for: 30m
        labels: {severity: ticket}
        annotations:
          summary: "slot {{ $labels.slot_name }} is inactive and retains WAL"
      - alert: PostgresWalArchiveFailing
        expr: increase(pg_stat_archiver_failed_count[15m]) > 0
        labels: {severity: page}
        annotations:
          summary: "WAL archiving on {{ $labels.instance }} is failing - PITR is broken and pg_wal grows"
      - alert: PostgresReplicationLag
        expr: pg_replication_lag_seconds > 300
        for: 5m
        labels: {severity: ticket}
EOF
$ promtool check rules /etc/prometheus/rules/postgres.yml
Checking /etc/prometheus/rules/postgres.yml
  SUCCESS: 6 rules found
$ sudo systemctl reload prometheus

Now break something on purpose and watch the alert go pending -> firing (for: 1m):

$ sudo systemctl stop postgresql@18-main
$ curl -s localhost:9090/api/v1/alerts | jq -r '.data.alerts[] | select(.labels.alertname | startswith("Postgres")) | "\(.labels.alertname) \(.state)"'
PostgresDown firing
$ sudo systemctl start postgresql@18-main

Things that make these alerts good rather than noisy:

  • for: on everything that can blip; none on things that are always bad (a failing archive).
  • Severity by impact: down and connection exhaustion page someone; a long transaction or an inactive slot is a ticket for working hours - unless the trend says the disk fills tonight.
  • Alert on the exporter itself too (up{job="postgres"} == 0): when the exporter dies, every other rule goes quiet - which looks exactly like "all fine".
  • A dashboard next to the alerts (connections by state and application, TPS, lag, top tables by dead tuples) - the community dashboards published for postgres_exporter are a good start.

In an interview: "What would you alert on for a PostgreSQL server?" - availability (pg_up, and the exporter's own up), saturation (connections vs max_connections, disk with predict_linear, XID age), the silent killers (oldest transaction / idle in transaction, inactive replication slots retaining WAL, failing WAL archiving), replication lag, and errors/deadlocks - with for: durations and paging only on user impact.

What you can do now

  • Install postgres_exporter with its own pg_monitor role and scrape it from Prometheus.
  • Name the PostgreSQL metrics behind each incident in this chapter.
  • Write and test alert rules for down, connections, long transactions, slots, archiving and lag.
  • Make alerts quiet when they should be, and loud when the monitoring itself breaks.

Why it helps

Every incident in this chapter had a number that rose for hours or days first: connections toward max_connections, the oldest transaction's age, dead tuples, archiver failures, WAL held by a slot, replication lag. postgres_exporter turns them into Prometheus series and alert rules turn them into pages before the outage.

The lesson also covers what makes alerts trustworthy: for: durations, severities by impact, and alerting on the exporter itself so a dead exporter does not look like "all fine".

On the blog

Commands in this lesson

apt systemctl grep curl psql echo sed printf promtool tee

FAQ

Why give the exporter its own role?

Connecting as postgres means a superuser password sits in a file read by a network-facing service. The built-in pg_monitor role allows reading every statistics view and setting the exporter needs and nothing more. Combine it with a narrow pg_hba.conf line from the exporter's address and keep the credentials file readable only by the exporter.

What is the first alert to write?

PostgresDown on pg_up == 0 for a minute, plus an alert on the exporter target itself being down (up == 0 for the job). Without the second, a crashed exporter makes every PostgreSQL alert go silent, which looks exactly like a healthy database. Then connection saturation, long transactions, slots, archiving and lag.

Which metric shows the vacuum horizon problem?

The age of the oldest open transaction - pg_stat_activity_max_tx_duration in postgres_exporter - and the number of sessions idle in transaction. A long-running or forgotten transaction holds back VACUUM on every table, so an alert at about ten minutes catches it long before the bloat shows up in query times.

Why use for: in alert rules?

To avoid paging on blips. Connections can spike for a few seconds during a deploy, and lag jumps during a large write. A for: of a few minutes makes the condition persist before the alert fires. Do not use it for conditions that are always bad, such as failed WAL archiving, where every minute counts.

Does this work with managed PostgreSQL?

Yes. postgres_exporter connects to RDS, Cloud SQL or Flexible Server like any client, with a role that has pg_monitor. The provider also has its own metrics (CPU, storage, IOPS, connections, replication lag). Combine both: provider metrics for the machine and storage, exporter metrics for what happens inside the database.

In an interview Mid

What would you monitor and alert on for a PostgreSQL server?

With postgres_exporter (a pg_monitor role, not superuser) scraped by a metrics server: availability - pg_up and the exporter target's own up; saturation - client connections as a share of max_connections (alert around 80%), disk on the data and WAL volumes with predict_linear, XID age; the silent killers - the oldest transaction and idle-in-transaction sessions, inactive replication slots and their retained WAL, a failing WAL archive; and replication lag in seconds and bytes. Use for: durations, page only on user impact, ticket the rest, and keep a dashboard next to the alerts.

Also asked: Why should the monitoring role not be a superuser? · How do you avoid alerts going silent when the exporter dies? · How would you alert before the database runs out of connections?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.