Crunchy Postgres Exporter

The crunchy-postgres-exporter container provides real time metrics about the PostgreSQL database via an API. These metrics are scraped and stored by a Prometheus time-series database and are then graphed and visualized through the open source data visualizer Grafana.

The crunchy-postgres-exporter container uses pgMonitor for advanced metric collection. It is required that the crunchy-postgres-ha container has the PGMONITOR_PASSWORD environment variable to create the appropriate user (ccp_monitoring) to collect metrics.

Custom queries to collect metrics can be specified by the user. By mounting a queries.yml file to /conf on the container, additional metrics can be specified for the API to collect. For an example of a queries.yml file, see here

Packages

The crunchy-postgres-exporter Docker image contains the following packages (versions vary depending on PostgreSQL version):

  • PostgreSQL (13.4, 12.8, 11.13, 10.18, 9.6.23 and 9.5.25)
  • CentOS 8 - publicly available
  • UBI 7, UBI 8 - customers only
  • PostgreSQL Exporter

Environment Variables

Required

Name Default Description
EXPORTER_PG_PASSWORD none Provides the password needed to generate the PostgreSQL URL required by the PostgreSQL Exporter to connect to a PG database. Should typically match the PGMONITOR_PASSWORD value set in the crunchy-postgres container.

Optional

Name Default Description
EXPORTER_PG_USER ccp_monitoring Provides the username needed to generate the PostgreSQL URL required by the PostgreSQL Exporter to connect to a PG database. Should typically be ccp_monitoring per the crunchy-postgres container specification (see environment varaible PGMONITOR_PASSWORD).
EXPORTER_PG_HOST 127.0.0.1 Provides the host needed to generate the PostgreSQL URL required by the PostgreSQL Exporter to connect to a PG database
EXPORTER_PG_PORT 5432 Provides the port needed to generate the PostgreSQL URL required by the PostgreSQL Exporter to connect to a PG database
EXPORTER_PG_DATABASE postgres Provides the name of the database used to generate the PostgreSQL URL required by the PostgreSQL Exporter to connect to a PG database
DATA_SOURCE_NAME None Explicitly defines the URL for connecting to the PostgreSQL database (must be in the form of postgresql://). If provided, overrides all other settings provided to generate the connection URL.
CRUNCHY_DEBUG FALSE Set this to true to enable debugging in logs. Note: this mode can reveal secrets in logs.
POSTGRES_EXPORTER_PORT 9187 Set the postgres-exporter port to listen on for web interface and telemetry.

Viewing Cluster Metrics

To view a particular cluster’s available metrics in a local browser window, port forwarding can be set up as follows. For a pgcluster, mycluster, deployed in the pgouser1 namespace, use

# If deployed to Kubernetes
kubectl port-forward -n pgouser1 svc/mycluster 9187:9187

# If deployed to OpenShift
oc port-forward -n pgouser1 svc/mycluster 9187:9187

Then, in your local browser, go to http://127.0.0.1:9187/metrics to view the available metrics for that cluster.

Crunchy Postgres Exporter Metrics

You can find more information about the metrics available in the Crunchy Postgres Exporter by visiting the pgMonitor project and viewing details in the exporter folder.

pgnodemx

In addition to the metrics above, the pgnodemx PostgreSQL extension provides SQL functions to allow the capture of node OS metrics via SQL queries. For more information, please see the pgnodemx project page:

https://github.com/CrunchyData/pgnodemx