--- title: Install & configure NRDOT for PostgreSQL monitoring with Chef source: https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/chef --- You can install and configure the NRDOT Collector for PostgreSQL monitoring using the `newrelic-install` Chef cookbook. The cookbook installs the collector, creates the monitoring role and its grants, and configures the collector when you run the `newrelic-install::default` recipe. ## Prerequisites [#prerequisites] - A New Relic account with a valid [license key](https://docs.newrelic.com/docs/apis/intro-apis/new-relic-api-keys/#overview-keys). - A New Relic [account ID](https://docs.newrelic.com/docs/accounts/accounts-billing/account-structure/account-id). - PostgreSQL 14 or later. - Chef 15 or later. - 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](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/cli/#server-prereqs) in place before running the recipe. - 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](https://docs.newrelic.com/docs/opentelemetry/best-practices/opentelemetry-otlp) for your region. For detailed requirements, refer to [Compatibility](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/compatibility). ## Download the Chef cookbook [#chef-download-cookbook] Download the `newrelic-install` [Chef cookbook](https://supermarket.chef.io/cookbooks/newrelic-install) from the Chef Supermarket to your chef-repo directory: ```bash knife supermarket install newrelic-install ``` ## Replace the default attributes [#chef-configure] Replace the default attributes in `attributes/default.rb` with your account details: **Self-hosted PostgreSQL** This cookbook 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 node: 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](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/cli/#cli-install) page for the exact file formats. ```ruby default['newrelic_install']['NEW_RELIC_API_KEY'] = default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = default['newrelic_install']['NEW_RELIC_REGION'] = default['newrelic_install']['targets'] = [ 'nrdot-collector-postgresql' ] default['newrelic_install']['env']['NR_CLI_POSTGRES_CONFIG_PRESET'] = <1 Basic, 2 Advanced, default 1> default['newrelic_install']['env']['NR_CLI_POSTGRES_INSTANCES_FILE'] = default['newrelic_install']['env']['NR_CLI_POSTGRES_SECRETS_FILE'] = default['newrelic_install']['env']['NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER'] = # See all available targets at: https://github.com/newrelic/chef-install ``` > #### ⚠️ IMPORTANT > > The cookbook 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 '';" > ``` | Attribute (under `env`) | 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 node. | 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 node. | 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 > > No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via `default['newrelic_install']['env'][...]`. > #### 💡 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. **PostgreSQL or Aurora on AWS RDS** This cookbook 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 node). ```ruby default['newrelic_install']['NEW_RELIC_API_KEY'] = default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = default['newrelic_install']['NEW_RELIC_REGION'] = default['newrelic_install']['targets'] = [ 'nrdot-collector-postgresql-rds' ] default['newrelic_install']['env']['NR_CLI_POSTGRES_CONFIG_PRESET'] = <1 Basic, 2 Advanced, default 1> default['newrelic_install']['env']['NR_CLI_POSTGRES_INSTANCES_FILE'] = default['newrelic_install']['env']['NR_CLI_POSTGRES_SECRETS_FILE'] = default['newrelic_install']['env']['NR_CLI_POSTGRES_ENABLE_EXPLAIN_HELPER'] = # See all available targets at: https://github.com/newrelic/chef-install ``` | Attribute (under `env`) | 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 node. | 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 node. | 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 > > No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via `default['newrelic_install']['env'][...]`. > #### 💡 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. ## Upload the Chef cookbook [#chef-upload-cookbook] Upload the `newrelic-install` Chef cookbook to your Chef server: ```bash knife cookbook upload newrelic-install ``` ## Update the run list [#chef-run-list] Add the `newrelic-install` recipe to the run list of a node: ```json "run_list": [ "recipe[newrelic-install]" ] ``` ## Find and use your data [#find] 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: 1. Go to **[one.newrelic.com](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. ## Related documentation [#related] [Introduction to PostgreSQL monitoring with NRDOT](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/introduction) Learn about all the available installation methods for PostgreSQL monitoring with New Relic. [CLI install](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/cli) Learn how to install and configure PostgreSQL monitoring with a single New Relic CLI command. [Ansible install](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/ansible) Learn how to install and configure PostgreSQL monitoring at scale with the newrelic.newrelic_install Ansible role. [Helm chart install](https://docs.newrelic.com/docs/opentelemetry/db360/postgresql/helm) Learn how to install and configure PostgreSQL monitoring on Kubernetes with the postgresql-otel Helm chart.