How can I access the stats/admin interface of PgBouncer running on my dynos?

Issue

I am using the PgBouncer buildpack and I want to debug or monitor PgBouncer connections.

Resolution

The stats interface of PgBouncer is accessible via the pgbouncer user when using a unix socket connection inside a dyno. Apps can connect to localhost:6000 and run stats queries for logging.

To directly access the internal pgbouncer database within a dyno:

1. Connect to the dyno running PgBouncer via Heroku Exec.
2. Access the pgbouncer database:

   $ psql -h /tmp -p 6000 -d pgbouncer -U pgbouncer
   

3. Once in the psql session, you can run SHOW HELP; to find all the available commands. Typically, SHOW DATABASES, SHOW POOLS and SHOW CLIENTS are the most useful ones when looking for waiting clients and general utilisation of the pool.

More information can be found in https://github.com/heroku/heroku-buildpack-pgbouncer

PgBouncer will also log aggregated stats every 60 seconds by default. You can run 'grep LOG' on your app's log stream (heroku logs -t --ps web | grep LOG) to check them, which will reflect when connections are made to the local PgBouncer process.

Note that the details above correspond to apps running in-dyno PgBouncer. Customers using PgBouncer server-side via the Heroku Postgres Connection Pooling feature can connect to pgbouncer's internal database as described through https://devcenter.heroku.com/articles/postgres-connection-pooling#viewing-connection-pool-stats.