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.
Pooler Error: Auth Failed
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.