PostgreSQL monitoring integration

Preview

We're still working on query-level monitoring feature of this integration, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

The New Relic PostgreSQL on-host integration receives and sends inventory metrics from your PostgreSQL instance to the New Relic platform, where you can aggregate and visualize key performance metrics. Data from instances, databases, and clusters helps you find the source of problems.

Important

For best results, regularly update the integration package and the infrastructure agent.

PostgreSQL users and permissions

If you also want to obtain table and index-related metrics (for example, table size and index size), the PostgreSQL role used by the integration (new_relic) also needs SELECT permissions on the tables from which it will gather metrics from. For example, to allow the integration to collect metrics from all the tables and indexes present in the database (in the public schema), use the following:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO new_relic;

If you also want to obtain query-level metrics from the PostgreSQL custom query config file, the PostgreSQL role used by the integration (new_relic) needs to be added to the (pg_read_all_stats) role. This is due to the user leveraging the (pg_stat_statements) extension.

GRANT pg_read_all_stats TO new_relic;

Enabling the pg_stat_statements extension may require you to manually create it from a query prompt:

CREATE EXTENSION pg_stat_statements;

The `postgresql-config.yml` file

The postgresql-config.ymlfile is the integration's YAML-format configuration where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference. The configuration file has common settings applicable to all integrations, such as interval, timeout, inventory_source.

Specific settings related to PostgreSQL are defined using the env section of the postgresql-config.ymlfile. These settings control the connection to your PostgreSQL instance as well as other security settings and features.

See these more complex config examples.

JSON array: Interpreted as a list of database names from which to collect all relevant metrics, including any tables and indexes belonging to that database.
For example:
```
collection_list: '["postgres"]'
```
JSON object: only entities specified in the object will be collected, no automatic discovery will be performed. The levels of JSON are database name -> schema name -> table name -> index name.
For example:
```
collection_list: '{"postgres":{"public":{"pg_table1":["pg_index1","pg_index2"],"pg_table2":[]}}}'
```

ALL: collect metrics for all databases, schemas, tables, and indexes discovered.

For example:

collection_list: 'ALL'

integrations:
  - name: nri-postgresql
    env:
      USERNAME: postgres
      PASSWORD: pass
      HOSTNAME: psql-sample.localnet
      PORT: 6432
      DATABASE: postgres

      COLLECT_DB_LOCK_METRICS: false
      COLLECTION_LIST: '{"postgres":{"public":{"pg_table1":["pg_index1","pg_index2"],"pg_table2":[]}}}'
      # Enable query performance monitoring by setting its value to true to obtain query monitoring metrics.
      ENABLE_QUERY_MONITORING: 'true'
      # Set the threshold for query response time (in ms) to retrieve individual query performance metrics. Defaults to 500 ms.
      # QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: '500ms' 
      # The number of records for each query performance metrics - Defaults to 20
      # QUERY_MONITORING_COUNT_THRESHOLD: '20' 
      TIMEOUT:  10
    interval: 15s
    labels:
      env: production
      role: postgresql
    inventory_source: config/postgresql

Azure/AWS SSL Enabled Options: Azure Flexible managed database offerings require SSL to connect. AWS RDS/Aurora may require SSL if your MySQL version is 5.7+ and require_secure_transport is set to ON in your Aurora Parameter Group. To accomodate the SSL requirement, these settings in the postgresql-config.yml need to be set to true.
For example:
```
ENABLE_SSL: "true"
TRUST_SERVER_CERTIFICATE: "true"
```
Azure/AWS SSL Disabled Options: In addition to the settings above, the following SSL settings should be commented out or removed from the config. This is due to the config trusting the server certificate above.
For example:
```
# SSL_ROOT_CERT_LOCATION: /etc/newrelic-infra/root_cert.crt
# SSL_CERT_LOCATION: /etc/newrelic-infra/postgresql.crt
# SSL_KEY_LOCATION: /etc/newrelic-infra/postgresql.key
```

Summary: Once these settings are in place, the complete Azure/AWS config file should look like the one below. Note: the infra agent and Postgresql integration should be installed on a host with network access to the database instances.

For example:

integrations:
  - name: nri-postgresql
    env:
      USERNAME: newrelic
      PASSWORD: password
      HOSTNAME: AWS-or-Azure-instance-name
      PORT: 5432
      DATABASE: postgres
      COLLECT_DB_LOCK_METRICS: false
      COLLECTION_LIST: 'ALL'
      # Enable query performance monitoring by setting its value to true to obtain query monitoring metrics.
      ENABLE_QUERY_MONITORING: 'true'
      # Set the threshold for query response time (in ms) to retrieve individual query performance metrics. Defaults to 500 ms.
      # QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: '500ms' 
      # The number of records for each query performance metrics - Defaults to 20
      # QUERY_MONITORING_COUNT_THRESHOLD: '20' 
      ENABLE_SSL: "true"
      TRUST_SERVER_CERTIFICATE: "true"
      # SSL_ROOT_CERT_LOCATION: /etc/newrelic-infra/root_cert.crt
      # SSL_CERT_LOCATION: /etc/newrelic-infra/postgresql.crt
      # SSL_KEY_LOCATION: /etc/newrelic-infra/postgresql.key
      TIMEOUT:  10
    interval: 15s
    labels:
      env: production
      role: postgresql
    inventory_source: config/postgresql

integrations:
  - name: nri-postgresql
    env:
      USERNAME: postgres
      PASSWORD: pass
      HOSTNAME: psql-sample.localnet
      PORT: 6432
      DATABASE: postgres

      COLLECT_DB_LOCK_METRICS: false
      COLLECTION_LIST: '["postgres"]'
      # Enable query performance monitoring by setting its value to true to obtain query monitoring metrics.
      ENABLE_QUERY_MONITORING: 'true'
      # Set the threshold for query response time (in ms) to retrieve individual query performance metrics. Defaults to 500 ms.
      # QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: '500ms' 
      # The number of records for each query performance metrics - Defaults to 20
      # QUERY_MONITORING_COUNT_THRESHOLD: '20' 
      ENABLE_SSL: true
      TRUST_SERVER_CERTIFICATE: false
      SSL_ROOT_CERT_LOCATION: /etc/newrelic-infra/root_cert.crt
      SSL_CERT_LOCATION: /etc/newrelic-infra/postgresql.crt
      SSL_KEY_LOCATION: /etc/newrelic-infra/postgresql.key
      TIMEOUT:  10
    interval: 15s
    labels:
      env: production
      role: postgresql
    inventory_source: config/postgresql

integrations:
  - name: nri-postgresql
    env:
      USERNAME: postgres
      PASSWORD: pass
      HOSTNAME: psql-sample.localnet
      PORT: 6432
      DATABASE: postgres

      COLLECT_DB_LOCK_METRICS: false
      COLLECTION_LIST: ALL
      # Enable query performance monitoring by setting its value to true to obtain query monitoring metrics.
      ENABLE_QUERY_MONITORING: 'true'
      # Set the threshold for query response time (in ms) to retrieve individual query performance metrics. Defaults to 500 ms.
      # QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: '500ms' 
      # The number of records for each query performance metrics - Defaults to 20
      # QUERY_MONITORING_COUNT_THRESHOLD: '20'
      CUSTOM_METRICS_QUERY: >-
        select
          'rows_inserted' as "metric_name",
          'delta' as "metric_type",
          sd.tup_inserted as "metric_value",
          sd.datid as "database_id"
          from pg_stat_database sd;
      TIMEOUT:  10
    interval: 15s
    labels:
      env: production
      role: postgresql
    inventory_source: config/postgresql

An additional YAML configuration file with one or more custom SQL can be defined and the integration will need the path to the file in the CUSTOM_METRICS_CONFIG parameter.

postgresql-config.yml

integrations:
  - name: nri-postgresql
    env:
      USERNAME: postgres
      PASSWORD: pass
      HOSTNAME: psql-sample.localnet
      PORT: 6432
      DATABASE: postgres

      COLLECT_DB_LOCK_METRICS: false
      COLLECTION_LIST: ALL
      # Enable query performance monitoring by setting its value to true to obtain query monitoring metrics.
      ENABLE_QUERY_MONITORING: 'true'
      # Set the threshold for query response time (in ms) to retrieve individual query performance metrics. Defaults to 500 ms.
      # QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: '500ms' 
      # The number of records for each query performance metrics - Defaults to 20
      # QUERY_MONITORING_COUNT_THRESHOLD: '20'
      CUSTOM_METRICS_CONFIG: "path/to/postgresql-custom-query.yml"
      TIMEOUT:  10
    interval: 15s
    labels:
      env: production
      role: postgresql
    inventory_source: config/postgresql

postgresql-custom-query.yml

---
queries:

  # Metric names are set to the column names in the query results
  - query: >-
      SELECT
      BG.checkpoints_timed AS scheduled_checkpoints_performed,
      BG.checkpoints_req AS requested_checkpoints_performed,
      BG.buffers_checkpoint AS buffers_written_during_checkpoint,
      BG.buffers_clean AS buffers_written_by_background_writer,
      BG.maxwritten_clean AS background_writer_stops,
      BG.buffers_backend AS buffers_written_by_backend,
      BG.buffers_alloc AS buffers_allocated
      FROM pg_stat_bgwriter BG;

    # database defaults to the auth database in the main config
    database: new_frontier_config_dev

    # If not set explicitly here, metric type will default to
    # 'gauge' for numbers and 'attribute' for strings
    metric_types:
      buffers_allocated: rate

    # If unset, sample_name defaults to PostgresqlCustomSample
    sample_name: MyCustomSample

For more about the general structure of on-host integration configuration, see On-host integration configuration overview.

Metrics collected by the integration

The PostgreSQL integration collects the following metrics. Some metric names are prefixed with a category indicator and a period, such as db. or index..

PostgreSQLDatabaseSample attributes	Description
`db.connections`	Number of backends currently connected to this database.
`db.maxconnections`	The maximum number of concurrent connections to the database server.
`db.commitsPerSecond`	Committed transactions per second.
`db.rollbacksPerSecond`	Transactions rolled back per second.
`db.readsPerSecond`	Number of disk blocks read in this database per second.
`db.bufferHitsPerSecond`	Number of times disk blocks were found already in the buffer cache, so that a read was not necessary. This only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache.
`db.rowsReturnedPerSecond`	Rows returned by queries per second.
`db.rowsFetchedPerSecond`	Rows fetched by queries per second.
`db.rowsInsertedPerSecond`	Rows inserted per second.
`db.rowsUpdatedPerSecond`	Rows updated per second.
`db.rowsDeletedPerSecond`	Rows deleted per second.
`db.conflicts.tablespacePerSecond`	Number of queries in this database that have been canceled due to dropped tablespaces.
`db.conflicts.locksPerSecond`	Number of queries in this database that have been canceled due to lock timeouts.
`db.conflicts.snapshotPerSecond`	Number of queries in this database that have been canceled due to old snapshots.
`db.conflicts.bufferpinPerSecond`	Number of queries in this database that have been canceled due to pinned buffers.
`db.conflicts.deadlockPerSecond`	Number of queries in this database that have been canceled due to deadlocks.
`db.tempFilesCreatedPerSecond`	Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (for example, sorting or hashing), and regardless of the `log_temp_files` setting.
`db.tempWrittenInBytesPerSecond`	Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the `log_temp_files` setting.
`db.deadlocksPerSecond`	Number of deadlocks detected in this database.
`db.readTimeInMillisecondsPerSecond`	Time spent reading data file blocks by backends in this database, in milliseconds.
`db.writeTimeInMillisecondsPerSecond`	Time spent writing data file blocks by backends in this database, in milliseconds.

PostgreSQLIndexSample attributes	Description
`index.sizeInBytes`	The size of an index.
`index.rowsReadPerSecond`	The number of index entries returned by scans on this index.
`index.rowsFetchedPerSecond`	The number of index entries fetched by scans on this index.

PostgreSQLInstanceSample attributes	Description
`bgwriter.checkpointsScheduledPerSecond`	Number of scheduled checkpoints that have been performed.
`bgwriter.checkpointsRequestedPerSecond`	Number of requested checkpoints that have been performed.
`bgwriter.buffersWrittenForCheckpointsPerSecond`	Number of buffers written during checkpoints.
`bgwriter.buffersWrittenByBackgroundWriterPerSecond`	Number of buffers written by the background writer.
`bgwriter.backgroundWriterStopsPerSecond`	Number of times the background writer stopped a cleaning scan because it had written too many buffers.
`bgwriter.buffersWrittenByBackendPerSecond`	Number of buffers written directly by a backend.
`bgwriter.buffersAllocatedPerSecond`	Number of buffers allocated.
`bgwriter.backendFsyncCallsPerSecond`	Number of times a backend had to execute its own `fsync` call. Normally the background writer handles them even when the backend does its own write.
`bgwriter.checkpointWriteTimeInMillisecondsPerSecond`	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds.
`bgwriter.checkpointSyncTimeInMillisecondsPerSecond`	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds.

PostgreSQLTableSample attributes	Description
`table.totalSizeInBytes`	The total disk space used by the table, including indexes and TOAST data.
`table.indexSizeInBytes`	The total disk space used by indexes attached to the specified table.
`table.liveRows`	Number of live rows.
`table.deadRows`	Number of dead rows.
`table.indexBlocksReadPerSecond`	The number of disk blocks read from all indexes on this table.
`table.indexBlocksHitPerSecond`	The number of buffer hits in all indexes on this table.
`table.indexToastBlocksReadPerSecond`	The number of disk blocks read from this table's TOAST table index.
`table.indexToastBlocksHitPerSecond`	The number of buffer hits in this table's TOAST table index.
`table.lastVacuum`	Time of last vacuum on table.
`table.lastAutoVacuum`	Time of last automatic vacuum on table.
`table.lastAnalyze`	Time of last analyze on table.
`table.lastAutoAnalyze`	Time of last automatic analyze on table.
`table.sequentialScansPerSecond`	Number of sequential scans initiated on this table per second.
`table.sequentialScanRowsFetchedPerSecond`	Number of live rows fetched by sequential scans per second.
`table.indexScansPerSecond`	Number of index scans initiated on this table.
`table.indexScanRowsFetchedPerSecon`	Number of live rows fetched by index scans.
`table.rowsInsertedPerSecond`	Rows inserted per second.
`table.rowsUpdatedPerSecond`	Rows updated per second.
`table.rowsDeletedPerSecond`	Rows deleted per second.
`table.bloatSizeInBytes`	Size of bloat in bytes.
`table.dataSizeInBytes`	Size of disk spaced used by the main fork of the table.
`table.bloatRatio`	Fraction of table data size that is bloat.

PgBouncerSample attributes	Description
`pgbouncer.stats.transactionsPerSecond`	The transaction rate.
`pgbouncer.stats.queriesPerSecond`	The query rate.
`pgbouncer.stats.bytesInPerSecond`	The total network traffic received.
`pgbouncer.stats.bytesOutPerSecond`	The total network traffic sent.
`pgbouncer.stats.totalTransactionDurationInMillisecondsPerSecond`	Time spent by `pgbouncer` in transaction.
`pgbouncer.stats.totalQueryDurationInMillisecondsPerSecond`	Time spent by `pgbouncer` actively querying PostgreSQL.
`pgbouncer.stats.avgTransactionCount`	The average number of transactions per second in last stat period.
`pgbouncer.stats.avgTransactionDurationInMilliseconds`	The average transaction duration.
`pgbouncer.stats.avgQueryCount`	The average number of queries per second in last stat period.
`pgbouncer.stats.avgBytesIn`	The client network traffic received.
`pgbouncer.stats.avgBytesOut`	The client network traffic sent.
`pgbouncer.stats.avgQueryDurationInMilliseconds`	The average query duration.
`pgbouncer.pools.clientConnectionsActive`	Client connections linked to server connection and able to process queries.
`pgbouncer.pools.clientConnectionsWaiting`	Client connections waiting on a server connection.
`pgbouncer.pools.clientConnectionsWaitingCancelReq`	Client connections that have not forwarded query cancellations to the server yet.
`pgbouncer.pools.clientConnectionsActiveCancelReq`	Client connections that have forwarded query cancellations to the server and are waiting for the server response.
`pgbouncer.pools.serverConnectionsActiveCancel`	Server connections that are currently forwarding a cancel request.
`pgbouncer.pools.serverConnectionsBeingCancel`	Servers that normally could become idle but are waiting to do so until all in-flight cancel requests have completed that were sent to cancel a query on this server.
`pgbouncer.pools.serverConnectionsActive`	Server connections linked to a client connection.
`pgbouncer.pools.serverConnectionsIdle`	Server connections idle and ready for a client query.
`pgbouncer.pools.serverConnectionsUsed`	Server connections idle more than `server_check_delay`, needing `server_check_query`.
`pgbouncer.pools.serverConnectionsTested`	Server connections currently running either `server_reset_query` or `server_check_query`.
`pgbouncer.pools.serverConnectionsLogin`	Server connections currently in the process of logging in.
`pgbouncer.pools.maxwaitInMilliseconds`	Age of oldest unserved client connection.

Metrics collected by the query monitoring

The PostgreSQL query monitoring integration collects the following metrics

PostgresSlowQueries attributes	Description
`query_id`	The unique identifier for each query.
`query_text`	The SQL statement that was run.
`database_name`	The name of the database where the query is run.
`schema_name`	The schema involved in the query.
`execution_count`	The number of times the query is run.
`avg_elapsed_time_ms`	The average time to run in milliseconds.
`avg_disk_reads`	The average number of disk reads performed by the query.
`avg_disk_writes`	The average number of disk writes performed by the query.
`statement_type`	The type of SQL statement (e.g., SELECT, INSERT).
`collection_timestamp`	The timestamp that indicates when the data is collected.

PostgresWaitEvents attributes	Description
`wait_event_name`	The name of the wait event encountered.
`wait_category`	The category of the wait event.
`total_wait_time_ms`	The total time spent on the wait event, in milliseconds.
`collection_timestamp`	The timestamp when the data was collected.
`query_id`	The unique identifier for each query.
`query_text`	The SQL statement that was run.
`database_name`	The name of the database where the query ran.

PostgresBlockingSessions attributes	Description
`blocked_pid`	The Process ID of the blocked query.
`blocked_query`	The SQL statement run in the blocked process.
`blocked_query_id`	The unique identifier for the blocked query.
`blocked_query_start`	The time at which the blocked query is started.
`database_name`	The name of the database in which blocking occurs.
`blocking_query`	The SQL statement causing the block.
`blocking_pid`	The process ID of the query causing the block.
`blocking_query_id`	The unique identifier of the query causing the block.
`blocking_query_start`	The time at which blocking query was executed.

PostgresIndividualQueries attributes	Description
`query_id`	The unique identifier for a query.
`query_text`	The SQL statement that was run.
`database_name`	The name of the database where the query was run.
`cpu_time_ms`	The CPU time used by the query, in milliseconds.
`exec_time_ms`	The execution time in milliseconds.
`plan_id`	The query identifier for the plan.

PostgresExecutionPlanMetrics attributes	Description
`node_type`	The type of operation performed at a node in the plan.
`parallel_aware`	Indicates if the node supports parallel operation.
`async_capable`	Indicates if the node can perform asynchronous operations.
`scan_direction`	The direction of scanning such as, forward or backward.
`index_name`	The name of the index used in the plan, if applicable.
`relation_name`	The name of the table or relation involved in the operation.
`alias`	The alias used in the query for the relation.
`startup_cost`	The estimated cost to start the node operation.
`total_cost`	The estimated total cost to complete the node operation.
`plan_rows`	The estimated number of rows this plan node returns.
`plan_width`	The estimated average width of rows this node output in bytes.
`rows_removed_by_filter`	The number of rows removed from this node by filters.
`database_name`	The name of the database where the query runs.
`query_id`	The unique identifier for the particular query operation.
`plan_id`	The identifier for this specific plan.
`level_id`	The depth or level of the node in the plan hierarchy.

PostgreSQL monitoring integration

Preview

Important

PostgreSQL users and permissions

The `postgresql-config.yml` file

PostgreSQL configuration collection file

PostgreSQL Azure Flexible and AWS RDS/Aurora configuration file

PostgreSQL SSL configuration collection file

PostgreSQL custom query

PostgreSQL custom query config file

Metrics collected by the integration

PostgresqlDatabaseSample metrics

PostgresqlIndexSample metrics

PostgresqlInstanceSample metrics

PostgresqlTableSample metrics

PgBouncerSample metrics

Metrics collected by the query monitoring

PostgresSlowQueries metrics

PostgresWaitEvents metrics

PostgresBlockingSessions metrics

PostgresIndividualQueries metrics

PostgresExecutionPlanMetrics metrics

PostgreSQL monitoring integration

Preview

Important

PostgreSQL users and permissions .css-21sua1{background:none;border:none;width:0;padding:0;}

The postgresql-config.yml file

PostgreSQL Azure Flexible and AWS RDS/Aurora configuration file

PostgreSQL SSL configuration collection file

PostgreSQL custom query

PostgreSQL custom query config file

Metrics collected by the integration

PostgresqlDatabaseSample metrics

PostgresqlIndexSample metrics

PostgresqlInstanceSample metrics

PostgresqlTableSample metrics

PgBouncerSample metrics

Metrics collected by the query monitoring

PostgresSlowQueries metrics

PostgresWaitEvents metrics

PostgresBlockingSessions metrics

PostgresIndividualQueries metrics

PostgresExecutionPlanMetrics metrics

PostgreSQL users and permissions

The `postgresql-config.yml` file