PostgreSQL Plugin

PostgreSQL is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. For more information visit postgresql.org.

Discovery

Even if the PostgreSQL plugin is not configured, Stackdriver Monitoring will discover PostgreSQL services running in your Cloud Platform project by:

  • searching instance names for postgresql
  • checking for ports opened to 5432 via firewall rules

The services discovered are displayed on the PostgreSQL Services page in the Resources menu. After you install the monitoring agent and configure the PostgreSQL plugin on your instances, Stackdriver Monitoring populates the PostgreSQL Services page with inventory and metrics.

Configuring Stackdriver Monitoring to monitor PostgreSQL

After you install the monitoring agent, use the following instructions to configure the PostgreSQL plugin on your instances.

Prerequisites

For PostgreSQL collection you must create a user/role with minimal privileges that can connect to the database using password or md5 authentication. This user and password are referred to as STATS_USER and STATS_PASS in the following config file. Some installations set access controls to ident authentication by default. You need to edit your pg_hba.conf file to enable access for the agent role. Assuming you have named your rolestackdriver-agent, you might need to add one of these lines to your pg_hba.conf file and restart PostgreSQL:

  • For connecting through unix domain sockets:

    local all stackdriver-agent md5

  • For connecting through TCP/IP to localhost:

    host all stackdriver-agent 127.0.0.1/32 md5

Enabling the PostgreSQL monitoring plugin

PostgreSQL is configured per database on your system. At the moment, no drop-in configuration is available. The following configuration file shows how to configure one database, named DATABASE_NAME. You would need to use that configuration as a template for your databases.

On your VM instance, download postgresql.conf from the GitHub configuration repository and place it in the directory /opt/stackdriver/collectd/etc/collectd.d/:

(cd /opt/stackdriver/collectd/etc/collectd.d/ && curl -O https://raw.githubusercontent.com/Stackdriver/stackdriver-agent-service-configs/master/etc/collectd.d/postgresql.conf)

Edit the downloaded configuration file and add information about your databases. Inline comments point out where you need to add configuration for additional databases. Wherever you see DATABASE_NAME, substitute the name of your database.

After editing the configuration file, restart the monitoring agent by running the following command:

sudo service stackdriver-agent restart

Information on other plugin configuration options can be found at collectd.org.

What is monitored

The PostgreSQL plugin monitors the following metrics:

  • Connections (count): Number of connections to PostgreSQL.
  • Disk Usage (byte): Number of bytes currently being used on disk.
  • Commits (count/s): Number of commits per second.
  • Rollbacks (count/s): Number of rollbacks per second.
  • Heap Blocks Read Rate (count/s): Number of blocks read from the heap.
  • Heap Cache Hit Rate (count/s): Number of blocks read directly out of the cache.
  • Index Blocks Read Rate (count/s): Number of blocks read from the index.
  • Index Cache Hit Rate (count/s): Number of index blocks read directly out of the cache.
  • Toast Blocks Read Rate (count/s): Number of reads from the toast blocks.
  • Toast Cache Hit Rate (count/s): Number of toast blocks read directly out of the cache.
  • Toast Index Blocks Read Rate (count/s): Number of blocks read from the toast index.
  • Toast Index Cache Hit Rate (count/s): Number of toast index blocks read directly out of the cache.
  • Operations [delete, insert, update, heap only update] (count/s): Number of rows [deleted, inserted, updated, heap only updated] in the db.
  • Dead Tuples (count): Number of tuples that are dead in the db.
  • Live Tuples (count): Number of tuples that are live in the db.

Send feedback about...

Stackdriver Monitoring