New Relic Monitoring Pgbouncer + PostgreSQL

Issue:

How to monitor pgbouncer PostgreSQL with New Relic on host integration.

Short introduction:

When monitoring the PostgreSQL database using New Relic on host integration it is supported to also monitor the Pgbouncer connection pool, in this post I will show the basic configuration to make it work. Please see the following link for PgBouncerSample metrics to see the metrics you can find when using New Relic to monitor Pgbouncer.

Requirements:

  • Install the Infrastructure Agent. Please see the following steps to Install the infrastructure agent.
  • Have the PostgreSQL working. Please see the following to have more details about New Relic PostgreSQL on-host integration

Resources used in this example:

  • Ubuntu - 18.04.
  • PostgreSQL 14.0.
  • PgBouncer 1.16.0.

Resolution

Once you have PostgreSQL metrics reporting to New Relic and have the Pgbouncer installed (make sure Pgbouncer is installed on the same server as PostgreSQL), please follow the steps below for the configuration for Pgbouncer + New Relic:
There are the 3 main configuration files that will need to be used to correctly configure it in this use case:

A - postgresql-config.yml
B - userlist.txt
C - pgbouncer.ini

  • postgresql-config.yml

With PostgreSQL on-host integration working you would have a default postgresql-config.yml file as below. It can be checked with the command below:

sudo cat /etc/newrelic-infra/integrations.d/postgresql-config.yml

  - name: nri-postgresql
    env:
      HOSTNAME: localhost
      PORT: 5432
      USERNAME: newrelic
      DATABASE: postgres
      PASSWORD: xxxxxxx
      COLLECTION_LIST: 'ALL'
      COLLECT_DB_LOCK_METRICS: false
      ENABLE_SSL: false
      timeout: 10
    inventory_source: config/postgresql
    interval: 15

To make sure New Relic PostgreSQL integration connect collect Pgbouncer metrics you need to configure the postgresql-config.yml with 2 main steps:

1- Make sure you add on the file the following Environment variables:

  • PGBOUNCER: true

More details about environment variables on the following link for Configuration structure.

2- Next that needs to be changed in the postgresql-config.yml is the Port number which by default for PostgreSQL is 5432, however, as now we will be connecting to Pgbouncer the port number by default is 6432.

Therefore, now we would have the postgresql-config.yml file looking like the example below:

  - name: nri-postgresql
    env:
      HOSTNAME: localhost
      PORT: 6432
      USERNAME: newrelic
      DATABASE: postgres
      PASSWORD: xxxxxxx
      PGBOUNCER: true
      COLLECTION_LIST: 'ALL'
      COLLECT_DB_LOCK_METRICS: false
      ENABLE_SSL: false
      timeout: 10
    inventory_source: config/postgresql
    interval: 15

At this point, no other changes in regards to New Relic will be needed.

  • userlist.txt

As Pgbouncer is a connection pool which you can find more details about it on the following link >> https://www.pgbouncer.org/ we need New Relic PostgreSQL integration to connect to it and then Pgbouncer will point to which PostgreSQL databases it needs to collect data from. Like that the integration will collect data from the Pgbouncer + PostgreSQL databases.

By default, Pgbouncer has the userlist.txt you can have more information about it on the link for Authentication file format. For this example it looks like the below:

~$ sudo cat /etc/pgbouncer/userlist.txt

"newrelic" "123"45678"

To avoid any authentication issue make sure you are able to connect to Pgbouncer using the same credentials, user, passwords as the user that will be on the postgresql-config.yml file:

psql (14.0 (Ubuntu 14.0-1.pgdg18.04+1))
Type "help" for help.

postgres=# 
  • pgbouncer.ini

Firstly, we will need to make sure that the pgbouncer.ini configuration file is correctly configured, please note that this post goal is not on troubleshooting Pgbouncer Authentication or any customization with it. Below is an example of a basic configuration that can be tested to make sure New Relic collects metrics from Pgbouncer:

You can get the Pgbouncer file like the below command:

~$ sudo vi /etc/pgbouncer/pgbouncer.ini

The Pgbouncer file has many options of configuration here will show examples of the main user on this example:

Here is where you will add which PostgreSQL databases you wish to Pgbouncer access that will be the PostgreSQL databases that New Relic will be collecting metrics from. Metrics that can be found in New Relic on PostgresqlDatabaseSample. In the example below we have two PostgreSQL databases postgres and mydb. Both are connected by the same user ‘postgres’ which is a superuser to make sure we have all permissions and avoid any permissions issue. The recommendation for a production environment is to create a user for it making your environment secure.

[databases]

postgres = host = localhost port = 5432 user = postgres password = 12345678
mydb = host = localhost port = 5432 user = postgres password = 12345678 

Please for more details on the Pgbouncer parameters from pgbouncer feel free to review the documentation - pgbouncer.ini

Once the pgbouncer.ini has been configured please make sure you restart Pgbouncer:

sudo service pgbouncer restart

New Relic ONE

You can check if Pgbouncer metrics are collecting using the below:

NRQL:

SELECT * from PgBouncerSample

New Relic ONE > Browser data > Metrics > In the search box type pgbouncer > select the metric you wish:

For PgbouncerSample metrics please see the link for PgBouncerSample metrics

I hope it helps set up the Pgbouncer + PostgreSQL metrics to be collected within New Relic ONE. Please feel free to add any comments or questions.

4 Likes