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=localhost port=5432 dbname=template1
64 [pgbouncer]
66 listen_port = 6432
67 listen_addr = localhost
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, --daemon
110 Run in the background. Without it, the process will run in the
111 foreground.
112 In daemon mode, setting pidfile as well as logfile or syslog is
114 required. No log messages will be written to stderr after going
115 into the background.
116 Note: Does not work on Windows; pgbouncer need to run as service
118 there.
119 -R, --reboot
121 Do an online restart. That means connecting to the running
122 process, loading the open sockets from it, and then using them.
123 If there is no active process, boot normally. Note: Works only
124 if OS supports Unix sockets and the unix_socket_dir is not dis‐
125 abled in configuration. Does not work on Windows. Does not
126 work with TLS connections, they are dropped.
127 -u USERNAME, --user=USERNAME
129 Switch to the given user on startup.
130 -v, --verbose
132 Increase verbosity. Can be used multiple times.
133 -q, --quiet
135 Be quiet: do not log to stderr. This does not affect logging
136 verbosity, only that stderr is not to be used. For use in
137 init.d scripts.
138 -V, --version
140 Show version.
141 -h, --help
143 Show short help.
144 --regservice
146 Win32: Register pgbouncer to run as Windows service. The ser‐
147 vice_name configuration parameter value is used as the name to
148 register under.
149 --unregservice
151 Win32: Unregister Windows service.
152
154 The console is available by connecting as normal to the database pg‐
155 bouncer:
156 $ psql -p 6432 pgbouncer
158 Only users listed in the configuration parameters admin_users or
160 stats_users are allowed to log in to the console. (Except when
161 auth_type=any, then any user is allowed in as a stats_user.)
162 Additionally, the user name pgbouncer is allowed to log in without
164 password, if the login comes via the Unix socket and the client has
165 same Unix user UID as the running process.
166 Show commands
168 The SHOW commands output information. Each command is described below.
169 SHOW STATS
171 Shows statistics. In this and related commands, the total figures are
172 since process start, the averages are updated every stats_period.
173 database
175 Statistics are presented per database.
176 total_xact_count
178 Total number of SQL transactions pooled by pgbouncer.
179 total_query_count
181 Total number of SQL queries pooled by pgbouncer.
182 total_received
184 Total volume in bytes of network traffic received by pgbouncer.
185 total_sent
187 Total volume in bytes of network traffic sent by pgbouncer.
188 total_xact_time
190 Total number of microseconds spent by pgbouncer when connected
191 to PostgreSQL in a transaction, either idle in transaction or
192 executing queries.
193 total_query_time
195 Total number of microseconds spent by pgbouncer when actively
196 connected to PostgreSQL, executing queries.
197 total_wait_time
199 Time spent by clients waiting for a server, in microseconds.
200 avg_xact_count
202 Average transactions per second in last stat period.
203 avg_query_count
205 Average queries per second in last stat period.
206 avg_recv
208 Average received (from clients) bytes per second.
209 avg_sent
211 Average sent (to clients) bytes per second.
212 avg_xact_time
214 Average transaction duration, in microseconds.
215 avg_query_time
217 Average query duration, in microseconds.
218 avg_wait_time
220 Time spent by clients waiting for a server, in microseconds (av‐
221 erage per second).
222 SHOW STATS_TOTALS
224 Subset of SHOW STATS showing the total values (total_).
225 SHOW STATS_AVERAGES
227 Subset of SHOW STATS showing the average values (avg_).
228 SHOW TOTALS
230 Like SHOW STATS but aggregated across all databases.
231 SHOW SERVERS
233 type S, for server.
234 user User name pgbouncer uses to connect to server.
236 database
238 Database name.
239 state State of the pgbouncer server connection, one of active, idle,
241 used, tested, new.
242 addr IP address of PostgreSQL server.
244 port Port of PostgreSQL server.
246 local_addr
248 Connection start address on local machine.
249 local_port
251 Connection start port on local machine.
252 connect_time
254 When the connection was made.
255 request_time
257 When last request was issued.
258 wait Current waiting time in seconds.
260 wait_us
262 Microsecond part of the current waiting time.
263 close_needed
265 1 if the connection will be closed as soon as possible, because
266 a configuration file reload or DNS update changed the connection
267 information or RECONNECT was issued.
268 ptr Address of internal object for this connection. Used as unique
270 ID.
271 link Address of client connection the server is paired with.
273 remote_pid
275 PID of backend server process. In case connection is made over
276 Unix socket and OS supports getting process ID info, its OS PID.
277 Otherwise it’s extracted from cancel packet the server sent,
278 which should be the PID in case the server is PostgreSQL, but
279 it’s a random number in case the server it is another PgBouncer.
280 tls A string with TLS connection information, or empty if not using
282 TLS.
283 SHOW CLIENTS
285 type C, for client.
286 user Client connected user.
288 database
290 Database name.
291 state State of the client connection, one of active or waiting.
293 addr IP address of client.
295 port Source port of client.
297 local_addr
299 Connection end address on local machine.
300 local_port
302 Connection end port on local machine.
303 connect_time
305 Timestamp of connect time.
306 request_time
308 Timestamp of latest client request.
309 wait Current waiting time in seconds.
311 wait_us
313 Microsecond part of the current waiting time.
314 close_needed
316 not used for clients
317 ptr Address of internal object for this connection. Used as unique
319 ID.
320 link Address of server connection the client is paired with.
322 remote_pid
324 Process ID, in case client connects over Unix socket and OS sup‐
325 ports getting it.
326 tls A string with TLS connection information, or empty if not using
328 TLS.
329 SHOW POOLS
331 A new pool entry is made for each couple of (database, user).
332 database
334 Database name.
335 user User name.
337 cl_active
339 Client connections that are linked to server connection and can
340 process queries.
341 cl_waiting
343 Client connections that have sent queries but have not yet got a
344 server connection.
345 cl_cancel_req
347 Client connections that have not forwarded query cancellations
348 to the server yet.
349 sv_active
351 Server connections that are linked to a client.
352 sv_idle
354 Server connections that are unused and immediately usable for
355 client queries.
356 sv_used
358 Server connections that have been idle for more than serv‐
359 er_check_delay, so they need server_check_query to run on them
360 before they can be used again.
361 sv_tested
363 Server connections that are currently running either server_re‐
364 set_query or server_check_query.
365 sv_login
367 Server connections currently in the process of logging in.
368 maxwait
370 How long the first (oldest) client in the queue has waited, in
371 seconds. If this starts increasing, then the current pool of
372 servers does not handle requests quickly enough. The reason may
373 be either an overloaded server or just too small of a pool_size
374 setting.
375 maxwait_us
377 Microsecond part of the maximum waiting time.
378 pool_mode
380 The pooling mode in use.
381 SHOW LISTS
383 Show following internal information, in columns (not rows):
384 databases
386 Count of databases.
387 users Count of users.
389 pools Count of pools.
391 free_clients
393 Count of free clients.
394 used_clients
396 Count of used clients.
397 login_clients
399 Count of clients in login state.
400 free_servers
402 Count of free servers.
403 used_servers
405 Count of used servers.
406 dns_names
408 Count of DNS names in the cache.
409 dns_zones
411 Count of DNS zones in the cache.
412 dns_queries
414 Count of in-flight DNS queries.
415 dns_pending
417 not used
418 SHOW USERS
420 name The user name
421 pool_mode
423 The user’s override pool_mode, or NULL if the default will be
424 used instead.
425 SHOW DATABASES
427 name Name of configured database entry.
428 host Host pgbouncer connects to.
430 port Port pgbouncer connects to.
432 database
434 Actual database name pgbouncer connects to.
435 force_user
437 When the user is part of the connection string, the connection
438 between pgbouncer and PostgreSQL is forced to the given user,
439 whatever the client user.
440 pool_size
442 Maximum number of server connections.
443 min_pool_size
445 Minimum number of server connections.
446 reserve_pool
448 Maximum number of additional connections for this database.
449 pool_mode
451 The database’s override pool_mode, or NULL if the default will
452 be used instead.
453 max_connections
455 Maximum number of allowed connections for this database, as set
456 by max_db_connections, either globally or per database.
457 current_connections
459 Current number of connections for this database.
460 paused 1 if this database is currently paused, else 0.
462 disabled
464 1 if this database is currently disabled, else 0.
465 SHOW FDS
467 Internal command - shows list of file descriptors in use with internal
468 state attached to them.
469 When the connected user has the user name “pgbouncer”, connects through
471 the Unix socket and has same the UID as the running process, the actual
472 FDs are passed over the connection. This mechanism is used to do an
473 online restart. Note: This does not work on Windows.
474 This command also blocks the internal event loop, so it should not be
476 used while PgBouncer is in use.
477 fd File descriptor numeric value.
479 task One of pooler, client or server.
481 user User of the connection using the FD.
483 database
485 Database of the connection using the FD.
486 addr IP address of the connection using the FD, unix if a Unix socket
488 is used.
489 port Port used by the connection using the FD.
491 cancel Cancel key for this connection.
493 link fd for corresponding server/client. NULL if idle.
495 SHOW SOCKETS, SHOW ACTIVE_SOCKETS
497 Shows low-level information about sockets or only active sockets. This
498 includes the information shown under SHOW CLIENTS and SHOW SERVERS as
499 well as other more low-level information.
500 SHOW CONFIG
502 Show the current configuration settings, one per row, with the follow‐
503 ing columns:
504 key Configuration variable name
506 value Configuration value
508 default
510 Configuration default value
511 changeable
513 Either yes or no, shows if the variable can be changed while
514 running. If no, the variable can be changed only at boot time.
515 Use SET to change a variable at run time.
516 SHOW MEM
518 Shows low-level information about the current sizes of various internal
519 memory allocations. The information presented is subject to change.
520 SHOW DNS_HOSTS
522 Show host names in DNS cache.
523 hostname
525 Host name.
526 ttl How many seconds until next lookup.
528 addrs Comma separated list of addresses.
530 SHOW DNS_ZONES
532 Show DNS zones in cache.
533 zonename
535 Zone name.
536 serial Current serial.
538 count Host names belonging to this zone.
540 SHOW VERSION
542 Show the PgBouncer version string.
543 Process controlling commands
545 PAUSE [db]
546 PgBouncer tries to disconnect from all servers, first waiting for all
547 queries to complete. The command will not return before all queries
548 are finished. To be used at the time of database restart.
549 If database name is given, only that database will be paused.
551 New client connections to a paused database will wait until RESUME is
553 called.
554 DISABLE db
556 Reject all new client connections on the given database.
557 ENABLE db
559 Allow new client connections after a previous DISABLE command.
560 RECONNECT [db]
562 Close each open server connection for the given database, or all data‐
563 bases, after it is released (according to the pooling mode), even if
564 its lifetime is not up yet. New server connections can be made immedi‐
565 ately and will connect as necessary according to the pool size set‐
566 tings.
567 This command is useful when the server connection setup has changed,
569 for example to perform a gradual switchover to a new server. It is not
570 necessary to run this command when the connection string in pgbounc‐
571 er.ini has been changed and reloaded (see RELOAD) or when DNS resolu‐
572 tion has changed, because then the equivalent of this command will be
573 run automatically. This command is only necessary if something down‐
574 stream of PgBouncer routes the connections.
575 After this command is run, there could be an extended period where some
577 server connections go to an old destination and some server connections
578 go to a new destination. This is likely only sensible when switching
579 read-only traffic between read-only replicas, or when switching between
580 nodes of a multimaster replication setup. If all connections need to
581 be switched at the same time, PAUSE is recommended instead. To close
582 server connections without waiting (for example, in emergency failover
583 rather than gradual switchover scenarios), also consider KILL.
584 KILL db
586 Immediately drop all client and server connections on given database.
587 New client connections to a killed database will wait until RESUME is
589 called.
590 SUSPEND
592 All socket buffers are flushed and PgBouncer stops listening for data
593 on them. The command will not return before all buffers are empty. To
594 be used at the time of PgBouncer online reboot.
595 New client connections to a suspended database will wait until RESUME
597 is called.
598 RESUME [db]
600 Resume work from previous KILL, PAUSE, or SUSPEND command.
601 SHUTDOWN
603 The PgBouncer process will exit.
604 RELOAD
606 The PgBouncer process will reload its configuration file and update
607 changeable settings.
608 PgBouncer notices when a configuration file reload changes the connec‐
610 tion parameters of a database definition. An existing server connec‐
611 tion to the old destination will be closed when the server connection
612 is next released (according to the pooling mode), and new server con‐
613 nections will immediately use the updated connection parameters.
614 WAIT_CLOSE [db]
616 Wait until all server connections, either of the specified database or
617 of all databases, have cleared the “close_needed” state (see SHOW
618 SERVERS). This can be called after a RECONNECT or RELOAD to wait until
619 the respective configuration change has been fully activated, for exam‐
620 ple in switchover scripts.
621 Other commands
623 SET key = arg
624 Changes a configuration setting (see also SHOW CONFIG). For example:
625 SET log_connections = 1;
627 SET server_check_query = 'select 2';
628 (Note that this command is run on the PgBouncer admin console and sets
630 PgBouncer settings. A SET command run on another database will be
631 passed to the PostgreSQL backend like any other SQL command.)
632 Signals
634 SIGHUP Reload config. Same as issuing the command RELOAD on the con‐
635 sole.
636 SIGINT Safe shutdown. Same as issuing PAUSE and SHUTDOWN on the con‐
638 sole.
639 SIGTERM
641 Immediate shutdown. Same as issuing SHUTDOWN on the console.
642 SIGUSR1
644 Same as issuing PAUSE on the console.
645 SIGUSR2
647 Same as issuing RESUME on the console.
648 Libevent settings
650 From the Libevent documentation:
651 It is possible to disable support for epoll, kqueue, devpoll,
653 poll or select by setting the environment variable EVENT_NOE‐
654 POLL, EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or
655 EVENT_NOSELECT, respectively.
656 By setting the environment variable EVENT_SHOW_METHOD, libevent
658 displays the kernel notification method that it uses.
659
661 pgbouncer(5) - man page of configuration settings descriptions
662 <https://www.pgbouncer.org/>
6641.16.1 PGBOUNCER(1)