Docs » Integrations Guide » Integrations Reference » PostgreSQL

../../_images/integrations_postgresql.png PostgreSQL ๐Ÿ”—

DESCRIPTION ๐Ÿ”—

This integration primarily consists of the Smart Agent monitor postgresql. Below is an overview of that monitor.

Smart Agent Monitor ๐Ÿ”—

This monitor pulls metrics from all PostgreSQL databases from a specific Postgres server instance. It pulls basic information that is applicable to any database. It gathers these metrics via SQL queries.

INSTALLATION ๐Ÿ”—

This integration is part of the SignalFx Smart Agent as the postgresql monitor. You should first deploy the Smart Agent to the same host as the service you want to monitor, and then continue with the configuration instructions below.

CONFIGURATION ๐Ÿ”—

To activate this monitor in the Smart Agent, add the following to your agent config:

monitors:  # All monitor config goes under this key
 - type: postgresql
   ...  # Additional config

For a list of monitor options that are common to all monitors, see Common Configuration.

Config option Required Type Description
host no string
port no integer (default: 0)
masterDBName no string The "master" database to which the agent first connects to query the list of databases available in the server. This database should be accessible to the user specified with connectionString and params below, and that user should have permission to query pg_database. If you want to filter which databases are monitored, use the databases option below. (default: postgres)
connectionString no string See https://godoc.org/github.com/lib/pq#hdr-Connection_String_Parameters.
params no map of strings Parameters to the connection string that can be templated into the connection string with the syntax {{.key}}.
databases no list of strings List of databases to send database-specific metrics about. If omitted, metrics about all databases will be sent. This is an overridable set. (default: [*])
databasePollIntervalSeconds no integer How frequently to poll for new/deleted databases in the DB server. Defaults to the same as intervalSeconds if not set. (default: 0)
topQueryLimit no integer The number of top queries to consider when publishing query-related metrics (default: 10)

METRICS ๐Ÿ”—

Metric Name Description Type
postgres_block_hit_ratio The proportion (between 0 and 1, inclusive) of block reads that used the cache and did not have to go to the disk gauge
postgres_database_size Size in bytes of the database on disk gauge
postgres_deadlocks Total number of deadlocks detected by the system cumulative
postgres_index_scans Total number of index scans on the table cumulative
postgres_live_rows Number of rows live (not deleted) in the table gauge
postgres_queries_average_time Top N queries based on the average execution time broken down by database cumulative
postgres_queries_calls Top N most frequently executed queries broken down by database cumulative
postgres_queries_total_time Top N queries based on the total execution time broken down by database cumulative
postgres_query_count Total number of queries executed on the database, broken down by user cumulative
postgres_query_time Total time taken to execute queries on the database, broken down by user cumulative
postgres_rows_deleted Number of rows deleted from the table cumulative
postgres_rows_inserted Number of rows inserted into the table cumulative
postgres_rows_updated Number of rows updated in the table cumulative
postgres_sequential_scans Total number of sequential scans on the table cumulative
postgres_sessions Number of sessions currently on the server instance gauge
postgres_table_size The size in bytes of the table on disk gauge

postgres_block_hit_ratio ๐Ÿ”—

gauge

The proportion (between 0 and 1, inclusive) of block reads that used the cache and did not have to go to the disk. Is sent for table, index, and the database as a whole.

postgres_database_size ๐Ÿ”—

gauge

Size in bytes of the database on disk

postgres_deadlocks ๐Ÿ”—

cumulative

Total number of deadlocks detected by the system

postgres_index_scans ๐Ÿ”—

cumulative

Total number of index scans on the table.

postgres_live_rows ๐Ÿ”—

gauge

Number of rows live (not deleted) in the table.

postgres_queries_average_time ๐Ÿ”—

cumulative

Top N queries based on the average execution time broken down by database

postgres_queries_calls ๐Ÿ”—

cumulative

Top N most frequently executed queries broken down by database

postgres_queries_total_time ๐Ÿ”—

cumulative

Top N queries based on the total execution time broken down by database

postgres_query_count ๐Ÿ”—

cumulative

Total number of queries executed on the database, broken down by user. Note that the accuracy of this metric depends on the PostgreSQL pg_stat_statements.max config option being large enough to hold all queries.

postgres_query_time ๐Ÿ”—

cumulative

Total time taken to execute queries on the database, broken down by user.

postgres_rows_deleted ๐Ÿ”—

cumulative

Number of rows deleted from the table.

postgres_rows_inserted ๐Ÿ”—

cumulative

Number of rows inserted into the table.

postgres_rows_updated ๐Ÿ”—

cumulative

Number of rows updated in the table.

postgres_sequential_scans ๐Ÿ”—

cumulative

Total number of sequential scans on the table.

postgres_sessions ๐Ÿ”—

gauge

Number of sessions currently on the server instance. The state dimension will specify which which type of session (see state row of pg_stat_activity).

postgres_table_size ๐Ÿ”—

gauge

The size in bytes of the table on disk.

In order to get metrics about query execution time, you must enable the pg_stat_statements extension. This extension must be specified in the shared_preload_libraries config option in the main PostgreSQL configuration at server start up. Then the extension must be enabled for each database by running CREATE EXTENSION IF NOT EXISTS pg_stat_statements; on each database.

Note that in order to get consistent and accurate query execution time metrics, you must set the pg_stat_statements.max config option to larger than the number of distinct queries on the server.

Here is a sample configuration of Postgres to enable statement tracking.

Tested with PostgreSQL 9.2+.

If you want to collect additional metrics about PostgreSQL, use the sql monitor.