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, --daemon
110 Run in the background. Without it, the process will run in the
111 foreground. Note: Does not work on Windows; pgbouncer need to
112 run as service there.
113 -R, --reboot
115 Do an online restart. That means connecting to the running
116 process, loading the open sockets from it, and then using them.
117 If there is no active process, boot normally. Note: Works only
118 if OS supports Unix sockets and the unix_socket_dir is not dis‐
119 abled in configuration. Does not work on Windows. Does not
120 work with TLS connections, they are dropped.
121 -u USERNAME, --user=USERNAME
123 Switch to the given user on startup.
124 -v, --verbose
126 Increase verbosity. Can be used multiple times.
127 -q, --quiet
129 Be quiet: do not log to stdout. This does not affect logging
130 verbosity, only that stdout is not to be used. For use in
131 init.d scripts.
132 -V, --version
134 Show version.
135 -h, --help
137 Show short help.
138 --regservice
140 Win32: Register pgbouncer to run as Windows service. The ser‐
141 vice_name configuration parameter value is used as the name to
142 register under.
143 --unregservice
145 Win32: Unregister Windows service.
146
148 The console is available by connecting as normal to the database pg‐
149 bouncer:
150 $ psql -p 6432 pgbouncer
152 Only users listed in the configuration parameters admin_users or
154 stats_users are allowed to log in to the console. (Except when
155 auth_type=any, then any user is allowed in as a stats_user.)
156 Additionally, the user name pgbouncer is allowed to log in without
158 password, if the login comes via the Unix socket and the client has
159 same Unix user UID as the running process.
160 Show commands
162 The SHOW commands output information. Each command is described below.
163 SHOW STATS
165 Shows statistics. In this and related commands, the total figures are
166 since process start, the averages are updated every stats_period.
167 database
169 Statistics are presented per database.
170 total_xact_count
172 Total number of SQL transactions pooled by pgbouncer.
173 total_query_count
175 Total number of SQL queries pooled by pgbouncer.
176 total_received
178 Total volume in bytes of network traffic received by pgbouncer.
179 total_sent
181 Total volume in bytes of network traffic sent by pgbouncer.
182 total_xact_time
184 Total number of microseconds spent by pgbouncer when connected
185 to PostgreSQL in a transaction, either idle in transaction or
186 executing queries.
187 total_query_time
189 Total number of microseconds spent by pgbouncer when actively
190 connected to PostgreSQL, executing queries.
191 total_wait_time
193 Time spent by clients waiting for a server, in microseconds.
194 avg_xact_count
196 Average transactions per second in last stat period.
197 avg_query_count
199 Average queries per second in last stat period.
200 avg_recv
202 Average received (from clients) bytes per second.
203 avg_sent
205 Average sent (to clients) bytes per second.
206 avg_xact_time
208 Average transaction duration, in microseconds.
209 avg_query_time
211 Average query duration, in microseconds.
212 avg_wait_time
214 Time spent by clients waiting for a server, in microseconds (av‐
215 erage per second).
216 SHOW STATS_TOTALS
218 Subset of SHOW STATS showing the total values (total_).
219 SHOW STATS_AVERAGES
221 Subset of SHOW STATS showing the average values (avg_).
222 SHOW TOTALS
224 Like SHOW STATS but aggregated across all databases.
225 SHOW SERVERS
227 type S, for server.
228 user User name pgbouncer uses to connect to server.
230 database
232 Database name.
233 state State of the pgbouncer server connection, one of active, idle,
235 used, tested, new.
236 addr IP address of PostgreSQL server.
238 port Port of PostgreSQL server.
240 local_addr
242 Connection start address on local machine.
243 local_port
245 Connection start port on local machine.
246 connect_time
248 When the connection was made.
249 request_time
251 When last request was issued.
252 wait Current waiting time in seconds.
254 wait_us
256 Microsecond part of the current waiting time.
257 close_needed
259 1 if the connection will be closed as soon as possible, because
260 a configuration file reload or DNS update changed the connection
261 information or RECONNECT was issued.
262 ptr Address of internal object for this connection. Used as unique
264 ID.
265 link Address of client connection the server is paired with.
267 remote_pid
269 PID of backend server process. In case connection is made over
270 Unix socket and OS supports getting process ID info, its OS PID.
271 Otherwise it's extracted from cancel packet the server sent,
272 which should be the PID in case the server is PostgreSQL, but
273 it's a random number in case the server it is another PgBouncer.
274 tls A string with TLS connection information, or empty if not using
276 TLS.
277 SHOW CLIENTS
279 type C, for client.
280 user Client connected user.
282 database
284 Database name.
285 state State of the client connection, one of active or waiting.
287 addr IP address of client.
289 port Port client is connected to.
291 local_addr
293 Connection end address on local machine.
294 local_port
296 Connection end port on local machine.
297 connect_time
299 Timestamp of connect time.
300 request_time
302 Timestamp of latest client request.
303 wait Current waiting time in seconds.
305 wait_us
307 Microsecond part of the current waiting time.
308 close_needed
310 not used for clients
311 ptr Address of internal object for this connection. Used as unique
313 ID.
314 link Address of server connection the client is paired with.
316 remote_pid
318 Process ID, in case client connects over Unix socket and OS sup‐
319 ports getting it.
320 tls A string with TLS connection information, or empty if not using
322 TLS.
323 SHOW POOLS
325 A new pool entry is made for each couple of (database, user).
326 database
328 Database name.
329 user User name.
331 cl_active
333 Client connections that are linked to server connection and can
334 process queries.
335 cl_waiting
337 Client connections that have sent queries but have not yet got a
338 server connection.
339 sv_active
341 Server connections that are linked to a client.
342 sv_idle
344 Server connections that are unused and immediately usable for
345 client queries.
346 sv_used
348 Server connections that have been idle for more than serv‐
349 er_check_delay, so they need server_check_query to run on them
350 before they can be used again.
351 sv_tested
353 Server connections that are currently running either server_re‐
354 set_query or server_check_query.
355 sv_login
357 Server connections currently in the process of logging in.
358 maxwait
360 How long the first (oldest) client in the queue has waited, in
361 seconds. If this starts increasing, then the current pool of
362 servers does not handle requests quickly enough. The reason may
363 be either an overloaded server or just too small of a pool_size
364 setting.
365 maxwait_us
367 Microsecond part of the maximum waiting time.
368 pool_mode
370 The pooling mode in use.
371 SHOW LISTS
373 Show following internal information, in columns (not rows):
374 databases
376 Count of databases.
377 users Count of users.
379 pools Count of pools.
381 free_clients
383 Count of free clients.
384 used_clients
386 Count of used clients.
387 login_clients
389 Count of clients in login state.
390 free_servers
392 Count of free servers.
393 used_servers
395 Count of used servers.
396 dns_names
398 Count of DNS names in the cache.
399 dns_zones
401 Count of DNS zones in the cache.
402 dns_queries
404 Count of in-flight DNS queries.
405 dns_pending
407 not used
408 SHOW USERS
410 name The user name
411 pool_mode
413 The user's override pool_mode, or NULL if the default will be
414 used instead.
415 SHOW DATABASES
417 name Name of configured database entry.
418 host Host pgbouncer connects to.
420 port Port pgbouncer connects to.
422 database
424 Actual database name pgbouncer connects to.
425 force_user
427 When the user is part of the connection string, the connection
428 between pgbouncer and PostgreSQL is forced to the given user,
429 whatever the client user.
430 pool_size
432 Maximum number of server connections.
433 reserve_pool
435 Maximum number of additional connections for this database.
436 pool_mode
438 The database's override pool_mode, or NULL if the default will
439 be used instead.
440 max_connections
442 Maximum number of allowed connections for this database, as set
443 by max_db_connections, either globally or per database.
444 current_connections
446 Current number of connections for this database.
447 paused 1 if this database is currently paused, else 0.
449 disabled
451 1 if this database is currently disabled, else 0.
452 SHOW FDS
454 Internal command - shows list of file descriptors in use with internal
455 state attached to them.
456 When the connected user has the user name "pgbouncer", connects through
458 the Unix socket and has same the UID as the running process, the actual
459 FDs are passed over the connection. This mechanism is used to do an
460 online restart. Note: This does not work on Windows.
461 This command also blocks the internal event loop, so it should not be
463 used while PgBouncer is in use.
464 fd File descriptor numeric value.
466 task One of pooler, client or server.
468 user User of the connection using the FD.
470 database
472 Database of the connection using the FD.
473 addr IP address of the connection using the FD, unix if a Unix socket
475 is used.
476 port Port used by the connection using the FD.
478 cancel Cancel key for this connection.
480 link fd for corresponding server/client. NULL if idle.
482 SHOW SOCKETS, SHOW ACTIVE_SOCKETS
484 Shows low-level information about sockets or only active sockets. This
485 includes the information shown under SHOW CLIENTS and SHOW SERVERS as
486 well as other more low-level information.
487 SHOW CONFIG
489 Show the current configuration settings, one per row, with the follow‐
490 ing columns:
491 key Configuration variable name
493 value Configuration value
495 changeable
497 Either yes or no, shows if the variable can be changed while
498 running. If no, the variable can be changed only at boot time.
499 Use SET to change a variable at run time.
500 SHOW MEM
502 Shows low-level information about the current sizes of various internal
503 memory allocations. The information presented is subject to change.
504 SHOW DNS_HOSTS
506 Show host names in DNS cache.
507 hostname
509 Host name.
510 ttl How many seconds until next lookup.
512 addrs Comma separated list of addresses.
514 SHOW DNS_ZONES
516 Show DNS zones in cache.
517 zonename
519 Zone name.
520 serial Current serial.
522 count Host names belonging to this zone.
524 SHOW VERSION
526 Show the PgBouncer version string.
527 Process controlling commands
529 PAUSE [db]
530 PgBouncer tries to disconnect from all servers, first waiting for all
531 queries to complete. The command will not return before all queries
532 are finished. To be used at the time of database restart.
533 If database name is given, only that database will be paused.
535 New client connections to a paused database will wait until RESUME is
537 called.
538 DISABLE db
540 Reject all new client connections on the given database.
541 ENABLE db
543 Allow new client connections after a previous DISABLE command.
544 RECONNECT [db]
546 Close each open server connection for the given database, or all data‐
547 bases, after it is released (according to the pooling mode), even if
548 its lifetime is not up yet. New server connections can be made immedi‐
549 ately and will connect as necessary according to the pool size set‐
550 tings.
551 This command is useful when the server connection setup has changed,
553 for example to perform a gradual switchover to a new server. It is not
554 necessary to run this command when the connection string in pgbounc‐
555 er.ini has been changed and reloaded (see RELOAD) or when DNS resolu‐
556 tion has changed, because then the equivalent of this command will be
557 run automatically. This command is only necessary if something down‐
558 stream of PgBouncer routes the connections.
559 After this command is run, there could be an extended period where some
561 server connections go to an old destination and some server connections
562 go to a new destination. This is likely only sensible when switching
563 read-only traffic between read-only replicas, or when switching between
564 nodes of a multimaster replication setup. If all connections need to
565 be switched at the same time, PAUSE is recommended instead. To close
566 server connections without waiting (for example, in emergency failover
567 rather than gradual switchover scenarios), also consider KILL.
568 KILL db
570 Immediately drop all client and server connections on given database.
571 New client connections to a killed database will wait until RESUME is
573 called.
574 SUSPEND
576 All socket buffers are flushed and PgBouncer stops listening for data
577 on them. The command will not return before all buffers are empty. To
578 be used at the time of PgBouncer online reboot.
579 New client connections to a suspended database will wait until RESUME
581 is called.
582 RESUME [db]
584 Resume work from previous KILL, PAUSE, or SUSPEND command.
585 SHUTDOWN
587 The PgBouncer process will exit.
588 RELOAD
590 The PgBouncer process will reload its configuration file and update
591 changeable settings.
592 PgBouncer notices when a configuration file reload changes the connec‐
594 tion parameters of a database definition. An existing server connec‐
595 tion to the old destination will be closed when the server connection
596 is next released (according to the pooling mode), and new server con‐
597 nections will immediately use the updated connection parameters.
598 WAIT_CLOSE [db]
600 Wait until all server connections, either of the specified database or
601 of all databases, have cleared the "close_needed" state (see SHOW
602 SERVERS). This can be called after a RECONNECT or RELOAD to wait until
603 the respective configuration change has been fully activated, for exam‐
604 ple in switchover scripts.
605 Other commands
607 SET key = arg
608 Changes a configuration setting (see also SHOW CONFIG). For example:
609 SET log_connections = 1;
611 SET server_check_query = 'select 2';
612 (Note that this command is run on the PgBouncer admin console and sets
614 PgBouncer settings. A SET command run on another database will be
615 passed to the PostgreSQL backend like any other SQL command.)
616 Signals
618 SIGHUP Reload config. Same as issuing the command RELOAD on the con‐
619 sole.
620 SIGINT Safe shutdown. Same as issuing PAUSE and SHUTDOWN on the con‐
622 sole.
623 SIGTERM
625 Immediate shutdown. Same as issuing SHUTDOWN on the console.
626 SIGUSR1
628 Same as issuing PAUSE on the console.
629 SIGUSR2
631 Same as issuing RESUME on the console.
632 Libevent settings
634 From the Libevent documentation:
635 It is possible to disable support for epoll, kqueue, devpoll,
637 poll or select by setting the environment variable EVENT_NOE‐
638 POLL, EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or
639 EVENT_NOSELECT, respectively.
640 By setting the environment variable EVENT_SHOW_METHOD, libevent
642 displays the kernel notification method that it uses.
643
645 pgbouncer(5) - man page of configuration settings descriptions
646 <https://www.pgbouncer.org/>
6481.14.0 PGBOUNCER(1)