Many thanks for you assistance. I come from a MySQL and Oracle background, and don't have much experience with postgres... So this is a bit of a learning experience for me.
Sebastian Harl wrote: > Hi, > > On Mon, Mar 08, 2010 at 01:04:19PM -0800, James Armstrong wrote: >> We're trying to add a monitor for some table sizes to collectd for a >> postgres database. Our configuration is to use a central server to host >> the data, and various machines send the data to that server. >> >> I've added the following to /etc/collectd.conf on a working machine and >> restarted collectd, but the postgres data is not appearing in the UDP >> packets. I have confirmed this with tcpdump. >> >> LoadPlugin "postgresql" >> >> ... >> >> <Plugin "postgresql"> >> <Query eventcount> >> Statement "SELECT COUNT(*) AS count FROM events;" >> <Result> >> Type counter >> InstancePrefix "eventcount" >> ValuesFrom "count" >> </Result> >> </Query> >> >> <Database dwh> >> Host "192.168.X.YY" >> User "dbuser" >> Password (correct password) >> Query eventcount >> </Database> >> </Plugin> > > That config snippet looks fine to me. > >> The connection information for the database works, confirmed with psql. > > What's the exact psql command line you were using to verify that? psql -h 192.168.X.YY -U dbuser -d dwh I enter the password and run the query, and get valid output: dwh=> SELECT COUNT(*) AS count FROM events; count ---------- 23048887 (1 row) I also see in the logfile this: [2010-03-05 21:16:05] postgresql: Sucessfully connected to database dwh (user dbuser) at server 192.168.X.YY:5432 (server version: 8.3.9, protocol version: 3, pid: 27281) > Did you verify that the postgresql plugin actually connects to the > database (select * from pg_stat_activity where datname = 'dwh')? I see activity: dwh=> select * from pg_stat_activity where datname='dwh'; datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port -------+---------+---------+----------+----------+-----------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+---------------+------------- 16389 | dwh | 27281 | 16385 | dbuser | SELECT COUNT(*) AS count FROM events; | f | 2010-03-09 21:43:55.771195+00 | 2010-03-09 21:43:55.771195+00 | 2010-03-05 21:16:05.760945+00 | 192.168.10.93 | 38455 16389 | dwh | 21765 | 16385 | dbuser | select * from pg_stat_activity where datname='dwh'; | f | 2010-03-09 21:44:00.138973+00 | 2010-03-09 21:44:00.138973+00 | 2010-03-09 21:39:25.109213+00 | 192.168.10.93 | 46765 > Given that nobody else accesses the same table, the following query > might give you an idea whether the query is executed at all: select > seq_scan, seq_tup_read from pg_stat_user_tables where relname = 'dwh'. > That query should return numbers that are increased after each collectd > interval. Else, you could also configure PostgreSQL to log every query > to make sure the query is actually executed by collectd. That returns 0 rows. > Which version of collectd do you use? 4.8.1-1.centos5 > >> There are no error messages in the logfile, we're using the logfile >> plugin at loglevel debug. > > To make use of loglevel "debug", collectd has to be compiled with > debugging enabled. Is that the case? You should then see *lots* of > messages in the log file. Nope... One possibility, this query takes a while to return, over 30 seconds. Could it be timing out, and if so, how can I change the timeout for this query without changing other intervals? > HTH, > Sebastian > _______________________________________________ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd