PostgreSQL Monitoring Within Red Hat OpenShift Container Platform

Jeff McCormick

3 min read

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. 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

Avatar for Jeff McCormick

Written by

Jeff McCormick

August 1, 2016 More by this author