Build Real-Time PGPool-II Monitoring with Prometheus & Grafana

In modern database architectures, high availability and performance optimization are essential, and PGPool-II provides connection pooling for PostgreSQL while enhancing scalability and stability. Effective monitoring is equally crucial.

pgpool-exporter Deployment

1. Download the pgpool2_exporter package

$ curl -L -O https://github.com/pgpool/pgpool2_exporter/releases/download/v1.2.2/pgpool2_exporter-1.2.2.linux-amd64.tar.gz

2. Extract the binary

$ temdir=$(mktemp -d)
$ tar xvf pgpool2_exporter-1.2.2.linux-amd64.tar.gz -C ${temdir}

$ sudo cp ${temdir}/pgpool2_exporter-1.2.2.linux-amd64/pgpool2_exporter /usr/local/bin/

3. Create and start the service

$ cat <<'EOF' | sudo tee /usr/lib/systemd/system/pgpool2_exporter.service > /dev/null
[Unit]
Description=pgpool2_exporter
Documentation=https://github.com/pgpool/pgpool2_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=172.139.20.188:9999/postgres?sslmode=disable
ExecStart=/usr/local/bin/pgpool2_exporter --web.listen-address=:9719
ExecStop=/bin/kill -s SIGTERM $MAINPID
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

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

4. Clean up temporary files

$ sudo rm -rf ${temdir}

Collect pgpool Metrics

1. Add a Prometheus scrape job for pgpool2

$ kubectl -n kube-system edit cm prometheus
    - job_name: "pgpool2"
      static_configs:
      - targets:
        - "172.139.20.17:9719"

2. Verify that metrics are being collected

$ 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="pgpool2"}'
{"status":"success","data":{"resultType":"vector","result":[{"metric":{"__name__":"up","instance":"172.139.20.17:9719","job":"pgpool2"},"value":[1731486018.973,"1"]}]}}

Configure Grafana Dashboard

Add the official pgpool2_exporter dashboard to Grafana: pgpool2.json

Tip: Some adjustments may be needed to achieve the exact appearance shown in the screenshots.

Conclusion

By following these steps, we have built a Prometheus‑and‑Grafana based monitoring system for PGPool‑II, improving database management efficiency and helping prevent potential issues. Keep an eye on evolving monitoring tools to maintain optimal system health.