Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40554=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1TqWdp-0005Wy-T2
	for pgsql-admin@arkaria.postgresql.org; Wed, 02 Jan 2013 22:18:30 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40554=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1TqWdo-0006eL-QN
	for pgsql-admin@arkaria.postgresql.org; Wed, 02 Jan 2013 22:18:28 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1TqPe4-0006Rk-5k
	for pgsql-admin@postgresql.org; Wed, 02 Jan 2013 14:50:16 +0000
Received: from sam.nabble.com ([216.139.236.26])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1TqPe2-0007Ft-OU
	for pgsql-admin@postgresql.org; Wed, 02 Jan 2013 14:50:15 +0000
Received: from [192.168.236.26] (helo=sam.nabble.com)
	by sam.nabble.com with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1TqPe2-00077r-4E
	for pgsql-admin@postgresql.org; Wed, 02 Jan 2013 06:50:14 -0800
Date: Wed, 2 Jan 2013 06:50:14 -0800 (PST)
From: ynux <ynux@gmx.net>
To: pgsql-admin@postgresql.org
Message-ID: <1357138214121-5738484.post@n5.nabble.com>
Subject: SQL to monitor postgres with prtg /
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -0.8 (/)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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 =E2=80=8B;
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();=20
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 !=3D '<IDLE>'))::int

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

Alarm: if more than 10 min =3D 3600 secs

2.  "Check max XID"
select max(age(datfrozenxid)/20000000) FROM pg_database WHERE datallowconn
!=3D FALSE;=E2=80=8B

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 =3D
'max_connections') - ( select count(*) from pg_stat_activity);=E2=80=8B


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

=E2=80=8BOutput: 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=3Dp.procpid)
where l.granted =3D TRUE and p.current_query !=3D '<IDLE>'));

output: number of seconds that a query has been waiting

Alarm if more than 10 min =3D 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;
=20

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-t=
o-monitor-postgres-with-prtg-tp5738484.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin