[check_postgres] Fwd: output pg_stat_activity on warn/error

[James Skaggs]

Each check_postgres module checks for a specific condition, and adding
pg_stat_activity information would not be too helpful for all of the
modules.

However, the heck_postgres_locks, check_postgres_txn_idle and
heck_postgres_txn_time modules would greatly benefit from some of this
pg_stat_activity information, since it is very helpful to know *what* is
causing a lock or "idle in transaction".  I think they may already do so,
but perhaps not to the degree you'd like (and it may depend on the Postgres
version).

Happily, there is a "Custom Query" option, where you can do Anything You
Want!

First, I make a view that does what I want:

 create or replace view idle_txn as
select coalesce(max(sec_idle), 0) as result, coalesce(max(perf), 'No idle
transactions') as perf from (
    select  cast (extract(epoch from current_timestamp-xact_start) as
integer) as sec_idle,
     'backend_pid=' || pid || ' query=' || regexp_replace(query, E'[\r\n]',
' ', 'g') as perf
    from pg_stat_activity
    where state = 'idle in transaction' and current_timestamp-xact_start >
interval '10 seconds'
    order by sec_idle desc limit 1
) as idlers;

Then call it as follows:

PGBINDIR=/usr/pgsql-9.2/bin sudo -u postgres
/usr/lib64/nagios/plugins/check_postgres_custom_query -query='select * from
idle_txn' -valtype=time -w '4 hours' -c '48 hours'

Now you have a plugin that does exactly what you want.  Just make sure you
know what you want: there is no such thing as a "Something Wrong"
condition.  When you are happy with your custom plugin, post it and maybe
it will be included into check_postgres.



On Wed, Jun 11, 2014 at 1:36 AM, Thomas Güttler <
guettliml at thomas-guettler.de> wrote:

> Since there was no reply on github, I try it here:
>
> check_postgres works well. If I receive an error or warning, I know that
> something is wrong.
>
> But I can't see what is going on.
>
> It would be great to see the pg_stat_activity output if there is something
> wrong.
>
> http://www.postgresql.org/docs/9.2/static/monitoring-
> stats.html#PG-STAT-ACTIVITY-VIEW
>
> Our applications set the "application_name" which makes the output even
> more useful, but I guess a lot of applications don't do it.
>
> See: https://github.com/bucardo/check_postgres/issues/65
>
> We use check_postgres not with nagios, but a different solution which can
>  handle more output.
>
> Any idea how to get more info if there is warning or error. It would be
> great to get more info to
> see as soon as possible what makes the trouble.
>
>   Thomas Güttler
> _______________________________________________
> Check_postgres mailing list
> Check_postgres at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/check_postgres
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/check_postgres/attachments/20140611/96cd4430/attachment.html>