To configure this interface we have to adapt our config file. In our example we will simply add one line to the config (in the pgbouncer section of the file):

admin_users = zb

We want Zoltan, whose username is zb, to be in charge of the admin database so we simply add him here. If we want many users to have access to the system, we can list them one after another (comma separated).

After restarting pgbouncer, we can try connecting to the system:

psql -p 6432 -U zb pgbouncer
psql (9.2.4, server 1.5.4/bouncer)
WARNING: psql version 9.2, server version 1.5.
         Some psql features might not work.
Type "help" for help.

Don't worry about the warning message—it is just telling us that we have connected to a thing that does not look like a native PostgreSQL 9.2 database instance.

Once we have connected to this virtual management database, we can check which commands are available there. To do so we can run SHOW HELP:

pgbouncer=# SHOW HELP;
NOTICE:  Console usage
DETAIL:  
  SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
  SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
  SHOW DNS_HOSTS|DNS_ZONES
  SET key = arg
  RELOAD
  PAUSE [<db>]
  RESUME [<db>]
  KILL <db>
  SUSPEND
  SHUTDOWN
SHOW

As we have mentioned, the system will only accept administrative commands; normal SELECT statements are not possible in this virtual database:

pgbouncer=# SELECT 1+1;
ERROR:  invalid command 'SELECT 1+1;', use SHOW HELP;

One important thing you can do with the management interface is to figure out which databases have been configured for the system. To do that you can call the SHOW DATABASES command:

pgbouncer=# x
Expanded display is on.
pgbouncer=# SHOW DATABASES;
-[ RECORD 1 ]+----------
name         | p0
host         | localhost
port         | 5432
database     | p0
force_user   | 
pool_size    | 20
reserve_pool | 0
-[ RECORD 2 ]+----------
name         | p1
host         | localhost
port         | 5432
database     | p1
force_user   | 
pool_size    | 20
reserve_pool | 0
-[ RECORD 3 ]+----------
name         | pgbouncer
host         | 
port         | 6432
database     | pgbouncer
force_user   | pgbouncer
pool_size    | 2
reserve_pool | 0

As you can see we have two productive databases and the virtual pgbouncer database. What is important here to see is that the listing contains the pool size as well as the size of the reserved pool. It is a good check to see what is going on in your bouncer setup.

Once you have checked the list of databases on your system you can turn your attention to the clients active in your system. To extract the list of active clients pgbouncer offers the SHOW CLIENTS instruction:

pgbouncer=# x
Expanded display is on.
pgbouncer=# SHOW CLIENTS;
-[ RECORD 1 ]+--------------------
type         | C
user         | zb
database     | pgbouncer
state        | active
addr         | unix
port         | 6432
local_addr   | unix
local_port   | 6432
connect_time | 2013-04-29 11:08:54
request_time | 2013-04-29 11:10:39
ptr          | 0x19e3000
link         | 

At the moment we have exactly one user connection to the pgbouncer database. We can see nicely where the connection comes from and when it has been created. SHOW CLIENTS is especially important if there are hundreds or even thousands of servers on the system.

Sometimes it can be useful to extract aggregated information from the system. SHOW STATS will provide you with statistics about what is going on in your system. It shows how many requests have been performed and how many queries have been performed on average:

pgbouncer=# SHOW STATS;
-[ RECORD 1 ]----+----------
database         | pgbouncer
total_requests   | 3
total_received   | 0
total_sent       | 0
total_query_time | 0
avg_req          | 0
avg_recv         | 0
avg_sent         | 0
avg_query        | 0

Finally we can take a look at the memory consumption we are facing. pgbouncer will return this information if SHOW MEM is executed:

pgbouncer=# SHOW MEM;
name          | size | used | free |  memtotal
--------------+------+------+------+----------
user_cache    |  184 |    4 |   85 |     16376
db_cache      |  160 |    3 |   99 |     16320
pool_cache    |  408 |    1 |   49 |     20400
server_cache  |  360 |    0 |    0 |         0
client_cache  |  360 |    1 |   49 |     18000
iobuf_cache   | 2064 |    1 |   49 |    103200
(6 rows)

As you can see pgbouncer is really lightweight and does not consume very much memory as other connection pools do.

One of the core reasons to use the interactive virtual database is to be able to suspend and resume normal operations. It is also possible to reload the config on the fly just as shown in the following example:

pgbouncer=# RELOAD;
RELOAD

RELOAD will re-read the config so that there is no need to restart the entire bouncer for most small changes. This is especially useful if there is just a new user or something like that.

An additional feature of pgbouncer is the ability to stop operations for a while. Why would anybody want to stop queries for some time? Well, let us assume you want to perform a small change somewhere in your infrastructure. Just interrupt operations briefly without actually throwing errors. Of course, you have to be a little careful to make sure that your frontend infrastructure can handle such an interruption nicely. From database side, however, it can come in handy.

To temporarily stop queries we can call SUSPEND:

pgbouncer=# SUSPEND;
SUSPEND

Once you are done with your changes, you can resume normal operations easily:

pgbouncer=# RESUME;
RESUME

Once this has been called, you can continue to send queries to the server.

Finally you can even stop pgbouncer entirely from the interactive shell. It is highly recommended that you be careful when doing that:

pgbouncer=# SHUTDOWN;
The connection to the server was lost. Attempting reset: Failed.
!>

The system will be shut down instantly.