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, 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
24 can 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 a 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 a client only during a trans‐
43 action. 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 the pool immediately after a query completes. Multi-state‐
49 ment transactions are disallowed in this mode as they would
50 break.
51 The administration interface of pgbouncer consists of some new SHOW
53 commands available when connected to a special "virtual" database pg‐
54 bouncer.
55
57 Basic setup and usage is as follows.
58 1. Create a pgbouncer.ini file. Details in pgbouncer(5). Simple exam‐
60 ple:
61 [databases]
63 template1 = host=127.0.0.1 port=5432 dbname=template1
64 [pgbouncer]
66 listen_port = 6432
67 listen_addr = 127.0.0.1
68 auth_type = md5
69 auth_file = userlist.txt
70 logfile = pgbouncer.log
71 pidfile = pgbouncer.pid
72 admin_users = someuser
73 2. Create a userlist.txt file that contains the users allowed in:
75 "someuser" "same_password_as_in_server"
77 3. Launch pgbouncer:
79 $ pgbouncer -d pgbouncer.ini
81 4. Have your application (or the psql client) connect to pgbouncer in‐
83 stead of directly to the PostgreSQL server:
84 $ psql -p 6432 -U someuser template1
86 5. Manage pgbouncer by connecting to the special administration data‐
88 base pgbouncer and issuing SHOW HELP; to begin:
89 $ psql -p 6432 -U someuser pgbouncer
91 pgbouncer=# SHOW HELP;
92 NOTICE: Console usage
93 DETAIL:
94 SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION|...]
95 SET key = arg
96 RELOAD
97 PAUSE
98 SUSPEND
99 RESUME
100 SHUTDOWN
101 [...]
102 6. If you made changes to the pgbouncer.ini file, you can reload it
104 with:
105 pgbouncer=# RELOAD;
107
109 -d Run in the background. Without it, the process will run in the
110 foreground. Note: Does not work on Windows; pgbouncer need to
111 run as service there.
112 -R Do an online restart. That means connecting to the running
114 process, loading the open sockets from it, and then using them.
115 If there is no active process, boot normally. Note: Works only
116 if OS supports Unix sockets and the unix_socket_dir is not dis‐
117 abled in configuration. Does not work on Windows. Does not
118 work with TLS connections, they are dropped.
119 -u user
121 Switch to the given user on startup.
122 -v Increase verbosity. Can be used multiple times.
124 -q Be quiet: do not log to stdout. This does not affect logging
126 verbosity, only that stdout is not to be used. For use in
127 init.d scripts.
128 -V Show version.
130 -h Show short help.
132 --regservice
134 Win32: Register pgbouncer to run as Windows service. The ser‐
135 vice_name configuration parameter value is used as the name to
136 register under.
137 --unregservice
139 Win32: Unregister Windows service.
140
142 The console is available by connecting as normal to the database pg‐
143 bouncer:
144 $ psql -p 6432 pgbouncer
146 Only users listed in the configuration parameters admin_users or
148 stats_users are allowed to log in to the console. (Except when
149 auth_type=any, then any user is allowed in as a stats_user.)
150 Additionally, the user name pgbouncer is allowed to log in without
152 password, if the login comes via the Unix socket and the client has
153 same Unix user UID as the running process.
154 Show commands
156 The SHOW commands output information. Each command is described below.
157 SHOW STATS
159 Shows statistics. In this and related commands, the total figures are
160 since process start, the averages are updated every stats_period.
161 database
163 Statistics are presented per database.
164 total_xact_count
166 Total number of SQL transactions pooled by pgbouncer.
167 total_query_count
169 Total number of SQL queries pooled by pgbouncer.
170 total_received
172 Total volume in bytes of network traffic received by pgbouncer.
173 total_sent
175 Total volume in bytes of network traffic sent by pgbouncer.
176 total_xact_time
178 Total number of microseconds spent by pgbouncer when connected
179 to PostgreSQL in a transaction, either idle in transaction or
180 executing queries.
181 total_query_time
183 Total number of microseconds spent by pgbouncer when actively
184 connected to PostgreSQL, executing queries.
185 total_wait_time
187 Time spent by clients waiting for a server, in microseconds.
188 avg_xact_count
190 Average transactions per second in last stat period.
191 avg_query_count
193 Average queries per second in last stat period.
194 avg_recv
196 Average received (from clients) bytes per second.
197 avg_sent
199 Average sent (to clients) bytes per second.
200 avg_xact_time
202 Average transaction duration, in microseconds.
203 avg_query_time
205 Average query duration, in microseconds.
206 avg_wait_time
208 Time spent by clients waiting for a server, in microseconds (av‐
209 erage per second).
210 SHOW STATS_TOTALS
212 Subset of SHOW STATS showing the total values (total_).
213 SHOW STATS_AVERAGES
215 Subset of SHOW STATS showing the average values (avg_).
216 SHOW TOTALS
218 Like SHOW STATS but aggregated across all databases.
219 SHOW SERVERS
221 type S, for server.
222 user User name pgbouncer uses to connect to server.
224 database
226 Database name.
227 state State of the pgbouncer server connection, one of active, used or
229 idle.
230 addr IP address of PostgreSQL server.
232 port Port of PostgreSQL server.
234 local_addr
236 Connection start address on local machine.
237 local_port
239 Connection start port on local machine.
240 connect_time
242 When the connection was made.
243 request_time
245 When last request was issued.
246 wait Current waiting time in seconds.
248 wait_us
250 Microsecond part of the current waiting time.
251 close_needed
253 1 if the connection will be closed as soon as possible, because
254 a configuration file reload or DNS update changed the connection
255 information or RECONNECT was issued.
256 ptr Address of internal object for this connection. Used as unique
258 ID.
259 link Address of client connection the server is paired with.
261 remote_pid
263 PID of backend server process. In case connection is made over
264 Unix socket and OS supports getting process ID info, its OS PID.
265 Otherwise it's extracted from cancel packet the server sent,
266 which should be the PID in case the server is PostgreSQL, but
267 it's a random number in case the server it is another PgBouncer.
268 tls A string with TLS connection information, or empty if not using
270 TLS.
271 SHOW CLIENTS
273 type C, for client.
274 user Client connected user.
276 database
278 Database name.
279 state State of the client connection, one of active, used, waiting or
281 idle.
282 addr IP address of client.
284 port Port client is connected to.
286 local_addr
288 Connection end address on local machine.
289 local_port
291 Connection end port on local machine.
292 connect_time
294 Timestamp of connect time.
295 request_time
297 Timestamp of latest client request.
298 wait Current waiting time in seconds.
300 wait_us
302 Microsecond part of the current waiting time.
303 close_needed
305 not used for clients
306 ptr Address of internal object for this connection. Used as unique
308 ID.
309 link Address of server connection the client is paired with.
311 remote_pid
313 Process ID, in case client connects over Unix socket and OS sup‐
314 ports getting it.
315 tls A string with TLS connection information, or empty if not using
317 TLS.
318 SHOW POOLS
320 A new pool entry is made for each couple of (database, user).
321 database
323 Database name.
324 user User name.
326 cl_active
328 Client connections that are linked to server connection and can
329 process queries.
330 cl_waiting
332 Client connections that have sent queries but have not yet got a
333 server connection.
334 sv_active
336 Server connections that are linked to a client.
337 sv_idle
339 Server connections that are unused and immediately usable for
340 client queries.
341 sv_used
343 Server connections that have been idle for more than serv‐
344 er_check_delay, so they need server_check_query to run on them
345 before they can be used again.
346 sv_tested
348 Server connections that are currently running either server_re‐
349 set_query or server_check_query.
350 sv_login
352 Server connections currently in the process of logging in.
353 maxwait
355 How long the first (oldest) client in the queue has waited, in
356 seconds. If this starts increasing, then the current pool of
357 servers does not handle requests quickly enough. The reason may
358 be either an overloaded server or just too small of a pool_size
359 setting.
360 maxwait_us
362 Microsecond part of the maximum waiting time.
363 pool_mode
365 The pooling mode in use.
366 SHOW LISTS
368 Show following internal information, in columns (not rows):
369 databases
371 Count of databases.
372 users Count of users.
374 pools Count of pools.
376 free_clients
378 Count of free clients.
379 used_clients
381 Count of used clients.
382 login_clients
384 Count of clients in login state.
385 free_servers
387 Count of free servers.
388 used_servers
390 Count of used servers.
391 dns_names
393 Count of DNS names in the cache.
394 dns_zones
396 Count of DNS zones in the cache.
397 dns_queries
399 Count of in-flight DNS queries.
400 dns_pending
402 not used
403 SHOW USERS
405 name The user name
406 pool_mode
408 The user's override pool_mode, or NULL if the default will be
409 used instead.
410 SHOW DATABASES
412 name Name of configured database entry.
413 host Host pgbouncer connects to.
415 port Port pgbouncer connects to.
417 database
419 Actual database name pgbouncer connects to.
420 force_user
422 When the user is part of the connection string, the connection
423 between pgbouncer and PostgreSQL is forced to the given user,
424 whatever the client user.
425 pool_size
427 Maximum number of server connections.
428 reserve_pool
430 Maximum number of additional connections for this database.
431 pool_mode
433 The database's override pool_mode, or NULL if the default will
434 be used instead.
435 max_connections
437 Maximum number of allowed connections for this database, as set
438 by max_db_connections, either globally or per database.
439 current_connections
441 Current number of connections for this database.
442 paused 1 if this database is currently paused, else 0.
444 disabled
446 1 if this database is currently disabled, else 0.
447 SHOW FDS
449 Internal command - shows list of file descriptors in use with internal
450 state attached to them.
451 When the connected user has the user name "pgbouncer", connects through
453 the Unix socket and has same the UID as the running process, the actual
454 FDs are passed over the connection. This mechanism is used to do an
455 online restart. Note: This does not work on Windows.
456 This command also blocks the internal event loop, so it should not be
458 used while PgBouncer is in use.
459 fd File descriptor numeric value.
461 task One of pooler, client or server.
463 user User of the connection using the FD.
465 database
467 Database of the connection using the FD.
468 addr IP address of the connection using the FD, unix if a Unix socket
470 is used.
471 port Port used by the connection using the FD.
473 cancel Cancel key for this connection.
475 link fd for corresponding server/client. NULL if idle.
477 SHOW SOCKETS, SHOW ACTIVE_SOCKETS
479 Shows low-level information about sockets or only active sockets. This
480 includes the information shown under SHOW CLIENTS and SHOW SERVERS as
481 well as other more low-level information.
482 SHOW CONFIG
484 Show the current configuration settings, one per row, with the follow‐
485 ing columns:
486 key Configuration variable name
488 value Configuration value
490 changeable
492 Either yes or no, shows if the variable can be changed while
493 running. If no, the variable can be changed only at boot time.
494 Use SET to change a variable at run time.
495 SHOW MEM
497 Shows low-level information about the current sizes of various internal
498 memory allocations. The information presented is subject to change.
499 SHOW DNS_HOSTS
501 Show host names in DNS cache.
502 hostname
504 Host name.
505 ttl How many seconds until next lookup.
507 addrs Comma separated list of addresses.
509 SHOW DNS_ZONES
511 Show DNS zones in cache.
512 zonename
514 Zone name.
515 serial Current serial.
517 count Host names belonging to this zone.
519 SHOW VERSION
521 Show the PgBouncer version string.
522 Process controlling commands
524 PAUSE [db]
525 PgBouncer tries to disconnect from all servers, first waiting for all
526 queries to complete. The command will not return before all queries
527 are finished. To be used at the time of database restart.
528 If database name is given, only that database will be paused.
530 New client connections to a paused database will wait until RESUME is
532 called.
533 DISABLE db
535 Reject all new client connections on the given database.
536 ENABLE db
538 Allow new client connections after a previous DISABLE command.
539 RECONNECT [db]
541 Close each open server connection for the given database, or all data‐
542 bases, after it is released (according to the pooling mode), even if
543 its lifetime is not up yet. New server connections can be made immedi‐
544 ately and will connect as necessary according to the pool size set‐
545 tings.
546 This command is useful when the server connection setup has changed,
548 for example to perform a gradual switchover to a new server. It is not
549 necessary to run this command when the connection string in pgbounc‐
550 er.ini has been changed and reloaded (see RELOAD) or when DNS resolu‐
551 tion has changed, because then the equivalent of this command will be
552 run automatically. This command is only necessary if something down‐
553 stream of PgBouncer routes the connections.
554 After this command is run, there could be an extended period where some
556 server connections go to an old destination and some server connections
557 go to a new destination. This is likely only sensible when switching
558 read-only traffic between read-only replicas, or when switching between
559 nodes of a multimaster replication setup. If all connections need to
560 be switched at the same time, PAUSE is recommended instead. To close
561 server connections without waiting (for example, in emergency failover
562 rather than gradual switchover scenarios), also consider KILL.
563 KILL db
565 Immediately drop all client and server connections on given database.
566 New client connections to a killed database will wait until RESUME is
568 called.
569 SUSPEND
571 All socket buffers are flushed and PgBouncer stops listening for data
572 on them. The command will not return before all buffers are empty. To
573 be used at the time of PgBouncer online reboot.
574 New client connections to a suspended database will wait until RESUME
576 is called.
577 RESUME [db]
579 Resume work from previous KILL, PAUSE, or SUSPEND command.
580 SHUTDOWN
582 The PgBouncer process will exit.
583 RELOAD
585 The PgBouncer process will reload its configuration file and update
586 changeable settings.
587 PgBouncer notices when a configuration file reload changes the connec‐
589 tion parameters of a database definition. An existing server connec‐
590 tion to the old destination will be closed when the server connection
591 is next released (according to the pooling mode), and new server con‐
592 nections will immediately use the updated connection parameters.
593 WAIT_CLOSE [db]
595 Wait until all server connections, either of the specified database or
596 of all databases, have cleared the "close_needed" state (see SHOW
597 SERVERS). This can be called after a RECONNECT or RELOAD to wait until
598 the respective configuration change has been fully activated, for exam‐
599 ple in switchover scripts.
600 Other commands
602 SET key = arg
603 Changes a configuration setting (see also SHOW CONFIG). For example:
604 SET log_connections = 1;
606 SET server_check_query = 'select 2';
607 (Note that this command is run on the PgBouncer admin console and sets
609 PgBouncer settings. A SET command run on another database will be
610 passed to the PostgreSQL backend like any other SQL command.)
611 Signals
613 SIGHUP Reload config. Same as issuing the command RELOAD on the con‐
614 sole.
615 SIGINT Safe shutdown. Same as issuing PAUSE and SHUTDOWN on the con‐
617 sole.
618 SIGTERM
620 Immediate shutdown. Same as issuing SHUTDOWN on the console.
621 SIGUSR1
623 Same as issuing PAUSE on the console.
624 SIGUSR2
626 Same as issuing RESUME on the console.
627 Libevent settings
629 From the Libevent documentation:
630 It is possible to disable support for epoll, kqueue, devpoll,
632 poll or select by setting the environment variable EVENT_NOE‐
633 POLL, EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or
634 EVENT_NOSELECT, respectively.
635 By setting the environment variable EVENT_SHOW_METHOD, libevent
637 displays the kernel notification method that it uses.
638
640 pgbouncer(5) - man page of configuration settings descriptions
641 <https://www.pgbouncer.org/>
6431.12.0 PGBOUNCER(1)