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.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
