PgBouncer is an open-source, lightweight, single-binary connection pooler for PostgreSQL. It can pool connections to one or more databases (on possibly different servers) and serve clients over TCP and Unix domain sockets.

Prerequisites

  1. Create a user in the PgBouncer /etc/pgbouncer/pgbouncer.ini file:
stats_users = <Username>
  1. Add an associated password for user in the PgBouncer /etc/pgbouncer/userlist.txt file:
"<Username>" "<PASSWORD>"
  1. Uncomment the ignore_startup_parameters = extra_float_digits option in the file /etc/pgbouncer/pgbouncer.ini such that the connection to pgpooler instance with many modern PostgreSQL clients does not fail.

  2. For Virtual machines, install the Linux agent.

Configuring the credentials

Configure the credentials in the directory /opt/opsramp/agent/conf/app.d/creds.yaml

pgbouncer:
- name: pgbouncer
  user: <username>
  pwd: <Password>
  encoding-type: plain
  labels:
    key1: val1
    key2: val2
  

Configuring the application

Virtual machine

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-detection.yaml

- name: pgbouncer
  instance-checks:
    service-check:
      - pgbouncer
    process-check:
      - pgbouncer
    port-check:
      - 6432
  

Docker environment

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-container-detection.yaml

- name: pgbouncer
  container-checks:
    image-check:
      - pgbouncer
    port-check:
      - 6432
  

Kubernetes environment

Configure the application in config.yaml

- name: pgbouncer
  container-checks:
    image-check:
      - pgbouncer
    port-check:
      - 6432
  

Validate

Go to Resources under the Infrastructure tab to check if your resources are onboarded and the metrics are collected.

Metrics

OpsRamp MetricMetric Display NameUnit
pgbouncer_stats_queries_per_second

Query rate
Stats Queries Per Secondqueries/second
pgbouncer_stats_transactions_per_second

Transaction rate
Stats Transactions Per Secondtransactions/second
pgbouncer_stats_bytes_received_per_second

Total network traffic received
Stats Bytes Received Per Secondbytes/second
pgbouncer_stats_bytes_sent_per_second

Total network traffic sent
Stats Bytes Sent Per Secondbytes/second
pgbouncer_stats_total_query_time

Time spent by pgbouncer actively querying PostgreSQL
Stats Total Query Timemicroseconds
pgbouncer_stats_total_transaction_time

Time spent by pgbouncer in transactions
Stats Total Transaction Timemicroseconds
pgbouncer_stats_avg_query_count

Average number of queries per second in last stat period
Stats Avg Query CountQueries
pgbouncer_stats_total_wait_time

Time spent by clients waiting for a server, in microseconds
Stats Total Wait Timemicroseconds
pgbouncer_stats_avg_wait_time

Time spent by clients waiting for a server, in microseconds (average per second)
Stats Avg Wait Timemicroseconds
pgbouncer_stats_avg_transaction_count

Average number of transactions per second in last stat period
Stats Avg Transaction CountTransactions
pgbouncer_stats_avg_recv

Client network traffic received
Stats Avg Recvbytes
pgbouncer_stats_avg_sent

Client network traffic sent
Stats Avg Sentbytes
pgbouncer_stats_avg_query_time

Average query duration
Stats Avg Query Timemicroseconds
pgbouncer_stats_avg_transaction_time

Average transaction duration
Stats Avg Transaction Timemicroseconds
pgbouncer_pools_cl_active

Client connections linked to server connection and able to process queries
Pools Cl ActiveConnections
pgbouncer_pools_cl_waiting

Client connections waiting on a server connection
Pools Cl WaitingConnections
pgbouncer_pools_sv_active

Server connections linked to a client connection
Pools Sv ActiveConnections
pgbouncer_pools_sv_idle

Server connections idle and ready for a client query
Pools Sv IdleConnections
pgbouncer_pools_sv_used

Server connections idle more than server_check_delay, needing server_check_query
Pools Sv UsedConnections
pgbouncer_pools_sv_tested

Server connections currently running either server_reset_query or server_check_query
Pools Sv TestedConnections
pgbouncer_pools_sv_login

Server connections currently in the process of logging in
Pools Sv LoginConnections
pgbouncer_pools_maxwait

How long the first (oldest) client in the queue has waited, in seconds
Pools Maxwaitseconds
pgbouncer_pools_maxwait_us

Microsecond part of the maximum waiting time
Pools Maxwait Usmicroseconds
pgbouncer_databases_pool_size

Maximum number of server connections
Databases Pool SizeConnections
pgbouncer_databases_max_connections

Maximum number of allowed connections
Databases Max ConnectionsConnections
pgbouncer_databases_current_connections

Current number of connections for this database
Databases Current ConnectionsConnections