CHECK_PGACTIVITY(1)

📦

version 2.10 · 5 versions

💅 CHECK_PGACTIVITY(1) — `check_pgactivity`

okay bestie, buckle up 💖 you're reading the manual page for check_pgactivity (yes, the one from the check_pgactivity manual, section 1) — it's the PostgreSQL plugin for Nagios, and it is so much. Let's go. 🌸

✨ NAME ✨

check_pgactivity — PostgreSQL plugin for Nagios 🐘👑

🦋 SYNOPSIS 🦋

check_pgactivity {-w|--warning THRESHOLD} {-c|--critical THRESHOLD} [-s|--service SERVICE ] [-h|--host HOST] [-U|--username ROLE] [-p|--port PORT] [-d|--dbname DATABASE] [-S|--dbservice SERVICE_NAME] [-P|--psql PATH] [--debug] [--status-file FILE] [--path PATH] [-t|--timeout TIMEOUT]
check_pgactivity [-l|--list]
check_pgactivity [--help]

🌈 DESCRIPTION 🌈

So basically, check_pgactivity was designed to monitor PostgreSQL clusters from Nagios. 🫶 It ships with a ton of options so you can measure and monitor all the useful performance metrics. it's giving observability, no cap ✨

💜 COMPATIBILITY 💜

Every service is available from a different PostgreSQL version, all the way back from 7.4, and it's documented below. 📜

Ideally, the psql client should be 8.4 at least, but lowkey it can be used with an older server. If your client is older, go peek at --psql and --old-psql for more info.

Please report any undocumented incompatibility. 🙏 fr fr, they wanna know.

🎀 THE OPTIONS 🎀

-s, --service SERVICE — the Nagios service to run. See the SERVICES section for what's available, or just hit --list for a short service + description list.
-h, --host HOST — database server host or socket directory (default: $PGHOST or "localhost").

See the CONNECTIONS section for more info.

-U, --username ROLE — database user name (default: $PGUSER or "postgres").

See the CONNECTIONS section for more info.

-p, --port PORT — database server port (default: $PGPORT or "5432").

See the CONNECTIONS section for more info.

-d, --dbname DATABASE — database name to connect to (default: $PGDATABASE or "template1").

WARNING! 💀 This is not necessarily one of the databases that will be checked. See --dbinclude and --dbexclude.

See the CONNECTIONS section for more info.

-S, --dbservice SERVICE_NAME — the connection service name from pg_service.conf to use.

See the CONNECTIONS section for more info.

--dbexclude REGEXP — some services automatically check all the databases of your cluster (note: that does not mean they always need to connect on all of them to check them though). --dbexclude excludes any database whose name matches the given Perl regular expression. Repeat this option as many times as you need. 🔁

See --dbinclude as well. If a database matches both dbexclude and dbinclude arguments, it is excluded.

--dbinclude REGEXP — some services automatically check all the databases of your cluster (note: that does not imply that they always need to connect to all of them though). Some always exclude the 'postgres' database and templates. --dbinclude checks ONLY databases whose names match the given Perl regular expression. Repeat this option as many times as you need. 🔁

See --dbexclude as well. If a database matches both dbexclude and dbinclude arguments, it is excluded.

-w, --warning THRESHOLD — the Warning threshold.
-c, --critical THRESHOLD — the Critical threshold.
-F, --format OUTPUT_FORMAT — the output format. The supported outputs are: binary, debug, human, nagios, nagios_strict, json and json_strict.

Using the binary format, the results are written in a binary file (using the perl module Storable) given in the argument --output. If no output is given, it defaults to the file check_pgactivity.out in the same directory as the script.

The nagios_strict and json_strict formats are equivalent to the nagios and json formats respectively. The only difference is that they enforce the units to follow the strict Nagios specs: B, c, s or %. Any unit absent from this list is dropped (Bps, Tps, etc). 👑

--tmpdir DIRECTORY — path to a directory where the script can create temporary files. The script relies on the system default temporary directory if possible.
-P, --psql FILE — path to the psql executable (default: "psql").

Because check_pgactivity uses psql -w, psql must be version 8.4 at least, but the server can be older. If your psql is older and does not support -w, look at the argument --old-psql.

--old-psql — stay compatible with psql 8.3 and below.

Can also be set by setting the environment variable CHECK_PGA_OLD_PSQL.

This option removes the -w argument from the psql argument list. This means that if your password-less authentication is not correctly set up, psql might either wait indefinitely for a password, effectively blocking check_pgactivity, or fail immediately with an explicit error message. 😭

--status-file PATH — path to the file where service status information is kept between successive calls. Default is to save a file called check_pgactivity.data in the same directory as the script.

Note that this file is protected from concurrent writes using a lock file located in the same directory, having the same name as the status file, but with the extension .lock. 🔒

On some platforms, network filesystems may not be supported correctly by the locking mechanism. See perldoc -f flock for more information.

--dump-status-file — dump the content of the status file and exit. This is useful for debugging purposes.
--dump-bin-file [PATH] — dump the content of the given binary file previously created using --format binary. If no path is given, it defaults to the file check_pgactivity.out in the same directory as the script.
-t, --timeout TIMEOUT — timeout (default: "30s"), as raw (in seconds) or as an interval. This timeout will be used as statement_timeout for psql and URL timeout for the minor_version service.
-l, --list — list available services. 📋
-V, --version — print version and exit.
--debug — print some debug messages.
-?, --help — show this help page. 🫶

💫 THRESHOLDS 💫

THRESHOLDS you pass as warning and critical values can be raw numbers, percentages, intervals or sizes. Each available service supports one or more formats (eg. a size and a percentage). 🌈

🍓 Percentage

If THRESHOLD is a percentage, the value should end with a % (no space). For instance: 95%.

🧚 Interval

If THRESHOLD is an interval, the following units are accepted (not case sensitive): s (second), m (minute), h (hour), d (day). You can use more than one unit per given value. If not set, the last unit is in seconds. For instance: "1h 55m 6" = "1h55m6s".

💎 Size

If THRESHOLD is a size, the following units are accepted (not case sensitive): b (Byte), kb (KB), mb (MB), gb (GB), tb (TB), pb (PB), eb (EB) or Zb (ZB). Only integers are accepted. Eg. 1.5MB will be refused, use 1500kB. 🙅

The factor between units is 1024 bytes. Eg. 1g = 1G = 1024*1024*1024.

🔌 CONNECTIONS 🔌

check_pgactivity allows two different connection specifications: by service, or by specifying values for host, user, port and database. Some services can run on multiple hosts, or need to connect to multiple hosts. ✨

You might specify one of the parameters below to connect to your PostgreSQL instance. If you don't, no connection parameters are given to psql: connection relies on binary defaults and environment.

The format for connection parameters is:

🌸 Parameter `--dbservice SERVICE_NAME`

Define a new host using the given service. Multiple hosts can be defined by listing multiple services separated by a comma. Eg.

--dbservice service1,service2

For more information about service definition, see: <https://www.postgresql.org/docs/current/libpq-pgservice.html>

🌟 Parameters `--host HOST`, `--port PORT`, `--user ROLE` or `--dbname DATABASE`

One parameter is enough to define a new host. Usual environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGSERVICE, PGPASSWORD) or default values are used for missing parameters.

As for usual PostgreSQL tools, there is no command line argument to set the password, to avoid exposing it. Use PGPASSWORD, .pgpass or a service file (recommended). 🔐

If multiple values are given, define as many hosts as maximum given values.

Values are associated by position. Eg.:

--host h1,h2 --port 5432,5433

Means "host=h1 port=5432" and "host=h2 port=5433".

If the number of values is different between parameters, any host missing a parameter will use the first given value for this parameter. Eg.:

--host h1,h2 --port 5433

Means: "host=h1 port=5433" and "host=h2 port=5433".

💫 Services are defined first

For instance:

--dbservice s1 --host h1 --port 5433

means: use "service=s1" and "host=h1 port=5433" in this order. If the service supports only one host, the second host is ignored.

🚫 Mutual exclusion between both methods

You can not overwrite services connections variables with parameters --host HOST, --port PORT, --user ROLE or --dbname DATABASE. periodt.

👑 SERVICES 👑

Descriptions and parameters of available services.

📁 archive_folder

Check if all archived WALs exist between the oldest and the latest WAL in the archive folder and make sure they are 16MB. The given folder must have archived files from ONE cluster. The version of PostgreSQL that created the archives is only checked on the last one, for performance consideration.

This service requires the argument --path on the command line to specify the archive folder path to check. Obviously, it must have access to this folder at the filesystem level: you may have to execute it on the archiving server rather than on the PostgreSQL instance. 🖥️

The optional argument --suffix defines the suffix of your archived WALs; this is useful for compressed WALs (eg. .gz, .bz2, ...). Default is no suffix.

This service needs to read the header of one of the archives to define how many segments a WAL owns. check_pgactivity automatically handles files with extensions .gz, .bz2, .xz, .zip or .7z using the following commands:

gzip -dc
bzip2 -dc
xz -dc
unzip -qqp
7z x -so

If needed, provide your own command that writes the uncompressed file to standard output with the --unarchiver argument. 🛠️

Optional argument --ignore-wal-size skips the WAL size check. This is useful if your archived WALs are compressed and check_pgactivity is unable to guess the original size. Here are the commands check_pgactivity uses to guess the original size of .gz, .xz or .zip files:

gzip -ql
xz -ql
unzip -qql

Default behaviour is to check the WALs size. 💖

Perfdata's serving two things, bestie: the number of archived WALs and the age of the most recent one. ✨

Critical and Warning are literally just defining the max age of the latest archived WAL, expressed as an interval (so like 5m or 300s). That's it, that's the whole vibe check. 💅

Required privileges: unprivileged role; the system user needs read access to archived WAL files.

Sample commands:

check_pgactivity -s archive_folder --path /path/to/archives -w 15m -c 30m
check_pgactivity -s archive_folder --path /path/to/archives --suffix .gz -w 15m -c 30m
check_pgactivity -s archive_folder --path /path/to/archives --ignore-wal-size --suffix .bz2 -w 15m -c 30m
check_pgactivity -s archive_folder --path /path/to/archives --unarchiver "unrar p" --ignore-wal-size --suffix .rar -w 15m -c 30m

🗄️ archiver (8.1+) 🗄️

Okay so this one is checking two things at once: is the archiver actually working properly, and how many WAL files are sitting there ✨ready to archive✨.

Perfdata returns the number of WAL files waiting to be archived. That's the tea. 🍵

Critical and Warning thresholds are optional — they apply to the number of files waiting to be archived, and they only accept a raw number of files. No percentages, no intervals, just a number, bestie.

Whatever the given threshold, a critical alert is raised if the archiver process did not archive the oldest waiting WAL to be archived since last call. That's the non-negotiable part — thresholds don't get to override it. 😤

Required privileges: superuser (<v10), <v10: superuser, v10: nonsuper user but output will lack perfdata oldest_ready_wal or superuser, v10+: grant execute on function pg_stat_file(text, bool).

🧹 autovacuum (8.1+) 🧹

Checking the autovacuum activity on the cluster. Like, is it doing its lil chores?? 🧼

Perfdata contains the age of oldest running autovacuum and the number of workers by type (VACUUM, VACUUM ANALYZE, ANALYZE, VACUUM FREEZE).

Thresholds, if any, are ignored. Yep. They just get tossed in the void. 🫠

Required privileges: unprivileged role.

🔌 backends (all) 🔌

Check the total number of connections in the PostgreSQL cluster — the whole headcount, fr fr.

Perfdata contains the number of connections per database.

Critical and Warning thresholds accept either a raw number or a percentage (eg. 80%). When a threshold is a percentage, it gets compared to the difference between the cluster parameters max_connections and superuser_reserved_connections. Math, but make it monitoring. ✨

Autovacuum workers and walsender process are ignored. So if you wanna check autovacuum, use the autovacuum service, okay bestie?

Required privileges: an unprivileged user only sees its own queries; a pg_monitor (10+) or superuser (<10) role is required to see all queries.

🚦 backends_status (8.2+) 🚦

Checks the status of all backends. Depending on your PostgreSQL version, the statuses are: idle, idle in transaction, idle in transaction (aborted) (>=9.0 only), fastpath function call, active, waiting for lock, undefined, disabled and insufficient privilege. The insufficient privilege one pops up when you're not allowed to see the statuses of other connections. Rude, but it's the rules. 💀

This service supports the argument --exclude REGEX to exclude queries matching the given regular expression.

You can use multiple --exclude REGEX arguments. Stack 'em. 🎀

Critical and Warning thresholds are optional. They accept a list of 'status_label=value' separated by a comma. Available labels are idle, idle_xact, aborted_xact, fastpath, active and waiting. Values are raw numbers or time units and empty lists are forbidden. Here's an example, bestie:

-w 'waiting=5,idle_xact=10' -c 'waiting=20,idle_xact=30,active=1d'

Perfdata contains the number of backends for each status and the oldest one for each of them, for 8.2+.

Heads up: the number of backends reported in the Nagios message includes excluded backends. So don't come for me when the count looks higher than your exclude list suggests. 😭

Required privileges: an unprivileged user only sees its own queries; a pg_monitor (10+) or superuser (<10) role is required to see all queries.

✅ checksum_errors (12+) ✅

Checks for data checksums error, reported in pg_stat_database. This is the "is my data actually intact" girlie. 🙈

This service requires that data checksums are enabled on the target instance. UNKNOWN will be returned if that's not the case.

Critical and Warning thresholds are optional. They only accept a raw number of checksums errors per database. If the thresholds are not provided, a default value of 1 will be used for both thresholds.

Checksums errors are CRITICAL issues, so it's highly recommended to keep the default threshold, as immediate action should be taken as soon as such a problem arises. Like, drop everything and go. 🚨

Perfdata contains the number of error per database.

Required privileges: unprivileged user.

🏷️ backup_label_age (8.1+) 🏷️

Checks the age of the backup label file. Iconic little file, honestly.

Perfdata returns the age of the backup_label file, -1 if not present.

Critical and Warning thresholds only accept an interval (eg. 1h30m25s).

Required privileges: grant execute on function pg_stat_file(text, boolean) (pg12+); unprivileged role (9.3+); superuser (<9.3)

✍️ bgwriter (8.3+) ✍️

Checks the percentage of pages written by backends since last check.

This service uses the status file (see --status-file parameter). 📁

Perfdata contains the ratio per second for each pg_stat_bgwriter counter since last execution. Units Nps for checkpoints, max written clean and fsyncs are the number of "events" per second.

Critical and Warning thresholds are optional. If set, they only accept a percentage.

Required privileges: unprivileged role.

🎈 btree_bloat 🎈

Estimates bloat on B-tree indexes. The "how puffy is this index" check, if you will. 🫧

Warning and critical thresholds accept a comma-separated list of either raw number (for a size), size (eg. 125M) or percentage. The thresholds apply to bloat size, not object size. If a percentage is given, the threshold will apply to the bloat size compared to the total index size. If multiple threshold values are passed, check_pgactivity will choose the largest (bloat size) value.

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded. Not up for discussion. 💅

It also supports a --exclude REGEX parameter to exclude relations matching a regular expression. The regular expression applies to "database.schema_name.relation_name". This enables you to filter either on a relation name for all schemas and databases, on a qualified named relation (schema + relation) for all databases or on a qualified named relation in only one database. So many options, so much power. 👑

You can use multiple --exclude REGEX parameters.

Perfdata will return the number of indexes of concern, by warning and critical threshold per database.

A list of the bloated indexes will be returned after the perfdata. This list contains the fully qualified bloated index name, the estimated bloat size, the index size and the bloat percentage. Receipts. 🧾

Required privileges: superuser (<10) able to log in all databases, or at least those in --dbinclude; superuser (<10); on PostgreSQL 10+, a user with the role pg_monitor suffices, provided that you grant SELECT on the system table pg_statistic to the pg_monitor role, in each database of the cluster: GRANT SELECT ON pg_statistic TO pg_monitor;

📊 session_stats (14+) 📊

Gathers miscellaneous session statistics. The little odds and ends of session lore. 🌸

This service uses the status file (see --status-file parameter).

Perfdata contains the session / active / idle-in-transaction times for each database since last call, as well as the number of sessions per second, and the number of sessions killed / abandoned / terminated by fatal errors. 😵

Required privileges: unprivileged role.

🔁 commit_ratio (all) 🔁

Checks the commit and rollback rate per second since last call.

This service uses the status file (see --status-file parameter).

Perfdata contains the commit rate, rollback rate, transaction rate and rollback ratio for each database since last call.

Critical and Warning thresholds are optional. They accept a list of comma separated 'label=value'. Available labels are rollbacks, rollback_rate and rollback_ratio, which will be compared to the number of rollbacks, the rollback rate and the rollback ratio of each database. Warning or critical will be raised if the reported value is greater than rollbacks, rollback_rate or rollback_ratio.

Required privileges: unprivileged role.

⚙️ configuration (8.0+) ⚙️

Checks the most important settings. The main-character settings, honestly. 👑

Warning and Critical thresholds are ignored.

Specific parameters are: --work_mem, --maintenance_work_mem, --shared_buffers, --wal_buffers, --checkpoint_segments, --effective_cache_size, --no_check_autovacuum, --no_check_fsync, --no_check_enable, --no_check_track_counts.

Required privileges: unprivileged role.

🔗 connection (all) 🔗

Performs a simple connection test. Just… can we connect? Yes? Slay. ✨

No perfdata is returned.

This service ignores critical and warning arguments.

Required privileges: unprivileged role.

🪄 custom_query (all) 🪄

Performs the given user query. Your query, your rules, bestie.

Specify the query with --query. The first column will be used to perform the test for the status if warning and critical are provided.

The warning and critical arguments are optional. They can be of format integer (default), size or time depending on the --type argument. Warning and Critical will be raised if they are greater than the first column, or less if the --reverse option is used.

All other columns will be used to generate the perfdata. Each field name is used as the name of the perfdata. The field value must contain your perfdata value and its unit appended to it. You can add as many fields as needed. Eg.:

SELECT pg_database_size('postgres'),
       pg_database_size('postgres')||'B' AS db_size

Required privileges: unprivileged role (depends on the query).

💾 database_size (8.1+) 💾

Checks the variation of database sizes, and returns the size of every databases. Every. Single. One. 🍓

This service uses the status file (see --status-file parameter).

Perfdata contains the size of each database and their size delta since last call.

Critical and Warning thresholds are optional. They are a list of optional 'label=value' separated by a comma. It allows to fine tune the alert based on the absolute size and/or the delta size. Eg.:

-w 'size=500GB' -c 'size=600GB'
-w 'delta=1%' -c 'delta=10%'
-w 'size=500GB,delta=1%' -c 'size=600GB,delta=10GB'

The size label takes either a raw number or a size, and it checks the total database size. The delta label takes a raw number, a percentage, or a size. What's the delta param even for? Catching unexpected database size glow-ups (and glow-downs 🙈). Delta thresholds are absolute values, and delta percentages get computed against the previous database size. And bestie, listen: the same label has to be filled for both warning and critical, okay? No mixing and matching 💅

For backward compatibility vibes, if you hand over a single raw number, percentage, or size with no label at all, it applies to the size difference for each database since the last execution. So these two thresholds bellow are literally equivalent:

-w 'delta=1%' -c 'delta=10%'
-w '1%' -c '10%'

This service supports both --dbexclude and --dbinclude parameters. 🫶

Required privileges: unprivileged role.

✨ `extensions_versions` (9.1+)

Checks all extensions installed in all databases (templates included, yes including those) and raises a critical alert if the current version isn't the default version available on the instance (according to pg_available_extensions). It's giving "oh no you forgot to upgrade me" energy 😭

Typically you use this to catch forgotten extension upgrades after package upgrades or a pg_upgrade. Iconic little safety net.

Perfdata returns the number of outdated extensions in each database.

This service supports both --dbexclude and --dbinclude parameters. Schemas are ignored, because an extension can't be installed more than once in a database. Periodt.

This service supports multiple --exclude arguments to exclude one or more extensions from the check. To ignore an extension only in a particular database, use the 'dbname/extension_name' syntax. 🎀

Examples:

--dbexclude 'devdb' --exclude 'testdb/postgis' --exclude 'testdb/postgis_topology'
--dbinclude 'proddb' --dbinclude 'testdb'  --exclude 'powa'

Required privileges: unprivileged role able to log in all databases.

💖 `hit_ratio` (all)

Checks the cache hit ratio on the cluster. Slay.

This service uses the status file (see --status-file parameter).

Perfdata returns the cache hit ratio per database. Template databases and databases that don't allow connections will not be checked, and neither will databases that have never been accessed — they're off the guest list 🚫

Critical and Warning thresholds are optional. They only accept a percentage.

This service supports both --dbexclude and --dbinclude parameters.

Required privileges: unprivileged role.

🦋 `slru_hit_ratio` (13+)

Checks the cache SLRU ratio on the cluster.

This service uses the status file (see --status-file parameter).

Perfdata returns the cache SLRU ratio for one cluster.

Critical and Warning thresholds are optional. They only accept a percentage.

Required privileges: unprivileged role.

🔥 `hot_standby_delta` (9.0)

Checks the data delta between a cluster and its hot standbys.

You must give the connection parameters for two or more clusters. No skipping this part, bestie.

Perfdata returns the data delta in bytes between the master and each hot standby cluster listed.

Critical and Warning thresholds are optional. They can take one or two values separated by a comma. If only one value is given, it applies to both received and replayed data. If two values are given, the first one applies to received data, and the second one to replayed ones. These thresholds only accept a size (eg. 2.5G).

This service raises a Critical if it doesn't find exactly ONE valid master cluster (ie. critical when 0 or 2 and more masters). One master only, we don't do chaos here 💀

Required privileges: unprivileged role.

🌸 `is_hot_standby` (9.0+)

Checks if the cluster is in recovery and accepts read only queries.

This service ignores critical and warning arguments.

No perfdata is returned.

Required privileges: unprivileged role.

👑 `is_master` (all)

Checks if the cluster accepts read and/or write queries. This state is reported as "in production" by pg_controldata.

This service ignores critical and warning arguments.

No perfdata is returned.

Required privileges: unprivileged role.

💅 `invalid_indexes` (8.2+)

Checks if there are invalid indexes in a database. A critical alert is raised if an invalid index is detected. Not the vibe we want. 😭

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

This service supports a --exclude REGEX parameter to exclude indexes matching a regular expression. The regular expression applies to "database.schema_name.index_name". This enables you to filter either on a relation name for all schemas and databases, on a qualified named index (schema + index) for all databases, or on a qualified named index in only one database. So many options, we love flexibility 🎀

You can use multiple --exclude REGEX parameters.

Perfdata will return the number of invalid indexes per database.

A list of invalid indexes will be returned after the perfdata. This list contains the fully qualified index name. If an excluded index is set, the number of excluded indexes is returned.

Required privileges: unprivileged role able to log in all databases.

🍓 `unused_indexes` (8.2+)

Checks if there are unused indexes in a database. A warning alert is raised if an unused index is detected. (Warning, not critical — we're not mad, just disappointed 🙈)

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

You can use multiple --exclude REGEX parameters.

Perfdata will return the number of unused indexes per database.

A list of unused indexes will be returned after the perfdata. This list contains the fully qualified index name. If an excluded index is set, the number of excluded indexes is returned.

Required privileges: unprivileged role able to log in all databases.

⭐ `is_replay_paused` (9.1+)

Checks if the replication is paused. The service will return UNKNOWN if executed on a master server. Rude, but okay.

Thresholds are optional. They must be specified as an interval. OK will always be returned if the standby is not paused, even if replication delta time hits the thresholds.

Critical or warning are raised if the last reported replayed timestamp is greater than the given threshold AND some data received from the master isn't applied yet. OK will always be returned if the standby is paused, or if the standby has already replayed everything from the master and until some write activity happens on the master.

Perfdata returned:

paused status (0 no, 1 yes, NaN if master)
lag time (in second)
data delta with master (0 no, 1 yes)

Required privileges: unprivileged role.

🧚 `last_analyze` (8.2+)

Checks on each database that the oldest analyze (from autovacuum or not) isn't older than the given threshold. This is to check that a scheduled ANALYZE is working correctly. Bestie, keep your stats fresh 💅

This service uses the status file (see --status-file parameter) with PostgreSQL 9.1+.

Perfdata returns the oldest analyze per database in seconds. With PostgreSQL 9.1+, the number of [auto]analyses per database since last call is also returned.

Critical and Warning thresholds only accept an interval (eg. 24h30m) and apply to the oldest execution of analyse.

Tables that were never analyzed, or whose analyze date was lost due to a crash, will raise a critical alert.

NOTE: this service does not raise alerts if the database had strictly no writes since last call. In consequence, a read-only database can have its oldest analyze reported in perfdata way after your thresholds, but not raise any alerts, unless you change a threshold.

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

This service supports a --exclude REGEX parameter to exclude relations matching a regular expression. The regular expression applies to "database.schema_name.relation_name". This enables you to filter either on a relation name for all schemas and databases, on a qualified named relation (schema + relation) for all databases, or on a qualified named relation in only one database.

You can use multiple --exclude REGEX parameters.

Required privileges: unprivileged role able to log in all databases.

🌈 `last_vacuum` (8.2+)

Checks that the oldest vacuum (from autovacuum or otherwise) in each database in the cluster isn't older than the given threshold. This is to check that a scheduled VACUUM is working correctly. Clean your room, database edition 🧹

This service uses the status file (see --status-file parameter) with PostgreSQL 9.1+.

Perfdata returns oldest vacuum per database in seconds. With PostgreSQL 9.1+, it also returns the number of [auto]vacuums per database since last execution.

Critical and Warning thresholds only accept an interval (eg. 24h30m) and apply to the oldest vacuum.

Tables that were never vacuumed, or whose vacuum date was lost due to a crash, will raise a critical alert.

NOTE: this service does not raise alerts if the database had strictly no writes since last call. In consequence, a read-only database can have its oldest vacuum reported in perfdata way after your thresholds, but not raise any alerts, unless you change a threshold.

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

You can use multiple --exclude REGEX parameters.

Required privileges: unprivileged role able to log in all databases.

🔒 `locks` (all)

Checks the number of locks on the hosts.

Perfdata returns the number of locks, by type.

Critical and Warning thresholds accept either a raw number of locks or a percentage. For percentage, it is computed using the following limits for 7.4 to 8.1:

…and here's the math for it, bestie ✨

max_locks_per_transaction * max_connections

for 8.2+:

max_locks_per_transaction * (max_connections + max_prepared_transactions)

for 9.1+, regarding lockmode:

max_locks_per_transaction * (max_connections + max_prepared_transactions)
or max_pred_locks_per_transaction * (max_connections + max_prepared_transactions)

Required privileges: unprivileged role. that's it, that's the vibe 💅

longest_query (all) 🌸

Checks out the longest query currently running in your cluster. the main character of slow queries, lowkey.

Perfdata gives you the max/avg/min running time, plus how many queries there are per database. 📊

Critical and Warning thresholds only accept an interval (no raw numbers here, sorry bestie).

This service supports both --dbexclude and --dbinclude parameters.

It also supports the argument --exclude REGEX to exclude queries matching that regular expression from the check. bye bye, noisy query 🫶

Above 9.0, it also supports --exclude REGEX to filter out application_name.

You can stack multiple --exclude REGEX parameters. go off 👑

Required privileges: an unprivileged role only checks its own queries; a pg_monitor (10+) or superuser (<10) role is required to check all queries. so if you want the full picture, get that rank up, no cap.

max_freeze_age (all) 🧊

Checks the oldest database by transaction age.

Critical and Warning thresholds are optional here. They accept either a raw number or a percentage for PostgreSQL 8.2 and more. If you give a percentage, the thresholds get computed based on the "autovacuum_freeze_max_age" parameter. Heads up: 100% means that some table(s) already reached the maximum age and will trigger an autovacuum freeze. So percentage thresholds should therefore be greater than 100%. like, always. 🔥

Even with no threshold set, this service will raise a critical alert if a database has a negative age. dramatic but correct.

Perfdata returns the age of each database.

This service supports both --dbexclude and --dbinclude parameters.

Required privileges: unprivileged role.

minor_version (all) 🎀

Checks if the cluster is running the most recent minor version of PostgreSQL. glow-up check 💖

Latest versions of PostgreSQL can be fetched from the PostgreSQL official website if check_pgactivity has access to it, or they must be given as a parameter. your call.

Without --critical or --warning parameters, this service attempts to fetch the latest version numbers online. A critical alert is raised if the minor version is not the most recent. it's giving "you're behind, bestie".

You can optionally set the path to your preferred retrieval tool using the --path parameter (eg. --path '/usr/bin/wget'). Supported programs are: GET, wget, curl, fetch, lynx, links, links2. pick your fighter 🌈

If you do not want to (or cannot) query the PostgreSQL website, provide the expected versions using either --warning OR --critical, depending on which return value you want to raise.

The given string must contain one or more MINOR versions separated by anything but a .. For instance, the following parameters are all equivalent:

--critical "10.1 9.6.6 9.5.10 9.4.15 9.3.20 9.2.24 9.1.24 9.0.23 8.4.22"
--critical "10.1, 9.6.6, 9.5.10, 9.4.15, 9.3.20, 9.2.24, 9.1.24, 9.0.23, 8.4.22"
--critical "10.1,9.6.6,9.5.10,9.4.15,9.3.20,9.2.24,9.1.24,9.0.23,8.4.22"
--critical "10.1/9.6.6/9.5.10/9.4.15/9.3.20/9.2.24/9.1.24/9.0.23/8.4.22"

Any other value than 3 numbers separated by dots (before version 10.x) or 2 numbers separated by dots (version 10 and above) will be ignored. If the running PostgreSQL major version is not found, the service raises an unknown status. 💀

Perfdata returns the numerical version of PostgreSQL.

Required privileges: unprivileged role; access to http://www.postgresql.org required to download version numbers.

oldest_2pc (8.1+) 🦋

Checks the oldest two-phase commit transaction (aka. prepared transaction) in the cluster.

Perfdata contains the max/avg age time and the number of prepared transactions per databases.

Critical and Warning thresholds only accept an interval.

Required privileges: unprivileged role.

oldest_idlexact (8.3+) 😴

Checks the oldest idle transaction.

Perfdata contains the max/avg age and the number of idle transactions per databases.

Critical and Warning thresholds only accept an interval.

This service supports both --dbexclude and --dbinclude parameters.

Above 9.2, it supports --exclude to filter out connections. Eg., to filter out pg_dump and pg_dumpall, set this to 'pg_dump,pg_dumpall'. 🙈

Before 9.2, this service checks for idle transactions with their start time. Thus, the service can mistakenly take account of a transaction transiently in idle state. From 9.2 and up, the service checks for transactions that really had no activity since the given thresholds. so pre-9.2 it's a little messy, ngl.

Required privileges: an unprivileged role checks only its own queries; a pg_monitor (10+) or superuser (<10) role is required to check all queries.

oldest_xmin (8.4+) 👑

Checks the xmin horizon from distinct sources of xmin retention.

Per default, Perfdata outputs the oldest known xmin age for each database among running queries, opened or idle transactions, pending prepared transactions, replication slots and walsender. For versions prior to 9.4, only the 2pc source of xmin retention is checked. (less to look at, honestly chill)

Using --detailed, Perfdata contains the oldest xmin and maximum age for the following source of xmin retention: query (a running query), active_xact (an opened transaction currently executing a query), idle_xact (an opened transaction being idle), 2pc (a pending prepared transaction), repslot (a replication slot) and walwender (a WAL sender replication process), for each connectable database. If a source doesn't retain any transaction for a database, NaN is returned. For versions prior to 9.4, only the 2pc source of xmin retention is available, so other sources won't appear in the perfdata. Note that xmin retention from walsender is only set if hot_standby_feedback is enabled on remote standby. 🛰️

Critical and Warning thresholds are optional. They only accept a raw number of transaction.

This service supports both --dbexclude and --dbinclude parameters.

Required privileges: a pg_read_all_stats (10+) or superuser (<10) role is required to check pg_stat_replication. 2PC, pg_stat_activity, and replication slots don't require special privileges. so it's partially chill 💜

pg_dump_backup 📦

Checks the age and size of backups. the "did you actually back up" service, we love her.

This service uses the status file (see --status-file parameter).

The --path argument contains the location to the backup folder. The supported format is a glob pattern matching every folder or file that you need to check.

The --pattern is required, and must contain a regular expression matching the backup file name, extracting the database name from the first matching group.

Optionally, a --global-pattern option can be supplied to check for an additional global file.

Examples:

To monitor backups like:

/var/lib/backups/mydb-20150803.dump
/var/lib/backups/otherdb-20150803.dump
/var/lib/backups/mydb-20150804.dump
/var/lib/backups/otherdb-20150804.dump

you must set:

--path    '/var/lib/backups/*'
--pattern '(\w+)-\d+.dump'

If the path contains the date, like this:

/var/lib/backups/2015-08-03-daily/mydb.dump
/var/lib/backups/2015-08-03-daily/otherdb.dump

then you can set:

--path    '/var/lib/backups/*/*.dump'
--pattern '/\d+-\d+-\d+-daily/(.*).dump'

For compatibility with pg_back (https://github.com/orgrim/pg_back), you should use:

--path '/path/*{dump,sql}'
--pattern '(\w+)_[0-9-_]+.dump'
--global-pattern 'pg_global_[0-9-_]+.sql'

The --critical and --warning thresholds are optional. They accept a list of 'metric=value' separated by a comma. Available metrics are oldest and newest, respectively the age of the oldest and newest backups, and size, which must be the maximum variation of size since the last check, expressed as a size or a percentage. mindeltasize, expressed in B, is the minimum variation of size needed to raise an alert. 📏

This service supports the --dbinclude and --dbexclude arguments, to respectively test for the presence of include or exclude files.

The argument --exclude enables you to exclude files younger than an interval. This is useful to ignore files from a backup in progress. Eg., if your backup process takes 2h, set this to '125m'. 🕐

Perfdata returns the age of the oldest and newest backups, as well as the size of the newest backups.

Required privileges: unprivileged role; the system user needs read access on the directory containing the dumps (but not on the dumps themselves).

pgss_dealloc (14+) 🍓

Checks the number of pg_stat_statements deallocations.

Too many deallocations can have a negative performance impact. This could be due to:

an application performing many different queries (ORM, lists of varying length, ...)
some utility statements (SET application_name...). In this case, try to disable pg_stat_statements.track_utility.
pg_stat_statements.max is too low

This service uses the status file (see --status-file parameter).

The --critical and --warning thresholds are optional. Warning or critical will be raised if the number of deallocations since the previous check exceeds the thresholds.

Perfdata returns a counter of deallocs since last call.

The pg_stat_statements extension must be installed in the database you're connecting into (including shared_preload_libraries setup). non-negotiable bestie 🫶

Required privileges: unprivileged role able to log in to any database in which the pg_stat_statements extension has been installed.

pga_version 💅

Checks if this script is running the given version of check_pgactivity. You must provide the expected version using either --warning OR --critical.

No perfdata is returned. clean, minimal, iconic.

Required privileges: none. literally zero gatekeeping.

pgdata_permission (8.2+) 🔐

Checks that the instance data directory rights are 700, and belongs to the system user currently running postgresql.

The check on rights works on all Unix systems. 🌈

Checking the user only works on Linux systems (it uses /proc to avoid dependencies). Before 9.3, you need to provide the expected owner using the --uid argument, or the owner will not be checked. so if you're ancient, speak up about your uid.

Required privileges:

<11: superuser
v11: user with pg_monitor or pg_read_all_setting

And bestie, the system user running this also has to be able to read the folder that contains PGDATA — meaning the service has to be executed locally on the monitored server. No remote vibes allowed for this one. 🖥️💅

replication_slots (9.4+)

okay so this one checks the number of WAL files retained and the spilled files, for each replication slot. 👀

Perfdata hands back the number of WAL kept for each slot, plus the number of spilled files in pg_replslot for every logical replication slot. And since v13, if max_slot_wal_keep_size is greater than or equal to 0, perfdata also reports the size of WAL to produce before each slot becomes unreserved or lost. 🫠 Heads up though: that size can go negative if the WAL status is such for the limited time where the slot becomes unreserved. It gets set to 0 as soon as the last checkpoint finishes and the status becomes lost. that's the deal, no notes.

This service needs superuser privileges to obtain the number of spill files — or it returns 0 as a last resort. 🤷

Critical and Warning thresholds are optional. They accept either a raw number (for backward compatibility, only the wal threshold will be used) or a list of 'wal=value' and/or 'spilled=value' and/or 'remaining=size'. Respectively, those are: the number of kept wal files, the number of spilled files in pg_replslot for each logical slot, and the remaining bytes before a slot becomes unreserved or lost. ✨

And moreover, with v13 and after, the service raises a warning alert if a slot becomes unreserved. It raises a critical alert if the slot becomes lost. 😱

Required privileges:

v9.4: unprivileged role, or superuser to monitor spilled files for logical replication
v11+: unprivileged user with GRANT EXECUTE on function pg_ls_dir(text)

Here's somes examples for you:

-w 'wal=50,spilled=20' -c 'wal=100,spilled=40'
-w 'spilled=20,remaining=160MB' -c 'spilled=40,remaining=48MB'

settings (9.0+)

Check if the current settings have changed since they were stored in the service file. 🎀

The "known" settings are recorded during the very first call of the service. To update the known settings after a configuration change, call this service again with the argument --save. that's it, that's the trick.

No perfdata.

The Critical threshold is ignored. 🚫

A Warning is raised when the time since configuration change is less than the warning threshold. Obviously these alerts will disappear from themselves once enough time has passed — girl they ghost you on their own 💨. This doesn't apply for configuration changes with pending restart warnings.

Required privileges: unprivileged role.

sequences_exhausted (7.4+)

Check all the sequences and raise an alarm if the column or sequence gets too close to the maximum value. 📈 The maximum value is calculated from the maxvalue of the sequence, or from the column type when the sequence is owned by a column (that's the smallserial, serial and bigserial types, iykyk).

Perfdata returns the sequences that trigger the alert. 🔔

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded. periodt.

Critical and Warning thresholds accept a percentage of the sequence filled.

Required privileges: unprivileged role able to log in all databases.

stat_snapshot_age (9.5 to 14 included)

Check the age of the statistics snapshot (the statistics collector's statistics). This probe helps to detect a frozen stats collector process 🥶 — basically a vibe check on your stats collector.

Perfdata returns the statistics snapshot age.

Critical and Warning thresholds only accept an interval (eg. 1h30m25s). ⏱️

Required privileges: unprivileged role.

streaming_delta (9.1+)

Check the data delta between a cluster and its standbys in streaming replication. 🔄

Optional argument --slave lets you specify some slaves that MUST be connected. This argument can be used as many times as you want to check multiple slave connections, or you can specify multiple slave connections at one time using comma separated values. And bestie, both methods can be used in a single call. 💅 The provided values must be of the form "APPLICATION_NAME IP". Both of these examples will check for the presence of two slaves:

--slave 'slave1 192.168.1.11' --slave 'slave2 192.168.1.12'
--slave 'slave1 192.168.1.11','slave2 192.168.1.12'

This service supports a --exclude REGEX parameter to exclude every result matching a regular expression on application_name or IP address fields.

You can use multiple --exclude REGEX parameters.

Perfdata returns the data delta in bytes between the master and every standbies found, the number of standbies connected, and the number of excluded standbies. 📊

Critical and Warning thresholds are optional. They can take one or two values separated by a comma. If only one value is supplied, it applies to both flushed and replayed data. If two values are supplied, the first one applies to flushed data and the second one to replayed data. These thresholds only accept a size (eg. 2.5G). 🍓

Required privileges: unprivileged role.

table_unlogged (9.5+)

Check if tables are changed to unlogged. In 9.5, you can switch between logged and unlogged. 🔁

Without --critical or --warning parameters, this service attempts to fetch all unlogged tables.

A critical alert is raised if an unlogged table is detected. 🚨

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

You can use multiple --exclude REGEX parameters.

Perfdata will return the number of unlogged tables per database.

A list of the unlogged tables will be returned after the perfdata. This list contains the fully qualified table name. If --exclude REGEX is set, the number of excluded tables is returned. 📝

Required privileges: unprivileged role able to log in all databases, or at least those in --dbinclude.

relation_size (9.0+)

Check relations' size. 📏

Without --critical or --warning parameters, this service attempts to fetch all relations' size.

An alert is raised if an relation's size is above the warning or critical level.

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

You can use multiple --exclude REGEX parameters.

Perfdata will return the number of relations per database.

A list of the relations and their sizes will be returned after the perfdata. This list contains the fully qualified relation name. If --exclude REGEX is set, the number of excluded relations is returned. 👑

Required privileges: unprivileged role able to log in all databases, or at least those in --dbinclude.

table_bloat

Estimate bloat on tables. 🎈

Warning and critical thresholds accept a comma-separated list of either a raw number (for a size), a size (eg. 125M) or a percentage. The thresholds apply to bloat size, not object size. If a percentage is given, the threshold applies to the bloat size compared to the table + TOAST size. If multiple threshold values are passed, check_pgactivity will choose the largest (bloat size) value. 💅

This service supports both --dbexclude and --dbinclude parameters. The 'postgres' database and templates are always excluded.

This service supports a --exclude REGEX parameter to exclude relations matching the given regular expression. The regular expression applies to "database.schema_name.relation_name". This enables you to filter either on a relation name for all schemas and databases, on a qualified named relation (schema + relation) for all databases, or on a qualified named relation in only one database.

You can use multiple --exclude REGEX parameters.

Warning: With a non-superuser role, this service can only check the tables that the given role is granted to read! 🙈

Perfdata will return the number of tables matching the warning and critical thresholds, per database.

A list of the bloated tables will be returned after the perfdata. This list contains the fully qualified bloated table name, the estimated bloat size, the table size, and the bloat percentage. 📋

Required privileges: superuser (<10) able to log in all databases, or at least those in --dbinclude; superuser (<10); on PostgreSQL 10+, a user with the role pg_monitor suffices, provided that you grant SELECT on the system table pg_statistic to the pg_monitor role, in each database of the cluster: GRANT SELECT ON pg_statistic TO pg_monitor; 🔥

temp_files (8.1+)

Check the number and size of temp files. 🗂️

This service uses the status file (see --status-file parameter) for 9.2+.

Perfdata returns the number and total size of temp files found in pgsql_tmp folders. They are aggregated by database until 8.2, then by tablespace (see GUC temp_tablespaces). 🌀

Starting with 9.2, perfdata returns as well the number of temp files per database since last run, the total size of temp files per database since last run, and the rate at which temp files were generated.

Critical and Warning thresholds are optional. They accept either a number of file (raw value), a size (the unit is mandatory to define a size), or both values separated by a comma.

Thresholds are applied on current temp files being created AND the number/size of temp files created since last execution. no cap. 💜

Required privileges:

<10 — superuser, that's the whole vibe, no workarounds 💅
v10 — an unprivileged role is possible but it will not monitor databases that it cannot access, nor live temp files
v11 — an unprivileged role is possible but must be granted EXECUTE on functions pg_ls_dir(text), pg_read_file(text), pg_stat_file(text, boolean); the same restrictions than on v10 will still apply
v12+ — a role with pg_monitor privilege. periodt.

hugepages (17+) 🐘

Check whether huge pages are in use.

The default is to expect the huge pages to be unused. If --with-hugepages is specified, the service expects them to be in use instead. Choose your fighter 🎀

The perfdata returns the status where 0 is off, 1 is on and 2 is unknown.

uptime (8.1+) ⏱️

Returns time since postmaster start ("uptime", from 8.1), since configuration reload (from 8.4), and since shared memory initialization (from 10).

Please note that the uptime is unaffected when the postmaster resets all its children (for example after a kill -9 on a process or a failure). Like, rude, but it just doesn't care.

From 10+, the 'time since shared memory init' aims at detecting this situation: in fact we use the age of the oldest non-client child process (usually checkpointer, writer or startup). This needs pg_monitor access to read pg_stat_activity — so no shortcuts bestie.

Critical and Warning thresholds are optional. If both are set, Critical is raised when the postmaster uptime or the time since shared memory initialization is less than the critical threshold.

Warning is raised when the time since configuration reload is less than the warning threshold. If only a warning or critical threshold is given, it will be used for both cases. Obviously these alerts will disappear from themselves once enough time has passed — they're just dramatic like that, give them a minute 🫶

Perfdata contain the three values (when available).

Required privileges: pg_monitor on PG10+; otherwise unprivileged role.

wal_files (8.1+) 📂

Check the number of WAL files.

Perfdata returns the total number of WAL files, current number of written WAL, the current number of recycled WAL, the rate of WAL written to disk since the last execution on the master cluster and the current timeline. So much data, so few pixels ✨

Critical and Warning thresholds accept either a raw number of files or a percentage. In case of percentage, the limit is computed based on:

100% = 1 + checkpoint_segments * (2 + checkpoint_completion_target)

For PostgreSQL 8.1 and 8.2:

100% = 1 + checkpoint_segments * 2

If wal_keep_segments is set for 9.0 to 9.4, the limit is the greatest of the following formulas:

100% = 1 + checkpoint_segments * (2 + checkpoint_completion_target)
100% = 1 + wal_keep_segments + 2 * checkpoint_segments

For 9.5 to 12, the limit is:

100% =  max_wal_size      (as a number of WAL)
     + wal_keep_segments (if set)

For 13 and above:

100% =  max_wal_size + wal_keep_size (as numbers of WAL)

Required privileges:

<10 — superuser (<10)
v10 — unprivileged user with pg_monitor
v11+ — unprivileged user with pg_monitor, or with grant EXECUTE on function pg_ls_waldir

✨ EXAMPLES ✨

Y'all want receipts? Here are the receipts. Every single one, untouched, iconic 💖

Execute service "last_vacuum" on host "host=localhost port=5432":

check_pgactivity -h localhost -p 5432 -s last_vacuum -w 30m -c 1h30m

Execute service "hot_standby_delta" between hosts "service=pg92" and "service=pg92s":

check_pgactivity --dbservice pg92,pg92s --service hot_standby_delta -w 32MB -c 160MB

Execute service "streaming_delta" on host "service=pg92" to check its slave "stby1" with the IP address "192.168.1.11":

check_pgactivity --dbservice pg92 --slave "stby1 192.168.1.11" --service streaming_delta -w 32MB -c 160MB

Execute service "hit_ratio" on host "slave" port "5433 , excluding database matching the regexps "idelone" and "(?i:sleep)":

check_pgactivity -p 5433 -h slave --service hit_ratio --dbexclude idelone --dbexclude "(?i:sleep)" -w 90% -c 80%

Execute service "hit_ratio" on host "slave" port "5433 , only for databases matching the regexp "importantone":

check_pgactivity -p 5433 -h slave --service hit_ratio --dbinclude importantone -w 90% -c 80%

🌈 VERSION 🌈

check_pgactivity version 2.10, released on Mon May 18 2026.

new drop alert, mark your calendars 🎀

💜 LICENSING 💜

This program is open source, licensed under the PostgreSQL license. For license terms, see the LICENSE provided with the sources.

👑 AUTHORS 👑

Author: Open PostgreSQL Monitoring Development Group

And that's a wrap on the whole thing, bestie — this page is from the Debian build of CHECK_PGACTIVITY(1), and it's dated 2026-05-25 (while the program itself, as we just said, is version 2.10, released on Mon May 18 2026). Go monitor something and slay 💅✨🦋

📦 where this page comes from

ubuntu stonking: check-pgactivity 2.10-1 · OPMDG
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-11-02: a language model's rewrite of the original, under its terms

CHECK_PGACTIVITY(1)