PostgreSQL High Availability Cookbook - Second Edition

How to do it...

To create a collectd custom PostgreSQL query, simply follow these steps on a server running both collectd and PostgreSQL:

1. Create a file named local.conf in the /etc/collectd directory with these contents:

        LoadPlugin postgresql 
 
        <Plugin postgresql> 
          <Query tps> 
            Statement "SELECT datname, \ 
                           xact_commit + xact_rollback AS tps \ 
                      FROM pg_catalog.pg_stat_database;" 
 
        <Result> 
          Type derive 
          InstancePrefix "TPS" 
          InstancesFrom "datname" 
          ValuesFrom "tps" 
        </Result> 
          </Query> 
 
        <Database postgres> 
          Host "localhost" 
          User "perf_mon" 
          Password "testpw" 
          Instance "Production" 
 
          Query tps 
        </Database> 
        </Plugin> 

2. Reload the collectd configuration files with this command:

        sudo service collectd reload

3. Wait for 2 to 5 minutes.
4. Check the contents of the ...