PostgreSQL Monitoring Within Red Hat OpenShift Container Platform

Jeff McCormick

In this blog post, we will discuss how PostgreSQL metrics are collected and stored as part of the Crunchy Container Suite project running within the Red Hat OpenShift Container Platform. Crunchy Data is a Red Hat Technology Connect and OpenShift Primed partner.

The Crunchy Container Suite is an open source collection of docker container images designed to run and manage PostgreSQL in a cloud-native environment.  The Crunchy Container Suite can be found within this Github repository https://github.com/crunchydata/crunchy-containers


The central concern of the Crunchy Containers started with providing a solid open source set of PostgreSQL database containers, but we quickly found that customers would also want a means of monitoring their provisioned PostgreSQL database containers.  PostgreSQL has unique metrics which allows a DBA to gain insight into the health of their databases. The Crunchy Container Suite takes advantage of the Prometheus time series data store for metric collection. Crunchy is pleased that the Prometheus project has released Prometheus 1.0 which is now deployed in the 1.2.2 version of the crunchy-prometheus container described in this blog.   


For this purpose, Crunchy developed the following containers:

  • crunchy-collect - collects 32 different PostgreSQL metrics from a database container
  • crunchy-grafana - provides a web based graphing dashboard for collected PostgreSQL metrics, Grafana connects to a Prometheus data source to query metrics
  • crunchy-prometheus - contains Prometheus 1.0 for metrics collection
  • crunchy-promgateway - contains a Prometheus Push Gateway

These containers are pre-built and can be found on DockerHub at the following location https://hub.docker.com/u/crunchydata/dashboard/  

 

Collecting Metrics

The crunchy-collect container can be placed within a database pod to begin metrics collection.  An example of a pod that includes the crunchy-collect container is here.   In Figure 1, metrics are collected and pushed to the Prometheus Push Gateway where they will be scraped on a scheduled basis by the Prometheus server.

Crunchy_Containers_-_PostgreSQL_Metrics_Collection-1.png

Figure 1 - Metrics Collection


The Prometheus server is configured to treat the Prometheus Push Gateway as a Target on a 15 second interval.  The Prometheus configuration file is found in the following location:

https://github.com/CrunchyData/crunchy-containers/blob/master/conf/prometheus/prometheus.yml


Prometheus provides a web console where you can query the metrics directly as shown in Figure 2.

prometheus.png

Figure 2 - Prometheus Web Console Showing PostgreSQL Metric


The Prometheus Push Gateway also has a web console that shows the collected metrics ready to be scraped by the Prometheus server.  A sample screenshot of the Prometheus Push Gateway web console is shown in Figure 3.

promgateway.png

Figure 3 - Prometheus Push Gateway Web Console Showing PostgreSQL Metrics



The grafana graphing product is included in the crunchy-grafana container.   Grafana lets you specify the Prometheus server as a data source.  Its main feature is to let the user define custom graphs which use the Prometheus data source as the source for metrics.  The Grafana web console is shown in Figure 4.

grafana.png

Figure 4: Grafana Dashboard

 

PostgreSQL Metrics

Currently, the crunchy-collect container collects roughly 30 different types of PostgreSQL metrics from a running database container, a sample of the metrics is shown in the following table:


Metric Name

Purpose

crunchy_connections

the number of active connections

crunchy_databasesize

the size in Megabytes of a database

crunchy_tup_fetched

Count of tuples selected

crunchy_tup_inserted

Count of tuples inserted

crunchy_connectionutil

The pct utilization of max connections

crunchy_temp_files

Number of temp files

 

 

About Crunchy Data

Crunchy Data is a leading provider of trusted open source PostgreSQL and PostgreSQL support, technology and training.  Crunchy Data is the provider of Crunchy Certified PostgreSQL, an open source PostgreSQL 9.5 distribution including popular extensions such as PostGIS and enhanced audit logging capability.  Crunchy Certified PostgreSQL is currently the only open source database Common Criteria certified at the EAL2+ level.

When combined with Crunchy’s Secure Enterprise Support, Crunchy Certified PostgreSQL provides enterprises with an open source and trusted relational database management solution backed by enterprise support from leading experts in PostgreSQL technology.  For enterprises requiring dedicated PostgreSQL support, Crunchy provides on-premise PostgreSQL professional services and PostgreSQL training. Learn more at www.crunchydata.com

Newsletter