PostgreSQL¶

Create the Integration¶

The PostgreSQL integration depends on the Librato Agent. If you haven’t already, you will first need to install the Librato Agent. Once this is complete, select the PostgreSQL icon in the integrations catalogue.

Toggle the Enabled switch to activate the PostgreSQL integration on your Librato account and create the preconfigured PostgreSQL space.

At this point any PostgreSQL metrics associated with this integration will be allowed through your Librato Agent service-side filters. Proceed to configure the PostgreSQL plugin for each PostgreSQL server you would like to monitor.

Plugin Configuration¶

Librato Agent provides a default /opt/collectd/etc/collectd.conf.d/postgresql.conf configuration file, which must be edited to suit your environment.

The first Database instance gathers useful stats from the postgres database, connecting through the /var/run/postgresql socket as the postgres user. Most PostgreSQL installations will require changes to the authentication settings in pg_hba.conf to allow this connection. Canned Query statements are defined in /opt/collectd/share/collectd/postgresql_default.conf and referenced in the plugin configuration to run SQL queries and parse the results into metrics for this integration.

Please see the official collectd PostgreSQL plugin documentation for a thorough explanation of all settings, including query examples.

LoadPlugin postgresql
<Plugin postgresql>
  <Database postgres>
    Host "/var/run/postgresql"
    User "postgres"
    Query connections
    Query transactions
    Query queries
    Query query_plans
    Query table_states
    Query disk_io
    Query disk_usage
  </Database>
  #<Database foo>
  #  Instance "custom-name"
  #  Host "127.0.0.1"
  #  Port "5432"
  #  User "username"
  #  Password "password"
  #  SSLMode "prefer"
  #</Database>
</Plugin>

Note: You must restart the agent after any changes to your Librato Agent configuration files.

$ sudo service collectd restart

At this point you should begin seeing librato.postgresql.* metrics in your Librato account.

PostgreSQL Workspace¶

Visit your PostgreSQL preconfigured spaces to observe your new metrics as they stream in.

Use our dynamic source field at the top to filter your view to a specific PostgreSQL instance or subset of metric sources. For example, to isolate the view to the PostgreSQL instance associated with the web database, use a dynamic source value of *.web.

Remote Databases¶

It’s also possible to monitor remote PostgreSQL instances with this integration, such as those running on AWS RDS or Heroku Postgres. You’ll need to pick a server to run the Librato Agent plugin, then configure it with the Host and Port settings for the remote service, and your authentication credentials for the database.

LoadPlugin postgresql
<Plugin postgresql>
  <Database web>
    Instance "custom-name"
    Host "remote-instance.example.com"
    Port "5432"
    User "username"
    Password "password"
    SSLMode "prefer"
    Query connections
    Query transactions
    Query queries
    Query query_plans
    Query table_states
    Query disk_io
    Query disk_usage
  </Database>
</Plugin>

Custom Queries¶

The following snippet demonstrates one of the included canned Query statements, collecting the disk usage for the specified database. Queries like this one are straight-forward, where the column name and value returned are directly mapped to the metric name and measurement reported to Librato.

<Query disk_usage>
  Statement "SELECT pg_database_size($1) AS size;"
  Param database
  <Result>
    Type pg_db_size
    ValuesFrom "size"
  </Result>
</Query>

This example is a bit more advanced. Here we want to count the number of instances a particular string was found in the query results. Using some PostgreSQL case statements we’re able to tabulate the running total and return the results in a format that the collectd plugin can parse.

<Query connections>
  Statement "SELECT \
              sum(case when state = 'active' then 1 else 0 end) active, \
              sum(case when state = 'idle' then 1 else 0 end) idle \
              FROM pg_stat_activity WHERE datname = $1;"
  Param database
  <Result>
    Type "pg_numbackends"
    InstancePrefix "active"
    ValuesFrom "active"
  </Result>
  <Result>
    Type "pg_numbackends"
    InstancePrefix "idle"
    ValuesFrom "idle"
  </Result>
</Query>

The official collectd PostgreSQL plugin documentation contains full explanations for each Query option.

FAQ¶

For specific answers to Librato Agent questions check out our Librato Agent FAQ.

Let us know what you think when you take this for a spin. We would love to incorporate your feedback and any new dashboards you design into the ongoing development of this key server monitoring technology.