[collectd] Not seeing postgres data from collectd.

[James Armstrong]

James Armstrong wrote:
> Sebastian Harl wrote:
>> Hi,
>>
>> On Wed, Mar 10, 2010 at 10:47:39AM -0800, James Armstrong wrote:
>>> Sebastian Harl wrote:
>>>> 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?
>> Yes, in case you're connecting through a UNIX socket or when connecting
>> to "localhost". I suppose, you're running PostgreSQL on the same
>> machine, right? In that case, simply leaving out the "Host" config
>> option will let the plugin connect to the UNIX socket.
>>
>> So, you're getting values on the local machine now? Do you get the same
>> values on the central server as well?
> 
> Many thanks for the help so far.
> 
> I've gotten the database communicating on the unix socket, but the rrd
> file is filled with no data ("nan.")
> 
> When I run the query manually on the socket, I get output, but it takes
> 72 seconds to run:
> 
>   count
> ----------
>  33306648
> (1 row)
> 
> This data is not being collected.  Is "counter" the right type for this?

More testing:  I tried changing the type from counter to gauge, and it
then included the initial entry in the RRD file, but no subsequent
entries.  Using Type counter had no entries at all.

Looking at select * from pg_stat_activity where datname = 'dwh';
shows teh query is running, and is being fired off regularly.

 select seq_scan, seq_tup_read from pg_stat_user_tables where relname =
'events'; shows regular increases in seq_scan to match the queries.

The data isn't appearing in the rrd file, apart from the first instance.
 (The actual data is constant at present, this is a test database.)

James