[collectd] Not seeing postgres data from collectd.

[James Armstrong]

A hah!  Now we have something!

Sebastian Harl wrote:
> Hi James,
> 
> On Tue, Mar 09, 2010 at 03:36:31PM -0800, James Armstrong wrote:
>> Sebastian Harl wrote:
>>> On Tue, Mar 09, 2010 at 01:51:24PM -0800, James Armstrong wrote:
>>>> Sebastian Harl wrote:
>>>> 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?
> 
>>> I would not expect the query to time out (it runs fine from an
>>> interactive client, which should basically be the same). However, it
>>> will cause RRDtool to generate an empty graph. Do you use the rrdtool
>>> plugin? Did you really make sure that *no* data at all arrives at the
>>> server? Does collectd create any RRD files at all ($hostname/ 
>>> postgresql-dwh/counter-eventcount.rrd)? Is there any data in that file
>>> (run 'rrdtool fetch path/to/counter-eventcount.rrd MAX')? Did you try to
>>> store the collected data on the client as well? Does that work fine?
>> Yes, we use rrdtool.  No data was arriving at the remote machine (none
>> was in the UDP packet I captured with tcpdump.)  So, I enabled rrdtool
>> on the local machine -- no data was being captured there, either.
> 
> For how long did you capture packets? If there query takes over 30
> seconds, you'd, obviously, have to capture for at least that amount of
> time.

Yes.  Ran the capture for 10 minutes, nothing.

> Your server does receive and collect the other data, right?

Yes.

> On your local machine, are there any files in /var/lib/collectd/rrd/
> <hostname>/postgresql-dwh/?
> 
> Are there any other files in /var/lib/collectd/rrd/ (on the local
> machine)?

Here is the problem!

The data is appearing in
/var/lib/collectd/rrd/192.168.X.YY/postgresql-dwh/counter-eventcount.rrd

All the other data appears under the hostname directory.

So, it appears the postgresql plugin is using the Host from the
<Database> stanza.  The database doesn't allow connections through the
fully qualified domain name (that we use for the collectd collections.)
Is there a way to tell the plugin that data from Host 192.168.X.YY
should be associated with Hostname FQDN?



> Btw., if the query would time out, you should get a warning / error
> message in the logs.
> 
> Are there *any* messages related to postgresql in the log file?
> grep -Fi postgresql /var/log/collectd.log
> 
>> Here's the complete /etc/collectd.conf file (with IP's and passwords
>> changed for all the obvious reasons!) in case there are conflicts:
> 
> That config looks fine to me.
> 
> 
> Sorry for being a bit in the dark and possibly asking stupid questions,
> but I could not reproduce that behavior so far (I'll see if I can find
> some query that takes such a long time) and your setup looks fine to me.
> 
> Cheers,
> Sebastian
>