PostgreSQL Query

Run your own SELECT query on a PostgreSQL server and get alerted on its row count or execution time.

The PostgreSQL Query check runs your own SQL query on a PostgreSQL server and evaluates either the number of rows returned or the execution time. Use it, for example, to watch for failed jobs, stuck orders or slow queries. The check runs on the agent device you assign it to and connects from there over TCP to the host you enter. It runs on Windows, Linux and macOS (each in x64 and Arm64), every 5 minutes by default. The database server itself does not need to be an agent device; any agent device that can reach it over the network is enough.

Prerequisites

A login role and a host line in pg_hba.conf that admits this role from the agent device's address.
CONNECT on the database, USAGE on the schema and SELECT on the tables your query reads.

To create the monitoring role, its read rights and the pg_hba.conf entry, see Set up the database monitoring account.

Steps

Go to Devices and open the device.
Click the Checks tab → Add Check → PostgreSQL Query.

Settings

Field	Type	Required	Default	Description
Host	Text	Yes	—	Hostname or IP address of the PostgreSQL server, as seen from the agent device
Port	Integer	No	5432	TCP port of the server. The check replaces values outside 1–65535 with 5432
Database	Text	No	postgres	Database the query runs in. An empty field is treated as `postgres`
Username	Text	Yes	—	Role the check signs in with
Password	Text	No	—	Password of the role
SSL mode	Select	No	Prefer	`Prefer` encrypts when the server offers it; `Require` insists on an encrypted connection; `Disable` connects unencrypted
Query (read-only SELECT)	Text	Yes	—	The statement to run. It runs inside a `READ ONLY` transaction that is rolled back afterwards
Alert on	Select	No	Row count	Row count evaluates the number of rows returned, Execution time (ms) the query duration in milliseconds
Alert when below threshold	Yes/No	No	No	Reverses the comparison: alert when the value reaches or falls below the thresholds
Warning threshold	Number	No	1	Value at which a warning is reported
Error threshold	Number	No	1	Value at which a critical result is reported

The check counts rows, not values. SELECT count(*) FROM jobs WHERE failed always returns exactly one row and is therefore always evaluated as 1, no matter how many jobs failed. Write the query so that it returns one row per problem, for example SELECT id FROM jobs WHERE failed.

Results

The check compares the chosen value (row count or execution time) with both thresholds. Thresholds also trigger on equality.

Situation	Result
Value at or above Error threshold (with Alert when below threshold: at or below)	Critical
Value at or above Warning threshold (with Alert when below threshold: at or below)	Warning
Otherwise	OK
Sign-in rejected, server unreachable, query error or timeout	Critical
No host or no query entered	Warning
Unexpected error in the check	Failure

With the defaults (both thresholds 1, mode Row count), the check is therefore Critical as soon as the query returns at least one row, and OK otherwise. If the two thresholds are in the wrong order, the check swaps them before evaluation. Connection setup and query have a fixed 15-second timeout.

Metrics collected

Number of rows returned
Query execution time in milliseconds (excluding connection setup)

Notes

The READ ONLY transaction is a safety net, not a replacement for a read-only role. Grant the monitoring role SELECT only.
The host is part of the check configuration. If you assign the check to many devices through a config package, each of them runs the same query against the same server. Assign it to a single device instead.

Was this helpful?

Still need help? Ask the team