Install & configure NRDOT for PostgreSQL monitoring with Ansible

You can install and configure the NRDOT Collector for PostgreSQL monitoring using the newrelic.newrelic_install Ansible role. The role installs the collector, creates the monitoring role and its grants, and configures the collector in a single play.

Prerequisites

A New Relic account with a valid license key.
A New Relic account ID.
PostgreSQL 14 or later.
Ansible Core 2.13 or 2.14, Python 3.10, and the ansible.windows and ansible.utils collections.
A Linux collector host running Debian/Ubuntu or RHEL/CentOS.
pg_stat_statements must be loaded via shared_preload_libraries, which requires a database restart. Put the server-level prerequisites in place before running the playbook.
For self-hosted PostgreSQL: administrative access to your PostgreSQL server (the postgres superuser or equivalent), with a password set and pg_hba.conf permitting password-based host authentication.
For PostgreSQL or Aurora on AWS RDS: network connectivity from the collector host to the RDS endpoint, and the RDS master username and password.
Network connectivity to New Relic OTLP endpoint for your region.

For detailed requirements, refer to Compatibility.

Install the role

bash

$ansible-galaxy install newrelic.newrelic_install

Make sure the required collections are also installed:

bash

$ansible-galaxy collection install ansible.windows ansible.utils

Configure the playbook

This role monitors one or more PostgreSQL instances from a single collector. Instead of a single host/port/credential set, it reads two files that must already exist on the target host: an instances file (YAML) listing every instance to monitor and the databases to monitor on each, and a secrets file (KEY=VALUE) with a superuser login and password for each, indexed to match. See the CLI install page for the exact file formats.

Create a file named playbook.yml and copy the following content into it. Set targets to postgresql-otel and replace the placeholder values with your own:

- name: Install New Relic NRDOT for PostgreSQL
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - postgresql-otel
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_POSTGRES_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_POSTGRES_INSTANCES_FILE: <path to the instances YAML file on the target host>
    NR_CLI_POSTGRES_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>
    NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER: <y or N, default N; PREVIEW feature, see table below>

重要

The role connects to PostgreSQL over TCP using a password, so each instance's superuser must have a password set and pg_hba.conf must permit password-based host authentication (md5 or scram-sha-256) for TCP connections:

bash

$sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '<password>';"

On RHEL/CentOS, the default pg_hba.conf often doesn't permit password-based host authentication at all. Verify and update it, then reload PostgreSQL before running the playbook.

ヒント

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable	Description	Default
`NR_CLI_POSTGRES_CONFIG_PRESET`	NRDOT configuration: `1` Basic, `2` Advanced.	`1`
`NR_CLI_POSTGRES_INSTANCES_FILE`	Required. Path to the instances YAML file, already present on the target host.	None
`NR_CLI_POSTGRES_SECRETS_FILE`	Required. Path to the secrets file (`KEY=VALUE`, one superuser login/password pair per instance), already present on the target host.	None
`NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER`	Collect query plans for locking/write statements too. Creates a `SECURITY DEFINER` helper function (`otel.explain_statement`) in each monitored database, granting the monitoring user `EXPLAIN` execution without granting DML access. This is a PREVIEW feature. `y`/`N`.	`n`

ヒント

To monitor more than one PostgreSQL instance from this collector, add more entries to the instances file and matching numbered credentials (_2, _3, ...) to the secrets file. An instance that fails its checks (or has no databases listed) is skipped with a logged reason rather than aborting the whole install.

This role monitors one or more RDS PostgreSQL or Aurora endpoints from a single collector, using the same instances file / secrets file pattern as self-hosted (both files must already exist on the target host). Create a file named playbook.yml and copy the following content into it. Set targets to postgresql-otel-rds and replace the placeholder values with your own:

- name: Install New Relic NRDOT for PostgreSQL on RDS
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - postgresql-otel-rds
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_POSTGRES_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_POSTGRES_INSTANCES_FILE: <path to the instances YAML file on the target host, using RDS or Aurora endpoints as host>
    NR_CLI_POSTGRES_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>
    NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER: <y or N, default N; PREVIEW feature, see table below>

ヒント

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable	Description	Default
`NR_CLI_POSTGRES_CONFIG_PRESET`	NRDOT configuration: `1` Basic, `2` Advanced.	`1`
`NR_CLI_POSTGRES_INSTANCES_FILE`	Required. Path to the instances YAML file, using RDS or Aurora endpoints as `host`, already present on the target host.	None
`NR_CLI_POSTGRES_SECRETS_FILE`	Required. Path to the secrets file (`KEY=VALUE`, one RDS master username/password pair per instance), already present on the target host.	None
`NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER`	Collect query plans for locking/write statements too. Creates a `SECURITY DEFINER` helper function (`otel.explain_statement`) in each monitored database, granting the monitoring user `EXPLAIN` execution without granting DML access. This is a PREVIEW feature. `y`/`N`.	`n`

ヒント

To monitor more than one RDS PostgreSQL or Aurora endpoint from this collector, add more entries to the instances file and matching numbered credentials to the secrets file. An instance that fails its checks (or has no databases listed) is skipped with a logged reason rather than aborting the whole install.

Run the playbook

bash

$ansible-playbook -i <inventory_file> <playbook_file>.yml

Find and use your data

Once your data is being collected, you can access comprehensive PostgreSQL database monitoring through the New Relic UI.

To find your PostgreSQL database entity in New Relic:

Go to one.newrelic.com > All capabilities > Databases.
From the Entity type dropdown, select PostgreSQL instance, then click Apply.
Select your PostgreSQL database from the list of entities.

Introduction to PostgreSQL monitoring with NRDOT

Learn about all the available installation methods for PostgreSQL monitoring with New Relic.

CLI install

Learn how to install and configure PostgreSQL monitoring with a single New Relic CLI command.

Chef install

Learn how to install and configure PostgreSQL monitoring at scale with the newrelic-install Chef cookbook.

Helm chart install

Learn how to install and configure PostgreSQL monitoring on Kubernetes with the postgresql-otel Helm chart.

Install & configure NRDOT for PostgreSQL monitoring with Ansible

Prerequisites .css-21sua1{background:none;border:none;width:0;padding:0;}

Install the role

Configure the playbook

PostgreSQL or Aurora on AWS RDS

Run the playbook

Find and use your data

Related documentation

Introduction to PostgreSQL monitoring with NRDOT

CLI install

Chef install

Helm chart install

Prerequisites