SQL to monitor postgres with prtg /

Hi. This is a collection of very basic monitoring sql statements returning
one numerical value. We switched from nagios to prtg network monitoring. I
had to reformulate the monitoring statements, making them invariable worse,
but like this they work with PRTG.

For Postgres 8.1 and 8.4

A. Preparations - thanks Tony Wasson that this works, and Robert Treat for
making it more secure.
As postgres superuser do:
Create role monitoring with own database, edit pg_hba.conf accordingly.
create language plpgsql ​;
CREATE OR REPLACE FUNCTION public.pg_stat_activity() RETURNS SETOF
pg_catalog.pg_stat_activity
AS $BODY$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM pg_stat_activity
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;
revoke all on function pg_stat_activity() from public;
create view pg_stat_activity as select * from pg_stat_activity();
revoke all on pg_stat_activity from public;
grant execute on function pg_stat_activity() to monitoring;
grant select on pg_stat_activity to monitoring;

B: SQL Statements, as monitoring user
1. "Check for long running queries"
SELECT EXTRACT(EPOCH FROM (select
max((((timeofday()::TIMESTAMP)-query_start))) FROM pg_stat_activity() where
current_query != '<IDLE>'))::int

Ouput: Seconds the longest non idle query has been running.

Alarm: if more than 10 min = 3600 secs

2. "Check max XID"
select max(age(datfrozenxid)/20000000) FROM pg_database WHERE datallowconn
!= FALSE;​

ouput: percentage of used XID

Alarm: if > 80

3. "Number of sessions"
SELECT COUNT(*) FROM pg_stat_activity;

Output: int, number of sessions (active and inactive)

Alarm: None

If you want to see if max_connections is close:
select (select to_number(setting,'999999') from pg_settings where name =
'max_connections') - ( select count(*) from pg_stat_activity);​

4. "Locks"
SELECT COUNT(*) FROM pg_locks WHERE granted = 'f';

​Output: number of locks that could not be granted

Alarm: if > 0

5. "Waiting Queries"

select extract(epoch from (select
max(NOW()::timestamp(0)-query_start::timestamp(0))
FROM pg_stat_activity AS p
LEFT JOIN pg_locks AS l ON (l.pid=p.procpid)
where l.granted = TRUE and p.current_query != '<IDLE>'));

output: number of seconds that a query has been waiting

Alarm if more than 10 min = 3600 secs

6. "Size of database"
select pg_database_size('your_db')/(1024*1024*1024);

Output: int. Size of database in GB.

Alarm: None

C. Provoke waiting queries and lock, to see if this works:
Session 1

\c some_db

begin work;

lock table some_table;

Session 2

\c some_db

select * from some_table;

Any corrections are welcome, Ynux.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-to-monitor-postgres-with-prtg-tp5738484.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.