Install & configure NRDOT for PostgreSQL monitoring with CLI

You can install and configure the NRDOT Collector for PostgreSQL monitoring using the New Relic CLI. The CLI installs the collector, creates the monitoring role and its grants, and configures the collector in a single command.

Prerequisites

A New Relic account with a valid license key.
A New Relic account ID.
PostgreSQL 14 or later.
A Linux collector host running Debian/Ubuntu or RHEL/CentOS, with curl and systemd installed, and the psql client available.
pg_stat_statements must be loaded via shared_preload_libraries. The installer checks this and fails if it isn't. See Server-level prerequisites below.
For self-hosted PostgreSQL: administrative access to your PostgreSQL server (the postgres superuser or equivalent).
For AWS RDS/Aurora PostgreSQL: network connectivity from the collector host to the RDS endpoint (inbound access on the PostgreSQL port in the RDS security group), and the RDS master username and password.
Network connectivity to New Relic OTLP endpoint for your region.

For detailed requirements, refer to Compatibility.

Server-level prerequisites

pg_stat_statements can't be created without shared_preload_libraries, and that parameter requires a database restart, so the CLI can't configure it for you. Put these parameters in place before running the install:

Parameter	Value
`shared_preload_libraries`	`pg_stat_statements`
`pg_stat_statements.track`	`ALL`
`pg_stat_statements.max`	`10000`
`pg_stat_statements.save`	`on`
`track_activity_query_size`	`4096`
`track_functions`	`all`

Self-hosted: set these in postgresql.conf, then restart PostgreSQL. Some distributions need the postgresql-contrib package installed for pg_stat_statements to be available.
AWS RDS or Aurora: set these in the DB parameter group, not a config file. track_activity_query_size and pg_stat_statements.max are static parameters: apply them with apply_method: pending-reboot, then reboot the instance. For Aurora, the parameter-group family name must match your engine's major version exactly, for example aurora-postgresql16.

Install and configure the NRDOT Collector

This recipe monitors one or more PostgreSQL instances from a single collector. Instead of prompting for a single host/port/credential set, it reads two files you create ahead of time: 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.

Create the instances file. Add one entry per PostgreSQL instance you want this collector to monitor; still just one entry if you're only running a single instance. Each instance requires at least one database: PostgreSQL has no monitor-all-databases mode:
bash
```
$cat > ~/postgresql-instances.yml << 'EOF'
$instances:
$  - host: localhost
$    port: 5432
$    login_name: newrelic
$    databases: [app1, app2]
$EOF
```

Create the secrets file with a superuser role (any superuser role works; it doesn't have to be postgres) and password for each instance, indexed to match the instances file's order:

bash

$umask 077
$cat > ~/postgresql-secrets.env << 'EOF'
$NR_CLI_POSTGRES_SUPERUSER_USER_1=postgres
$NR_CLI_POSTGRES_SUPERUSER_PASSWORD_1=YourSuperuserPassword1
$EOF
$chmod 600 ~/postgresql-secrets.env

bash

$curl -Ls https://download.newrelic.com/install/newrelic-cli/scripts/install.sh | bash && sudo NEW_RELIC_API_KEY=INSERT_YOUR_API_KEY NEW_RELIC_ACCOUNT_ID=INSERT_YOUR_ACCOUNT_ID NEW_RELIC_REGION=INSERT_YOUR_REGION /usr/local/bin/newrelic install -n nrdot-collector-postgresql

Important

This installer 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. A fresh install has no password set on postgres by default. Set one first:

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

The CLI prompts interactively for the following variables. You can also set them ahead of time as environment variables to run non-interactively.

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.	None
`NR_CLI_POSTGRES_SECRETS_FILE`	Required. Path to the secrets file (`KEY=VALUE`, one superuser login/password pair per instance).	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`

Tip

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; the install only fails outright if none of the instances pass.

This recipe monitors one or more RDS PostgreSQL or Aurora endpoints from a single collector, using the same instances file / secrets file pattern as self-hosted.

Create the instances file, using the RDS or Aurora endpoint as host; still just one entry if you're only monitoring a single instance. Each instance requires at least one database: PostgreSQL has no monitor-all-databases mode:
bash
```
$cat > ~/postgresql-instances.yml << 'EOF'
$instances:
$  - host: mydb1.abcdefg12345.us-east-1.rds.amazonaws.com
$    port: 5432
$    login_name: newrelic
$    databases: [app1, app2]
$EOF
```

Create the secrets file with the RDS master username and password for each instance, indexed to match the instances file's order:

bash

$umask 077
$cat > ~/postgresql-secrets.env << 'EOF'
$NR_CLI_POSTGRES_ADMIN_USER_1=postgres
$NR_CLI_POSTGRES_ADMIN_PASSWORD_1=YourMasterPassword1
$EOF
$chmod 600 ~/postgresql-secrets.env

bash

$curl -Ls https://download.newrelic.com/install/newrelic-cli/scripts/install.sh | bash && sudo NEW_RELIC_API_KEY=INSERT_YOUR_API_KEY NEW_RELIC_ACCOUNT_ID=INSERT_YOUR_ACCOUNT_ID NEW_RELIC_REGION=INSERT_YOUR_REGION /usr/local/bin/newrelic install -n nrdot-collector-postgresql-rds

The CLI prompts interactively for the following variables. You can also set them ahead of time as environment variables to run non-interactively.

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`.	None
`NR_CLI_POSTGRES_SECRETS_FILE`	Required. Path to the secrets file (`KEY=VALUE`, one RDS master username/password pair per instance).	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`

Tip

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.

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.

To confirm data is arriving, run this query in the query builder:

SELECT count(*) FROM Metric
WHERE metricName LIKE 'postgresql.%'
AND instrumentation.provider = 'opentelemetry'
SINCE 10 minutes ago

Introduction to PostgreSQL monitoring with NRDOT

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

Ansible install

Learn how to install and configure PostgreSQL monitoring at scale with the newrelic.newrelic_install Ansible role.

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.

Docker install

Learn how to run the NRDOT Collector as a sibling container to a PostgreSQL instance already running in Docker.

Install & configure NRDOT for PostgreSQL monitoring with CLI

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

Server-level prerequisites

Install and configure the NRDOT Collector

AWS RDS/Aurora PostgreSQL

Find and use your data

Related documentation

Introduction to PostgreSQL monitoring with NRDOT

Ansible install

Chef install

Helm chart install

Docker install

Prerequisites