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
Comments

Hi Ajay,

 

Thanks. it is very helpful.

One question 

 - Here, you ran two postgres exporters for two different databases. Let's say, I have two databases on same machine ( e.g. PG 11 & EPAS 12 ), Can i use one postgres_exporter for the same ? 

Hi @neel , Yes you can run exporter for 2 databases. Below is the example to run 2 different Postgres exporter for databases running on 5432 and 5444. The only key is to make sure that both of your exporters listens to different port $ export DATA_SOURCE_NAME="postgresql://enterprisedb:postgres@ec2-3-211-84-110.compute-1.amazonaws.com:5444/edb" $nohup ./postgres_exporter --web.listen-address=":9188" & $ export DATA_SOURCE_NAME="postgresql://postgres:postgres@ec2-3-211-84-110.compute-1.amazonaws.com:5432/postgres" $nohup ./postgres_exporter --web.listen-address=":9199" & Hope this helps. Please let me know if you face any problems. Regards, Aj

Hi @Ajay Thank you for sharing your thoughts. I want to run only one exporter here to monitor 2 databases running on port 5432 & 5444. I have managed to do that with below entry of DATA_SOURCE_NAME.

 

DATA_SOURCE_NAME="postgresql://postgres:edb@127.0.0.1:5434/postgres?sslmode=disable,postgresql://enterprisedb:edb@127.0.0.1:5444/edb?sslmode=disable"

 

And after restarting the postgres_exporter, I can see it is exposing metrics for both the databases as below in screenshot.

 

prometheus.png

 

Is there one to one mapping for postgres exporter and database ? Why can't we run one postgres exporter for more than one databases. Actually, I wanted to know, is there any limitation that i don't know ?

Hi @neel, I don't think to there is one to one Exporter to database mapping issue. I would suggest to just register one of the database and verify it on Grafana dashboard or Promethues dashboard. I believe there was a one to one mapping earlier but that's not the case any more.