Jason Schoonover
2007-04-16
PostgreSQL Statistics Collecting

This file will explain the steps required to add statistic collecting
for any postgresql databases that need monitoring.

The plugin has been successfully tested on:
	PostgreSQL 7.4 for Linux
	PostgreSQL 8.1 for Linux

Currently, the postgreSQL plugin monitors the following five 
characteristics of a postgresql database:

	- Number of active backend server processes
	- Total transactions committed
	- Total transactions rolled back
	- Total disk blocks read
	- Total number of buffer hits


This all comes from the pg_stat_database view enabled in PostgreSQL 7.4
and above.

First, the perl modules DBD::Pg needs to be installed.

Debian:
	apt-get install libdbd-pg-perl

Other OS's, the CPAN module is available
	http://search.cpan.org/dist/DBD-Pg/


2. PostgreSQL enable statistics

************************************************************************

** WARNING: before you do any of the following steps make sure that you
** have read the appropriate documentation for PostgreSQL.  It is
** possible that enabled stats and the block level could have a minor
** performance hit.  PLEASE READ THE POSTGRESQL DOCS FIRST.

http://postgresql.org
http://developer.postgresql.org/pgdocs/postgres/monitoring.html

************************************************************************

Once the perl module is installed, you need to create a user that has
access to the database.  Login as a super-user user into the postgres
database, and issue the following command (change the username/password
to match whatever you want to create):

	For PostgreSQL 7.4:
	CREATE USER unnoc PASSWORD 'test123';

	For PostgreSQL 8.1:
	CREATE USER unnoc WITH nosuperuser nocreatedb password 'test123';

That will create a username "unnoc" with pasword test123.  Make sure
change the username/password combo.

Next you will need to enable access from the pg_hba.conf file.  Unnoc
will connect to the 'template1' database, so the following example will
allow access from the user "unnoc" coming from the IP 10.2.3.4:

	host    template1       unnoc           10.2.3.4/32    md5


Also, you need to enable statistics in the postgresql.conf.

	stats_block_level = true
	stats_row_level = true


3. Unnoc Configuration

Then, in the etc/unnoc.conf file, you should tell unnoc about the new
psql host.  The proper plugin is "psql"

	host {
		hostname = server1
		communtiy = public
		updown = ping
		plugin = psql
		psql_username = unnoc
		psql_password test123
	}

At this time, only standard PostgreSQL configurations are supported:
	PostgreSQL port is TCP 5432
	PostgreSQL SSL is not supported

vim:tw=72:wm=1