BASIC FILTERING

The options –include and –exclude can be combined to limit which
things are checked, depending on the action. The name of the database can
be filtered when using the following actions:
backends, database_size, locks, query_time, txn_idle, and txn_time.
The name of a relation can be filtered when using the following actions:
bloat, index_size, table_size, relation_size, last_vacuum, last_autovacuum,
last_analyze, and last_autoanalyze.
The name of a setting can be filtered when using the settings_checksum action.
The name of a file system can be filtered when using the disk_space action.

If only an include option is given, then ONLY those entries that match will be
checked. However, if given both exclude and include, the exclusion is done first,
and the inclusion after, to reinstate things that may have been excluded. Both
–include and –exclude can be given multiple times,
and/or as comma-separated lists. A leading tilde will match the following word
as a regular expression.

To match a schema, end the search term with a single period. Leading tildes can
be used for schemas as well.

Examples:

Only checks items named pg_class:

bash

  1. --include=pg_class

Only checks items containing the letters ‘pg_’:

bash

  1. --include=~pg_

Only check items beginning with ‘pg_’:

bash

  1. --include=~^pg_

Exclude the item named ‘test’:

bash

  1. --exclude=test

Exclude all items containing the letters ‘test:

bash

  1. --exclude=~test

Exclude all items in the schema ‘pg_catalog’:

bash

  1. --exclude='pg_catalog.'

Exclude all items containing the letters ‘ace’, but allow the item ‘faceoff’:

bash

  1. --exclude=~ace --include=faceoff

Exclude all items which start with the letters ‘pg_’, which contain the letters ‘slon’,
or which are named ‘sql_settings’ or ‘green’. Specifically check items with the letters ‘prod’ in their names, and always check the item named ‘pg_relname’:

bash

  1. --exclude=~^pg_,~slon,sql_settings --exclude=green --include=~prod,pg_relname


USER NAME FILTERING

The options –includeuser and –excludeuser can be used on some actions
to only examine database objects owned by (or not owned by) one or more users.
An –includeuser option always trumps an –excludeuser option. You can
give each option more than once for multiple users, or you can give a
comma-separated list. The actions that currently use these options are:

database_size
last_analyze
last_autoanalyze
last_vacuum
last_autovacuum
query_time
relation_size
txn_time

Examples:

Only check items owned by the user named greg:

bash

  1. --includeuser=greg

Only check items owned by either watson or crick:

bash

  1. --includeuser=watson,crick

Only check items owned by crick,franklin, watson, or wilkins:

bash

  1. --includeuser=watson --includeuser=franklin --includeuser=crick,wilkins

Check all items except for those belonging to the user scott:

bash

  1. --excludeuser=scott


TEST MODE

To help in setting things up, this program can be run in a "test mode" by
specifying the –test option. This will perform some basic tests to
make sure that the databases can be contacted, and that certain per-action
prerequisites are met, such as whether the user is a superuser, if the version
of Postgres is new enough, and if stats_row_level is enabled.


TIPS AND TRICKS

Since this program uses the psql program, make sure it is accessible to the
user running the script. If run as a cronjob, this often means modifying the
PATH environment variable.

If you are using Nagios in embedded Perl mode, use the --action argument
instead of symlinks, so that the plugin only gets compiled one time.


DEPENDENCIES

Access to a working version of psql, and the following very standard Perl modules:

Cwd
Getopt::Long
File::Basename
File::Temp
Time::HiRes (if $opt{showtime} is set to true, which is the default)

The settings_checksum action requires the Digest::MD5 module.

Some actions require access to external programs. If psql is not explicitly
specified, the command which is used to find it. The program /bin/df
is needed by the disk_space action.


Leave a Reply

Your email address will not be published. Required fields are marked *

clear formPost comment