0x4a42.net

Hello World.

Some notes on collectd and PostgreSQL

collectd comes with a PostgreSQL plugin, which is one of these so-called generic plugins, i.e. it won’t do anything useful unless you explicitly configure it. But it can actually do some pretty nice things with almost no configuration. But don’t expect anything too fancy in this post, it is more like a “you should take a look at this if you are using PostgreSQL and collectd anyways” kind of post.

First of all, collectd has to connect to PostgreSQL in order to gather any information. As collectd is usually running as root anyways, the easiest way is to just create a superuser role for root in PostgreSQL and use the local ident authentication mechanism. You can do so by running the following shell command as postgres user:

createuser --superuser root

I’m running Icinga2 on Debian configured with the PostgreSQL database backend, so it might be worth monitoring its database. To do so, just load the postgresql plugin in collectd.conf and add a <Database $name> section to the <Plugin postgresql> section like this:

LoadPlugin postgresql

<Plugin postgresql>
    <Database icinga2idopgsql>
    </Database>
</Plugin>

By using the root role and local user authentication, there is no need to specify any additional credentials.

Together with Collectd Graph Panel, this will you get these pretty graphs:

So that’s pretty nice, but what if you have quite a few databases (which I actually don’t have) and you’re only interested in their size? You could either add a <Database> section for each one or you can use the power of the PostgreSQL plugin to run your own query and plot the results. So here’s a query from PostgreSQL wiki that does all the magic. I removed the check, whether the user can connect to the database as it’s running as superuser anyways:

<Query database_size>
    Statement "SELECT \
                 datname AS name, \
                 pg_catalog.pg_database_size(datname) AS size \
               FROM pg_catalog.pg_database;"

    <Result>
        Type bytes
        InstancePrefix "dbsize"
        InstancesFrom "name"
        ValuesFrom "size"
    </Result>
</Query>

<Database postgres>
    Query database_size
</Database>

The result is not that pretty as the other graphs, but at least it’s a graph that show the information I wanted:

/images/2016/collectd-postgresql-all-db-sizes.png