Skip to content

Latest commit

 

History

History
183 lines (138 loc) · 10.1 KB

postgresql.md

File metadata and controls

183 lines (138 loc) · 10.1 KB
Raw

postgresql

Monitor Type: postgresql (Source)

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Overview

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.

Metrics about Queries

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.

Metrics about Replication

Replication metrics could not be available on some PostgreSQL servers. For now, this monitor automatically disable replication metrics group if it detects Aurora to avoid following error:

Function pg_last_xlog_receive_location() is currently not supported for Aurora

The metric postgres_replication_state will only be reported for master and postgres_replication_lag only for standby role (replica).

Example Configuration

This example uses the Vault remote config source to connect to PostgreSQL using the params map that allows you to pull out the username and password individually from Vault and interpolate them into the connectionString config option.

monitors:
 - type: postgresql
   connectionString: 'sslmode=disable user={{.username}} password={{.password}}'
   params: &psqlParams
     username: {"#from": "vault:secret/my-database[username]"}
     password: {"#from": "vault:secret/my-database[password]"}
   discoveryRule: 'container_image =~ "postgres" && port == 5432'

 # This monitor will monitor additional queries from PostgreSQL using the
 # provided SQL queries.
 - type: sql
   dbDriver: postgres
   connectionString: 'sslmode=disable user={{.username}} password={{.password}}'
   # This is a YAML reference to avoid duplicating the above config.
   params: *psqlParams
   queries:
     - query: 'SELECT COUNT(*) as count, country, status FROM customers GROUP BY country, status;'
       metrics:
         - metricName: "customers"
           valueColumn: "count"
           dimensionColumns: ["country", "status"]

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)
logQueries no bool If true, queries will be logged at the info level. (default: false)
topQueryLimit no integer The number of top queries to consider when publishing query-related metrics (default: 10)

Metrics

These are the metrics available for this monitor. Metrics that are categorized as container/host (default) are in bold and italics in the list below.

  • 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_conflicts (cumulative)
    The number of conflicts.

  • 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_locks (gauge)
    The number of locks active.

  • postgres_pct_connections (gauge)
    The number of connections to this database as a fraction of the maximum number of allowed connections.

  • 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 type of session (see state row of pg_stat_activity).

  • postgres_table_size (gauge)
    The size in bytes of the table on disk.

  • postgres_xact_commits (cumulative)
    The number of transactions that have been committed in this database.

  • postgres_xact_rollbacks (cumulative)
    The number of transactions that have been rolled back in this database.

Group queries

All of the following metrics are part of the queries metric group. All of the non-default metrics below can be turned on by adding queries to the monitor config option extraGroups:

  • 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

Group replication

All of the following metrics are part of the replication metric group. All of the non-default metrics below can be turned on by adding replication to the monitor config option extraGroups:

  • postgres_replication_lag (gauge)
    The current replication delay in seconds. Always = 0 on master.
  • postgres_replication_state (gauge)
    The current replication state.

Non-default metrics (version 4.7.0+)

To emit metrics that are not default, you can add those metrics in the generic monitor-level extraMetrics config option. Metrics that are derived from specific configuration options that do not appear in the above list of metrics do not need to be added to extraMetrics.

To see a list of metrics that will be emitted you can run agent-status monitors after configuring this monitor in a running agent instance.

Dimensions

The following dimensions may occur on metrics emitted by this monitor. Some dimensions may be specific to certain metrics.

Name Description
database The name of the database within a PostgreSQL server to which the metric pertains.
index For index metrics, the name of the index
replication_role For "replication_lag" metric only, could be "master" or "standby".
schemaname The name of the schema within which the object being monitored resides (e.g. public).
slot_name For "replication_state" metric only, the name of replication slot.
slot_type For "replication_state" metric only, the type of replication.
table The name of the table to which the metric pertains.
tablespace For table metrics, the tablespace in which the table belongs, if not null.
type Whether the object (table, index, function, etc.) belongs to the system or user.
user For query metrics, the user name of the user that executed the queries.