Monitoring Postgres with Prometheus Exporter and Grafana

A Prometheus exporter is a tool that collects metrics from a specific system and exposes them in a format that Prometheus can scrape. The PostgreSQL Prometheus Exporter specifically collects metrics from PostgreSQL databases.

In this article, you will deploy PostgreSQL alongside the PostgreSQL Prometheus Exporter to scrape metrics from your database. You'll then visualize these metrics using a Grafana dashboard.

Table of Contents

1. Clone the Repository
2. Deploy PostgreSQL Resources
3. Install PostgreSQL Exporter
4. Verify Metrics
5. Deploy Grafana Dashboard
6. Access Grafana
7. Cleaning Up Resources

Prerequisites

This guide assumes you have already installed Helm and set up Prometheus in your Kubernetes cluster. If you haven't done this yet, please check out this article on setting up Prometheus with Kubernetes before continuing with this guide.

Clone the Repository

First, clone the repository containing the necessary PostgreSQL resources, exporter configuration, and dashboard:

git clone https://github.com/rslim087a/postgres-prometheus-sample

cd postgres-prometheus-sample

Deploy PostgreSQL Resources

Create the namespace database-monitoring:

kubectl create namespace database-monitoring

Deploy the PostgreSQL resources (Secret, Service, and StatefulSet) using the following command:

kubectl apply -f postgres/

This command applies all the Kubernetes manifests in the postgres/ directory, setting up your PostgreSQL instance.

Install PostgreSQL Exporter

Add the Prometheus Community Helm repository and update it:

helm repo add prometheus-community https://prometheus-community.github.io/helm-charts
helm repo update

Now, install the PostgreSQL Exporter using the custom values file:

helm install postgres-exporter prometheus-community/prometheus-postgres-exporter \
  -f postgres-exporter/postgresql-exporter-values.yaml \
  -n database-monitoring

Let's examine the custom values in postgresql-exporter-values.yaml:

# PostgreSQL Exporter Helm Values

# Image configuration
image:
  tag: "v0.12.0"

# Datasource configuration
config:
  datasource:
    host: "postgresql.database-monitoring.svc.cluster.local"
    user: "postgres"
    pass: "password123"
    port: "5432"
    database: "postgres"
    sslmode: "disable"

  # Metrics collectors configuration
  exporter:
    default_metrics:
      enabled: true

serviceMonitor:
  enabled: true
  interval: 30s
  scrapeTimeout: 10s
  labels:
    release: prometheus

# Explicitly set the DATA_SOURCE_NAME environment variable
extraEnvs:
  - name: DATA_SOURCE_NAME
    value: "postgresql://postgres:[e-mail]:5432/postgres?sslmode=disable"

Explanation of key fields:

• config.datasource: Specifies the PostgreSQL connection details.

  • host: The Kubernetes service name for PostgreSQL.
  • user and pass: Database credentials.
  • port: Default PostgreSQL port.
  • database: The database to connect to.
  • sslmode: SSL mode for the connection.

• serviceMonitor: Configuration for Prometheus to automatically discover and scrape metrics from the PostgreSQL exporter.

  • enabled: true: Activates the creation of a ServiceMonitor resource.
  • interval: 30s: Sets how often Prometheus should scrape metrics.
  • scrapeTimeout: 10s: Defines the maximum time Prometheus should wait for a response.
  • labels: Ensures that the correct Prometheus instance picks up this ServiceMonitor.

• extraEnvs: Explicitly sets the DATA_SOURCE_NAME environment variable, which is used by the exporter to connect to PostgreSQL.

Verify Metrics

Port-forward the PostgreSQL Exporter service and check the metrics:

kubectl port-forward svc/postgres-exporter-prometheus-postgres-exporter 9187:80 -n database-monitoring &

curl http://localhost:9187/metrics

After verifying, stop the port-forward:

kill %1

Deploy Grafana Dashboard

Apply the Grafana dashboard ConfigMap:

kubectl apply -f grafana -n monitoring

This creates a ConfigMap with the grafana_dashboard: "1" label, which Grafana will automatically detect and import.

Access Grafana

Port-forward the Grafana service:

kubectl port-forward svc/prometheus-grafana 3000:80 -n monitoring &

Access Grafana at http://localhost:3000 using the default credentials (usually admin/prom-operator).

The PostgreSQL dashboard should now be available in your Grafana instance. To find it, follow these steps:

1. Log into Grafana
2. Click on the "Dashboards" icon in the left sidebar
3. Select "Browse"
4. Look for a dashboard named "PostgreSQL Dashboard" or similar

This dashboard will provide detailed insights into your PostgreSQL performance and health, including metrics on connections, transactions, database sizes, and more.

Cleaning Up Resources

If you've deployed this setup for testing or learning purposes, you may want to clean up the resources afterwards to avoid unnecessary costs or cluster clutter.

Here are the steps to remove the deployed resources:

Delete the PostgreSQL Exporter:

   helm uninstall postgres-exporter -n database-monitoring

If you deployed PostgreSQL as part of this guide:

   kubectl delete -f postgres/ -n database-monitoring

Remove the Grafana dashboard:

   kubectl delete -f grafana -n monitoring

If you no longer need the Prometheus Community Helm repository:

   helm repo remove prometheus-community

Finally, delete the namespace

   kubectl delete namespace database-monitoring

Kubernetes Training

If you found this guide helpful, check out our Kubernetes Training course