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 DEPRECATED: Instead of this option use a rolling restart with
122 multiple pgbouncer processes listening on the same port using
123 so_reuseport instead Do an online restart. That means connect‐
124 ing to the running process, loading the open sockets from it,
125 and then using them. If there is no active process, boot nor‐
126 mally. Note: Works only if OS supports Unix sockets and the
127 unix_socket_dir is not disabled in configuration. Does not work
128 on Windows. Does not work with TLS connections, they are
129 dropped.
130 -u USERNAME, --user=USERNAME
132 Switch to the given user on startup.
133 -v, --verbose
135 Increase verbosity. Can be used multiple times.
136 -q, --quiet
138 Be quiet: do not log to stderr. This does not affect logging
139 verbosity, only that stderr is not to be used. For use in
140 init.d scripts.
141 -V, --version
143 Show version.
144 -h, --help
146 Show short help.
147 --regservice
149 Win32: Register pgbouncer to run as Windows service. The ser‐
150 vice_name configuration parameter value is used as the name to
151 register under.
152 --unregservice
154 Win32: Unregister Windows service.
155
157 The console is available by connecting as normal to the database pg‐
158 bouncer:
159 $ psql -p 6432 pgbouncer
161 Only users listed in the configuration parameters admin_users or
163 stats_users are allowed to log in to the console. (Except when
164 auth_type=any, then any user is allowed in as a stats_user.)
165 Additionally, the user name pgbouncer is allowed to log in without
167 password, if the login comes via the Unix socket and the client has
168 same Unix user UID as the running process.
169 The admin console currently only supports the simple query protocol.
171 Some drivers use the extended query protocol for all commands; these
172 drivers will not work for this.
173 Show commands
175 The SHOW commands output information. Each command is described below.
176 SHOW STATS
178 Shows statistics. In this and related commands, the total figures are
179 since process start, the averages are updated every stats_period.
180 database
182 Statistics are presented per database.
183 total_xact_count
185 Total number of SQL transactions pooled by pgbouncer.
186 total_query_count
188 Total number of SQL queries pooled by pgbouncer.
189 total_received
191 Total volume in bytes of network traffic received by pgbouncer.
192 total_sent
194 Total volume in bytes of network traffic sent by pgbouncer.
195 total_xact_time
197 Total number of microseconds spent by pgbouncer when connected
198 to PostgreSQL in a transaction, either idle in transaction or
199 executing queries.
200 total_query_time
202 Total number of microseconds spent by pgbouncer when actively
203 connected to PostgreSQL, executing queries.
204 total_wait_time
206 Time spent by clients waiting for a server, in microseconds.
207 Updated when a client connection is assigned a backend connec‐
208 tion.
209 avg_xact_count
211 Average transactions per second in last stat period.
212 avg_query_count
214 Average queries per second in last stat period.
215 avg_recv
217 Average received (from clients) bytes per second.
218 avg_sent
220 Average sent (to clients) bytes per second.
221 avg_xact_time
223 Average transaction duration, in microseconds.
224 avg_query_time
226 Average query duration, in microseconds.
227 avg_wait_time
229 Average time spent by clients waiting for a server that were as‐
230 signed a backend connection within the current stats_period, in
231 microseconds (averaged per second within that period).
232 SHOW STATS_TOTALS
234 Subset of SHOW STATS showing the total values (total_).
235 SHOW STATS_AVERAGES
237 Subset of SHOW STATS showing the average values (avg_).
238 SHOW TOTALS
240 Like SHOW STATS but aggregated across all databases.
241 SHOW SERVERS
243 type S, for server.
244 user User name pgbouncer uses to connect to server.
246 database
248 Database name.
249 state State of the pgbouncer server connection, one of active, idle,
251 used, tested, new, active_cancel, being_canceled.
252 addr IP address of PostgreSQL server.
254 port Port of PostgreSQL server.
256 local_addr
258 Connection start address on local machine.
259 local_port
261 Connection start port on local machine.
262 connect_time
264 When the connection was made.
265 request_time
267 When last request was issued.
268 wait Not used for server connections.
270 wait_us
272 Not used for server connections.
273 close_needed
275 1 if the connection will be closed as soon as possible, because
276 a configuration file reload or DNS update changed the connection
277 information or RECONNECT was issued.
278 ptr Address of internal object for this connection. Used as unique
280 ID.
281 link Address of client connection the server is paired with.
283 remote_pid
285 PID of backend server process. In case connection is made over
286 Unix socket and OS supports getting process ID info, its OS PID.
287 Otherwise it’s extracted from cancel packet the server sent,
288 which should be the PID in case the server is PostgreSQL, but
289 it’s a random number in case the server it is another PgBouncer.
290 tls A string with TLS connection information, or empty if not using
292 TLS.
293 application_name
295 A string containing the application_name set on the linked
296 client connection, or empty if this is not set, or if there is
297 no linked connection.
298 prepared_statements
300 The amount of prepared statements that are prepared on the serv‐
301 er. This number is limited by the max_prepared_statements set‐
302 ting.
303 SHOW CLIENTS
305 type C, for client.
306 user Client connected user.
308 database
310 Database name.
311 state State of the client connection, one of active, waiting, ac‐
313 tive_cancel_req, or waiting_cancel_req.
314 addr IP address of client.
316 port Source port of client.
318 local_addr
320 Connection end address on local machine.
321 local_port
323 Connection end port on local machine.
324 connect_time
326 Timestamp of connect time.
327 request_time
329 Timestamp of latest client request.
330 wait Current waiting time in seconds.
332 wait_us
334 Microsecond part of the current waiting time.
335 close_needed
337 not used for clients
338 ptr Address of internal object for this connection. Used as unique
340 ID.
341 link Address of server connection the client is paired with.
343 remote_pid
345 Process ID, in case client connects over Unix socket and OS sup‐
346 ports getting it.
347 tls A string with TLS connection information, or empty if not using
349 TLS.
350 application_name
352 A string containing the application_name set by the client for
353 this connection, or empty if this was not set.
354 prepared_statements
356 The amount of prepared statements that the client has prepared
357 SHOW POOLS
359 A new pool entry is made for each couple of (database, user).
360 database
362 Database name.
363 user User name.
365 cl_active
367 Client connections that are either linked to server connections
368 or are idle with no queries waiting to be processed.
369 cl_waiting
371 Client connections that have sent queries but have not yet got a
372 server connection.
373 cl_active_cancel_req
375 Client connections that have forwarded query cancellations to
376 the server and are waiting for the server response.
377 cl_waiting_cancel_req
379 Client connections that have not forwarded query cancellations
380 to the server yet.
381 sv_active
383 Server connections that are linked to a client.
384 sv_active_cancel
386 Server connections that are currently forwarding a cancel re‐
387 quest.
388 sv_being_canceled
390 Servers that normally could become idle but are waiting to do so
391 until all in-flight cancel requests have completed that were
392 sent to cancel a query on this server.
393 sv_idle
395 Server connections that are unused and immediately usable for
396 client queries.
397 sv_used
399 Server connections that have been idle for more than serv‐
400 er_check_delay, so they need server_check_query to run on them
401 before they can be used again.
402 sv_tested
404 Server connections that are currently running either server_re‐
405 set_query or server_check_query.
406 sv_login
408 Server connections currently in the process of logging in.
409 maxwait
411 How long the first (oldest) client in the queue has waited, in
412 seconds. If this starts increasing, then the current pool of
413 servers does not handle requests quickly enough. The reason may
414 be either an overloaded server or just too small of a pool_size
415 setting.
416 maxwait_us
418 Microsecond part of the maximum waiting time.
419 pool_mode
421 The pooling mode in use.
422 SHOW PEER_POOLS
424 A new peer_pool entry is made for each configured peer.
425 database
427 ID of the configured peer entry.
428 cl_active_cancel_req
430 Client connections that have forwarded query cancellations to
431 the server and are waiting for the server response.
432 cl_waiting_cancel_req
434 Client connections that have not forwarded query cancellations
435 to the server yet.
436 sv_active_cancel
438 Server connections that are currently forwarding a cancel re‐
439 quest.
440 sv_login
442 Server connections currently in the process of logging in.
443 SHOW LISTS
445 Show following internal information, in columns (not rows):
446 databases
448 Count of databases.
449 users Count of users.
451 pools Count of pools.
453 free_clients
455 Count of free clients.
456 used_clients
458 Count of used clients.
459 login_clients
461 Count of clients in login state.
462 free_servers
464 Count of free servers.
465 used_servers
467 Count of used servers.
468 dns_names
470 Count of DNS names in the cache.
471 dns_zones
473 Count of DNS zones in the cache.
474 dns_queries
476 Count of in-flight DNS queries.
477 dns_pending
479 not used
480 SHOW USERS
482 name The user name
483 pool_mode
485 The user’s override pool_mode, or NULL if the default will be
486 used instead.
487 SHOW DATABASES
489 name Name of configured database entry.
490 host Host pgbouncer connects to.
492 port Port pgbouncer connects to.
494 database
496 Actual database name pgbouncer connects to.
497 force_user
499 When the user is part of the connection string, the connection
500 between pgbouncer and PostgreSQL is forced to the given user,
501 whatever the client user.
502 pool_size
504 Maximum number of server connections.
505 min_pool_size
507 Minimum number of server connections.
508 reserve_pool
510 Maximum number of additional connections for this database.
511 pool_mode
513 The database’s override pool_mode, or NULL if the default will
514 be used instead.
515 max_connections
517 Maximum number of allowed connections for this database, as set
518 by max_db_connections, either globally or per database.
519 current_connections
521 Current number of connections for this database.
522 paused 1 if this database is currently paused, else 0.
524 disabled
526 1 if this database is currently disabled, else 0.
527 SHOW PEERS
529 peer_id
530 ID of the configured peer entry.
531 host Host pgbouncer connects to.
533 port Port pgbouncer connects to.
535 pool_size
537 Maximum number of server connections that can be made to this
538 peer
539 SHOW FDS
541 Internal command - shows list of file descriptors in use with internal
542 state attached to them.
543 When the connected user has the user name “pgbouncer”, connects through
545 the Unix socket and has same the UID as the running process, the actual
546 FDs are passed over the connection. This mechanism is used to do an
547 online restart. Note: This does not work on Windows.
548 This command also blocks the internal event loop, so it should not be
550 used while PgBouncer is in use.
551 fd File descriptor numeric value.
553 task One of pooler, client or server.
555 user User of the connection using the FD.
557 database
559 Database of the connection using the FD.
560 addr IP address of the connection using the FD, unix if a Unix socket
562 is used.
563 port Port used by the connection using the FD.
565 cancel Cancel key for this connection.
567 link fd for corresponding server/client. NULL if idle.
569 SHOW SOCKETS, SHOW ACTIVE_SOCKETS
571 Shows low-level information about sockets or only active sockets. This
572 includes the information shown under SHOW CLIENTS and SHOW SERVERS as
573 well as other more low-level information.
574 SHOW CONFIG
576 Show the current configuration settings, one per row, with the follow‐
577 ing columns:
578 key Configuration variable name
580 value Configuration value
582 default
584 Configuration default value
585 changeable
587 Either yes or no, shows if the variable can be changed while
588 running. If no, the variable can be changed only at boot time.
589 Use SET to change a variable at run time.
590 SHOW MEM
592 Shows low-level information about the current sizes of various internal
593 memory allocations. The information presented is subject to change.
594 SHOW DNS_HOSTS
596 Show host names in DNS cache.
597 hostname
599 Host name.
600 ttl How many seconds until next lookup.
602 addrs Comma separated list of addresses.
604 SHOW DNS_ZONES
606 Show DNS zones in cache.
607 zonename
609 Zone name.
610 serial Current serial.
612 count Host names belonging to this zone.
614 SHOW VERSION
616 Show the PgBouncer version string.
617 SHOW STATE
619 Show the PgBouncer state settings. Current states are active, paused
620 and suspended.
621 Process controlling commands
623 PAUSE [db]
624 PgBouncer tries to disconnect from all servers. Disconnecting each
625 server connection waits for that server connection to be released ac‐
626 cording to the server pool’s pooling mode (in transaction pooling mode,
627 the transaction must complete, in statement mode, the statement must
628 complete, and in session pooling mode the client must disconnect). The
629 command will not return before all server connections have been discon‐
630 nected. To be used at the time of database restart.
631 If database name is given, only that database will be paused.
633 New client connections to a paused database will wait until RESUME is
635 called.
636 DISABLE db
638 Reject all new client connections on the given database.
639 ENABLE db
641 Allow new client connections after a previous DISABLE command.
642 RECONNECT [db]
644 Close each open server connection for the given database, or all data‐
645 bases, after it is released (according to the pooling mode), even if
646 its lifetime is not up yet. New server connections can be made immedi‐
647 ately and will connect as necessary according to the pool size set‐
648 tings.
649 This command is useful when the server connection setup has changed,
651 for example to perform a gradual switchover to a new server. It is not
652 necessary to run this command when the connection string in pgbounc‐
653 er.ini has been changed and reloaded (see RELOAD) or when DNS resolu‐
654 tion has changed, because then the equivalent of this command will be
655 run automatically. This command is only necessary if something down‐
656 stream of PgBouncer routes the connections.
657 After this command is run, there could be an extended period where some
659 server connections go to an old destination and some server connections
660 go to a new destination. This is likely only sensible when switching
661 read-only traffic between read-only replicas, or when switching between
662 nodes of a multimaster replication setup. If all connections need to
663 be switched at the same time, PAUSE is recommended instead. To close
664 server connections without waiting (for example, in emergency failover
665 rather than gradual switchover scenarios), also consider KILL.
666 KILL db
668 Immediately drop all client and server connections on given database.
669 New client connections to a killed database will wait until RESUME is
671 called.
672 SUSPEND
674 All socket buffers are flushed and PgBouncer stops listening for data
675 on them. The command will not return before all buffers are empty. To
676 be used at the time of PgBouncer online reboot.
677 New client connections to a suspended database will wait until RESUME
679 is called.
680 RESUME [db]
682 Resume work from previous KILL, PAUSE, or SUSPEND command.
683 SHUTDOWN
685 The PgBouncer process will exit.
686 RELOAD
688 The PgBouncer process will reload its configuration files and update
689 changeable settings. This includes the main configuration file as well
690 as the files specified by the settings auth_file and auth_hba_file.
691 PgBouncer notices when a configuration file reload changes the connec‐
693 tion parameters of a database definition. An existing server connec‐
694 tion to the old destination will be closed when the server connection
695 is next released (according to the pooling mode), and new server con‐
696 nections will immediately use the updated connection parameters.
697 WAIT_CLOSE [db]
699 Wait until all server connections, either of the specified database or
700 of all databases, have cleared the “close_needed” state (see SHOW
701 SERVERS). This can be called after a RECONNECT or RELOAD to wait until
702 the respective configuration change has been fully activated, for exam‐
703 ple in switchover scripts.
704 Other commands
706 SET key = arg
707 Changes a configuration setting (see also SHOW CONFIG). For example:
708 SET log_connections = 1;
710 SET server_check_query = 'select 2';
711 (Note that this command is run on the PgBouncer admin console and sets
713 PgBouncer settings. A SET command run on another database will be
714 passed to the PostgreSQL backend like any other SQL command.)
715 Signals
717 SIGHUP Reload config. Same as issuing the command RELOAD on the con‐
718 sole.
719 SIGINT Safe shutdown. Same as issuing PAUSE and SHUTDOWN on the con‐
721 sole.
722 SIGTERM
724 Immediate shutdown. Same as issuing SHUTDOWN on the console.
725 SIGUSR1
727 Same as issuing PAUSE on the console.
728 SIGUSR2
730 Same as issuing RESUME on the console.
731 Libevent settings
733 From the Libevent documentation:
734 It is possible to disable support for epoll, kqueue, devpoll,
736 poll or select by setting the environment variable EVENT_NOE‐
737 POLL, EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or
738 EVENT_NOSELECT, respectively.
739 By setting the environment variable EVENT_SHOW_METHOD, libevent
741 displays the kernel notification method that it uses.
742
744 pgbouncer(5) - man page of configuration settings descriptions
745 <https://www.pgbouncer.org/>
7471.21.0 PGBOUNCER(1)