Monitor your PostgreSQL databases

The platform runs PostgreSQL through CloudNativePG (CNPG). A CNPG Cluster is a set of PostgreSQL instances — one primary and some number of standbys — managed by an operator that handles failover, backups, and WAL archiving for you.

Every instance exports Prometheus metrics, and the platform scrapes them into the same metrics store as everything else. This page gives you the queries behind the platform's own CloudNativePG dashboard, so you can rebuild any part of it in whatever tooling you already use, and set thresholds that suit your service rather than ours.

What you'll learn

• Which metrics a CNPG instance exposes, and under which labels
• How to group instances back into the cluster they belong to, which the labels do not do for you
• PromQL for availability, replication, restarts, error rate, connections, storage, and backups
• The three values that read as healthy when they are not
• Threshold suggestions you can start from

What CNPG exposes

Each instance pod runs an exporter on port 9187. Every metric it emits is prefixed cnpg_. The platform's scrape gives each series these labels:

Label	Value
namespace	the namespace your cluster runs in
pod	the instance pod, CLUSTER_NAME-1, CLUSTER_NAME-2, and so on
container	postgres
job	NAMESPACE/postgres

Replace CLUSTER_NAME with the name of your CNPG cluster and NAMESPACE with its namespace. The examples below use mydb and myapp; substitute your own.

The label that does not mean what it says

cluster is the Kubernetes cluster, not your database cluster. The CNPG exporter does publish its own cluster label holding the database cluster name, but the platform's metrics pipeline overwrites it with the name of the Kubernetes cluster the database runs in. Every CNPG series in your metrics store therefore carries the same cluster value.

This matters because most CloudNativePG examples you will find elsewhere — including the upstream Grafana dashboard — group by cluster and expect the database name. Run those unchanged here and they select nothing, or silently fold every database together.

There is no label carrying the database cluster name. Derive it from the pod name, which CNPG always forms as CLUSTER_NAME-INSTANCE_NUMBER:

label_replace(cnpg_collector_up, "cnpg_cluster", "$1", "pod", "(.+)-[0-9]+")

That gives you a cnpg_cluster label to group by:

count by (namespace, cnpg_cluster) (
  label_replace(cnpg_collector_up, "cnpg_cluster", "$1", "pod", "(.+)-[0-9]+") == 1
)

If you only ever query one database, you can skip this and select on namespace and pod directly, as the rest of this page does for readability.

Empty is not zero

An empty result and a result of zero mean different things, and confusing them is the most common way a monitoring setup tells you everything is fine while it is not:

• Zero means the thing was measured and did not happen. No transactions rolled back.
• Empty means the thing was not measured. The instance might be gone, the scrape might be broken, or the metrics pipeline might have stopped.

Most dashboard tools draw both as an unremarkable panel. Treat an empty result as Unknown and render it as such. Three metrics on this page break the rule in the other direction, reporting a zero that is not a measurement; each is called out where it appears.

Is the database up?

cnpg_collector_up is 1 when the exporter reached PostgreSQL on that instance and 0 when it could not:

cnpg_collector_up{namespace="myapp"}

An instance that is gone produces no series at all rather than a 0, so check for absence explicitly:

absent(cnpg_collector_up{namespace="myapp"})

Counting ready instances tells you whether the cluster still has the replicas it should:

count(cnpg_collector_up{namespace="myapp"} == 1)

Compare that against the instance count you configured. The metrics do not carry the desired count, so a three-instance cluster reduced to two looks identical to a two-instance cluster that is perfectly healthy. If that distinction matters to you, assert the expected number in your alert rather than deriving it.

Scrape freshness catches a stalled pipeline that has not aged out yet:

time() - timestamp(cnpg_collector_up{namespace="myapp"})

Which instance is the primary?

Exactly one instance should be out of recovery. That one is the primary:

cnpg_pg_replication_in_recovery{namespace="myapp"} == 0

Two results mean a split brain; none means every instance is a standby and no instance is accepting writes. Both are urgent, and neither shows up in cnpg_collector_up, which reads 1 throughout.

The number of standbys streaming from an instance is only meaningful on the primary, because nothing streams from a standby:

cnpg_pg_replication_streaming_replicas{namespace="myapp"}

A 0 on a standby is the healthy, expected value. Read this metric on the primary only.

Replication lag, in seconds:

cnpg_pg_replication_lag{namespace="myapp"}

Restarts

An instance that keeps restarting is failing in a way that availability alone will not show, because each restart is followed by a healthy scrape:

sum by (pod) (
  increase(kube_pod_container_status_restarts_total{
    namespace="myapp", pod=~"mydb-.*", container="postgres"}[15m])
)

Error rate

PostgreSQL has no single error-rate counter. The closest honest proxy is the share of transactions that roll back:

100 * sum(rate(cnpg_pg_stat_database_xact_rollback{namespace="myapp"}[5m]))
  / clamp_min(
      sum(rate(cnpg_pg_stat_database_xact_commit{namespace="myapp"}[5m]))
      + sum(rate(cnpg_pg_stat_database_xact_rollback{namespace="myapp"}[5m])),
      1)

clamp_min keeps an idle database from dividing by zero and returning NaN. It also means a completely idle database reports 0, not "unknown" — so read this alongside a throughput signal rather than on its own.

Rollbacks are not automatically failures: an application that uses transactions for optimistic concurrency will roll back routinely. Baseline your own normal before setting a threshold on it.

Deadlocks are unambiguous, and rare enough that any sustained rate deserves attention:

sum(rate(cnpg_pg_stat_database_deadlocks{namespace="myapp"}[5m]))

If the exporter itself fails to collect, it says so, and everything above becomes untrustworthy while this is non-zero:

cnpg_collector_last_collection_error{namespace="myapp"}

Connections

Connection exhaustion presents as application errors with a database that looks fine:

100 * sum by (pod) (cnpg_backends_total{namespace="myapp"})
  / sum by (pod) (cnpg_pg_settings_setting{namespace="myapp", name="max_connections"})

The longest-running transaction catches a session holding locks or blocking vacuum:

max by (pod) (cnpg_backends_max_tx_duration_seconds{namespace="myapp"})

Storage and transaction ID age

Volume usage, as a percentage of each instance's persistent volume:

100 * max by (persistentvolumeclaim) (
  1 - kubelet_volume_stats_available_bytes{namespace="myapp"}
    / kubelet_volume_stats_capacity_bytes{namespace="myapp"}
)

A database that fills its volume stops accepting writes, and recovering from that is considerably harder than preventing it.

Transaction ID age is the slow-moving one that nobody watches until it is an emergency. PostgreSQL must freeze old transaction IDs before the counter wraps; if age approaches two billion, the database shuts down to protect itself:

max by (pod) (cnpg_pg_database_xid_age{namespace="myapp"})

Normal values sit in the low millions. Sustained growth means autovacuum is not keeping up.

Backups and WAL archiving

WAL archiving is the part of backup that fails quietly. A failing archiver does not affect a running database at all — it only means you cannot restore:

sum(rate(cnpg_pg_stat_archiver_failed_count{namespace="myapp"}[15m]))

max(cnpg_pg_stat_archiver_seconds_since_last_archival{namespace="myapp"})

The backup age query needs a guard, and without it you will page on every cluster. cnpg_collector_last_available_backup_timestamp reports 0 on an instance with no backup — and on every instance that does not hold the backup, even when the cluster has one. Subtracting that from time() yields the seconds since the Unix epoch, which presents as a backup roughly fifty-six years old:

time() - max by (namespace) (
  cnpg_collector_last_available_backup_timestamp{namespace="myapp"} > 0
)

The > 0 filter drops the placeholder zeros. As a result this query returns nothing for a cluster that has never been backed up, and that emptiness is the signal — it is a different condition from "the backup is old", and worth alerting on separately.

Is the operator healthy?

The CNPG operator reconciles your clusters: failover, backups, and configuration changes all flow through it. Your databases keep serving while it is down; what stops is change.

up{job="cnpg-system/manager"}

sum by (controller) (
  rate(controller_runtime_reconcile_errors_total{namespace="cnpg-system"}[15m])
)

Suggested thresholds

Starting points, not rules. Every one of them depends on what your service does, and the right value is the one you arrive at after watching your own baseline.

Signal	Healthy	Watch	Act
Instance availability	all instances at 1	any instance at 0 for 1–5 min	any instance at 0 for over 5 min, or the series absent
Primary count	exactly 1	—	0 or more than 1, immediately
Restarts	0–1 in 15 min	2–3 in 15 min	4 or more in 15 min, or a crash loop
Rollback share	below your baseline	1–5%	above 5% for 5 min
Deadlocks	0	any sustained rate	rising
Replication lag	under 10s	10–60s	over 60s
Connections used	under 70%	70–85%	over 85%
Volume used	under 75%	75–85%	over 85%
Transaction ID age	under 200M	200M–500M	over 500M
WAL archive failures	0	any	sustained
Backup age	under 24h	24–48h	over 48h, or no backup at all

Two notes on building alerts from these. First, alert on absence as well as value: a rule that only fires on a bad number will stay silent when the metric disappears, which is the worse condition. Second, the platform does not ship CNPG alert rules, so nothing here fires unless you configure it in your own tooling.

Metric reference

The metrics used on this page. An instance exposes considerably more; these are the ones worth starting from.

Metric	Type	Notes
cnpg_collector_up	Gauge	1 when the exporter reached PostgreSQL. Absent, not 0, when the instance is gone.
cnpg_collector_last_collection_error	Gauge	Non-zero means every other cnpg_ metric on that instance is suspect.
cnpg_pg_replication_in_recovery	Gauge	0 on the primary, 1 on a standby.
cnpg_pg_replication_streaming_replicas	Gauge	Standbys streaming from this instance. Meaningful on the primary only.
cnpg_pg_replication_lag	Gauge	Replication delay in seconds.
cnpg_pg_stat_database_xact_commit	Counter	Committed transactions, by database.
cnpg_pg_stat_database_xact_rollback	Counter	Rolled-back transactions, by database.
cnpg_pg_stat_database_deadlocks	Counter	Deadlocks detected, by database.
cnpg_pg_database_xid_age	Gauge	Transaction ID age. Watch for sustained growth.
cnpg_pg_database_size_bytes	Gauge	Database size, by database.
cnpg_backends_total	Gauge	Current backends, by state and user.
cnpg_backends_max_tx_duration_seconds	Gauge	Longest running transaction.
cnpg_pg_settings_setting	Gauge	PostgreSQL settings as numbers, selected by name.
cnpg_pg_stat_archiver_failed_count	Counter	Failed WAL archive attempts.
cnpg_pg_stat_archiver_seconds_since_last_archival	Gauge	Time since the last WAL segment was archived.
cnpg_collector_last_available_backup_timestamp	Gauge	Unix seconds of the newest backup. 0 where there is none — always filter > 0.
cnpg_collector_postgres_version	Gauge	PostgreSQL version as a number, such as 16.15.

Related

• Monitoring — the other platform components you can watch from your own tooling.
• Observability — instrumenting your own applications with logs, metrics, traces, and health checks.
• CloudNativePG documentation — the full exporter metric list and how to add custom queries.