Docs » Configure application receivers » Configure application receivers for databases » PostgreSQL

PostgreSQL 🔗

Description 🔗

The Splunk Distribution of OpenTelemetry Collector deploys this integration as the postgresql monitor via the Smart Agent Receiver.

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

See signalfx-agent/pkg/monitors/postgresql/ for the monitor source.

Installation 🔗

This monitor is provided by the Smart Agent and is available by using the SignalFx Smart Agent Receiver in the Splunk Distribution of OpenTelemetry Collector.

To install this integration:

  1. Deploy the Splunk Distribution of OpenTelemetry Collector to your host or container platform.

  2. Configure the monitor, as described in the next section.

To report all available metrics, enable the pg_stat_statements extension in your PostgreSQL deployment. This extension must be specified in the shared_preload_libraries configuration option in the main PostgreSQL configuration at server start up. You must also enable the extension for each database by running CREATE EXTENSION IF NOT EXISTS pg_stat_statements; on each database.

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

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

This configuration was tested with PostgreSQL 9.2+.

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

Configuration 🔗

The Splunk Distribution of OpenTelemetry Collector allows embedding a Smart Agent monitor configuration in an associated Smart Agent Receiver instance.

Note: Providing a postgresql monitor entry in your Smart Agent or Collector configuration is required for its use. Use the appropriate form for your agent type.

Smart Agent 🔗

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

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

See Smart Agent example configuration for an autogenerated example of a YAML configuration file, with default values where applicable.

Splunk Distribution of OpenTelemetry Collector 🔗

To activate this monitor in the Splunk Distribution of OpenTelemetry Collector, add the following to your agent configuration:

receivers:
  smartagent/postgresql:
    type: postgresql
    ...  # Additional config

To complete the monitor activation, you must also include the smartagent/postgresql receiver item in a metrics pipeline. To do this, add the receiver item to the service > pipelines > metrics > receivers section of your configuration file.

See configuration examples for specific use cases that show how the collector can integrate and complement existing environments.

Configuration settings 🔗

The following table shows the configuration options for the postgresql monitor:

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 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)

The following is an example postgresql Smart Agent monitor configuration. This example uses the Vault remote configuration source to connect to PostgreSQL using the params map. This map allows you to pull out the username and password individually from Vault and interpolate them into the connectionString configuration 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"]

Metrics 🔗

These are the metrics available for this integration.

Metrics about replication 🔗

Replication metrics may not be available on some PostgreSQL servers. For now, this monitor automatically disables the replication metrics group if the monitor detects Aurora. This helps avoid following the error: Function pg_last_xlog_receive_location() is currently not supported for Aurora

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