Monitoring PostgreSQL

PostgreSQL is recognised by its server process, and the port it listens on is read from that process, so a cluster on a non-default port needs no configuration. What it does need is one credential: a role the agent can log in as to read pg_stat_database. You store it once on the host with a single command; until then the check says exactly what is missing.

How PostgreSQL is recognised

The signed definition matches a host that runs a process named postgres. The agent looks up the TCP port that process listens on and connects to it locally through the database driver, never through psql. Every minute it runs two queries:

• one over pg_stat_database, summing backends, commits, rollbacks, buffer hits, disk block reads and deadlocks across every database of the cluster;
• one over the server settings, which derives the log directory when logging_collector is on: an absolute log_directory is taken as is, a relative one is joined to data_directory. With the collector off there is no directory to report, and the file side of log collection stays idle. The same query records log_timezone, the zone the server stamps its log lines in.

A companion definition for logs runs every 30 seconds. It reads the newest file in the log directory, follows the switch to the next dated file to the last line of the old one, and reads timestamps in the server's own log zone, so a server logging in UTC on a host set to local time lands every line at the right minute.

What is collected

Connections. The sum of numbackends across databases, the one value here that goes up and down.

Transactions and cache. Commits, rollbacks, buffer cache hits and disk block reads, as the cumulative counters the server keeps, plus the rates the agent derives from them on the host between two readings: commits and disk block reads per second, rollbacks per minute. The hit ratio is not derived for you; charting hits against reads gives it.

Deadlocks. The cumulative deadlock count across all databases, and deadlocks per minute derived from it. The rate is what the built-in rule watches.

Facts shown on the host page: the log directory, when the logging collector is on.

Signal	Metric
Backends across all databases	svc.postgres.connections
Transactions committed / rolled back	svc.postgres.commits, svc.postgres.rollbacks
Commits per second, rollbacks per minute	svc.postgres.commits_rate, svc.postgres.rollbacks_per_min
Buffer cache hits / disk block reads	svc.postgres.blks_hit, svc.postgres.blks_read
Disk block reads per second	svc.postgres.blks_read_rate
Deadlocks, total and per minute	svc.postgres.deadlocks, svc.postgres.deadlocks_per_min

Names and units are in the metrics reference.

Logs. The server log ships by default at notice level and above, from two sources: the journal entries of the postgres and postgresql identifiers, and the newest .log file in the discovered log directory, where the level is read from the severity word the server prints after the process id (LOG, WARNING, ERROR, FATAL). A cluster that logs to stderr under systemd is covered by the journal side alone. See Logs.

Alerts out of the box

Rule	Fires when	Severity	Ships
PostgreSQL keeps hitting deadlocks	half a deadlock per minute or more, sustained 10 minutes	warning	auto

The rule arms itself as soon as the deadlock counter flows and watches the per-minute rate, so it fires while deadlocks keep happening and closes when they stop; a threshold on the cumulative total would fire once and never clear. One deadlock an hour is an application quirk, one every two minutes for ten minutes is two transactions fighting over the same rows in a loop. Every other metric above can drive a custom rule in the console under Alerting, rules; svc.postgres.connections is the natural candidate for a plain threshold. Thresholds and hysteresis are in the rules catalog.

Credentials

The definition names the credential pg-monitor, and it is required: there is no socket fallback as with MySQL. Create a role that can connect to the postgres database, read pg_stat_database and read the logging_collector, log_directory and data_directory settings, then store it on the host:

sudo wakora secret set pg-monitor --user wakora

The command prompts for the password. It is stored encrypted on the host, bound to that machine, and never sent anywhere; the probes pick it up on their next cycle. Until the secret exists, the checks fail with a message naming the missing secret and this command. The connection goes over TCP to the local port through the driver, so the password never appears in a process list. See Secrets stay local.

When a path is not where PostgreSQL says it is

The log directory comes from the running server's settings. To point the agent elsewhere:

sudo wakora --set postgres.log-dir=/var/log/postgresql

The newest .log file in that directory is tailed. Keys are listed in Agent configuration.

Limitations

• Everything is summed across databases; there is no per-database breakdown.
• Replication lag, per-table statistics, vacuum and bloat are not collected.
• No metric flows until the pg-monitor credential is stored; the definition does not try the local socket first.
• The file half of log collection needs logging_collector = on; with it off, only the journal is read.
• The server version is not recorded as a fact.
• One server per host: a second cluster on another port is not followed separately.

Related

• Monitoring MySQL and MariaDB, Monitoring Redis, Monitoring MongoDB
• Monitoring for hosting providers on the main site