Skip to main content
The PostgreSQL integration requires CXDOT Collector 1.4.0 or greater. PostgreSQL is an open source object-relational database system. Use the PostgreSQL CXDOT Collector integration with the CXDOT Collector to collect connection and workload metrics from PostgreSQL servers running in your environment.

Supported telemetry types

The integration supports these telemetry types:

Prerequisites

The integration has the following prerequisites:
  • Create a PostgreSQL role that can authenticate with a password and connect to the postgres database and every non-template database on each target.
  • To collect ANALYZE progress metrics (postgresql.analyze.*, PostgreSQL 13 and later) for operations run by other roles, grant the monitoring role the predefined pg_monitor role. Grant pg_monitor only when you need that metric family. The role can read privileged server statistics.
  • Make each PostgreSQL endpoint reachable from the CXDOT Collector.

Configure

The integration is enabled by default. You can configure targets either by annotating pods in your Kubernetes cluster or by listing static endpoints in your Helm values. To configure the integration, follow these steps:
  1. To collect from PostgreSQL pods in Kubernetes, add a CXDOT integration annotation to the pod template. The annotation key must end with the container name, and its value can reference credentials stored in a Kubernetes Secret in the same namespace. For example, add the following annotation to your PostgreSQL workload:
    The Collector replaces ${target.host} with the pod IP address, ${target.port.postgres} with the container port named postgres, and ${k8s_secret:postgres-monitoring/password} with the password key of the postgres-monitoring Secret. To allow the Collector to read the Secret, add the pod’s namespace to apiServer.metadataServer.secretAllowlist in the Helm values for your CXDOT Collector.
  2. Optional: To collect from PostgreSQL servers outside your Kubernetes cluster, configure static targets, and provide the password through your deployment’s secret management. For example, add the following to the values.yaml for your Helm chart:
    When you configure static targets, this integration instance collects from exactly those targets instead of discovered targets. To add attributes to every time series from a target, list key:value entries in its tags, such as tags: ["team:billing"] under an instances entry.
  3. Optional: Configure a second integration instance to collect from both annotated pods and static targets. For example, add the following to the values.yaml for your Helm chart:
    The postgres/external key defines a named instance for the static targets, with its own credentials. The bare postgres key continues to collect from annotated pods.
  4. Optional: Report per-table metrics for selected tables: live and dead rows, heap-only tuple updates, sequential scans, index scans, and locks. List the tables in relations, by exact name or by regular expression. For example, add the following to the values.yaml for your Helm chart:
    The integration reports these metrics only for tables in the database it connects to. Discovered targets use the relations in their annotation instead.
  5. Optional: Disable the integration. For example, add the following to the values.yaml for your Helm chart:
Metrics from each discovered pod include that pod’s Kubernetes metadata. For more information, see autodiscovery.

Validate

To validate the integration, follow these steps:
  1. In the Live Telemetry Analyzer, filter for __name__=cxdot.integration.target.health and cxdot.integration.name=postgres. Confirm that each reachable target reports 1 for cxdot.integration.target.health, identified by its server.address and server.port attributes.
  2. In Metrics Explorer, run the following query while clients are connected to the PostgreSQL servers:
    Confirm that the query returns the expected time series for each target.

Configuration reference

Configure one PostgreSQL integration instance with the following settings. In Helm values, place these settings under collection.cxdot.integrations.postgres. In a Collector configuration file, place them under cxdot.integrations.postgres.

Optional settings

  • enabled Type: boolean. Optional. Default: true. Whether to enable this PostgreSQL integration instance. If true, the Collector collects PostgreSQL metrics. If false, the Collector doesn’t run this integration instance.
  • username Type: string. Optional. Default: postgres. PostgreSQL user for connections to every configured instance.
  • password Type: string. Optional. PostgreSQL password for connections to every configured instance. The Collector requires it to start collecting from instances entries, and masks the value in diagnostic output, logs, and errors.
  • database Type: string. Optional. Default: postgres. Database the integration connects to on every configured instance, for the statistics PostgreSQL reports per connected database. Targets discovered through annotations connect to the database their annotation names.
  • relations Type: array of object. Optional. Default: []. Tables to report per-table metrics for, in the connected database: live and dead rows, heap-only tuple updates, sequential scans, index scans for each index, and locks. A table matches when it matches any entry. When the list is empty, the integration doesn’t report these metrics. Targets discovered through annotations use the relations in their annotation instead.
  • relations[].relation_name Type: string. Optional. Minimum length: 1. Exact name of a table to match. Set either relation_name or relation_regex.
  • relations[].relation_regex Type: string. Optional. Minimum length: 1. PostgreSQL regular expression that matches table names, such as ^orders_. The expression matches anywhere in the name unless you anchor it. PostgreSQL checks the expression when it runs the queries, so an invalid expression stops these metrics rather than failing at startup. Set either relation_name or relation_regex.
  • relations[].schemas Type: array of string. Optional. Default: []. Schemas the entry matches tables in. When empty, the entry matches tables in every schema.
  • relations[].relkind Type: array of string. Optional. Default: []. Kinds of relation to report locks for, as PostgreSQL pg_class.relkind codes, such as r (table), i (index), S (sequence), v (view), m (materialized view), p (partitioned table), and I (partitioned index). A code PostgreSQL doesn’t use matches nothing. When empty, the entry reports locks on relations of every kind. Only lock metrics use this setting.
  • max_relations Type: integer. Optional. Default: 300. Minimum: 1. Maximum number of tables to report live and dead rows, heap-only tuple updates, and sequential scans for in each collection. When more tables match, which ones report is unspecified. Index scans and locks aren’t limited.
  • collection_interval Type: duration. Optional. Default: 10s. How often the integration collects metrics from each PostgreSQL instance.
  • timeout Type: duration. Optional. Default: 10s. Maximum time allowed to collect metrics from a PostgreSQL instance during one interval. A value of 0s disables the timeout.
  • instances Type: array of object. Optional. Default: []. PostgreSQL instances to monitor. Each discovered target uses the credentials and TLS settings in its annotation.
  • instances[].endpoint Type: string. Required. Network address of the PostgreSQL instance in host:port format.
  • instances[].tags Type: array of string. Optional. Default: []. Resource attributes in key:value format to add to every time series from this instance. For example, team:billing adds team with the value billing. Values can contain colons. The integration ignores entries without a colon and keeps the last value for repeated keys. A tag can’t replace an attribute set by the integration or by the collector-wide resource_attributes. Discovered targets apply tags from their annotation instance in the same way.
  • tls Type: object. Optional. Transport Layer Security (TLS) settings for connections to the configured instances. By default, both of the integration’s connections use TLS, but only one verifies the server certificate: the cxdot_internal_postgres receiver does and the postgresql receiver doesn’t. Set insecure_skip_verify to choose for both. Set insecure to true to connect without TLS. Targets discovered through annotations use the TLS settings in their annotations instead.
  • tls.ca_file Type: string. Optional. Path to a PEM-encoded certificate authority (CA) certificate file used to verify the PostgreSQL server certificate. If omitted, the Collector uses the system certificate authority pool.
  • tls.ca_pem Type: string. Optional. PEM-encoded certificate authority certificate content used to verify the PostgreSQL server certificate.
  • tls.cert_file Type: string. Optional. Path to the PEM-encoded client certificate file to present when the PostgreSQL endpoint requires mutual TLS authentication.
  • tls.cert_pem Type: string. Optional. PEM-encoded client certificate content to present when the PostgreSQL instance requires mutual TLS authentication.
  • tls.cipher_suites Type: array of string. Optional. TLS cipher suites that the Collector can use, in preference order. If omitted, the Collector uses the secure defaults from the Go TLS library. For supported names, see https://go.dev/src/crypto/tls/cipher_suites.go.
  • tls.curve_preferences Type: array of string. Optional. Elliptic curves that the Collector can use for an elliptic curve Diffie-Hellman ephemeral handshake, in preference order. If omitted, the Collector uses the Go TLS library defaults.
  • tls.include_system_ca_certs_pool Type: boolean. Optional. Whether to add certificates from the system certificate authority pool to the configured certificate authorities. If true, the Collector trusts both sources. If false, the Collector trusts only the configured certificate authorities.
  • tls.insecure Type: boolean. Optional. Whether to disable transport security. If true, the Collector uses an unencrypted connection. If false, the Collector connects with TLS.
  • tls.insecure_skip_verify Type: boolean. Optional. Default: true. Whether to skip verification of the PostgreSQL server certificate. If true, the connection is encrypted but isn’t authenticated and is vulnerable to man-in-the-middle attacks. If false, the Collector verifies the certificate.
  • tls.key_file Type: string. Optional. Path to the PEM-encoded client private key file used with cert_file for mutual TLS authentication.
  • tls.key_pem Type: string. Optional. PEM-encoded client private key content used with cert_pem for mutual TLS authentication.
  • tls.reload_interval Type: duration. Optional. How often the Collector reloads certificate and key files. If omitted, the Collector doesn’t reload the files.
  • tls.tpm Type: object. Optional. Trusted platform module settings for the TLS private key.
  • tls.tpm.auth Type: string. Optional. Authorization value for the trusted platform module key.
  • tls.tpm.enabled Type: boolean. Optional. Whether to use a trusted platform module for the TLS private key. If true, the Collector loads the key from the configured device or socket. If false, the Collector uses the configured key file or in-memory key.
  • tls.tpm.owner_auth Type: string. Optional. Owner authorization value for the trusted platform module.
  • tls.tpm.path Type: string. Optional. Path to the trusted platform module device or Unix domain socket, such as /dev/tpm0 or /dev/tpmrm0.