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
- Create a user in the
PgBouncer /etc/pgbouncer/pgbouncer.ini
file:
stats_users = <Username>
- Add an associated password for
user in the PgBouncer /etc/pgbouncer/userlist.txt
file:
"<Username>" "<PASSWORD>"
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.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.
Supported metrics
OpsRamp Metric | Metric Display Name | Unit |
---|---|---|
pgbouncer_stats_queries_per_second Query rate | Stats Queries Per Second | queries/second |
pgbouncer_stats_transactions_per_second Transaction rate | Stats Transactions Per Second | transactions/second |
pgbouncer_stats_bytes_received_per_second Total network traffic received | Stats Bytes Received Per Second | bytes/second |
pgbouncer_stats_bytes_sent_per_second Total network traffic sent | Stats Bytes Sent Per Second | bytes/second |
pgbouncer_stats_total_query_time Time spent by pgbouncer actively querying PostgreSQL | Stats Total Query Time | microseconds |
pgbouncer_stats_total_transaction_time Time spent by pgbouncer in transactions | Stats Total Transaction Time | microseconds |
pgbouncer_stats_avg_query_count Average number of queries per second in last stat period | Stats Avg Query Count | Queries |
pgbouncer_stats_total_wait_time Time spent by clients waiting for a server, in microseconds | Stats Total Wait Time | microseconds |
pgbouncer_stats_avg_wait_time Time spent by clients waiting for a server, in microseconds (average per second) | Stats Avg Wait Time | microseconds |
pgbouncer_stats_avg_transaction_count Average number of transactions per second in last stat period | Stats Avg Transaction Count | Transactions |
pgbouncer_stats_avg_recv Client network traffic received | Stats Avg Recv | bytes |
pgbouncer_stats_avg_sent Client network traffic sent | Stats Avg Sent | bytes |
pgbouncer_stats_avg_query_time Average query duration | Stats Avg Query Time | microseconds |
pgbouncer_stats_avg_transaction_time Average transaction duration | Stats Avg Transaction Time | microseconds |
pgbouncer_pools_cl_active Client connections linked to server connection and able to process queries | Pools Cl Active | Connections |
pgbouncer_pools_cl_waiting Client connections waiting on a server connection | Pools Cl Waiting | Connections |
pgbouncer_pools_sv_active Server connections linked to a client connection | Pools Sv Active | Connections |
pgbouncer_pools_sv_idle Server connections idle and ready for a client query | Pools Sv Idle | Connections |
pgbouncer_pools_sv_used Server connections idle more than server_check_delay, needing server_check_query | Pools Sv Used | Connections |
pgbouncer_pools_sv_tested Server connections currently running server_reset_query or server_check_query | Pools Sv Tested | Connections |
pgbouncer_pools_sv_login Server connections currently in the process of logging in | Pools Sv Login | Connections |
pgbouncer_pools_maxwait How long the first (oldest) client in the queue has waited, in seconds | Pools Maxwait | seconds |
pgbouncer_pools_maxwait_us Microsecond part of the maximum waiting time | Pools Maxwait Us | microseconds |
pgbouncer_databases_pool_size Maximum number of server connections | Databases Pool Size | Connections |
pgbouncer_databases_max_connections Maximum number of allowed connections | Databases Max Connections | Connections |
pgbouncer_databases_current_connections Current number of connections for this database | Databases Current Connections | Connections |