Guest Blog By Jeff McCormick (Crunchy Data)

Crunchy Data,  a member of the CNCF and an active collaborator in the OpenShift Commons, discusses in this blog how PostgreSQL metrics are collected and stored as part of the Crunchy Containers project with Prometheus on OpenShift, Red Hat’s container platform that combines and optimizes the power of Docker containers and the Kubernetes container cluster manager  for enterprise app development and deployment.

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.   

The Crunchy containers are open source and are 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.

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/  

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.

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/prom…

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

Figure 2

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.

Figure 3

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.

Figure 4

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 member of Red Hat Technology Connect, OpenShift Primed Partner, a contributor to OpenShift Open Source project, and an active collaborator in the OpenShift Commons.

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