PostgreSQL Monitor

This agent monitor plugin records performance and usage data from a PostgreSQL server.

An agent monitor plugin is a component of the Scalyr Agent. To use a plugin, simply add it to the monitors section of the Scalyr Agent configuration file (/etc/scalyr/agent.json). For more information, see Agent Plugins.

Configuring PostgreSQL

You will need to configure your PostgreSQL server to allow the Scalyr Agent to connect. First, make sure password authentication is enabled for connections from localhost. You will need a line like the following in your pg_hba.conf file:

host    all             all             127.0.0.1/32            md5

Second, you'll need a PostgreSQL user account with password login. If you want to create a new user account for use by the Scalyr Agent, see http://www.postgresql.org/docs/9.3/static/sql-createrole.html.

Supposing you created a user "statusmon" with password "getstatus", you can verify the configuration using the psql command:

$ psql -U statusmon -W postgres -h localhost
Password for user statusmon: <enter password>
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

If the configuration is incorrect or you enter an invalid password, you will see something like this:

$ psql -U statusmon -W postgres -h localhost
psql: FATAL:  password authentication failed for user "statusmon"
FATAL:  password authentication failed for user "statusmon"

Configuring the PostgreSQL monitor

To use the PostgreSQL monitor, you;ll need to add a stanza to the Scalyr agent config file (agent.json). Here is an example:

monitors: [
  {
    module:              "scalyr_agent.builtin_monitors.postgres_monitor",
    id:                  "mydb",
    database_host:       "localhost",
    database_name:       "<database>",
    database_username:   "<username>",
    database_password:   "<password>"
  }
]

This configuration assumes that PostgreSQL is running on the same server as the Scalyr Agent, and is using the default PostgreSQL socket. If not, you will need to specify the server's socket file, or hostname (or IP address) and port number; see Configuration Reference.

Viewing Data

After adding this plugin to the agent configuration file, wait one minute for data to begin recording. Then click the Dashboards menu and select Postgres Monitor. (The dashboard may not be listed until the agent begins sending PostgreSQL data.) You will see an overview of PostgreSQL performance statistics across all servers where you are running the PostgreSQL plugin. Use the ServerHost dropdown to show data for a specific server.

The dashboard shows only some of the data collected by the PostgreSQL monitor plugin. To explore the full range of data collected, go to the Search page and search for $monitor = 'postgres_monitor'. This will show all data collected by this plugin, across all servers. You can use the Refine search by dropdown to narrow your search to specific servers and monitors.

The Search overview page describes the tools you can use to view and analyze log data. Query Language lists the operators you can use to select specific metrics and values. You can also use this data in Dashboards and Alerts.

Configuration Reference

Option Usage
module Always scalyr_agent.builtin_monitors.postgres_monitor
id Optional. Included in each log message generated by this monitor, as a field named instance. Allows you to distinguish between values recorded by different monitors. This is especially useful if you are running multiple PostgreSQL instances on a single server; you can monitor each instance with a separate postgresql_monitor record in the Scalyr Agent configuration.
database_host Name of host on which PostgreSQL is running. Defaults to localhost.
database_port ID of the port to PostgreSQL. Defaults to 5432.
database_name Name of the PostgreSQL database the agent will connect to.
database_username Username which the agent uses to connect to PostgreSQL.
database_password Password for connecting to PostgreSQL.

Log Reference

Each event recorded by this plugin will have the following fields:

Field Meaning
monitor Always postgres_monitor
instance The id value from the monitor configuration.
metric The name of a metric being measured, e.g. "postgres.vars"
value The metric value

Connection Metrics

Metric Description
postgres.database.connections The number of active connections.

General Metrics

Metric Fields Description
postgres.database.transactions result=committed The number of database transactions that have been committed. The value is relative to postgres.database.stats_reset.
postgres.database.transactions result=rolledback The number of database transactions that have been rolled back. The value is relative to postgres.database.stats_reset.
postgres.database.disk_blocks type=read The number of disk blocks read into the database. The value is relative to postgres.database.stats_reset.
postgres.database.disk_blocks type=hit The number of disk blocks read that were found in the buffer cache. The value is relative to postgres.database.stats_reset.
postgres.database.query_rows op=returned The number of rows returned by all queries in the database. The value is relative to postgres.database.stats_reset.
postgres.database.query_rows op=fetched The number of rows fetched by all queries in the database. The value is relative to postgres.database.stats_reset.
postgres.database.query_rows op=inserted The number of rows inserted by all queries in the database. The value is relative to postgres.database.stats_reset.
postgres.database.query_rows op=updated The number of rows updated by all queries in the database. The value is relative to postgres.database.stats_reset.
postgres.database.query_rows op=deleted The number of rows deleted by all queries in the database. The value is relative to postgres.database.stats_reset.
postgres.database.temp_files The number of temporary files created by queries to the database. The value is relative to postgres.database.stats_reset.
postgres.database.temp_bytes The total amount of data written to temporary files by queries to the database. The value is relative to postgres.database.stats_reset.
postgres.database.deadlocks The number of deadlocks detected in the database. The value is relative to postgres.database.stats_reset.
postgres.database.blocks_op_time op=read The amount of time data file blocks are read by clients in the database (in milliseconds). The value is relative to postgres.database.stats_reset.
postgres.database.blocks_op_time op=write The amount of time data file blocks are written by clients in the database (in milliseconds). The value is relative to postgres.database.stats_reset.
postgres.database.stats_reset The time at which database statistics were last reset.
postgres.database.size The number of bytes the database is taking up on disk.