PgBouncer Cheat Sheet
To inspect pgbouncer operation ensure to add at least one user you defined in the user credentials (e.g. /etc/pgbouncer/userlist.txt) to the “stats_users” key in pgbouncer.ini:
stats_users = myuser
Use this user to connect to pgbouncer with psql by requesting the “pgbouncer” database:
psql -p 6432 -U myuser -W pgbouncer
At psql prompt list supported commands
PgBouncer will present all statistics and configuration options:
pgbouncer=# SHOW HELP; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SET key = arg RELOAD PAUSE  SUSPEND RESUME  SHUTDOWN
The “SHOW” commands are all self-explanatory. Very useful are the “SUSPEND” and “RESUME” commands when you use pools.
If you ever need to restart pgbouncer under traffic load use “-R” to avoid disconnecting clients. This option gets the new process to reuse the Unix sockets of the old one. A possible use case could be that you think pgbouncer has become stuck, overloaded or instable.
Aside from this in most cases SIGHUP should be fine.
If connections to your pgbouncer setup fail with “Pooler Error: Auth failed” check the following configuration values in your pgbouncer.ini
- auth_file = … : Ensure to point this path to your pg_auth file in your Postgres setup.
- auth_type = … : Ensure to set the correct authentication type. E.g. “md5” for MD5 hashed passwords.
- Check if your pg_auth file has the needed passwords entries.