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 NameUnitDescription
pgbouncer_stats_queries_per_secondStats Queries Per Secondqueries/secondQuery rate
pgbouncer_stats_transactions_per_secondStats Transactions Per Secondtransactions/secondTransaction rate
pgbouncer_stats_bytes_received_per_secondStats Bytes Received Per Secondbytes/secondTotal network traffic received
pgbouncer_stats_bytes_sent_per_secondStats Bytes Sent Per Secondbytes/secondTotal network traffic sent
pgbouncer_stats_total_query_timeStats Total Query TimemicrosecondsTime spent by pgbouncer actively querying PostgreSQL
pgbouncer_stats_total_transaction_timeStats Total Transaction TimemicrosecondsTime spent by pgbouncer in transactions
pgbouncer_stats_avg_query_countStats Avg Query CountQueriesAverage number of queries per second in last stat period
pgbouncer_stats_total_wait_timeStats Total Wait TimemicrosecondsTime spent by clients waiting for a server, in microseconds
pgbouncer_stats_avg_wait_timeStats Avg Wait TimemicrosecondsTime spent by clients waiting for a server, in microseconds (average per second)
pgbouncer_stats_avg_transaction_countStats Avg Transaction CountTransactionsAverage number of transactions per second in last stat period
pgbouncer_stats_avg_recvStats Avg RecvbytesClient network traffic received
pgbouncer_stats_avg_sentStats Avg SentbytesClient network traffic sent
pgbouncer_stats_avg_query_timeStats Avg Query TimemicrosecondsAverage query duration
pgbouncer_stats_avg_transaction_timeStats Avg Transaction TimemicrosecondsAverage transaction duration
pgbouncer_pools_cl_activePools Cl ActiveConnectionsClient connections linked to server connection and able to process queries
pgbouncer_pools_cl_waitingPools Cl WaitingConnectionsClient connections waiting on a server connection
pgbouncer_pools_sv_activePools Sv ActiveConnectionsServer connections linked to a client connection
pgbouncer_pools_sv_idlePools Sv IdleConnectionsServer connections idle and ready for a client query
pgbouncer_pools_sv_usedPools Sv UsedConnectionsServer connections idle more than server_check_delay, needing server_check_query
pgbouncer_pools_sv_testedPools Sv TestedConnectionsServer connections currently running either server_reset_query or server_check_query
pgbouncer_pools_sv_loginPools Sv LoginConnectionsServer connections currently in the process of logging in
pgbouncer_pools_maxwaitPools MaxwaitsecondsHow long the first (oldest) client in the queue has waited, in seconds
pgbouncer_pools_maxwait_usPools Maxwait UsmicrosecondsMicrosecond part of the maximum waiting time
pgbouncer_databases_pool_sizeDatabases Pool SizeConnectionsMaximum number of server connections
pgbouncer_databases_max_connectionsDatabases Max ConnectionsConnectionsMaximum number of allowed connections
pgbouncer_databases_current_connectionsDatabases Current ConnectionsConnectionsCurrent number of connections for this database