PostgreSQL monitoring integration
The New Relic PostgreSQL on-host integration receives and sends inventory metrics from your PostgreSQL instance to the New Relic platform, where you can aggregate and visualize key performance metrics. Data from instances, databases, and clusters helps you find the source of problems.
Important
For best results, regularly update the integration package and the infrastructure agent.
Choose your framework
Choose your install path
Check the compatibility and requirements
Our integration is compatible with PostgreSQL version 15.6 and lower.
A New Relic account. Don't have one? Sign up for free! No credit card required.
Install the infrastructure agent
To use the PostgreSQL integration, you need to also install the infrastructure agent on the same host. The infrastructure agent monitors the host itself, while the integration you'll install in the next step extends your monitoring with PostgreSQL-specific data such as database and instance metrics.
Download using APT
In your terminal, run these commands:
$sudo apt-get update
$sudo apt-get install nri-postgresql
Configure the PostgreSQL integration
Change directory to the integrations configuration folder by running:
bash$cd /etc/newrelic-infra/integrations.dCopy the sample configuration file by running:
bash$sudo cp postgresql-config.yml.sample postgresql-config.ymlEdit the
postgresql-config.yml
file. This example config file collects all metrics:integrations:- name: nri-postgresqlenv:USERNAME: postgresPASSWORD: passHOSTNAME: psql-sample.localnetPORT: 6432DATABASE: postgresCOLLECT_DB_LOCK_METRICS: falseCOLLECTION_LIST: 'ALL'TIMEOUT: 10interval: 15slabels:env: productionrole: postgresqlinventory_source: config/postgresql
Create a user
Make sure you have a user with the required permissions or create one with SELECT
permissions on:
pg_stat_database
pg_stat_database_conflicts
pg_stat_bgwriter
To create the user for the PostgreSQL integration, run these commands:
CREATE USER new_relic WITH PASSWORD MY_PASSWORD;GRANT SELECT ON pg_stat_database TO new_relic;GRANT SELECT ON pg_stat_database_conflicts TO new_relic;GRANT SELECT ON pg_stat_bgwriter TO new_relic;
This will allow the integration to gather global metrics related to the PostgreSQL instance.
Find and use data
Go to one.newrelic.com > Integrations & Agents and type PostgreSQL.
Under Dashboards, click PostgreSQL.
A popup window opens.
Click Edit if you want to change the account.
Click Setup PostgreSQL to setup the data source or click Skip this step if the data source is already setup.
Click View dashboard to see your PostgreSQL data in New Relic.
For more on how to find and use your data, see how to understand integration data. You can find a full list of metrics and their attributes at the bottom of this doc.
PostgreSQL users and permissions
If you also want to obtain table and index-related metrics (for example, table size and index size), the PostgreSQL role used by the integration (new_relic
) also needs SELECT
permissions on the tables from which it will gather metrics from. For example, to allow the integration to collect metrics from all the tables and indexes present in the database (in the public schema
), use the following:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO new_relic;
If you also want to obtain query-level metrics from the PostgreSQL custom query config file, the PostgreSQL role used by the integration (new_relic
) needs to be added to the (pg_read_all_stats
) role. This is due to the user leveraging the (pg_stat_statements
) extension.
GRANT pg_read_all_stats TO new_relic;
Enabling the pg_stat_statements
extension may require you to manually create it from a query prompt:
CREATE EXTENSION pg_stat_statements;
The postgresql-config.yml
file
The postgresql-config.yml
file is the integration's YAML-format configuration where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference. The configuration file has common settings applicable to all integrations, such as interval
, timeout
, inventory_source
.
Specific settings related to PostgreSQL are defined using the env section of the postgresql-config.yml
file. These settings control the connection to your PostgreSQL instance as well as other security settings and features.
See these more complex config examples.
For more about the general structure of on-host integration configuration, see On-host integration configuration overview.
Metrics collected by the integration
The PostgreSQL integration collects the following metrics. Some metric names are prefixed with a category indicator and a period, such as db.
or index.
.