Script 'mail_helper' called by obssrc Hello community, here is the log from the commit of package check_postgres for openSUSE:Factory checked in at 2023-04-12 12:51:31 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Comparing /work/SRC/openSUSE:Factory/check_postgres (Old) and /work/SRC/openSUSE:Factory/.check_postgres.new.19717 (New) ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Package is "check_postgres" Wed Apr 12 12:51:31 2023 rev:5 rq:1078503 version:2.26.0 Changes: -------- --- /work/SRC/openSUSE:Factory/check_postgres/check_postgres.changes 2022-03-11 11:40:55.774584967 +0100 +++ /work/SRC/openSUSE:Factory/.check_postgres.new.19717/check_postgres.changes 2023-04-12 12:51:32.296910211 +0200 @@ -1,0 +2,22 @@ +Fri Apr 7 14:24:27 UTC 2023 - Martin Hauke <mar...@gmx.de> + +- Update to version 2.26.0 + * Add new action "pgbouncer_maxwait". + * For the bloat check, add option to populate all known databases, + as well as includsion and exclusion regexes. + * Add Partman premake check. + * Add --role flag to explicitly set the role of the user after + connecting. + * Fix check_replication_slots on recently promoted servers. + * Allow the check_disk_space action to handle relative + log_directory paths. + * Fix MINPAGES and MINIPAGES in the "check_bloat" action. + * Replace 'which' with 'command -v'. + * Fix check_replication_slots on recently promoted servers. + * Fix undefined variable warning. + * In the tests, force log_destination to stderr. + * Add to docs how to exclude all items in the 'pg_temp_nnn' + per-session temporary schemas. + * Various improvements to the tests. + +------------------------------------------------------------------- Old: ---- check_postgres-2.25.0.tar.gz New: ---- check_postgres-2.26.0.tar.gz ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Other differences: ------------------ ++++++ check_postgres.spec ++++++ --- /var/tmp/diff_new_pack.VrzXIJ/_old 2023-04-12 12:51:32.716912666 +0200 +++ /var/tmp/diff_new_pack.VrzXIJ/_new 2023-04-12 12:51:32.720912689 +0200 @@ -1,7 +1,7 @@ # # spec file for package check_postgres # -# Copyright (c) 2022 SUSE LLC +# Copyright (c) 2023 SUSE LLC # # All modifications and additions to the file contributed by third parties # remain the property of their copyright owners, unless otherwise agreed @@ -17,7 +17,7 @@ Name: check_postgres -Version: 2.25.0 +Version: 2.26.0 Release: 0 Summary: Postgres monitoring script License: GPL-2.0-or-later ++++++ check_postgres-2.25.0.tar.gz -> check_postgres-2.26.0.tar.gz ++++++ diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/.travis.yml new/check_postgres-2.26.0/.travis.yml --- old/check_postgres-2.25.0/.travis.yml 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/.travis.yml 2023-04-03 19:24:14.000000000 +0200 @@ -2,35 +2,31 @@ --- # versions to run on env: + - PGVERSION=13 + - PGVERSION=12 + - PGVERSION=11 - PGVERSION=10 - PGVERSION=9.6 - PGVERSION=9.5 - PGVERSION=9.4 - PGVERSION=9.3 - - PGVERSION=9.2 - - PGVERSION=9.1 - - PGVERSION=9.0 - - PGVERSION=8.4 -dist: trusty +dist: focal sudo: required language: perl perl: - - '5.8' # 5.8.8 is shipped with RHEL 5, also oldest version supported by Travis - - '5.24' + - '5.30.0' + - '5.14' # 5.14 is shipped with Ubuntu precise (12.04), also oldest version supported by Travis on focal before_install: - sudo apt-get -qq update install: - # install PostgreSQL $PGVERSION if not there yet - - | - if [ ! -x /usr/lib/postgresql/$PGVERSION/bin/postgres ]; then - sudo apt-get install postgresql-common - sudo /etc/init.d/postgresql stop # travis wants only one version running - sudo apt-get install postgresql-contrib-$PGVERSION - fi - - sudo /etc/init.d/postgresql stop + - sudo apt-get install curl ca-certificates gnupg + - curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - + - sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' + - sudo apt-get update + - sudo apt-get install postgresql-$PGVERSION - pg_lsclusters - dpkg -l postgresql\* | cat - printenv | sort diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/LICENSE new/check_postgres-2.26.0/LICENSE --- old/check_postgres-2.25.0/LICENSE 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/LICENSE 2023-04-03 19:24:14.000000000 +0200 @@ -1,4 +1,4 @@ -Copyright (c) 2007-2020 Greg Sabino Mullane +Copyright 2007 - 2023 Greg Sabino Mullane Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/META.yml new/check_postgres-2.26.0/META.yml --- old/check_postgres-2.25.0/META.yml 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/META.yml 2023-04-03 19:24:14.000000000 +0200 @@ -1,6 +1,6 @@ --- #YAML:1.0 name : check_postgres.pl -version : 2.25.0 +version : 2.26.0 abstract : Postgres monitoring script author: - Greg Sabino Mullane <g...@turnstep.com> @@ -30,7 +30,7 @@ provides: check_postgres: file : check_postgres.pl - version : 2.25.0 + version : 2.26.0 keywords: - Postgres @@ -44,7 +44,6 @@ homepage : http://bucardo.org/check_postgres/ license : http://bucardo.org/check_postgres/ bugtracker : https://github.com/bucardo/check_postgres/issues - MailingList : https://mail.endcrypt.com/mailman/listinfo/check_postgres Repository : git://bucardo.org/check_postgres.git meta-spec: diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/Makefile.PL new/check_postgres-2.26.0/Makefile.PL --- old/check_postgres-2.25.0/Makefile.PL 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/Makefile.PL 2023-04-03 19:24:14.000000000 +0200 @@ -6,7 +6,7 @@ use warnings; use 5.008; -my $VERSION = '2.25.0'; +my $VERSION = '2.26.0'; if ($VERSION =~ /_/) { print "WARNING! This is a test version ($VERSION) and should not be used in production!\n"; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/README.md new/check_postgres-2.26.0/README.md --- old/check_postgres-2.25.0/README.md 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/README.md 2023-04-03 19:24:14.000000000 +0200 @@ -1,7 +1,7 @@ check_postgres ============== -[](https://travis-ci.org/bucardo/check_postgres) +[](https://travis-ci.com/bucardo/check_postgres) This is check_postgres, a monitoring tool for Postgres. @@ -14,8 +14,8 @@ Quick method ------------ -For the impatient Nagios admin, just copy the "check_postgres.pl" file -to your Nagios scripts directory, and perhaps symlink entries to that +For the impatient Nagios admin, just copy the "check_postgres.pl" file +to your Nagios scripts directory, and perhaps symlink entries to that file by: cd <the directory you just copied the file to> @@ -23,8 +23,6 @@ cd postgres perl ../check_postgres.pl --symlinks -Then join the announce mailing list (see below) - Complete method --------------- @@ -35,17 +33,17 @@ env -i make test make install -The last step usually needs to be done as the root user. You may want to -copy the script to a place that makes more sense for Nagios, if using it +The last step usually needs to be done as the root user. You may want to +copy the script to a place that makes more sense for Nagios, if using it for that purpose. See the "Quick" instructions above. -For `make test`, please report any failing tests to check_postg...@bucardo.org. -The tests need to have some standard Postgres binaries available, such as -`initdb`, `psql`, and `pg_ctl`. If these are not in your path, or you want to -use specific ones, please set the environment variable `PGBINDIR` first. More +For `make test`, please report any failing tests to check_postg...@bucardo.org. +The tests need to have some standard Postgres binaries available, such as +`initdb`, `psql`, and `pg_ctl`. If these are not in your path, or you want to +use specific ones, please set the environment variable `PGBINDIR` first. More details on running the testsuite are available in `README.dev`. -Once `make install` has been done, you should have access to the complete +Once `make install` has been done, you should have access to the complete documentation by typing: man check_postgres @@ -54,20 +52,6 @@ https://bucardo.org/check_postgres/check_postgres.pl.html -Mailing lists -------------- - -The final step should be to subscribe to the low volume check_postgres-announce -mailing list, so you learn of new versions and important changes. Information -on joining can be found at: - -https://mail.endcrypt.com/mailman/listinfo/check_postgres-announce - -General questions and development issues are discussed on the check_postgres list, -which we recommend people join as well: - -https://mail.endcrypt.com/mailman/listinfo/check_postgres - Development happens via git. You can check out the repository by doing: https://github.com/bucardo/check_postgres @@ -76,27 +60,27 @@ COPYRIGHT --------- - Copyright (c) 2007-2020 Greg Sabino Mullane + Copyright 2007 - 2023 Greg Sabino Mullane LICENSE INFORMATION ------------------- -Redistribution and use in source and binary forms, with or without +Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - 1. Redistributions of source code must retain the above copyright notice, + 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - 2. Redistributions in binary form must reproduce the above copyright notice, - this list of conditions and the following disclaimer in the documentation + 2. Redistributions in binary form must reproduce the above copyright notice, + this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. -THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED -WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF -MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO +THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED +WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, -EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT -OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS -INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN -CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING -IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY +EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT +OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS +INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN +CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING +IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/check_postgres.pl new/check_postgres-2.26.0/check_postgres.pl --- old/check_postgres-2.25.0/check_postgres.pl 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/check_postgres.pl 2023-04-03 19:24:14.000000000 +0200 @@ -34,7 +34,7 @@ binmode STDOUT, ':encoding(UTF-8)'; -our $VERSION = '2.25.0'; +our $VERSION = '2.26.0'; our $COMMA = ','; use vars qw/ %opt $PGBINDIR $PSQL $res $COM $SQL $db /; @@ -139,7 +139,7 @@ 'die-nosetting' => q{Could not fetch setting '$1'}, 'diskspace-fail' => q{Invalid result from command "$1": $2}, 'diskspace-msg' => q{FS $1 mounted on $2 is using $3 of $4 ($5%)}, - 'diskspace-nodata' => q{Could not determine data_directory: are you connecting as a superuser?}, + 'diskspace-nodata' => q{Could not determine data_directory: are you running as a superuser?}, 'diskspace-nodf' => q{Could not find required executable /bin/df}, 'diskspace-nodir' => q{Could not find data directory "$1"}, 'file-noclose' => q{Could not close $1: $2}, @@ -193,6 +193,7 @@ 'no-match-slot' => q{No matching replication slots found due to exclusion/inclusion options}, 'no-match-slotok' => q{No replication slots found}, 'no-parse-psql' => q{Could not parse psql output!}, + 'no-role' => q{Need psql 9.6 or higher to use --role}, 'no-time-hires' => q{Cannot find Time::HiRes, needed if 'showtime' is true}, 'opt-output-invalid' => q{Invalid output: must be 'nagios' or 'mrtg' or 'simple' or 'cacti'}, 'opt-psql-badpath' => q{Invalid psql argument: must be full path to a file named psql}, @@ -201,12 +202,18 @@ 'opt-psql-nofind' => q{Could not find a suitable psql executable}, 'opt-psql-nover' => q{Could not determine psql version}, 'opt-psql-restrict' => q{Cannot use the --PGBINDIR or --PSQL option when NO_PSQL_OPTION is on}, + 'partman-premake-ok' => q{All premade partitions are present}, + 'partman-conf-tbl' => q{misconfigured in partman.part_config}, + 'partman-conf-mis' => q{missing table in partman.part_config}, 'pgagent-jobs-ok' => q{No failed jobs}, 'pgbouncer-pool' => q{Pool=$1 $2=$3}, 'pgb-backends-mrtg' => q{DB=$1 Max connections=$2}, 'pgb-backends-msg' => q{$1 of $2 connections ($3%)}, 'pgb-backends-none' => q{No connections}, 'pgb-backends-users' => q{$1 for number of users must be a number or percentage}, + 'pgb-maxwait-msg' => q{longest wait: $1s}, + 'pgb-maxwait-nomatch'=> q{No matching rows were found}, + 'pgb-maxwait-skipped'=> q{No matching rows were found (skipped rows: $1)}, 'PID' => q{PID}, 'port' => q{port}, 'preptxn-none' => q{No prepared transactions found}, @@ -1686,6 +1693,9 @@ 'critical=s', 'include=s@', 'exclude=s@', + 'alldb', + 'includedb=s@', + 'excludedb=s@', 'includeuser=s@', 'excludeuser=s@', @@ -1695,6 +1705,7 @@ 'dbuser|u|dbuser1|u1=s@', 'dbpass|dbpass1=s@', 'dbservice|dbservice1=s@', + 'role=s', 'PGBINDIR=s', 'PSQL=s', @@ -1897,6 +1908,7 @@ new_version_cp => [0, 'Checks if a newer version of check_postgres.pl is available.'], new_version_pg => [0, 'Checks if a newer version of Postgres is available.'], new_version_tnm => [0, 'Checks if a newer version of tail_n_mail is available.'], + partman_premake => [1, 'Checks if premake partitions are present.'], pgb_pool_cl_active => [1, 'Check the number of active clients in each pgbouncer pool.'], pgb_pool_cl_waiting => [1, 'Check the number of waiting clients in each pgbouncer pool.'], pgb_pool_sv_active => [1, 'Check the number of active server connections in each pgbouncer pool.'], @@ -1907,6 +1919,7 @@ pgb_pool_maxwait => [1, 'Check the current maximum wait time for client connections in pgbouncer pools.'], pgbouncer_backends => [0, 'Check how many clients are connected to pgbouncer compared to max_client_conn.'], pgbouncer_checksum => [0, 'Check that no pgbouncer settings have changed since the last check.'], + pgbouncer_maxwait => [0, 'Check how long the first (oldest) client in queue has been waiting.'], pgagent_jobs => [0, 'Check for no failed pgAgent jobs within a specified period of time.'], prepared_txns => [1, 'Checks number and age of prepared transactions.'], query_runtime => [0, 'Check how long a specific query takes to run.'], @@ -1958,6 +1971,9 @@ -c value, --critical=value the critical threshold, range depends on the action --include=name(s) items to specifically include (e.g. tables), depends on the action --exclude=name(s) items to specifically exclude (e.g. tables), depends on the action + --alldb list all postgres databases and run action over them + --excludedb=name regex filter for the alldb option to select only certain databases + --includedb=name regex filter for the alldb option to select only certain databases --includeuser=include objects owned by certain users --excludeuser=exclude objects owned by certain users @@ -2088,7 +2104,7 @@ } else { my $psql = (defined $PGBINDIR) ? "$PGBINDIR/psql" : 'psql'; - chomp($PSQL = qx{which "$psql"}); + chomp($PSQL = qx{command -v "$psql"}); $PSQL or ndie msg('opt-psql-nofind'); } -x $PSQL or ndie msg('opt-psql-noexec', $PSQL); @@ -2101,6 +2117,44 @@ $opt{defaultdb} = $psql_version >= 8.0 ? 'postgres' : 'template1'; $opt{defaultdb} = 'pgbouncer' if $action =~ /^pgb/; +## If alldb is set then run a psql command to find out all the databases +if (defined $opt{alldb}){ + + my $pg_port = $opt{defaultport}; + if ($opt{port}[0]){ + $pg_port = $opt{port}[0]; + } + my $psql_output = join(",", map /^([\w|-]+?)\|/, qx{$PSQL -A -l -t -p $pg_port }); + my $pg_db; + # optionally exclude or include each db + my @psql_output_array = split(/,/, $psql_output); + for $pg_db (@psql_output_array) { + if (defined $opt{includedb}){ + if ($pg_db =~ /$opt{includedb}[0]/) { + # do nothing + } else { + # strip the database from the listing + $psql_output =~ s/($pg_db),//; + } + } + if (defined $opt{excludedb}){ + if ($pg_db =~ /$opt{excludedb}[0]/) { + # strip the database from the listing + $psql_output =~ s/($pg_db),//; + } else { + # do nothing + } + } + } + # strip out some dbs we're not interested in + $psql_output =~ s/(template0,)//; + $psql_output =~ s/(root,)//; + # pg8.4 + $psql_output =~ s/(,:)//g; + $opt{dbname}[0] = $psql_output; +} + + ## Check the current database mode our $STANDBY = 0; our $MASTER = 0; @@ -2733,6 +2787,9 @@ ## Make sure Slony is behaving check_slony_status() if $action eq 'slony_status'; +## Make sure Partman premake is working +check_partman_premake() if $action eq 'partman_premake'; + ## Verify that the pgbouncer settings are what we think they should be check_pgbouncer_checksum() if $action eq 'pgbouncer_checksum'; @@ -2760,6 +2817,9 @@ ## Check the current maximum wait time for client connections in pgbouncer pools check_pgb_pool('maxwait') if $action eq 'pgb_pool_maxwait'; +## Check how long the first (oldest) client in queue has been waiting. +check_pgbouncer_maxwait() if $action eq 'pgbouncer_maxwait'; + ## Check how many clients are connected to pgbouncer compared to max_client_conn. check_pgbouncer_backends() if $action eq 'pgbouncer_backends'; @@ -2972,9 +3032,11 @@ $tempdir = tempdir(@tempdirargs); ($tempfh,$tempfile) = tempfile('check_postgres_psql.XXXXXXX', SUFFIX => '.tmp', DIR => $tempdir); + binmode($tempfh, ':utf8'); ## Create another one to catch any errors ($errfh,$errorfile) = tempfile('check_postgres_psql_stderr.XXXXXXX', SUFFIX => '.tmp', DIR => $tempdir); + binmode($errfh, ':utf8'); ## Mild cleanup of the query $string =~ s/^\s*(.+?)\s*$/$1/s; @@ -3081,6 +3143,14 @@ local $SIG{ALRM} = sub { die "Timed out\n" }; alarm 0; + if ($opt{role}) { + if ($psql_version < 9.6) { + ndie msg('no-role') + } + else { + push @args, '-c', "SET ROLE $opt{role}"; + } + } push @args, '-c', $string; $VERBOSE >= 3 and warn Dumper \@args; @@ -4307,8 +4377,8 @@ ## Can also specify percentages ## Don't bother with tables or indexes unless they have at least this many bloated pages - my $MINPAGES = 0; - my $MINIPAGES = 10; + my $MINPAGES = 10; + my $MINIPAGES = 15; my $LIMIT = 10; if ($opt{perflimit}) { @@ -5211,7 +5281,7 @@ ## Check log_directory: relative or absolute if (length $logdir) { - if ($logdir =~ /^\w/) { ## relative, check only if symlinked + if ($logdir =~ /^[\w\.]/) { ## relative, check only if symlinked $logdir = "$datadir/$logdir"; if (-l $logdir) { my $linkdir = readlink($logdir); @@ -5705,7 +5775,11 @@ WITH slots AS (SELECT slot_name, slot_type, coalesce(restart_lsn, '0/0'::pg_lsn) AS slot_lsn, - coalesce(pg_xlog_location_diff(coalesce(pg_last_xlog_receive_location(), pg_current_xlog_location()), restart_lsn),0) AS delta, + coalesce( + pg_xlog_location_diff( + case when pg_is_in_recovery() then pg_last_xlog_receive_location() else pg_current_xlog_location() end, + restart_lsn), + 0) AS delta, active FROM pg_replication_slots) SELECT *, pg_size_pretty(delta) AS delta_pretty FROM slots; @@ -6509,6 +6583,171 @@ return; } +sub check_partman_premake { + + ## Checks if all premade partitions are present + ## Monthly and daily interval only + ## Supports: Nagios + + my $msg = msg('partman-premake-ok'); + my $found = 0; + my ($warning, $critical) = validate_range + ({ + type => 'integer', # in days + default_warning => '1', + default_critical => '3', + }); + + # check missing Config for range partitioned tables + + my $SQL = q{ +SELECT + current_database() AS database, + c.relnamespace::regnamespace || '.' || c.relname AS parent_table +FROM + pg_class c + JOIN pg_partitioned_table t ON t.partrelid = c.oid +WHERE + c.relkind = 'p' + AND t.partstrat = 'r' + AND NOT EXISTS ( + SELECT + 1 + FROM + partman.part_config + WHERE + parent_table = c.relnamespace::regnamespace || '.' || c.relname); +}; + + my $info = run_command($SQL, {regex => qr[\w+], emptyok => 1 } ); + my (@crit,@warn,@ok); + + for $db (@{$info->{db}}) { + my ($maxage,$maxdb) = (0,''); ## used by MRTG only + ROW: for my $r (@{$db->{slurp}}) { + my ($dbname,$parent_table) = ($r->{database},$r->{parent_table}); + $found = 1 if ! $found; + next ROW if skip_item($dbname); + $found = 2; + + $msg = "$dbname=$parent_table " . msg('partman-conf-mis'); + push @crit => $msg; + }; + }; + + # check Config Errors + + $SQL = q{ +SELECT + current_database() as database, + parent_table +FROM ( + SELECT + parent_table, + retention, + partition_interval, + EXTRACT(EPOCH FROM retention::interval) / EXTRACT(EPOCH FROM partition_interval::interval) AS configured_partitions + FROM + partman.part_config) p +WHERE + configured_partitions < 1; +}; + + $info = run_command($SQL, {regex => qr[\w+], emptyok => 1 } ); + + for $db (@{$info->{db}}) { + my ($maxage,$maxdb) = (0,''); ## used by MRTG only + ROW: for my $r (@{$db->{slurp}}) { + my ($dbname,$parent_table) = ($r->{database},$r->{parent_table}); + $found = 1 if ! $found; + next ROW if skip_item($dbname); + $found = 2; + + $msg = "$dbname=$parent_table " . msg('partman-conf-tbl'); + push @warn => $msg; + }; + }; + + + $SQL = q{ +SELECT + current_database() as database, + a.parent_table, + b.date - a.date::date AS missing_days +FROM +( +SELECT parent_table, date +FROM ( SELECT + i.inhparent::regclass as parent_table, + substring(pg_catalog.pg_get_expr(c.relpartbound, i.inhrelid)::text FROM '%TO __#"_{10}#"%' FOR '#') as date, + rank() OVER (PARTITION BY i.inhparent ORDER BY pg_catalog.pg_get_expr(c.relpartbound, i.inhrelid) DESC) + FROM pg_inherits i + JOIN pg_class c ON c.oid = i.inhrelid +WHERE c.relkind = 'r' + AND pg_catalog.pg_get_expr(c.relpartbound, i.inhrelid) != 'DEFAULT') p +WHERE + p.rank = 1 +) a +JOIN +( +SELECT + parent_table, + (now() + premake * partition_interval::interval)::date +FROM + partman.part_config +) b +ON + a.parent_table::text = b.parent_table::text +WHERE + b.date - a.date::date > 0 +ORDER BY 3, 2 DESC +}; + + $info = run_command($SQL, {regex => qr[\w+], emptyok => 1 } ); + + for $db (@{$info->{db}}) { + my ($maxage,$maxdb) = (0,''); ## used by MRTG only + ROW: for my $r (@{$db->{slurp}}) { + my ($dbname,$parent_table,$missing_days) = ($r->{database},$r->{parent_table},$r->{missing_days}); + $found = 1 if ! $found; + next ROW if skip_item($dbname); + $found = 2; + + $msg = "$dbname=$parent_table ($missing_days)"; + print "$msg"; + $db->{perf} .= sprintf ' %s=%sd;%s;%s', + perfname($dbname), $missing_days, $warning, $critical; + if (length $critical and $missing_days >= $critical) { + push @crit => $msg; + } + elsif (length $warning and $missing_days >= $warning) { + push @warn => $msg; + } + else { + push @ok => $msg; + } + } + if (0 == $found) { + add_ok msg('partman-premake-ok'); + } + elsif (1 == $found) { + add_unknown msg('no-match-db'); + } + elsif (@crit) { + add_critical join ' ' => @crit; + } + elsif (@warn) { + add_warning join ' ' => @warn; + } + else { + add_ok join ' ' => @ok; + } + } + + return; + +} ## end of check_partman_premake + sub check_pgbouncer_checksum { ## Verify the checksum of all pgbouncer settings @@ -6570,6 +6809,107 @@ } ## end of check_pgbouncer_checksum +sub check_pgbouncer_maxwait { + + ## Check how long the first (oldest) client in queue has waited, in + ## seconds. + ## Supports: Nagios, MRTG + ## Warning and critical are time limits - defaults to seconds + ## Valid units: s[econd], m[inute], h[our], d[ay] + ## All above may be written as plural as well (e.g. "2 hours") + ## Can also ignore databases with exclude and limit with include + + my $arg = shift || {}; + + my ($warning, $critical) = validate_range + ({ + type => 'time', + }); + + ## Grab information from the pg_stat_activity table + ## Since we clobber old info on a qtime "tie", use an ORDER BY + $SQL = qq{SHOW POOLS}; + + my $info = run_command($SQL, { regex => qr{\d+}, emptyok => 1 } ); + + ## Default values for information gathered + my ($maxwait, $database, $user, $cl_active, $cl_waiting) = + (0,'?','?',0,0); + + for $db (@{$info->{db}}) { + + ## Parse the psql output and gather stats from the winning row + ## Read in and parse the psql output + my $skipped = 0; + ROW: for my $r (@{$db->{slurp}}) { + + ## Apply --exclude and --include arguments to the database name + if (skip_item($r->{database})) { + $skipped++; + next ROW; + } + + ## Assign stats if we have a new winner + if ($r->{maxwait} > $maxwait) { + $database = $r->{database}; + $user = $r->{user}; + $cl_active = $r->{cl_active}; + $cl_waiting = $r->{cl_waiting}; + $maxwait = $r->{maxwait}; + } + } + + ## We don't really care why things matches as far as the final output + ## But it's nice to report what we can + if ($database eq '?') { + $MRTG and do_mrtg({one => 0, msg => 'No rows'}); + $db->{perf} = "0;$warning;$critical"; + + if ($skipped) { + add_ok msg('pgb-maxwait-skipped', $skipped); + } + else { + add_ok msg('pgb-maxwait-nomatch', $maxwait); + } + return; + } + + ## Details on who the offender was + my $whodunit = sprintf q{%s:%s %s:%s cl_active:%s cl_waiting:%s}, + msg('database'), + $database, + msg('username'), + $user, + $cl_active, + $cl_waiting; + + $MRTG and do_mrtg({one => $maxwait, msg => "$whodunit"}); + + $db->{perf} .= sprintf q{'%s'=%s;%s;%s}, + $whodunit, + $maxwait, + $warning, + $critical; + + my $m = msg('pgb-maxwait-msg', $maxwait); + my $msg = sprintf '%s (%s)', $m, $whodunit; + + if (length $critical and $maxwait >= $critical) { + add_critical $msg; + } + elsif (length $warning and $maxwait >= $warning) { + add_warning $msg; + } + else { + add_ok $msg; + } + } + + return; + + +} ## end of check_pgbouncer_maxwait + sub check_pgbouncer_backends { ## Check the number of connections to pgbouncer compared to @@ -8778,7 +9118,7 @@ $maxr->{client_addr} eq '' ? '' : (sprintf ' %s:%s', msg('address'), $maxr->{client_addr}), ($maxr->{client_port} eq '' or $maxr->{client_port} < 1) ? '' : (sprintf ' %s:%s', msg('port'), $maxr->{client_port}), - msg('query'), $maxr->{query} || $maxr->{current_query}; + msg('query'), $maxr->{query} // $maxr->{current_query}; } ## For MRTG, we can simply exit right now @@ -9049,7 +9389,7 @@ B<check_postgres.pl> - a Postgres monitoring script for Nagios, MRTG, Cacti, and others -This documents describes check_postgres.pl version 2.25.0 +This documents describes check_postgres.pl version 2.26.0 =head1 SYNOPSIS @@ -9196,6 +9536,13 @@ The documentation for this file can be found at L<https://www.postgresql.org/docs/current/static/libpq-pgservice.html> +=item B<--role=ROLE> + +Provides the role to switch to after connecting to the database but before running the given check. +This provides the ability to have superuser privileges assigned to a role without LOGIN access for +the purposes of audit and other security considerations. Requires a local `psql` version 9.6 or +higher. + =back The database connection options can be grouped: I<--host=a,b --host=c --port=1234 --port=3344> @@ -9420,7 +9767,7 @@ If the archive command fail, number of WAL in your F<pg_xlog> directory will grow until exhausting all the disk space and force PostgreSQL to stop immediately. -To avoid connecting as a database superuser, a wrapper function around +To avoid running as a database superuser, a wrapper function around C<pg_ls_dir()> should be defined as a superuser with SECURITY DEFINER, and the I<--lsfunc> option used. This example function, if defined by a superuser, will allow the script to connect as a normal user @@ -9475,8 +9822,9 @@ You can also filter the databases by use of the I<--include> and I<--exclude> options. See the L</"BASIC FILTERING"> section for more details. -To view only non-idle processes, you can use the I<--noidle> argument. Note that the -user you are connecting as must be a superuser for this to work properly. +To view only non-idle processes, you can use the I<--noidle> argument. Note that the user you are +running as (either connecting directly or switching via I<--role>) must be a superuser for this to +work properly. Example 1: Give a warning when the number of connections on host quirm reaches 120, and a critical if it reaches 150. @@ -9845,7 +10193,8 @@ (C<symlink: check_postgres_disk_space>) Checks on the available physical disk space used by Postgres. This action requires that you have the executable "/bin/df" available to report on disk sizes, and it -also needs to be run as a superuser, so it can examine the B<data_directory> +also needs to be run as a superuser (either connecting directly or switching via I<--role>), +so it can examine the B<data_directory> setting inside of Postgres. The I<--warning> and I<--critical> options are given in either sizes or percentages or both. If using sizes, the standard unit types are allowed: bytes, kilobytes, gigabytes, megabytes, gigabytes, terabytes, or @@ -10258,9 +10607,9 @@ of the I<--include> and I<--exclude> options. See the L</"BASIC FILTERING"> section for more details. -To view only non-idle processes, you can use the I<--noidle> argument. Note -that the user you are connecting as must be a superuser for this to work -properly. +To view only non-idle processes, you can use the I<--noidle> argument. Note that the user you are +running as (either connecting directly or switching via I<--role>) must be a superuser for this to +work properly. Example 1: Give a warning when the number of connections on host quirm reaches 120, and a critical if it reaches 150. @@ -10307,6 +10656,30 @@ checksum must be provided as the C<--mrtg> argument. The fourth line always gives the current checksum. +=head2 B<pgbouncer_maxwait> + +(C<symlink: check_postgres_pgbouncer_maxwait>) Checks how long the first +(oldest) client in the queue has been waiting, in seconds. If this starts +increasing, then the current pool of servers does not handle requests quick +enough. Reason may be either overloaded server or just too small of a +pool_size setting in pbouncer config file. Databases can be filtered by use +of the I<--include> and I<--exclude> options. See the L</"BASIC FILTERING"> +section for more details. The values or the I<--warning> and I<--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: + + check_postgres_pgbouncer_maxwait -p 6432 -u pgbouncer --critical='10 minutes' + +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. + =head2 B<pgagent_jobs> (C<symlink: check_postgres_pgagent_jobs>) Checks that all the pgAgent jobs @@ -10845,6 +11218,10 @@ --exclude='pg_catalog.' +Exclude all items in the 'pg_temp_nnn' per-session temporary schemas: + + --exclude=~^pg_temp_. + Exclude all items containing the letters 'ace', but allow the item 'faceoff': --exclude=~ace --include=faceoff @@ -10987,6 +11364,38 @@ =over 4 +=item B<Version 2.26.0> Released April 3, 2023 + + Add new action "pgbouncer_maxwait" (Ruslan Kabalin) [Github pull #59] + + For the bloat check, add option to populate all known databases, + as well as includsion and exclusion regexes. (Giles Westwood) [Github pull #86] + + Add Partman premake check (Jens Wilke) [Github pull #196] + + Add --role flag to explicitly set the role of the user after connecting (David Christensen) + + Fix check_replication_slots on recently promoted servers (Christoph Berg) + + Allow the check_disk_space action to handle relative log_directory paths (jacksonfoz) [Github pull #174] + + Fix MINPAGES and MINIPAGES in the "check_bloat" action (Christoph Moench-Tegeder) [Github pull #82] + + Replace 'which' with 'command -v' (Christoph Berg) + + Fix check_replication_slots on recently promoted servers (Christoph Berg) + + Fix undefined variable warning (Michael van Bracht) [Github pull #158] + + In the tests, force log_destination to stderr (Christoph Moench-Tegeder) [Github pull #185] + + Add to docs how to exclude all items in the 'pg_temp_nnn' per-session temporary schemas (Michael Banck) + + Various fixes for the CI system (Emre Hasegeli) [Github pull #181] + + Various improvements to the tests (Christoph Berg, Emre Hasegeli) + + =item B<Version 2.25.0> Released February 3, 2020 Allow same_schema objects to be included or excluded with --object and --skipobject @@ -11821,7 +12230,7 @@ =head1 LICENSE AND COPYRIGHT -Copyright (c) 2007-2020 Greg Sabino Mullane <g...@turnstep.com>. +Copyright 2007 - 2023 Greg Sabino Mullane <g...@turnstep.com>. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/check_postgres.pl.html new/check_postgres-2.26.0/check_postgres.pl.html --- old/check_postgres-2.25.0/check_postgres.pl.html 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/check_postgres.pl.html 2023-04-03 19:24:14.000000000 +0200 @@ -74,6 +74,7 @@ <li><a href="#pgb_pool_maxwait">pgb_pool_maxwait</a></li> <li><a href="#pgbouncer_backends">pgbouncer_backends</a></li> <li><a href="#pgbouncer_checksum">pgbouncer_checksum</a></li> + <li><a href="#pgbouncer_maxwait">pgbouncer_maxwait</a></li> <li><a href="#pgagent_jobs">pgagent_jobs</a></li> <li><a href="#prepared_txns">prepared_txns</a></li> <li><a href="#query_runtime">query_runtime</a></li> @@ -114,7 +115,7 @@ <p><b>check_postgres.pl</b> - a Postgres monitoring script for Nagios, MRTG, Cacti, and others</p> -<p>This documents describes check_postgres.pl version 2.25.0</p> +<p>This documents describes check_postgres.pl version 2.26.0</p> <h1 id="SYNOPSIS">SYNOPSIS</h1> @@ -229,11 +230,17 @@ <dt id="dbservice-NAME"><b>--dbservice=NAME</b></dt> <dd> -<p>The name of a service inside of the pg_service.conf file. Before version 9.0 of Postgres, this is a global file, usually found in /etc/pg_service.conf. If you are using version 9.0 or higher of Postgres, you can use the file ".pg_service.conf" in the home directory of the user running the script, e.g. nagios.</p> +<p>The name of a service inside of the pg_service.conf file. Before version 9.0 of Postgres, this is a global file, usually found in <i>/etc/pg_service.conf</i>. If you are using version 9.0 or higher of Postgres, you can use the file ".pg_service.conf" in the home directory of the user running the script, e.g. nagios.</p> <p>This file contains a simple list of connection options. You can also pass additional information when using this option such as --dbservice="maindatabase sslmode=require"</p> -<p>The documentation for this file can be found at https://www.postgresql.org/docs/current/static/libpq-pgservice.html</p> +<p>The documentation for this file can be found at <a href="https://www.postgresql.org/docs/current/static/libpq-pgservice.html";>https://www.postgresql.org/docs/current/static/libpq-pgservice.html</a></p> + +</dd> +<dt id="role-ROLE"><b>--role=ROLE</b></dt> +<dd> + +<p>Provides the role to switch to after connecting to the database but before running the given check. This provides the ability to have superuser privileges assigned to a role without LOGIN access for the purposes of audit and other security considerations. Requires a local `psql` version 9.6 or higher.</p> </dd> </dl> @@ -443,7 +450,7 @@ <p>If the archive command fail, number of WAL in your <i>pg_xlog</i> directory will grow until exhausting all the disk space and force PostgreSQL to stop immediately.</p> -<p>To avoid connecting as a database superuser, a wrapper function around <code>pg_ls_dir()</code> should be defined as a superuser with SECURITY DEFINER, and the <i>--lsfunc</i> option used. This example function, if defined by a superuser, will allow the script to connect as a normal user <i>nagios</i> with <i>--lsfunc=ls_archive_status_dir</i></p> +<p>To avoid running as a database superuser, a wrapper function around <code>pg_ls_dir()</code> should be defined as a superuser with SECURITY DEFINER, and the <i>--lsfunc</i> option used. This example function, if defined by a superuser, will allow the script to connect as a normal user <i>nagios</i> with <i>--lsfunc=ls_archive_status_dir</i></p> <pre><code> BEGIN; CREATE FUNCTION ls_archive_status_dir() @@ -475,7 +482,7 @@ <p>(<code>symlink: check_postgres_backends</code>) 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 <b>max_connections</b>. The <i>--warning</i> and <i>--critical</i> 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 <b>max_connections</b> 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 <b>max_connections</b> is reached. The default values for <i>--warning</i> and <i>--critical</i> are '90%' and '95%'. You can also filter the databases by use of the <i>--include</i> and <i>--exclude</i> options. See the <a href="#BASIC-FILTERING">"BASIC FILTERING"</a> section for more details.</p> -<p>To view only non-idle processes, you can use the <i>--noidle</i> argument. Note that the user you are connecting as must be a superuser for this to work properly.</p> +<p>To view only non-idle processes, you can use the <i>--noidle</i> argument. Note that the user you are running as (either connecting directly or switching via <i>--role</i>) must be a superuser for this to work properly.</p> <p>Example 1: Give a warning when the number of connections on host quirm reaches 120, and a critical if it reaches 150.</p> @@ -774,7 +781,7 @@ <h2 id="disk_space"><b>disk_space</b></h2> -<p>(<code>symlink: check_postgres_disk_space</code>) Checks on the available physical disk space used by Postgres. This action requires that you have the executable "/bin/df" available to report on disk sizes, and it also needs to be run as a superuser, so it can examine the <b>data_directory</b> setting inside of Postgres. The <i>--warning</i> and <i>--critical</i> options are given in either sizes or percentages or both. If using sizes, the standard unit types are allowed: bytes, kilobytes, gigabytes, megabytes, gigabytes, terabytes, or exabytes. Each may be abbreviated to the first letter only; no units at all indicates 'bytes'. The default values are '90%' and '95%'.</p> +<p>(<code>symlink: check_postgres_disk_space</code>) Checks on the available physical disk space used by Postgres. This action requires that you have the executable "/bin/df" available to report on disk sizes, and it also needs to be run as a superuser (either connecting directly or switching via <i>--role</i>), so it can examine the <b>data_directory</b> setting inside of Postgres. The <i>--warning</i> and <i>--critical</i> options are given in either sizes or percentages or both. If using sizes, the standard unit types are allowed: bytes, kilobytes, gigabytes, megabytes, gigabytes, terabytes, or exabytes. Each may be abbreviated to the first letter only; no units at all indicates 'bytes'. The default values are '90%' and '95%'.</p> <p>This command checks the following things to determine all of the different physical disks being used by Postgres.</p> @@ -792,7 +799,7 @@ <pre><code> check_postgres_disk_space --port=5432 --warning='90%' --critical='90%'</code></pre> -<p>Example 2: Check that all file systems starting with /dev/sda are smaller than 10 GB and 11 GB (warning and critical)</p> +<p>Example 2: Check that all file systems starting with <i>/dev/sda</i> are smaller than 10 GB and 11 GB (warning and critical)</p> <pre><code> check_postgres_disk_space --port=5432 --warning='10 GB' --critical='11 GB' --include="~^/dev/sda"</code></pre> @@ -976,11 +983,11 @@ <h2 id="new_version_bc"><b>new_version_bc</b></h2> -<p>(<code>symlink: check_postgres_new_version_bc</code>) Checks if a newer version of the Bucardo program is available. The current version is obtained by running <code>bucardo_ctl --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (Bucardo is a master to slave, and master to master replication system for Postgres: see https://bucardo.org/ for more information). See also the information on the <code>--get_method</code> option.</p> +<p>(<code>symlink: check_postgres_new_version_bc</code>) Checks if a newer version of the Bucardo program is available. The current version is obtained by running <code>bucardo_ctl --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (Bucardo is a master to slave, and master to master replication system for Postgres: see <a href="https://bucardo.org/";>https://bucardo.org/</a> for more information). See also the information on the <code>--get_method</code> option.</p> <h2 id="new_version_box"><b>new_version_box</b></h2> -<p>(<code>symlink: check_postgres_new_version_box</code>) Checks if a newer version of the boxinfo program is available. The current version is obtained by running <code>boxinfo.pl --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (boxinfo is a program for grabbing important information from a server and putting it into a HTML format: see https://bucardo.org/Boxinfo/ for more information). See also the information on the <code>--get_method</code> option.</p> +<p>(<code>symlink: check_postgres_new_version_box</code>) Checks if a newer version of the boxinfo program is available. The current version is obtained by running <code>boxinfo.pl --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (boxinfo is a program for grabbing important information from a server and putting it into a HTML format: see <a href="https://bucardo.org/Boxinfo/";>https://bucardo.org/Boxinfo/</a> for more information). See also the information on the <code>--get_method</code> option.</p> <h2 id="new_version_cp"><b>new_version_cp</b></h2> @@ -992,7 +999,7 @@ <h2 id="new_version_tnm"><b>new_version_tnm</b></h2> -<p>(<code>symlink: check_postgres_new_version_tnm</code>) Checks if a newer version of the tail_n_mail program is available. The current version is obtained by running <code>tail_n_mail --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (tail_n_mail is a log monitoring tool that can send mail when interesting events appear in your Postgres logs. See: https://bucardo.org/tail_n_mail/ for more information). See also the information on the <code>--get_method</code> option.</p> +<p>(<code>symlink: check_postgres_new_version_tnm</code>) Checks if a newer version of the tail_n_mail program is available. The current version is obtained by running <code>tail_n_mail --version</code>. If a major upgrade is available, a warning is returned. If a revision upgrade is available, a critical is returned. (tail_n_mail is a log monitoring tool that can send mail when interesting events appear in your Postgres logs. See: <a href="https://bucardo.org/tail_n_mail/";>https://bucardo.org/tail_n_mail/</a> for more information). See also the information on the <code>--get_method</code> option.</p> <h2 id="pgb_pool_cl_active"><b>pgb_pool_cl_active</b></h2> @@ -1018,7 +1025,7 @@ <p>(<code>symlink: check_postgres_pgbouncer_backends</code>) Checks the current number of connections for one or more databases through pgbouncer, and optionally compares it to the maximum allowed, which is determined by the pgbouncer configuration variable <b>max_client_conn</b>. The <i>--warning</i> and <i>--critical</i> 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 <b>max_connections</b> 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 <b>max_connections</b> is reached. The default values for <i>--warning</i> and <i>--critical</i> are '90%' and '95%'. You can also filter the databases by use of the <i>--include</i> and <i>--exclude</i> options. See the <a href="#BASIC-FILTERING">"BASIC FILTERING"</a> section fo r more details.</p> -<p>To view only non-idle processes, you can use the <i>--noidle</i> argument. Note that the user you are connecting as must be a superuser for this to work properly.</p> +<p>To view only non-idle processes, you can use the <i>--noidle</i> argument. Note that the user you are running as (either connecting directly or switching via <i>--role</i>) must be a superuser for this to work properly.</p> <p>Example 1: Give a warning when the number of connections on host quirm reaches 120, and a critical if it reaches 150.</p> @@ -1050,6 +1057,18 @@ <p>For MRTG output, returns a 1 or 0 indicating success of failure of the checksum to match. A checksum must be provided as the <code>--mrtg</code> argument. The fourth line always gives the current checksum.</p> +<h2 id="pgbouncer_maxwait"><b>pgbouncer_maxwait</b></h2> + +<p>(<code>symlink: check_postgres_pgbouncer_maxwait</code>) Checks how long the first (oldest) client in the queue has been waiting, in seconds. If this starts increasing, then the current pool of servers does not handle requests quick enough. Reason may be either overloaded server or just too small of a pool_size setting in pbouncer config file. Databases can be filtered by use of the <i>--include</i> and <i>--exclude</i> options. See the <a href="#BASIC-FILTERING">"BASIC FILTERING"</a> section for more details. The values or the <i>--warning</i> and <i>--critical</i> 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.</p> + +<p>This action requires Postgres 8.3 or better.</p> + +<p>Example 1: Give a critical if any transaction has been open for more than 10 minutes:</p> + +<pre><code> check_postgres_pgbouncer_maxwait -p 6432 -u pgbouncer --critical='10 minutes'</code></pre> + +<p>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.</p> + <h2 id="pgagent_jobs"><b>pgagent_jobs</b></h2> <p>(<code>symlink: check_postgres_pgagent_jobs</code>) Checks that all the pgAgent jobs that have executed in the preceding interval of time have succeeded. This is done by checking for any steps that have a non-zero result.</p> @@ -1147,7 +1166,7 @@ <h2 id="same_schema"><b>same_schema</b></h2> -<p>(<code>symlink: check_postgres_same_schema</code>) Verifies that two or more databases are identical as far as their schema (but not the data within). This is particularly handy for making sure your slaves have not been modified or corrupted in any way when using master to slave replication. Unlike most other actions, this has no warning or critical criteria - the databases are either in sync, or are not. If they are different, a detailed list of the differences is presented.</p> +<p>(<code>symlink: check_postgres_same_schema</code>) Verifies that two or more databases are identical as far as their schema (but not the data within). Unlike most other actions, this has no warning or critical criteria - the databases are either in sync, or are not. If they are different, a detailed list of the differences is presented.</p> <p>You may want to exclude or filter out certain differences. The way to do this is to add strings to the <code>--filter</code> option. To exclude a type of object, use "noname", where 'name' is the type of object, for example, "noschema". To exclude objects of a certain type by a regular expression against their name, use "noname=regex". See the examples below for a better understanding.</p> @@ -1436,6 +1455,10 @@ <pre><code> --exclude='pg_catalog.'</code></pre> +<p>Exclude all items in the 'pg_temp_nnn' per-session temporary schemas:</p> + +<pre><code> --exclude=~^pg_temp_.</code></pre> + <p>Exclude all items containing the letters 'ace', but allow the item 'faceoff':</p> <pre><code> --exclude=~ace --include=faceoff</code></pre> @@ -1508,7 +1531,7 @@ <h1 id="FILES">FILES</h1> -<p>In addition to command-line configurations, you can put any options inside of a file. The file <i>.check_postgresrc</i> in the current directory will be used if found. If not found, then the file <i>~/.check_postgresrc</i> will be used. Finally, the file /etc/check_postgresrc will be used if available. The format of the file is option = value, one per line. Any line starting with a '#' will be skipped. Any values loaded from a check_postgresrc file will be overwritten by command-line options. All check_postgresrc files can be ignored by supplying a <code>--no-checkpostgresrc</code> argument.</p> +<p>In addition to command-line configurations, you can put any options inside of a file. The file <i>.check_postgresrc</i> in the current directory will be used if found. If not found, then the file <i>~/.check_postgresrc</i> will be used. Finally, the file <i>/etc/check_postgresrc</i> will be used if available. The format of the file is option = value, one per line. Any line starting with a '#' will be skipped. Any values loaded from a check_postgresrc file will be overwritten by command-line options. All check_postgresrc files can be ignored by supplying a <code>--no-checkpostgresrc</code> argument.</p> <h1 id="ENVIRONMENT-VARIABLES">ENVIRONMENT VARIABLES</h1> @@ -1565,15 +1588,15 @@ <p>Three mailing lists are available. For discussions about the program, bug reports, feature requests, and commit notices, send email to check_postg...@bucardo.org</p> -<p>https://mail.endcrypt.com/mailman/listinfo/check_postgres</p> +<p><a href="https://mail.endcrypt.com/mailman/listinfo/check_postgres";>https://mail.endcrypt.com/mailman/listinfo/check_postgres</a></p> <p>A low-volume list for announcement of new versions and important notices is the 'check_postgres-announce' list:</p> -<p>https://mail.endcrypt.com/mailman/listinfo/check_postgres-announce</p> +<p><a href="https://mail.endcrypt.com/mailman/listinfo/check_postgres-announce";>https://mail.endcrypt.com/mailman/listinfo/check_postgres-announce</a></p> <p>Source code changes (via git-commit) are sent to the 'check_postgres-commit' list:</p> -<p>https://mail.endcrypt.com/mailman/listinfo/check_postgres-commit</p> +<p><a href="https://mail.endcrypt.com/mailman/listinfo/check_postgres-commit";>https://mail.endcrypt.com/mailman/listinfo/check_postgres-commit</a></p> <h1 id="HISTORY">HISTORY</h1> @@ -1581,6 +1604,42 @@ <dl> +<dt id="Version-2.26.0-Not-yet-released"><b>Version 2.26.0</b> Not yet released</dt> +<dd> + +<pre><code> Add new action "pgbouncer_maxwait" (Ruslan Kabalin) [Github pull #59] + + Fix check_replication_slots on recently promoted servers (Christoph Berg) + + Allow the check_disk_space action to handle relative log_directory paths (jacksonfoz) [Github pull #174] + + Fix MINPAGES and MINIPAGES in the "check_bloat" action (Christoph Moench-Tegeder) [Github pull #82] + + Replace 'which' with 'command -v' (Christoph Berg) + + Fix check_replication_slots on recently promoted servers (Christoph Berg) + + Add --role flag to explicitly set the role of the user after connecting (David Christensen) + + Add Partman premake check (Jens Wilke) [Github pull #196] + + Add to docs how to exclude all items in the 'pg_temp_nnn' per-session temporary schemas (Michael Banck) + + Various fixes for the CI system (Emre Hasegeli) [Github pull #181] + + Various improvements to the tests (Christoph Berg, Emre Hasegeli)</code></pre> + +</dd> +<dt id="Version-2.25.0-Released-February-3-2020"><b>Version 2.25.0</b> Released February 3, 2020</dt> +<dd> + +<pre><code> Allow same_schema objects to be included or excluded with --object and --skipobject + (Greg Sabino Mullane) + + Fix to allow mixing service names and other connection parameters for same_schema + (Greg Sabino Mullane)</code></pre> + +</dd> <dt id="Version-2.24.0-Released-May-30-2018"><b>Version 2.24.0</b> Released May 30, 2018</dt> <dd> @@ -2604,7 +2663,7 @@ <h1 id="LICENSE-AND-COPYRIGHT">LICENSE AND COPYRIGHT</h1> -<p>Copyright (c) 2007-2020 Greg Sabino Mullane <g...@turnstep.com>.</p> +<p>Copyright 2007 - 2023 Greg Sabino Mullane <g...@turnstep.com>.</p> <p>Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:</p> diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/perlcriticrc new/check_postgres-2.26.0/perlcriticrc --- old/check_postgres-2.25.0/perlcriticrc 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/perlcriticrc 2023-04-03 19:24:14.000000000 +0200 @@ -4,7 +4,7 @@ profile-strictness = quiet [Documentation::PodSpelling] -stop_words = artemus Astill autovacuum backends battlestar boxinfo Bucardo bucardo burrick checksum checksums commitratio contrib criticals cronjob Cwd datadir datallowconn dbhost dbname dbpass dbport dbstats dbuser del dir dylan EB emma exabytes excludeuser ExclusiveLock executables faceoff filenames flagg franklin fsm garrett greg grimm GSM hitratio idxblkshit idxblksread idxscan idxtupfetch idxtupread includeuser Ioannis klatch lancre localhost logfile login lsfunc mallory maxwait MERCHANTABILITY minvalue morpork MRTG mrtg Mullane NAGIOS Nagios nagios nextval nofuncbody noidle nolanguage nols noname noobjectnames noperm noperms noposition noschema ok oskar pageslots parens perflimit petabytes pgAgent pgBouncer pgbouncer pgbouncer's pgpass plasmid plugin pluto postgres PostgreSQL psql queryname quirm refactoring ret robert Sabino salesrep sami schemas scott Seklecki seqscan seqtupread showperf skipcycled slon Slony slony Slony's snazzo speedtest SQL stderr symlink symlinked symlin ks syslog tablespace tablespaces Tambouras tardis timesync tuples unlinked upd uptime USERNAME usernames WAL watson wget wilkins xlog zettabytes +stop_words = artemus Astill autovacuum backends battlestar boxinfo Bucardo bucardo burrick checksum checksums commitratio contrib criticals cronjob Cwd datadir datallowconn dbhost dbname dbpass dbport dbstats dbuser del dir dylan EB emma exabytes excludeuser ExclusiveLock executables faceoff filenames flagg franklin fsm garrett greg grimm GSM hitratio idxblkshit idxblksread idxscan idxtupfetch idxtupread includeuser Ioannis klatch lancre localhost logfile login lsfunc mallory maxwait MERCHANTABILITY minvalue morpork MRTG mrtg Mullane NAGIOS Nagios nagios nextval nofuncbody noidle nols noname noobjectnames noperm noperms noposition noschema ok oskar pageslots parens perflimit petabytes pgAgent pgBouncer pgbouncer pgbouncer's pgpass plasmid plugin pluto postgres PostgreSQL psql queryname quirm refactoring ret robert Sabino salesrep sami schemas scott Seklecki seqscan seqtupread showperf skipcycled slon Slony slony Slony's snazzo speedtest SQL stderr symlink symlinked symlinks syslog t ablespace tablespaces Tambouras tardis timesync tuples unlinked upd uptime USERNAME usernames WAL watson wget wilkins xlog zettabytes [-Bangs::ProhibitDebuggingModules] [-Bangs::ProhibitFlagComments] diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_commitratio.t new/check_postgres-2.26.0/t/02_commitratio.t --- old/check_postgres-2.25.0/t/02_commitratio.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_commitratio.t 2023-04-03 19:24:14.000000000 +0200 @@ -80,6 +80,6 @@ like ($cp->run('-w 5% --include=postgres'), qr{ \| time=\d\.\d\ds postgres=\d+}, $t); $t=qq{$S with includeuser option returns nothing}; -like ($cp->run('--includeuser postgres --includeuser mycatbeda -w 10%'), qr{No matching entries found due to user exclusion}, $t); +like ($cp->run('--includeuser mycatbeda -w 10%'), qr{No matching entries found due to user exclusion}, $t); exit; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_connection.t new/check_postgres-2.26.0/t/02_connection.t --- old/check_postgres-2.25.0/t/02_connection.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_connection.t 2023-04-03 19:24:14.000000000 +0200 @@ -56,10 +56,10 @@ $cp->fake_version_timeout(); $t=qq{$S fails on timeout}; -like ($cp->run('--timeout 1'), qr{^$label CRITICAL:.*Timed out}, $t); +like ($cp->run('--timeout 1'), qr{^$label CRITICAL:.*(Timed out|statement timeout)}, $t); $cp->reset_path(); $t=qq{$S fails on nonexisting socket}; -like ($cp->run('--port=1023'), qr{^$label CRITICAL:.*could not connect to server}, $t); +like ($cp->run('--port=1023'), qr{^$label CRITICAL:.*(could not connect to server|connection to server.*failed)}, $t); exit; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_database_size.t new/check_postgres-2.26.0/t/02_database_size.t --- old/check_postgres-2.25.0/t/02_database_size.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_database_size.t 2023-04-03 19:24:14.000000000 +0200 @@ -114,6 +114,6 @@ like ($cp->run('-w 5g --include=postgres'), qr{ \| time=\d\.\d\ds postgres=\d+}, $t); $t=qq{$S with includeuser option returns nothing}; -like ($cp->run('--includeuser postgres --includeuser mycatbeda -w 10g'), qr{No matching entries found due to user exclusion}, $t); +like ($cp->run('--includeuser mycatbeda -w 10g'), qr{No matching entries found due to user exclusion}, $t); exit; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_hitratio.t new/check_postgres-2.26.0/t/02_hitratio.t --- old/check_postgres-2.25.0/t/02_hitratio.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_hitratio.t 2023-04-03 19:24:14.000000000 +0200 @@ -80,6 +80,6 @@ like ($cp->run('-w 5% --include=postgres'), qr{ \| time=\d\.\d\ds postgres=\d+}, $t); $t=qq{$S with includeuser option returns nothing}; -like ($cp->run('--includeuser postgres --includeuser mycatbeda -w 10%'), qr{No matching entries found due to user exclusion}, $t); +like ($cp->run('--includeuser mycatbeda -w 10%'), qr{No matching entries found due to user exclusion}, $t); exit; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_same_schema.t new/check_postgres-2.26.0/t/02_same_schema.t --- old/check_postgres-2.25.0/t/02_same_schema.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_same_schema.t 2023-04-03 19:24:14.000000000 +0200 @@ -6,7 +6,7 @@ use strict; use warnings; use Data::Dumper; -use Test::More tests => 76; +use Test::More tests => 75; use lib 't','.'; use CP_Testing; @@ -97,15 +97,12 @@ $t = qq{$S reports language on 3 but not 1 and 2}; $dbh3->do(q{CREATE LANGUAGE plpgsql}); like ($cp1->run($connect3), - qr{^$label CRITICAL.*Items not matched: 1 .* + qr{^$label CRITICAL.* Language "plpgsql" does not exist on all databases: \s*Exists on:\s+3 -\s+Missing on:\s+1, 2\s*$}s, +\s+Missing on:\s+1, 2}s, $t); -$t = qq{$S does not report language differences if the 'nolanguage' filter is given}; -like ($cp1->run("$connect3 --filter=nolanguage"), qr{^$label OK}, $t); - $dbh1->do(q{CREATE LANGUAGE plpgsql}); $dbh2->do(q{CREATE LANGUAGE plpgsql}); diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/02_txn_time.t new/check_postgres-2.26.0/t/02_txn_time.t --- old/check_postgres-2.25.0/t/02_txn_time.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/02_txn_time.t 2023-04-03 19:24:14.000000000 +0200 @@ -73,7 +73,7 @@ my $idle_dbh = $cp->test_database_handle(); $idle_dbh->do('SELECT 1'); sleep(1); -like ($cp->run(q{-w 0}), qr{longest txn: [12]s}, $t); +like ($cp->run(q{-w 0}), qr{longest txn: [1-9]s}, $t); $t .= ' (MRTG)'; my $query_patten = ($ver >= 90200) ? 'SELECT 1' : '<IDLE> in transaction'; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/04_timeout.t new/check_postgres-2.26.0/t/04_timeout.t --- old/check_postgres-2.25.0/t/04_timeout.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/04_timeout.t 2023-04-03 19:24:14.000000000 +0200 @@ -17,11 +17,11 @@ $dbh = $cp->test_database_handle(); $t=q{Setting the --timeout flag works as expected}; -$res = $cp->run('--query="SELECT pg_sleep(2)" -w 7 --timeout=1'); +$res = $cp->run('--query="SELECT pg_sleep(10)" -w 7 --timeout=1'); like ($res, qr{Command timed out}, $t); $t=q{Setting the --timeout flag works as expected}; -$res = $cp->run('--query="SELECT pg_sleep(1)" -w 7 --timeout=2'); +$res = $cp->run('--query="SELECT pg_sleep(1)" -w 7 --timeout=10'); like ($res, qr{Invalid format}, $t); exit; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/99_spellcheck.t new/check_postgres-2.26.0/t/99_spellcheck.t --- old/check_postgres-2.25.0/t/99_spellcheck.t 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/99_spellcheck.t 2023-04-03 19:24:14.000000000 +0200 @@ -425,7 +425,6 @@ nofunctions noidle noindexes -nolanguage nols noname noname diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/check_postgres-2.25.0/t/CP_Testing.pm new/check_postgres-2.26.0/t/CP_Testing.pm --- old/check_postgres-2.25.0/t/CP_Testing.pm 2020-02-04 01:55:05.000000000 +0100 +++ new/check_postgres-2.26.0/t/CP_Testing.pm 2023-04-03 19:24:14.000000000 +0200 @@ -155,6 +155,7 @@ print $cfh qq{listen_addresses = ''\n}; print $cfh qq{max_connections = 10\n}; print $cfh qq{fsync = off\n}; + print $cfh qq{log_destination = 'stderr'\n}; ## <= 8.0 if ($imaj < 8 or (8 == $imaj and $imin <= 1)) {
