query_time

(symlink: check_postgres_query_time) Checks the length of running queries on one or more databases. There is
no need to run this more than once on the same database cluster.
Databases can be filtered
by using the –include and –exclude options. See the BASIC FILTERING
section for more details. You can also filter on the user running the
query with the –includeuser and –excludeuser options.
See the USER NAME FILTERING section for more details.

The values for the –warning and –critical options are amounts of
time, and default to ‘2 minutes’ and ‘5 minutes’ respectively. Valid units
are ‘seconds’, ‘minutes’, ‘hours’, or ‘days’. Each may be written singular or
abbreviated to just the first letter. If no units are given, the unit is
assumed to be seconds.

Example 1: Give a warning if any query has been running longer than 3 minutes, and a critical if longer than 5 minutes.

bash

  1. check_postgres_query_time --port=5432 --warning='3 minutes' --critical='5 minutes'

Example 2: Using default values (2 and 5 minutes), check all databases except those starting with ‘template’.

bash

  1. check_postgres_query_time --port=5432 --exclude=~^template

Example 3: Warn if user ‘don’ has a query running over 20 seconds

bash

  1. check_postgres_query_time --port=5432 --inclucdeuser=don --warning=20s

For MRTG output, returns the length in seconds of the longest running query on the first line. The fourth
line gives the name of the database.

sequence

(symlink: check_postgres_sequence) Checks how much room is left on all sequences in the database.
This is measured as the percent of total possible values that have been used for each sequence.
The –warning and –critical options should be expressed as percentages. The default values
are 85% for the warning and 95% for the critical. You may use –include and –exclude to
control which sequences are to be checked. Note that this check does account for unusual minvalue
and increment by values, but does not care if the sequence is set to cycle or not.

The output for Nagios gives the name of the sequence, the percentage used, and the number of ‘calls’
left, indicating how many more times nextval can be called on that sequence before running into
the maximum value.

The output for MRTG returns the highest percentage across all sequences on the first line, and
the name of each sequence with that percentage on the fourth line, separated by a "|" (pipe)
if there are more than one sequence at that percentage.

Example 1: Give a warning if any sequences are approaching 95% full.

bash

  1. check_postgres_sequence --dbport=5432 --warning=95%

Example 2: Check that the sequence named "orders_id_seq" is not more than half full.

bash

  1. check_postgres_sequence --dbport=5432 --critical=50% --include=orders_id_seq

replicate_row

(symlink: check_postgres_replicate_row) Checks that master-slave replication is working to one or more slaves.
The slaves are specified the same as the normal databases, except with
the number 2 at the end of them, so "–port2" instead of "–port", etc.
The values or the –warning and –critical options are units of time, and
at least one must be provided (no defaults). Valid units are ‘seconds’, ‘minutes’, ‘hours’,
or ‘days’. Each may be written singular or abbreviated to just the first letter.
If no units are given, the units are assumed to be seconds.

This check updates a single row on the master, and then measures how long it
takes to be applied to the slaves. To do this, you need to pick a table that
is being replicated, then find a row that can be changed, and is not going
to be changed by any other process. A specific column of this row will be changed
from one value to another. All of this is fed to the repinfo option, and should
contain the following options, separated by commas: table name, primary key, key id,
column, first value, second value.

Example 1: Slony is replicating a table named ‘orders’ from host ‘alpha’ to
host ‘beta’, in the database ‘sales’. The primary key of the table is named
id, and we are going to test the row with an id of 3 (which is historical and
never changed). There is a column named ‘salesrep’ that we are going to toggle
from a value of ‘slon’ to ‘nols’ to check on the replication. We want to throw
a warning if the replication does not happen within 10 seconds.

bash

  1. check_postgres_replicate_row --host=alpha --dbname=sales --host2=beta
  2.   --dbname2=sales --warning=10 --repinfo=orders,id,3,salesrep,slon,nols

Example 2: Bucardo is replicating a table named ‘receipt’ from host ‘green’
to hosts ‘red’, ‘blue’, and ‘yellow’. The database for both sides is ‘public’.
The slave databases are running on port 5455. The primary key is named ‘receipt_id’,
the row we want to use has a value of 9, and the column we want to change for the
test is called ‘zone’. We’ll toggle between ‘north’ and ‘south’ for the value of
this column, and throw a critical if the change is not on all three slaves within 5 seconds.

bash

  1. check_postgres_replicate_row --host=green --port2=5455 --host2=red,blue,yellow
  2.   --critical=5 --repinfo=receipt,receipt_id,9,zone,north,south

For MRTG output, returns on the first line the time in seconds the replication takes to finish.
The maximum time is set to 4 minutes 30 seconds: if no replication has taken place in that long
a time, an error is thrown.

txn_time

(symlink: check_postgres_txn_time) Checks the length of open transactions on one or more databases.
There is no need to run this command more than once per database cluster.
Databases can be filtered by use of the
–include and –exclude options. See the BASIC FILTERING section
for more details. The owner of the transaction can also be filtered, by use of
the –includeuser and –excludeuser options.
See the USER NAME FILTERING section for more details.

The values or the –warning and –critical options are units of time, and
must be provided (no default). Valid units are ‘seconds’, ‘minutes’, ‘hours’,
or ‘days’. Each may be written singular or abbreviated to just the first letter.
If no units are given, the units are assumed to be seconds.

This action requires Postgres 8.3 or better.

Example 1: Give a critical if any transaction has been open for more than 10 minutes:

bash

  1. check_postgres_txn_time --port=5432 --critical='10 minutes'

Example 1: Warn if user ‘warehouse’ has a transaction open over 30 seconds

bash

  1. check_postgres_txn_time --port-5432 --warning=30s --includeuser=warehouse

For MRTG output, returns the maximum time in seconds a transaction has been open on the
first line. The fourth line gives the name of the database.

Leave a Reply

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

clear formPost comment