In addition to what we have already described in this chapter, pgbouncer has a nice interactive administration interface capable of performing basic administration and monitoring tasks.
How does it work? pgbouncer provides you with a fake database called pgbouncer. It cannot be used for queries as it only provides a simple syntax to handle basic administrative tasks.
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.
3.145.175.253