1PGBOUNCER(1) Databases PGBOUNCER(1)
2
6 pgbouncer - lightweight connection pooler for PostgreSQL
7
9 pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
10 pgbouncer -V|-h
11 On Windows computers, the options are:
13 pgbouncer.exe [-v][-u user] <pgbouncer.ini>
15 pgbouncer.exe -V|-h
16 Additional options for setting up a Windows service:
18 pgbouncer.exe --regservice <pgbouncer.ini>
20 pgbouncer.exe --unregservice <pgbouncer.ini>
21
23 pgbouncer is a PostgreSQL connection pooler. Any target application can
24 be connected to pgbouncer as if it were a PostgreSQL server, and
25 pgbouncer will create a connection to the actual server, or it will re‐
26 use one of its existing connections.
27 The aim of pgbouncer is to lower the performance impact of opening new
29 connections to PostgreSQL.
30 In order not to compromise transaction semantics for connection pool‐
32 ing, pgbouncer supports several types of pooling when rotating connec‐
33 tions:
34 Session pooling
36 Most polite method. When client connects, a server connection
37 will be assigned to it for the whole duration the client stays
38 connected. When the client disconnects, the server connection
39 will be put back into the pool. This is the default method.
40 Transaction pooling
42 A server connection is assigned to client only during a transac‐
43 tion. When PgBouncer notices that transaction is over, the
44 server connection will be put back into the pool.
45 Statement pooling
47 Most aggressive method. The server connection will be put back
48 into pool immediately after a query completes. Multi-statement
49 transactions are disallowed in this mode as they would break.
50 The administration interface of pgbouncer consists of some new SHOW
52 commands available when connected to a special 'virtual' database
53 pgbouncer.
54
56 Basic setup and usage as following.
57 1. Create a pgbouncer.ini file. Details in pgbouncer(5). Simple exam‐
59 ple:
60 [databases]
62 template1 = host=127.0.0.1 port=5432 dbname=template1
63 [pgbouncer]
65 listen_port = 6543
66 listen_addr = 127.0.0.1
67 auth_type = md5
68 auth_file = users.txt
69 logfile = pgbouncer.log
70 pidfile = pgbouncer.pid
71 admin_users = someuser
72 2. Create users.txt file that contains users allowed in:
74 "someuser" "same_password_as_in_server"
76 3. Launch pgbouncer:
78 $ pgbouncer -d pgbouncer.ini
80 4. Have your application (or the psql client) connect to pgbouncer
82 instead of directly to PostgreSQL server:
83 $ psql -p 6543 -U someuser template1
85 5. Manage pgbouncer by connecting to the special administration data‐
87 base pgbouncer and issuing show help; to begin:
88 $ psql -p 6543 -U someuser pgbouncer
90 pgbouncer=# show help;
91 NOTICE: Console usage
92 DETAIL:
93 SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION]
94 SET key = arg
95 RELOAD
96 PAUSE
97 SUSPEND
98 RESUME
99 SHUTDOWN
100 6. If you made changes to the pgbouncer.ini file, you can reload it
102 with:
103 pgbouncer=# RELOAD;
105
107 -d Run in background. Without it the process will run in fore‐
108 ground. Note: Does not work on Windows, pgbouncer need to run
109 as service there.
110 -R Do an online restart. That means connecting to the running
112 process, loading the open sockets from it, and then using them.
113 If there is no active process, boot normally. Note: Works only
114 if OS supports Unix sockets and the unix_socket_dir is not dis‐
115 abled in config. Does not work on Windows machines. Does not
116 work with TLS connections, they are dropped.
117 -u user
119 Switch to the given user on startup.
120 -v Increase verbosity. Can be used multiple times.
122 -q Be quiet - do not log to stdout. Note this does not affect log‐
124 ging verbosity, only that stdout is not to be used. For use in
125 init.d scripts.
126 -V Show version.
128 -h Show short help.
130 --regservice
132 Win32: Register pgbouncer to run as Windows service. The ser‐
133 vice_name config parameter value is used as name to register
134 under.
135 --unregservice
137 Win32: Unregister Windows service.
138
140 The console is available by connecting as normal to the database
141 pgbouncer:
142 $ psql -p 6543 pgbouncer
144 Only users listed in configuration parameters admin_users or
146 stats_users are allowed to login to the console. (Except when
147 auth_type=any, then any user is allowed in as a stats_user.)
148 Additionally, the username pgbouncer is allowed to log in without pass‐
150 word, if the login comes via Unix socket and the client has same Unix
151 user UID as the running process.
152 Show commands
154 The SHOW commands output information. Each command is described below.
155 SHOW STATS;
157 Shows statistics.
158 database
160 Statistics are presented per database.
161 total_xact_count
163 Total number of SQL transactions pooled by pgbouncer.
164 total_query_count
166 Total number of SQL queries pooled by pgbouncer.
167 total_received
169 Total volume in bytes of network traffic received by pgbouncer.
170 total_sent
172 Total volume in bytes of network traffic sent by pgbouncer.
173 total_xact_time
175 Total number of microseconds spent by pgbouncer when connected
176 to PostgreSQL in a transaction, either idle in transaction or
177 executing queries.
178 total_query_time
180 Total number of microseconds spent by pgbouncer when actively
181 connected to PostgreSQL, executing queries.
182 total_wait_time
184 Time spent by clients waiting for a server in microseconds.
185 avg_xact_count
187 Average transactions per second in last stat period.
188 avg_query_count
190 Average queries per second in last stat period.
191 avg_recv
193 Average received (from clients) bytes per second.
194 avg_sent
196 Average sent (to clients) bytes per second.
197 avg_xact_time
199 Average transaction duration in microseconds.
200 avg_query_time
202 Average query duration in microseconds.
203 avg_wait_time
205 Time spent by clients waiting for a server in microseconds
206 (average per second).
207 SHOW STATS_TOTALS;
209 Subset of SHOW STATS showing the total values (total_).
210 SHOW STATS_AVERAGES;
212 Subset of SHOW STATS showing the average values (avg_).
213 SHOW SERVERS;
215 type S, for server.
216 user User name pgbouncer uses to connect to server.
218 database
220 Database name.
221 state State of the pgbouncer server connection, one of active, used or
223 idle.
224 addr IP address of PostgreSQL server.
226 port Port of PostgreSQL server.
228 local_addr
230 Connection start address on local machine.
231 local_port
233 Connection start port on local machine.
234 connect_time
236 When the connection was made.
237 request_time
239 When last request was issued.
240 ptr Address of internal object for this connection. Used as unique
242 ID.
243 link Address of client connection the server is paired with.
245 remote_pid
247 PID of backend server process. In case connection is made over
248 Unix socket and OS supports getting process ID info, its OS PID.
249 Otherwise it's extracted from cancel packet server sent, which
250 should be PID in case server is PostgreSQL, but it's a random
251 number in case server it is another PgBouncer.
252 SHOW CLIENTS;
254 type C, for client.
255 user Client connected user.
257 database
259 Database name.
260 state State of the client connection, one of active, used, waiting or
262 idle.
263 addr IP address of client.
265 port Port client is connected to.
267 local_addr
269 Connection end address on local machine.
270 local_port
272 Connection end port on local machine.
273 connect_time
275 Timestamp of connect time.
276 request_time
278 Timestamp of latest client request.
279 ptr Address of internal object for this connection. Used as unique
281 ID.
282 link Address of server connection the client is paired with.
284 remote_pid
286 Process ID, in case client connects over Unix socket and OS sup‐
287 ports getting it.
288 SHOW POOLS;
290 A new pool entry is made for each couple of (database, user).
291 database
293 Database name.
294 user User name.
296 cl_active
298 Client connections that are linked to server connection and can
299 process queries.
300 cl_waiting
302 Client connections have sent queries but have not yet got a
303 server connection.
304 sv_active
306 Server connections that linked to client.
307 sv_idle
309 Server connections that unused and immediately usable for client
310 queries.
311 sv_used
313 Server connections that have been idle more than
314 server_check_delay, so they needs server_check_query to run on
315 it before it can be used.
316 sv_tested
318 Server connections that are currently running either
319 server_reset_query or server_check_query.
320 sv_login
322 Server connections currently in logging in process.
323 maxwait
325 How long the first (oldest) client in queue has waited, in sec‐
326 onds. If this starts increasing, then the current pool of
327 servers does not handle requests quick enough. Reason may be
328 either overloaded server or just too small of a pool_size set‐
329 ting.
330 pool_mode
332 The pooling mode in use.
333 SHOW LISTS;
335 Show following internal information, in columns (not rows):
336 databases
338 Count of databases.
339 users Count of users.
341 pools Count of pools.
343 free_clients
345 Count of free clients.
346 used_clients
348 Count of used clients.
349 login_clients
351 Count of clients in login state.
352 free_servers
354 Count of free servers.
355 used_servers
357 Count of used servers.
358 SHOW USERS;
360 name The user name
361 pool_mode
363 The user's override pool_mode, or NULL if the default will be
364 used instead.
365 SHOW DATABASES;
367 name Name of configured database entry.
368 host Host pgbouncer connects to.
370 port Port pgbouncer connects to.
372 database
374 Actual database name pgbouncer connects to.
375 force_user
377 When user is part of the connection string, the connection
378 between pgbouncer and PostgreSQL is forced to the given user,
379 whatever the client user.
380 pool_size
382 Maximum number of server connections.
383 pool_mode
385 The database's override pool_mode, or NULL if the default will
386 be used instead.
387 SHOW FDS;
389 Internal command - shows list of file descriptors in use with internal
390 state attached to them.
391 When the connected user has user name "pgbouncer", connects through
393 Unix socket and has same UID as the running process, the actual FDs are
394 passed over the connection. This mechanism is used to do an online
395 restart. Note: This does not work on Windows machines.
396 This command also blocks the internal event loop, so it should not be
398 used while PgBouncer is in use.
399 fd File descriptor numeric value.
401 task One of pooler, client or server.
403 user User of the connection using the FD.
405 database
407 Database of the connection using the FD.
408 addr IP address of the connection using the FD, unix if a Unix socket
410 is used.
411 port Port used by the connection using the FD.
413 cancel Cancel key for this connection.
415 link fd for corresponding server/client. NULL if idle.
417 SHOW CONFIG;
419 Show the current configuration settings, one per row, with following
420 columns:
421 key Configuration variable name
423 value Configuration value
425 changeable
427 Either yes or no, shows if the variable can be changed while
428 running. If no, the variable can be changed only boot-time.
429 SHOW DNS_HOSTS;
431 Show host names in DNS cache.
432 hostname
434 Host name.
435 ttl How many seconds until next lookup.
437 addrs Comma separated list of addresses.
439 SHOW DNS_ZONES
441 Show DNS zones in cache.
442 zonename
444 Zone name.
445 serial Current serial.
447 count Host names belonging to this zone.
449 Process controlling commands
451 PAUSE [db];
452 PgBouncer tries to disconnect from all servers, first waiting for all
453 queries to complete. The command will not return before all queries are
454 finished. To be used at the time of database restart.
455 If database name is given, only that database will be paused.
457 DISABLE db;
459 Reject all new client connections on the given database.
460 ENABLE db;
462 Allow new client connections after a previous DISABLE command.
463 KILL db;
465 Immediately drop all client and server connections on given database.
466 SUSPEND;
468 All socket buffers are flushed and PgBouncer stops listening for data
469 on them. The command will not return before all buffers are empty. To
470 be used at the time of PgBouncer online reboot.
471 RESUME [db];
473 Resume work from previous PAUSE or SUSPEND command.
474 SHUTDOWN;
476 The PgBouncer process will exit.
477 RELOAD;
479 The PgBouncer process will reload its configuration file and update
480 changeable settings.
481 Signals
483 SIGHUP Reload config. Same as issuing command RELOAD; on console.
484 SIGINT Safe shutdown. Same as issuing PAUSE; and SHUTDOWN; on console.
486 SIGTERM
488 Immediate shutdown. Same as issuing SHUTDOWN; on console.
489 Libevent settings
491 From libevent docs:
492 It is possible to disable support for epoll, kqueue, devpoll, poll
494 or select by setting the environment variable EVENT_NOEPOLL,
495 EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or EVENT_NOSELECT,
496 respectively.
497 By setting the environment variable EVENT_SHOW_METHOD, libevent
499 displays the kernel notification method that it uses.
500
502 pgbouncer(5) - man page of configuration settings descriptions.
503 https://pgbouncer.github.io/
505 https://wiki.postgresql.org/wiki/PgBouncer
5071.8.1 2017-12-20 PGBOUNCER(1)