Master PostgreSQL Monitoring with Grafana: Step-by-Step Guide

In a data‑driven business environment, monitoring database performance is essential for smooth data flow and system stability. PostgreSQL, as a powerful open‑source relational database, requires careful monitoring, and Grafana offers an intuitive interface for visualizing and analyzing metrics.

Deploy postgres_exporter

Download and install the exporter, then create a systemd service to run it.

$ curl -L -O https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

$ temdir=$(mktemp -d)
$ tar xvf postgres_exporter-0.15.0.linux-amd64.tar.gz -C ${temdir}
$ sudo cp ${temdir}/postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/

$ cat <<'EOF' | sudo tee /usr/lib/systemd/system/postgres_exporter.service > /dev/null
[Unit]
Description=postgres_exporter
Documentation=https://github.com/prometheus-community/postgres_exporter
Wants=network.service
After=network.service

[Service]
Type=simple
User=postgres
Environment=DATA_SOURCE_USER=postgres
Environment=DATA_SOURCE_PASS=123456
Environment=DATA_SOURCE_URI=localhost:5432/postgres?sslmode=disable
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187 --collector.postmaster --collector.stat_statements
ExecStop=/bin/kill -s SIGTERM $MAINPID
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

$ sudo systemctl daemon-reload
$ sudo systemctl enable postgres_exporter.service --now

$ sudo rm -rf ${temdir}

Configure PostgreSQL Extensions

Verify, compile, and enable the pg_stat_statements extension.

ls /app/postgresql/lib/pg_stat_statements.so

$ cd ./postgresql-14.10/contrib/pg_stat_statements/
$ make && sudo make install

$ grep shared_preload_libraries $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'  # (change requires restart)
$ sudo systemctl restart postgresql

$ psql -h localhost -p 5432 -U postgres -W -c "select name from pg_available_extensions where name = 'pg_stat_statements';"

$ psql -h localhost -p 5432 -U postgres -W -c "CREATE EXTENSION pg_stat_statements;"

Configure Prometheus and Grafana

Add a scrape job for postgres_exporter in Prometheus and verify collection.

$ kubectl -n kube-system edit cm prometheus
  - job_name: "postgres-exporter"
    static_configs:
    - targets:
      - "172.139.20.17:9187"
      - "172.139.20.81:9187"
      - "172.139.20.177:9187"

$ curl -s $(kubectl -n kube-system get svc prometheus -ojsonpath='{.spec.clusterIP}:{.spec.ports[0].port}')/prometheus/api/v1/query --data-urlencode 'query=up{job="postgres-exporter"}'

In Grafana, import the PostgreSQL exporter dashboard (ID 12485) and adjust the JSON if necessary.

Tip: The official dashboard URL is https://grafana.com/grafana/dashboards/12485-postgresql-exporter/; you may need to modify the JSON to fit your environment.

Conclusion

By following this guide, you should now be able to monitor PostgreSQL with Grafana effectively. Grafana’s powerful visualization combined with Prometheus integration provides a comprehensive solution for PostgreSQL performance monitoring, making your data‑ops work smarter and more efficient.