Adding metrics for pgbouncer

[momoadc]

Have an idea to improve PGO? We'd love to hear it! We're going to need some information from you to learn more about your feature requests.

Please be sure you've done the following:

• [x] Provide a concise description of your feature request.
• [x] Describe your use case. Detail the problem you are trying to solve.
• [x] Describe how you envision that the feature would work.
• [x] Provide general information about your current PGO environment.

Overview

Provide a concise description of your feature request.

Use Case

Crunchy Data v5 monitoring stack is lacking metrics, alerts and dashboards for pgboucner. PgMonitor repository shows metrics collection for pgBouncer using pgboucner_fdw extension.

Desired Behavior

Collect pgBouncer metrics and export them to the monitoring sidecar of the Postgres database.

Environment

Tell us about your environment:

Please provide the following details:

• Platform: OpenShift
• Platform Version: 4.10.15
• PGO Image Tag: ubi8-5.1.1-0
• Postgres Version 13
• Storage: nfs
• Number of Postgres clusters: (5)

Additional Information

CrunchyData v4 documents and supports metrics for pgBouncer using pgbouncer_fdw and pgMonitor

[andrewlecuyer]

@momoadc thank you for the enhancement request!

Enhanced PgBouncer metrics & monitoring support is something we're currently taking a look at for a future PGO release, and your feedback is much appreciated.

[szelenka]

It would be great to have pgBouncer metrics. pgMonitor suggests it's possible through the pgbouncer_fdw extension, but it doesn't appear possible to configure that extension with the v5.2.0 PGO (or at least I haven't found the documentation for this).

[nut-api]

My workaround is to deploy 2 sidecars alongside each pgbouncer pod.

• First sidecar is postgres with init script for setting up the pgbouncer_fdw extension which connect to each pgbouncer (localhost:5432 from postgresCluster).
• The other sidecar is postgres-exporter which query queries_pgbouncer.yml from first the postgres above.

With this workaround, I can use a PGbouncer dashboard from pgmonitor

You need to enable "PGBouncerSidecars=true" flag in PGO_FEATURE_GATES variable for sidecar feature.

Example for my postgresCluster:

  proxy:
    pgBouncer:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.19-3
      replicas: 3
      metadata:
        labels:
          postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true" # Lable for Prometheus scaping
      config:
        global:
          stats_users: "_crunchypgbouncer" #Add permission to the default crunchy user to access a bouncer database for scape the metrics.
      containers:
      - name: exporter
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.4.1-0 #Using exporter from Crunchy
        command: 
            - /bin/bash
            - -c
            - |
              echo "Starting postgres-exporter.."
              PG_EXP_HOME=$(find /opt/cpm/bin/ -type d -name 'postgres_exporter*')
              POSTGRES_EXPORTER_PIDFILE=/tmp/postgres_exporter.pid
              DATA_SOURCE_URI="localhost:${EXPORTER_PG_PORT}/${EXPORTER_PG_DATABASE}?sslmode=disable" DATA_SOURCE_USER="${EXPORTER_PG_USER}" DATA_SOURCE_PASS="${EXPORTER_PG_PASSWORD}" ${PG_EXP_HOME?}/postgres_exporter --extend.query-path=/opt/cpm/conf/queries_pgbouncer.yml --web.listen-address=:${POSTGRES_EXPORTER_PORT} >>/dev/stdout 2>&1 &
              echo $! > $POSTGRES_EXPORTER_PIDFILE
              wait
        env:
        - name: EXPORTER_PG_DATABASE
          value: monitoring
        - name: EXPORTER_PG_PORT
          value: "6543"
        - name: EXPORTER_PG_USER
          value: admin
        - name: EXPORTER_PG_PASSWORD
          value: <password>
        - name: POSTGRES_EXPORTER_PORT
          value: "9187"
        ports:
          - containerPort: 9187
            name: tcp-exporter
      - name: exporter-db
        image: <custom-postgres-with-pgbouncer_fdw-extension>
        env:
        - name: POSTGRES_USER
          value: admin
        - name: POSTGRES_PASSWORD
          value: <password>
        - name: POSTGRES_DB
          value: monitoring
        - name: PGPORT
          value: "6543"
        - name: BOUNCER_USER
          value: "_crunchypgbouncer" # Default cruchy user.
        - name: BOUNCER_PASS
          valueFrom: { secretKeyRef: { name: db-<name>-pgbouncer, key: pgbouncer-password} }
        ports:
          - containerPort: 6543
            name: tcp-db