PostgreSQL monitoring integration

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.

Choose your framework

Choose your install path

Check the compatibility and requirements

  • Our integration is compatible with PostgreSQL version 15.6 and lower.

  • A New Relic account. Don't have one? Sign up for free! No credit card required.

Install the infrastructure agent

To use the PostgreSQL integration, you need to also install the infrastructure agent on the same host. The infrastructure agent monitors the host itself, while the integration you'll install in the next step extends your monitoring with PostgreSQL-specific data such as database and instance metrics.

Download using APT

In your terminal, run these commands:

bash
$
sudo apt-get update
bash
$
sudo apt-get install nri-postgresql

Configure the PostgreSQL integration

  1. Change directory to the integrations configuration folder by running:

    bash
    $
    cd /etc/newrelic-infra/integrations.d
  2. Copy the sample configuration file by running:

    bash
    $
    sudo cp postgresql-config.yml.sample postgresql-config.yml
  3. Edit the postgresql-config.yml file. This example config file collects all metrics:

    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'
    TIMEOUT: 10
    interval: 15s
    labels:
    env: production
    role: postgresql
    inventory_source: config/postgresql

Create a user

Make sure you have a user with the required permissions or create one with SELECT permissions on:

  • pg_stat_database
  • pg_stat_database_conflicts
  • pg_stat_bgwriter

To create the user for the PostgreSQL integration, run these commands:

CREATE USER new_relic WITH PASSWORD MY_PASSWORD;
GRANT SELECT ON pg_stat_database TO new_relic;
GRANT SELECT ON pg_stat_database_conflicts TO new_relic;
GRANT SELECT ON pg_stat_bgwriter TO new_relic;

This will allow the integration to gather global metrics related to the PostgreSQL instance.

Find and use data

  1. Go to one.newrelic.com > Integrations & Agents and type PostgreSQL.

  2. Under Dashboards, click PostgreSQL.

    Installing the PostgreSQL dashboard
  3. A popup window opens.

  4. Click Edit if you want to change the account.

  5. Click Setup PostgreSQL to setup the data source or click Skip this step if the data source is already setup.

  6. Click View dashboard to see your PostgreSQL data in New Relic.

For more on how to find and use your data, see how to understand integration data. You can find a full list of metrics and their attributes at the bottom of this doc.

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.

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