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
menu and select . (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 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 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. |