cancel
Showing results for 
Search instead for 
Did you mean: 

How to use Prometheus/Grafana for EDB Cloud Database Services(CDS) Monitoring?

In my previous blog, we talked about Enterprise monitoring solution by Postgres Enterprise Manager(PEM) and how to use performance diagnostics to discover queries which are consuming high IO. Today we will learn how we can use Prometheus and Grafana for database monitoring in EDB CDS.

 

Prometheus is an open-source system monitoring and alerting toolkit which is based on a Server and agent mechanism as an overall solution. However, it doesn't have particularly strong graphical GUI capabilities. This is where Grafana comes into the picture for displaying different types of metrics using Prometheus as a data source.

 

Prometheus provides two deployment options for monitoring the system (server or database). The first option requires an agent installation on a monitored server, while the second option uses a remote agent. (Note: It will not allow monitoring of the OS). In this use case, we opted to use a remote agent as it’s easier to deploy and the CDS team doesn’t have to make the Prometheus exporter part of their standard setup.

 

Prerequisite:

  1. A CDS cluster running with a master and slave.
  2. Prometheus and Grafana installed and pre-configured on EC2 or on premises.

Registering Master Database with Prometheus Postgres Node Exporter

 

To begin:

  1. Login to the Prometheus server as the root user
  2. Build the Binary (from Github)

$ go get github.com/wrouesnel/postgres_exporter

$ cd ${GOPATH-$HOME/go}/src/github.com/wrouesnel/postgres_exporter

$ go run mage.go binary

 

Make sure that IP whitelisting has been done for the IP and port for the EDB CDS cluster.

 

Export the parameters DATA_SOURCE_NAME in below format

$ export DATA_SOURCE_NAME="postgresql://<Username>:<password>@<IP>:<Port>/<database>"

 

Note: If you have installed EDB Postgres, then DATA_SOURCE_NAME will look like the example below.

 

$ export DATA_SOURCE_NAME="postgresql://enterprisedb:postgres@ec2-3-211-84-94.compute-1.amazonaws.com:5444/edb"

 

Running Postgres exporter binary to remote monitor the CDS cluster database.

$nohup ./postgres_exporter --web.listen-address=":9187" &

 

Port 9187 is the default port for the Postgres exporter process. Since we are doing remote monitoring and one Postgres exporter can only be bound to one database, we will have to use a non-default port to enable Postgres exporter monitoring for another database.

 

Registering Master Database with Prometheus Postgres Node Exporter

 

Next:

  1. Login to the Prometheus server as the root user
  2. Since the binary has already been built in earlier steps, we don’t have to build it again.
  3. Make sure that IP whitelisting has been done for both the CDS cluster’s IP and port.
  4. Bind the Postgres exporter with the slave database.

Export the parameters DATA_SOURCE_NAME in below format

$ export DATA_SOURCE_NAME="postgresql://<Username>:<password>@<IP>:<Port>/<database>"

 

Note: if you have installed EDB Postgres, then DATA_SOURCE_NAME will look like below

$ export DATA_SOURCE_NAME="postgresql://enterprisedb:postgres@ec2-3-211-84-110.compute-1.amazonaws.com:5444/edb"

 

Running postgres exporter binary allows you the ability to remotely monitor the CDS cluster database (slave). Please make sure that we have to use a non-default port: 9188 (in my example) so the process can listen on a different port number.

$nohup ./postgres_exporter --web.listen-address=":9188" &

 

Making Prometheus Aware of New Exporters

 

  1. Modify the file as the root user and add the below entries:

$vim /etc/prometheus/prometheus.yml

 

 - job_name: 'cdscluster_master'

    scrape_interval: 5s

    static_configs:

     - targets: ['ec2-3-211-84-94.compute-1.amazonaws.com:9187']

 

 - job_name: 'cdscluster_replica'

    scrape_interval: 5s

    static_configs:

     - targets: ['ec2-3-211-84-110.compute-1.amazonaws.com:9188]

 

     2. Restart the Prometheus service.

 

/usr/local/bin/prometheus --config.file /etc/prometheus/prometheus.yml --storage.tsdb.path /var/lib/prometheus/ --web.console.templates=/etc/prometheus/consoles --web.console.libraries=/etc/prometheus/console_libraries


   3. Open the Grafana console. Click on the + icon > Import

 

Screen Shot 2019-04-12 at 9.49.43 AM.png

  

 

4. Provide the pre-created Grafana dashboard ID available on the Grafana website. There are several predefined dashboards created for the Postgres database. In my example have used:

https://grafana.com/dashboards/6742

 

Screen Shot 2019-04-12 at 9.50.26 AM.png

 

   5. Give a nice name to the dashboard and change the data source to Prometheus.

 

Screen Shot 2019-04-12 at 9.50.56 AM.png

 

   6. Now we are ready to roll! We can use this dashboard for live database statistics and for receiving notifications and critical alerts like max connections, long-running transactions, etc.  

 

Screen Shot 2019-04-05 at 2.44.01 PM.png

 

Screen Shot 2019-04-05 at 2.44.21 PM.png

 

 

I hope this is helpful. If you have any questions or comments, please feel free to send an email to cds-help@enterprisedb.com.

 

 

 

 

 

 

 

 

 

 

 

Version history
Revision #:
4 of 4
Last update:
‎04-16-2019 11:09 AM
Updated by:
 
Labels (1)
Contributors