autovac_freeze

(symlink: check_postgres_autovac_freeze) Checks how close each database is to the Postgres autovacuum_freeze_max_age setting. This
action will only work for databases version 8.2 or higher. The –warning and
–critical options should be expressed as percentages. The ‘age’ of the transactions
in each database is compared to the autovacuum_freeze_max_age setting (200 million by default)
to generate a rounded percentage. The default values are 90% for the warning and 95% for
the critical. Databases can be filtered by use of the –include and –exclude options. See
the BASIC FILTERING section for more details.

Example 1: Give a warning when any databases on port 5432 are above 80%

bash

  1. check_postgres_autovac_freeze --port=5432 --warning="80%"

For MRTG output, the highest overall percentage is reported on the first line, and the highest age is
reported on the second line. All databases which have the percentage from the first line are reported
on the fourth line, separated by a pipe symbol.

backends

(symlink: check_postgres_backends) Checks the current number of connections for one or more databases, and optionally
compares it to the maximum allowed, which is determined by the
Postgres configuration variable max_connections. The –warning and
–critical options can take one of three forms. First, a simple number can be
given, which represents the number of connections at which the alert will be
given. This choice does not use the max_connections setting. Second, the
percentage of available connections can be given. Third, a negative number can
be given which represents the number of connections left until max_connections
is reached. The default values for –warning and –critical are ‘90%’ and ‘95%’.
You can also filter the databases by use of the
–include and –exclude options. See the BASIC FILTERING section
for more details.

Example 1: Give a warning when the number of connections on host quirm reaches 120, and a critical if it reaches 150.

bash

  1. check_postgres_backends --host=quirm --warning=120 --critical=150

Example 2: Give a critical when we reach 75% of our max_connections setting on hosts lancre or lancre2.

bash

  1. check_postgres_backends --warning='75%' --critical='75%' --host=lancre,lancre2

Example 3: Give a warning when there are only 10 more connection slots left on host plasmid, and a critical
when we have only 5 left.

bash

  1. check_postgres_backends --warning=-10 --critical=-5 --host=plasmid

Example 4: Check all databases except those with "test" in their name, but allow ones that are named "pg_greatest". Connect as port 5432 on the first two hosts, and as port 5433 on the third one. We want to always throw a critical when we reach 30 or more connections.

bash

  1. check_postgres_backends --dbhost=hong,kong --dbhost=fooey --dbport=5432 --dbport=5433 --warning=30 --critical=30 --exclude="~test" --include="pg_greatest,~prod"

For MRTG output, the number of connections is reported on the first line, and the fourth line gives the name of the database,
plus the current maximum_connections. If more than one database has been queried, the one with the highest number of
connections is output.

bloat

(symlink: check_postgres_bloat) Checks the amount of bloat in tables and indexes. (Bloat is generally the amount
of dead unused space taken up in a table or index. This space is usually reclaimed
by use of the VACUUM command.) This action requires that stats collection be
enabled on the target databases, and requires that ANALYZE is run frequently.
The –include and –exclude options can be used to filter out which tables
to look at. See the BASIC FILTERING section for more details.

The –warning and –critical options can be specified as sizes or percents.
Valid size units are bytes, kilobytes, megabytes, gigabytes, terabytes, and exabytes.
You can abbreviate all of those with the first letter. Items without units are
assumed to be ‘bytes’. The default values are ‘1 GB’ and ‘5 GB’. The value
represents the number of "wasted bytes", or the difference between what is actually
used by the table and index, and what we compute that it should be.

Note that this action has two hard-coded values to avoid false alarms on
smaller relations. Tables must have at least 10 pages, and indexes at least 15,
before they can be considered by this test. If you really want to adjust these
values, you can look for the variables $MINPAGES and $MINIPAGES at the top of the
check_bloat subroutine.

Only the top 10 most bloated relations are shown. You can change this number by
using the –perflimit option to set your own limit.

The schema named ‘information_schema’ is excluded from this test, as the only tables
it contains are small and do not change.

Please note that the values computed by this action are not precise, and
should be used as a guideline only. Great effort was made to estimate the
correct size of a table, but in the end it is only an estimate. The correct
index size is even more of a guess than the correct table size, but both
should give a rough idea of how bloated things are.

Example 1: Warn if any table on port 5432 is over 100 MB bloated, and critical if over 200 MB

bash

  1. check_postgres_bloat --port=5432 --warning='100 M', --critical='200 M'

Example 2: Give a critical if table ‘orders’ on host ‘sami’ has more than 10 megs of bloat

bash

  1. check_postgres_bloat --host=sami --include=orders --critical='10 MB'

Example 3: Give a critical if table ‘q4’ on database ‘sales’ is over 50% bloated

bash

  1. check_postgres_bloat --db=sales --include=q4 --critical='50%'

For MRTG output, the first line gives the highest number of wasted bytes for the tables, and the
second line gives the highest number of wasted bytes for the indexes. The fourth line gives the database
name, table name, and index name information. If you want to output the bloat ration instead (how many
times larger the relation is compared to how large it should be), just pass in --mrtg=ratio.

connection

(symlink: check_postgres_connection) Simply connects, issues a ‘SELECT version()’, and leaves.
Takes no –warning or –critical options.

For MRTG output, simply outputs a 1 (good connection) or a 0 (bad connection) on the first line.

custom_query

(symlink: check_postgres_custom_query) Runs a custom query of your choosing, and parses the results. The query itself is passed in through
the custom_query argument, and should be kept as simple as possible. If at all possible, wrap it in
a view or a function to keep things easier to manage. The query should return one or two columns: the first
is the result that will be checked, and the second is any performance data you want sent.

At least one warning or critical argument must be specified. What these are set to depends on the type of
query you are running. There are four types of custom_queries that can be run, specified by the valtype
argument. If none is specified, this action defaults to ‘integer’. The four types are:

integer:
Does a simple integer comparison. The first column should be a simple integer, and the warning and
critical values should be the same.

string:
The warning and critical are strings, and are triggered only if the value in the first column matches
it exactly. This is case-sensitive.

time:
The warning and the critical are times, and can have units of seconds, minutes, hours, or days.
Each may be written singular or abbreviated to just the first letter. If no units are given,
seconds are assumed. The first column should be an integer representing the number of seconds
to check.

size:
The warning and the critical are sizes, and can have units of bytes, kilobytes, megabytes, gigabytes,
terabytes, or exabytes. Each may be abbreviated to the first letter. If no units are given,
bytes are assumed. The first column should be an integer representing the number of bytes to check.

Normally, an alert is triggered if the values returned are greater than or equal to the critical or warning
value. However, an option of –reverse will trigger the alert if the returned value is
lower than or equal to the critical or warning value.

Example 1: Warn if any relation over 100 pages is named "rad":

bash

  1. check_postgres_custom_query --valtype=string -w "rad" --query="SELECT relname FROM pg_class WHERE relpages > 100" --port=5432

Example 2: Give a critical if the "foobar" function returns a number over 5MB:

bash

  1. check_postgres_custom_query --port=5432 --critical='5MB'--valtype=size --query="SELECT foobar()"

Example 2: Warn if the function "snazzo" returns less than 42:

bash

  1. check_postgres_custom_query --port=5432 --critical=42 --query="SELECT snazzo()" --reverse

If you come up with a useful custom_query, consider sending in a patch to this program
to make it into a standard action that other people can use.

This action does not support MRTG or simple output yet.

Leave a Reply

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

clear formPost comment