Alright, let’s get your database metrics out of the terminal and onto a dashboard where they belong. Staring at pg_stat_statements output is a rite of passage, but it’s a terrible way to run a system. We’re going to hook your Postgres instance up to Prometheus and Grafana using the brilliant postgres_exporter. Think of it as a tiny, hyper-competent translator that lives next to your database, constantly asking it “how’s it going?” in a language Prometheus understands, and then Prometheus makes pretty pictures for Grafana.

The magic here is that postgres_exporter doesn’t store anything itself. It’s a stateless application that, when scraped by Prometheus, runs a collection of SQL queries against your database to get the current state of metrics. Prometheus hits the exporter’s HTTP endpoint (on port 9187 by default), the exporter fetches fresh data, and Prometheus stores it with a timestamp. This is the pull model, and it’s central to how Prometheus operates.

Getting the Exporter Running

You don’t install this thing by apt-geting some ancient version. We’re going with the modern, containerized approach because it’s simpler and more isolated. You can run it as a Docker container alongside your database. Here’s a basic docker run command that’ll get you started. Note the environment variables: we’re telling it how to connect to one database.

docker run -d --name postgres_exporter \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://username:password@your.postgres.host:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter:latest

Now, point your browser to http://your-exporter-host:9187/metrics. You should be greeted by a wall of text—this is the Prometheus format, a beautiful and ugly symphony of metrics and labels. This is what Prometheus will be scraping every 15-60 seconds.

The Prometheus Scrape Config

Prometheus needs to know where your new translator lives. You’ll add a new job to your prometheus.yml config file. The important bit is the static_configs target, which is just the hostname and port of your exporter.

scrape_configs:
  # ... your other jobs like node_exporter ...

  - job_name: 'postgres'
    scrape_interval: 30s # Every 30s is perfectly reasonable for DB metrics.
    static_configs:
      - targets: ['your-exporter-host:9187']
        labels:
          instance: 'my-production-db' # This label is gold for filtering in Grafana.

Restart Prometheus, and it will immediately start pulling those metrics in. You can check the “Targets” page in the Prometheus UI to see if it’s succeeding.

Grafana: The Reward

This is where the payoff happens. You don’t build these dashboards from scratch; that’s a fantastic way to waste an afternoon. You grab a proven dashboard from the Grafana community marketplace and customize it. The one everyone uses is https://grafana.com/grafana/dashboards/9628. It’s comprehensive, well-designed, and covers 95% of what you need.

In Grafana, go to Dashboards > New > Import, paste in 9628, and load it. It’ll ask you which Prometheus data source to use. Select it, and boom—you now have a professional-grade monitoring dashboard for your database. Watch those graphs populate. It’s a thing of beauty.

The Queries File: Unlocking Real Power

The exporter’s default metrics are good, but they’re generic. The real power move is to provide a custom queries file. This is a YAML file where you define your own SQL queries to extract your specific, important metrics. This is how you monitor things like “number of pending sign-ups” or “growth of the invoices table.”

You create a .yml file, mount it into the container, and point the exporter to it. Let’s say you want to monitor long-running transactions.

# custom-queries.yml
queries:
- name: long_running_transactions
  description: "Count of transactions longer than 10 minutes"
  query: |
    SELECT count(*) AS count
    FROM pg_stat_activity
    WHERE state <> 'idle'
      AND now() - xact_start > interval '10 minutes'
  metrics:
    - metric: long_running_transactions
      usage: GAUGE
      description: "Count of long-running transactions"

Your Docker run command gets a bit more complex:

docker run -d --name postgres_exporter \
  -p 9187:9187 \
  -v /path/on/host/custom-queries.yml:/etc/postgres_exporter/custom-queries.yml \
  -e DATA_SOURCE_NAME="postgresql://username:password@host:5432/postgres?sslmode=disable" \
  -e PG_EXPORTER_EXTEND_QUERY_PATH="/etc/postgres_exporter/custom-queries.yml" \
  quay.io/prometheuscommunity/postgres-exporter:latest

Now, your exporter will provide a new metric called pg_long_running_transactions that you can alert on. This is how you move from generic observation to specific, actionable insight.

Common Pitfalls and The SSL Question

First pitfall: connection overload. The exporter opens a new connection for every scrape. If you have a 15-second scrape interval and 50 Prometheus servers scraping it (don’t ask, I’ve seen things), you can blast your database with connections. The solution is to use a dedicated user for the exporter with a connection limit set in Postgres: ALTER USER exporter CONNECTION LIMIT 5;.

Second, and this is a biggie: sslmode=disable. My example code used it for simplicity, and it’s a crime against your data. In production, you must use sslmode=verify-full and provide the CA certificate to the exporter container. Not doing so means your metrics are flowing over the network in plain text, which is a fantastic way to leak sensitive information about your database’s inner state. The exporter supports this via the DATA_SOURCE_URI environment variable and mounting a cert file. Do it right.

The postgres_exporter is the unsung hero of the Postgres observability stack. It takes the incredible depth of Postgres’s internal statistics and makes them consumable. Set it up, build one custom query for your most critical table, and you’ll never go back to guessing.