Install & configure NRDOT for PostgreSQL monitoring with AWS RDS

Prerequisites

Before you install, make sure you have:

• PostgreSQL 14 and above
• A New Relic license key.
• Network connectivity between the host where you install the NRDOT Collector and your RDS PostgreSQL instance.
• Network connectivity to New Relic OTLP endpoints documentation
• RDS master user credentials.

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

Set up NRDOT Collector

Install the NRDOT Collector on a system that can connect to your RDS instance:

Configure database user and grants

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

To create the monitoring user and grants:

1. Connect to your RDS instance as the master user:

   bash

   Copiar

   $
   psql --host=<YOUR_RDS_ENDPOINT> --dbname=postgres --port=5432 --username=<YOUR_MASTER_USERNAME>

2. Create the monitoring user:

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

   Copiar

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;

  Copiar

  Dica

  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;

  Copiar

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

  CREATE EXTENSION IF NOT EXISTS vector;

  Copiar

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

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

  bash

  Copiar

  $
  psql --username=<YOUR_DB_USERNAME> --host=<YOUR_RDS_ENDPOINT> --port=5432 --dbname=<YOUR_DATABASE_NAME> --command '\conninfo'
  $
  psql --username=<YOUR_DB_USERNAME> --host=<YOUR_RDS_ENDPOINT> --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=<YOUR_RDS_ENDPOINT> --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=<YOUR_RDS_ENDPOINT> --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 RDS 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

   Copiar

   $
   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 RDS Endpoint

   Enter your PostgreSQL RDS endpoint

   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

   DownloadCopiar

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

   Copiar

   $
   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

   Copiar

   $
   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 RDS 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 RDS PostgreSQL database from the list of entities.