> ## Documentation Index > Fetch the complete documentation index at: https://otel.fyi/llms.txt > Use this file to discover all available pages before exploring further. # Postgresql > OpenTelemetry receiver for Postgresql # Postgresql Receiver ![Status](https://img.shields.io/badge/status-beta-yellow) **Available in:** `contrib` **Maintainers:** [@antonblock](https://github.com/antonblock), [@ishleenk17](https://github.com/ishleenk17), [@Caleb-Hurshman](https://github.com/Caleb-Hurshman), [@ebrdarSplunk](https://github.com/ebrdarSplunk) **Source:** [opentelemetry-collector-contrib](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/postgresqlreceiver) ## Supported Telemetry ![Logs](https://img.shields.io/badge/logs-development-blue) ![Metrics](https://img.shields.io/badge/metrics-beta-green) ## Overview ## Prerequisites See PostgreSQL documentation for [supported versions](https://www.postgresql.org/support/versioning). The monitoring user must be granted `SELECT` on `pg_stat_database`. > \[!NOTE] > The feature gate `receiver.postgresql.separateSchemaAttr` addresses an inconsistency in how schema names > are reported across different metric types. When enabled, schema names are consistently reported in a > dedicated `postgresql.schema.name` resource attribute. > > **Status:** Alpha (disabled by default) > > **When disabled (default behavior):** > > * Table metrics: `postgresql.table.name = "schema_name.table_name"` (schema included) > * Index metrics: `postgresql.table.name = "table_name"` (schema **missing**) > * Function metrics: Schema reported separately in some cases > > **When enabled (recommended for consistency):** > > * All metrics consistently use: > * `postgresql.schema.name = "schema_name"` > * `postgresql.table.name = "table_name"` > > This ensures reliable correlation of metrics when tables with identical names exist across different schemas. > To enable: > > ```bash theme={null} > otelcol-contrib --feature-gates=receiver.postgresql.separateSchemaAttr > ``` > > See [https://github.com/open-telemetry/opentelemetry-collector-contrib/issues/29559](https://github.com/open-telemetry/opentelemetry-collector-contrib/issues/29559) for more details. ## Configuration The following settings are required to create a database connection: * `username` * `password` The following settings are optional: * `endpoint` (default = `localhost:5432`): The endpoint of the PostgreSQL server. Whether using TCP or Unix sockets, this value should be `host:port`. If `transport` is set to `unix`, the endpoint will internally be translated from `host:port` to `/host.s.PGSQL.port` * `transport` (default = `tcp`): The transport protocol being used to connect to PostgreSQL. Available options are `tcp` and `unix`. * `databases` (default = `[]`): The list of databases for which the receiver will attempt to collect statistics. If an empty list is provided, the receiver will attempt to collect statistics for all non-template databases. * `exclude_databases` (default = `[]`): List of databases which will be excluded when collecting statistics. The following settings are also optional and nested under `tls` to help configure client transport security * `insecure` (default = `false`): Whether to enable client transport security for the PostgreSQL connection. * `insecure_skip_verify` (default = `true`): Whether to validate server name and certificate if client transport security is enabled. * `cert_file` (default = `$HOME/.postgresql/postgresql.crt`): A certificate used for client authentication, if necessary. * `key_file` (default = `$HOME/.postgresql/postgresql.key`): An SSL key used for client authentication, if necessary. * `ca_file` (default = ""): A set of certificate authorities used to validate the database server's SSL certificate. * `collection_interval` (default = `10s`): This receiver collects metrics on an interval. This value must be a string readable by Golang's [time.ParseDuration](https://pkg.go.dev/time#ParseDuration). Valid time units are `ns`, `us` (or `µs`), `ms`, `s`, `m`, `h`. * `initial_delay` (default = `1s`): defines how long this receiver waits before starting. ### Query Sample Collection We provide functionality to collect the query sample from PostgreSQL. It will get historical query from `pg_stat_activity`. To enable it, you will need the following configuration ``` ... events: db.server.query_sample: enabled: true ... ``` By default, query sample collection is disabled, also note, to use it, you will need to grant the user you are using `pg_monitor`. Take the example from `testdata/integration/init.sql` ```sql theme={null} GRANT pg_monitor TO otelu; ``` The following options are available: * `max_rows_per_query`: (optional, default=1000) The max number of rows would return from the query against `pg_stat_activity`. ### Top Query Collection We provide functionality to collect the most executed queries from PostgreSQL. It will get data from `pg_stat_statements` and report incremental value of `total_exec_time`, `total_plan_time`, `calls`, `rows`, `shared_blks_dirtied`, `shared_blks_hit`, `shared_blks_read`, `shared_blks_written`, `temp_blks_read`, `temp_blks_written`. To enable it, you will need the following configuration ``` ... events: db.server.top_query: enabled: true ... ``` Along with those attributes, we will also report the query plan we gathered if it is possible. By default, top query collection is disabled, also note, to use it, you will need to create the extension to every database. Take the example from `testdata/integration/02-create-extension.sh` ```sql theme={null} CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` The following options are available: * `max_rows_per_query`: (optional, default=1000) The max number of rows would return from the query against `pg_stat_statements`. * `top_n_query`: (optional, default=200) The maximum number of active queries to report (to the next consumer) in a single run. * `max_explain_each_interval`: (optional, default=1000). The maximum number of explain query to be sent in each scrape interval. The top query collection would not get the query plan directly. Instead, we need to mimic the query in the database and get the query plan from database separately. This could lead some resources usage and limit this will reduce the impact on your database. * `query_plan_cache_size`: (optional, default=1000). The query plan cache size. Once we got explain for one query, we will store it in the cache. This defines the cache's size for query plan. * `query_plan_cache_ttl`: (optional, default=1h). How long before the query plan cache got expired. Example values: `1m`, `1h`. * `collection_interval`: (optional, default=60s). This receiver can collect top\_query metrics on an interval. If not provided then the global collection\_interval takes effect. This value must be a string readable by Golang's [time.ParseDuration](https://pkg.go.dev/time#ParseDuration). Valid time units are `ns`, `us` (or `µs`), `ms`, `s`, `m`, `h`. ### Example Configuration ```yaml theme={null} receivers: postgresql: endpoint: localhost:5432 transport: tcp username: otel password: ${env:POSTGRESQL_PASSWORD} databases: - otel collection_interval: 10s tls: insecure: false insecure_skip_verify: false ca_file: /home/otel/authorities.crt cert_file: /home/otel/mypostgrescert.crt key_file: /home/otel/mypostgreskey.key events: db.server.query_sample: enabled: true db.server.top_query: enabled: true query_sample_collection: max_rows_per_query: 100 top_query_collection: max_rows_per_query: 100 top_n_query: 100 collection_interval: 60s ``` The full list of settings exposed for this receiver are documented in [config.go](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/postgresqlreceiver/config.go) with detailed sample configurations in [testdata/config.yaml](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/postgresqlreceiver/testdata/config.yaml). TLS config is documented further under the [opentelemetry collector's configtls package](https://github.com/open-telemetry/opentelemetry-collector/blob/main/config/configtls/README.md). ## Connection pool feature The feature gate `receiver.postgresql.connectionPool` allows to enable the creation & reuse of a pool per database for the connections instead of creating & closing on each scrape. This is generally a useful optimization but also alleviates the volume of generated audit logs when the PostgreSQL instance is configured with `log_connections=on` and/or `log_disconnections=on`. When this feature gate is enabled, the following optional settings are available nested under `connection_pool` to help configure the connection pools: * `max_idle_time`: The maximum amount of time a connection may be idle before being closed. * `max_lifetime`: The maximum amount of time a connection may be reused. * `max_idle`: The maximum number of connections in the idle connection pool. * `max_open`: The maximum number of open connections to the database. Those settings and their defaults are further documented in the [`sql/database`](https://pkg.go.dev/database/sql#DB) package. ### Example Configuration ```yaml theme={null} receivers: postgresql: endpoint: localhost:5432 transport: tcp username: otel password: ${env:POSTGRESQL_PASSWORD} connection_pool: max_idle_time: 10m max_lifetime: 0 max_idle: 2 max_open: 5 ``` ## Metrics Details about the metrics produced by this receiver can be found in [metadata.yaml](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/postgresqlreceiver/metadata.yaml) ## Metrics | Metric Name | Description | Unit | Type | Attributes | | ----------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------- | ------------------- | ------------- | ---------------------------------------- | | ✅ `postgresql.backends` | The number of backends. | 1 | UpDownCounter | | | ✅ `postgresql.bgwriter.buffers.allocated` | Number of buffers allocated. | \{buffers} | Counter | | | ✅ `postgresql.bgwriter.buffers.writes` | Number of buffers written. | \{buffers} | Counter | bg\_buffer\_source | | ✅ `postgresql.bgwriter.checkpoint.count` | The number of checkpoints performed. | \{checkpoints} | Counter | bg\_checkpoint\_type | | ✅ `postgresql.bgwriter.duration` | Total time spent writing and syncing files to disk by checkpoints. | ms | Counter | bg\_duration\_type | | ✅ `postgresql.bgwriter.maxwritten` | Number of times the background writer stopped a cleaning scan because it had written too many buffers. | 1 | Counter | | | ❌ `postgresql.blks_hit` | Number of times disk blocks were found already in the buffer cache. | \{blks\_hit} | Counter | | | ❌ `postgresql.blks_read` | Number of disk blocks read in this database. | \{blks\_read} | Counter | | | ✅ `postgresql.blocks_read` | The number of blocks read. | 1 | Counter | source | | ✅ `postgresql.commits` | The number of commits. | 1 | Counter | | | ✅ `postgresql.connection.max` | Configured maximum number of client connections allowed | \{connections} | Gauge | | | ✅ `postgresql.database.count` | Number of user databases. | \{databases} | UpDownCounter | | | ❌ `postgresql.database.locks` | The number of database locks. | \{lock} | Gauge | relation, mode, lock\_type | | ✅ `postgresql.db_size` | The database disk usage. | By | UpDownCounter | | | ❌ `postgresql.deadlocks` | The number of deadlocks. | \{deadlock} | Counter | | | ❌ `postgresql.function.calls` | The number of calls made to a function. Requires `track_functions=pl\|all` in Postgres config. | \{call} | Counter | function | | ✅ `postgresql.index.scans` | The number of index scans on a table. | \{scans} | Counter | | | ✅ `postgresql.index.size` | The size of the index on disk. | By | Gauge | | | ✅ `postgresql.operations` | The number of db row operations. | 1 | Counter | operation | | ✅ `postgresql.replication.data_delay` | The amount of data delayed in replication. | By | Gauge | replication\_client | | ✅ `postgresql.rollbacks` | The number of rollbacks. | 1 | Counter | | | ✅ `postgresql.rows` | The number of rows in the database. | 1 | UpDownCounter | state | | ❌ `postgresql.sequential_scans` | The number of sequential scans. | \{sequential\_scan} | Counter | | | ✅ `postgresql.table.count` | Number of user tables in a database. | \{table} | UpDownCounter | | | ✅ `postgresql.table.size` | Disk space used by a table. | By | UpDownCounter | | | ✅ `postgresql.table.vacuum.count` | Number of times a table has manually been vacuumed. | \{vacuum} | Counter | | | ❌ `postgresql.temp.io` | Total amount of data written to temporary files by queries. | By | Counter | | | ❌ `postgresql.temp_files` | The number of temp files. | \{temp\_file} | Counter | | | ❌ `postgresql.tup_deleted` | Number of rows deleted by queries in the database. | \{tup\_deleted} | Counter | | | ❌ `postgresql.tup_fetched` | Number of rows fetched by queries in the database. | \{tup\_fetched} | Counter | | | ❌ `postgresql.tup_inserted` | Number of rows inserted by queries in the database. | \{tup\_inserted} | Counter | | | ❌ `postgresql.tup_returned` | Number of rows returned by queries in the database. | \{tup\_returned} | Counter | | | ❌ `postgresql.tup_updated` | Number of rows updated by queries in the database. | \{tup\_updated} | Counter | | | ✅ `postgresql.wal.age` | Age of the oldest WAL file. | s | Gauge | | | ❌ `postgresql.wal.delay` | Time between flushing recent WAL locally and receiving notification that the standby server has completed an operation with it. | s | Gauge | wal\_operation\_lag, replication\_client | | ✅ `postgresql.wal.lag` | Time between flushing recent WAL locally and receiving notification that the standby server has completed an operation with it. | s | Gauge | wal\_operation\_lag, replication\_client | ## Attributes | Attribute Name | Description | Type | Values | | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------ | -------------------------------------------------------------------------------------------------- | | `source` | The source of a buffer write. | string | `backend`, `backend_fsync`, `checkpoints`, `bgwriter` | | `type` | The type of checkpoint state. | string | `requested`, `scheduled` | | `type` | The type of time spent during the checkpoint. | string | `sync`, `write` | | `db.namespace` | The namespace or schema of the database where the query is executed. | string | | | `db.query.text` | The text of the database query being executed. | string | | | `db.system.name` | The database management system (DBMS) product as identified by the client instrumentation. | string | `postgresql` | | `function` | The name of the function. | string | | | `lock_type` | Type of the lockable object. | string | | | `mode` | Name of the lock mode held or desired by the process. | string | | | `network.peer.address` | IP address of the client connected to this backend. | string | | | `network.peer.port` | TCP port number that the client is using for communication with this backend. | int | | | `operation` | The database operation. | string | `ins`, `upd`, `del`, `hot_upd` | | `postgresql.application_name` | Name of the application that is connected to this backend. | string | | | `postgresql.blocking.lock.mode` | The lock mode being requested by the blocked session (e.g. RowExclusiveLock, AccessExclusiveLock). Empty string when not blocked. | string | | | `postgresql.blocking.lock.relation` | The name of the relation (table) being waited on. Empty string when not blocked or when lock is not on a relation. | string | | | `postgresql.blocking.lock.type` | The type of lock resource being waited on (e.g. relation, transactionid, tuple). Empty string when not blocked. | string | | | `postgresql.blocking.pids` | Array of PIDs of sessions blocking this session (from pg\_blocking\_pids). Empty array when not blocked. | string | | | `postgresql.blocking.start_time` | UTC timestamp (RFC3339) when the current lock wait began, derived from pg\_locks.waitstart. Empty string when not blocked. | string | | | `postgresql.blocking.transaction.start_time` | UTC timestamp (RFC3339) when the current transaction started. Empty string when no active transaction. | string | | | `postgresql.blocking.wait_duration` | Whole seconds this session has been waiting for a lock, measured from pg\_locks.waitstart. 0 when not blocked. | int | | | `postgresql.calls` | Number of times the statement was executed, reported in delta value. | int | | | `postgresql.client_hostname` | Host name of the connected client, as reported by a reverse DNS lookup of client\_addr. | string | | | `postgresql.pid` | Process ID of this backend. | int | | | `postgresql.query_id` | Identifier of this backend's most recent query. If state is active this field shows the identifier of the currently executing query. In all other states, it shows the identifier of last query that was executed. | string | | | `postgresql.query_plan` | The execution plan used by PostgreSQL for the query. | string | | | `postgresql.query_start` | Time when the currently active query was started, or if state is not active, when the last query was started. | string | | | `postgresql.queryid` | Hash code to identify identical normalized queries. | string | | | `postgresql.rolname` | The name of the PostgreSQL role that executed the query. | string | | | `postgresql.rows` | Total number of rows retrieved or affected by the statement, reported in delta value. | int | | | `postgresql.shared_blks_dirtied` | Total number of shared blocks dirtied by the statement, reported in delta value. | int | | | `postgresql.shared_blks_hit` | Total number of shared block cache hits by the statement, reported in delta value. | int | | | `postgresql.shared_blks_read` | Total number of shared blocks read by the statement, reported in delta value. | int | | | `postgresql.shared_blks_written` | Total number of shared blocks written by the statement, reported in delta value. | int | | | `postgresql.state` | Current overall state of this backend | string | | | `postgresql.temp_blks_read` | Total number of temp blocks read by the statement, reported in delta value. | int | | | `postgresql.temp_blks_written` | Total number of temp blocks written by the statement, reported in delta value. | int | | | `postgresql.total_exec_time` | Total time spent executing the statement, in delta milliseconds. | double | | | `postgresql.total_plan_time` | Total time spent planning the statement, in delta milliseconds. | double | | | `postgresql.wait_event` | Wait event name if backend is currently waiting, otherwise NULL. | string | | | `postgresql.wait_event_type` | The type of event for which the backend is waiting, if any; otherwise NULL. | string | | | `relation` | OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation. | string | | | `replication_client` | The IP address of the client connected to this backend. If this field is "unix", it indicates either that the client is connected via a Unix socket. | string | | | `source` | The block read source type. | string | `heap_read`, `heap_hit`, `idx_read`, `idx_hit`, `toast_read`, `toast_hit`, `tidx_read`, `tidx_hit` | | `state` | The tuple (row) state. | string | `dead`, `live` | | `user.name` | Name of the user logged into this backend. | string | | | `operation` | The operation which is responsible for the lag. | string | `flush`, `replay`, `write` | ## Resource Attributes | Attribute Name | Description | Type | Enabled | | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ------ | ------- | | `postgresql.database.name` | The name of the database. | string | ✅ | | `postgresql.index.name` | The name of the index on a table. | string | ✅ | | `postgresql.schema.name` | The schema name. | string | ✅ | | `postgresql.table.name` | The table name. | string | ✅ | | `service.instance.id` | A unique identifier of the PostgreSQL instance in the format host:port (defaults to 'unknown:5432' in case of error in generating this value). | string | ✅ | | `service.name` | Logical name of the service. When enabled, defaults to unknown\_service:postgresql. | string | ❌ | | `service.namespace` | Logical namespace for the service (for example team or environment). When enabled, defaults to an empty string until set via configuration. | string | ❌ | ## Configuration ### Example Configuration ```yaml theme={null} postgresql/minimal: endpoint: localhost:5432 username: otel password: ${env:POSTGRESQL_PASSWORD} top_query_collection: top_n_query: 1234 query_plan_cache_ttl: 123s postgresql/pool: endpoint: localhost:5432 transport: tcp username: otel password: ${env:POSTGRESQL_PASSWORD} connection_pool: max_idle_time: 30s max_idle: 5 postgresql/all: endpoint: localhost:5432 transport: tcp username: otel password: ${env:POSTGRESQL_PASSWORD} databases: - otel exclude_databases: - template0 collection_interval: 10s tls: insecure: false insecure_skip_verify: false ca_file: /home/otel/authorities.crt cert_file: /home/otel/mypostgrescert.crt key_file: /home/otel/mypostgreskey.key connection_pool: max_idle_time: 30s max_lifetime: 1m max_idle: 5 max_open: 10 ``` *** *Last generated: 2026-07-06*