Install & configure NRDOT for PostgreSQL monitoring with Self-hosted

Prerequisites

Before you install, make sure you have:

• PostgreSQL 14 or later
• A New Relic license key.
• Administrative access to your PostgreSQL instance.
• Network connectivity between the host where you install the NRDOT Collector and your PostgreSQL database.
• Network connectivity to New Relic OTLP endpoints documentation

For supported PostgreSQL versions, required grants, and recommended server parameters, see Compatibility and prerequisites.

Set up NRDOT Collector

Install the NRDOT Collector on your system:

Configure database user and grants

Create a monitoring user with the necessary privileges for your PostgreSQL instance.

To create the monitoring user and grants:

1. Connect to your PostgreSQL instance as a superuser or another user with required permissions:

   bash

   コピー

   $
   sudo -u postgres psql --dbname=postgres --port=5432

2. Create the monitoring user:

   CREATE USER <YOUR_DB_USERNAME> WITH LOGIN PASSWORD '<YOUR_DB_PASSWORD>';

   コピー

3. Grant the monitoring user role membership as needed:

• If you're on PostgreSQL 15 and above, assign role to inherit privileges:

  ALTER ROLE <YOUR_DB_USERNAME> INHERIT;

  コピー

  ヒント

  If you're on PostgreSQL 14, the monitoring user inherits privileges by default, so you can skip this step.

• Create the following schema and grants in every database you want to monitor:

  CREATE SCHEMA IF NOT EXISTS otel;
  GRANT USAGE ON SCHEMA otel TO <YOUR_DB_USERNAME>;
  GRANT USAGE ON SCHEMA public TO <YOUR_DB_USERNAME>;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO <YOUR_DB_USERNAME>;
  GRANT pg_monitor TO <YOUR_DB_USERNAME>;
  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  コピー

• (Optional) To collect vector metrics, create the pgvector extension in each database:

  CREATE EXTENSION IF NOT EXISTS vector;

  コピー

  The l1, hamming, and jaccard distance functions require pgvector version 0.7.0 or above.

• (Optional) To verify the connection and grants, run:

  bash

  コピー

  $
  psql --username=<YOUR_DB_USERNAME> --host=localhost --port=5432 --dbname=<YOUR_DATABASE_NAME> --command '\conninfo'
  $
  psql --username=<YOUR_DB_USERNAME> --host=localhost --port=5432 --dbname=<YOUR_DATABASE_NAME> \
  >
    -c "SELECT * FROM pg_stat_database LIMIT 1;" && echo "pg_stat_database OK"
  >
  psql --username=<YOUR_DB_USERNAME> --host=localhost --port=5432 --dbname=<YOUR_DATABASE_NAME> \
  >
    -c "SELECT * FROM pg_stat_activity LIMIT 1;" && echo "pg_stat_activity OK"
  >
  psql --username=<YOUR_DB_USERNAME> --host=localhost --port=5432 --dbname=<YOUR_DATABASE_NAME> \
  >
    -c "SELECT * FROM pg_stat_statements LIMIT 1;" && echo "pg_stat_statements OK"

  If each command completes without an error, the monitoring user, host, and port are all correct.

Configure NRDOT Collector

Configure the NRDOT Collector with your PostgreSQL-specific settings.

This configuration focuses on essential PostgreSQL monitoring with the nrpostgresql receiver only.

1. Create a configuration file named as postgresql-config.yaml:

   bash

   コピー

   $
   sudo nano /etc/nrdot-collector/postgresql-config.yaml

2. Add the following configuration to the postgresql-config.yaml file you created in the previous step.

   PostgreSQL Hostname/IP

   Enter your PostgreSQL hostname or IP address

   PostgreSQL Port

   Enter your PostgreSQL port number. The default value is 5432.

   Database Username

   Enter your database username for authentication

   Database Password

   Enter the password you set for the monitoring user

   Database Name

   Enter the name of the database you want to monitor

   New Relic OTLP Endpoint

   Enter your New Relic OTLP endpoint. For more information, refer to https://docs.newrelic.com/docs/opentelemetry/best-practices/opentelemetry-otlp/

   New Relic License Key

   Enter your New Relic license key

   postgresql-config.yaml

   Downloadコピー

   1
   receivers:
   2
       nrpostgresql:
   3
         endpoint: "postgresql.example.com:5432"
   4
         username: "newrelic"
   5
         password: "YOUR_PASSWORD"
   6
         databases:
   7
           - YOUR_DATABASE_NAME
   8
         collection_interval: 15s
   9
         events:
   10
           db.server.top_query:
   11
             enabled: true
   12
           db.server.query_sample:
   13
             enabled: true
   14
           db.server.query_plan:
   15
             enabled: true
   16
         top_query_collection:
   17
           max_rows_per_query: 1000
   18
           top_n_query: 200
   19
           collection_interval: 60s
   20
           allowed_comment_keys: [nr_service_guid]
   21
         query_sample_collection:
   22
           max_rows_per_query: 1000
   23
           allowed_comment_keys: [nr_service_guid]
   24
         resource_attributes:
   25
           db.system.version:
   26
             enabled: true
   27
         # Metrics needed for the out-of-the-box dashboard but disabled by default:
   28
         # see "Available metrics" below for the full default-on/default-off breakdown.
   29
         metrics:
   30
           postgresql.database.locks:
   31
             enabled: true
   32
           postgresql.deadlocks:
   33
             enabled: true
   34
           postgresql.function.calls:
   35
             enabled: true
   36
           postgresql.query.conflicts:
   37
             enabled: true
   38
           postgresql.sequential_scans:
   39
             enabled: true
   40
           postgresql.temp.io:
   41
             enabled: true
   42
           postgresql.temp_files:
   43
             enabled: true
   44
   processors:
   45
       batch:
   46
       batch/metrics:
   47
         send_batch_size: 8192
   48
         send_batch_max_size: 8192
   49
         timeout: 10s
   50
   exporters:
   51
       otlp/newrelic:
   52
         endpoint: "https://otlp.nr-data.net:4318"
   53
         headers:
   54
           api-key: "YOUR_LICENSE_KEY"
   55
         compression: gzip
   56
         sending_queue:
   57
           enabled: true
   58
           sizer: bytes
   59
           queue_size: 100_000_000
   60
           num_consumers: 10
   61
           batch:
   62
             sizer: bytes
   63
             max_size: 1_000_000
   64
             min_size: 0
   65
             flush_timeout: 5s
   66
         retry_on_failure:
   67
           enabled: true
   68
           initial_interval: 5s
   69
           max_interval: 30s
   70
           max_elapsed_time: 300s
   71
   service:
   72
       telemetry:
   73
         metrics:
   74
           level: none
   75
       pipelines:
   76
         metrics:
   77
           receivers: [nrpostgresql]
   78
           processors: [batch/metrics]
   79
           exporters: [otlp/newrelic]
   80
         logs:
   81
           receivers: [nrpostgresql]
   82
           processors: [batch]
   83
           exporters: [otlp/newrelic]

Configuration parameters

The following table describes the key configuration parameters for the nrpostgresql receiver:

Validate NRDOT Collector configuration

1. Update the config path to point to your new postgresql-config.yaml file:

   bash

   コピー

   $
   sudo sed -i 's|OTELCOL_OPTIONS="--config=/etc/nrdot-collector/config.yaml"|OTELCOL_OPTIONS="--config=/etc/nrdot-collector/postgresql-config.yaml"|' /etc/nrdot-collector/nrdot-collector.conf

2. Validate the NRDOT Collector configuration to ensure it's correctly formatted and will work properly:

   bash

   コピー

   $
   sudo /usr/bin/nrdot-collector validate --config=/etc/nrdot-collector/postgresql-config.yaml

Restart NRDOT Collector

After configuring the collector, restart the NRDOT Collector service to apply the changes:

$
sudo systemctl restart nrdot-collector

To verify that the collector is running properly, check the service status:

$
sudo systemctl status nrdot-collector

Find and use your data

Query samples and top queries arrive as log events (event.name = 'db.server.query_sample' / 'db.server.top_query', db.system.name = 'postgresql'). Metrics arrive under the postgresql.* namespace.

To find your PostgreSQL database entity in New Relic:

1. Go to https://one.newrelic.com > All Capabilities > Databases.
2. From the Entity type dropdown, select PostgreSQL instance, then click Apply.
3. Select your PostgreSQL database from the list of entities.