PostgreSQL 🔗

Description 🔗

The Splunk Distribution of OpenTelemetry Collector deploys this integration as the postgresql monitor type for 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 using SQL queries.

Note

This monitor is deprecated in favor of the postgresql receiver. Switch to that receiver as the Smart Agent is deprecated. To learn more, see PostgreSQL receiver.

Benefits 🔗

After you configure the integration, you can access these features:

View metrics. You can create your own custom dashboards, and most monitors provide built-in dashboards as well. For information about dashboards, see View dashboards in Observability Cloud.
View a data-driven visualization of the physical servers, virtual machines, AWS instances, and other resources in your environment that are visible to Infrastructure Monitoring. For information about navigators, see Splunk Infrastructure Monitoring navigators.
Access the Metric Finder and search for metrics sent by the monitor. For information, see Use the Metric Finder.

Installation 🔗

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

Follow these steps to deploy this integration:

Deploy the Splunk Distribution of OpenTelemetry Collector to your host or container platform:
- Install on Kubernetes
- Install on Linux
Configure the monitor, as described in the Configuration section.
Restart the Splunk Distribution of OpenTelemetry Collector.

To report all available metrics, activate 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 activate 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 activate statement tracking.

This configuration was tested with PostgreSQL 9.2+.

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

Configuration 🔗

To use this integration of a Smart Agent monitor with the Collector:

Include the Smart Agent receiver in your configuration file.
Add the monitor type to the Collector configuration, both in the receiver and pipelines sections.

Read more on how to Use Smart Agent monitors with the Collector.
See how to set up the Smart Agent receiver.
Learn about config options in Collector default 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. For example:

service:
  pipelines:
    metrics:
      receivers: [smartagent/postgresql]

See configuration examples for specific use cases that show how the Splunk Distribution of OpenTelemetry 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 🔗

The following metrics are available for this integration:

Notes 🔗

Learn more about the available metric types in Observability Cloud.
Default metrics are those metrics included in host-based subscriptions in Observability Cloud, such as host, container, or bundled metrics. Custom metrics are not provided by default and might be subject to charges. See more about metric categories.
To add additional metrics, see how to configure extraMetrics using the Collector.

Metrics about replication 🔗

Replication metrics can’t be available on some PostgreSQL servers. For now, this monitor automatically deactivates 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).

Get help 🔗

If you are not able to see your data in Splunk Observability Cloud, try these tips:

Submit a case in the Splunk Support Portal
- Available to Splunk Observability Cloud customers
Call Splunk Customer Support
- Available to Splunk Observability Cloud customers
Ask a question and get answers through community support at Splunk Answers
- Available to Splunk Observability Cloud customers and free trial users
Join the Splunk #observability user group Slack channel to communicate with customers, partners, and Splunk employees worldwide
- Available to Splunk Observability Cloud customers and free trial users
- To learn how to join, see Get Started with Splunk Community - Chat groups

To learn about even more support options, see Splunk Customer Success.

Related Topics