1PGBOUNCER(1)                       Databases                      PGBOUNCER(1)
2
3
4

NAME

6       pgbouncer - lightweight connection pooler for PostgreSQL
7

SYNOPSIS

9              pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
10              pgbouncer -V|-h
11
12       On Windows, the options are:
13
14              pgbouncer.exe [-v][-u user] <pgbouncer.ini>
15              pgbouncer.exe -V|-h
16
17       Additional options for setting up a Windows service:
18
19              pgbouncer.exe --regservice   <pgbouncer.ini>
20              pgbouncer.exe --unregservice <pgbouncer.ini>
21

DESCRIPTION

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
28       The aim of pgbouncer is to lower the performance impact of opening  new
29       connections to PostgreSQL.
30
31       In  order  not to compromise transaction semantics for connection pool‐
32       ing, pgbouncer supports several types of pooling when rotating  connec‐
33       tions:
34
35       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
41       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
46       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
52       The  administration  interface  of  pgbouncer consists of some new SHOW
53       commands available when connected to a special “virtual”  database  pg‐
54       bouncer.
55

QUICK-START

57       Basic setup and usage is as follows.
58
59       1. Create a pgbouncer.ini file.  Details in pgbouncer(5).  Simple exam‐
60          ple:
61
62                   [databases]
63                   template1 = host=localhost port=5432 dbname=template1
64
65                   [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
74       2. Create a userlist.txt file that contains the users allowed in:
75
76                   "someuser" "same_password_as_in_server"
77
78       3. Launch pgbouncer:
79
80                   $ pgbouncer -d pgbouncer.ini
81
82       4. Have your application (or the psql client) connect to pgbouncer  in‐
83          stead of directly to the PostgreSQL server:
84
85                   $ psql -p 6432 -U someuser template1
86
87       5. Manage  pgbouncer  by connecting to the special administration data‐
88          base pgbouncer and issuing SHOW HELP; to begin:
89
90                   $ 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
103       6. If you made changes to the pgbouncer.ini file,  you  can  reload  it
104          with:
105
106                   pgbouncer=# RELOAD;
107

COMMAND LINE SWITCHES

109       -d, --daemon
110              Run  in the background.  Without it, the process will run in the
111              foreground.
112
113              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
117              Note: Does not work on Windows; pgbouncer need to run as service
118              there.
119
120       -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
128       -u USERNAME, --user=USERNAME
129              Switch to the given user on startup.
130
131       -v, --verbose
132              Increase verbosity.  Can be used multiple times.
133
134       -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
139       -V, --version
140              Show version.
141
142       -h, --help
143              Show short help.
144
145       --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
150       --unregservice
151              Win32: Unregister Windows service.
152

ADMIN CONSOLE

154       The console is available by connecting as normal to  the  database  pg‐
155       bouncer:
156
157              $ psql -p 6432 pgbouncer
158
159       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
163       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
167   Show commands
168       The SHOW commands output information.  Each command is described below.
169
170   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
174       database
175              Statistics are presented per database.
176
177       total_xact_count
178              Total number of SQL transactions pooled by pgbouncer.
179
180       total_query_count
181              Total number of SQL queries pooled by pgbouncer.
182
183       total_received
184              Total volume in bytes of network traffic received by pgbouncer.
185
186       total_sent
187              Total volume in bytes of network traffic sent by pgbouncer.
188
189       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
194       total_query_time
195              Total number of microseconds spent by  pgbouncer  when  actively
196              connected to PostgreSQL, executing queries.
197
198       total_wait_time
199              Time spent by clients waiting for a server, in microseconds.
200
201       avg_xact_count
202              Average transactions per second in last stat period.
203
204       avg_query_count
205              Average queries per second in last stat period.
206
207       avg_recv
208              Average received (from clients) bytes per second.
209
210       avg_sent
211              Average sent (to clients) bytes per second.
212
213       avg_xact_time
214              Average transaction duration, in microseconds.
215
216       avg_query_time
217              Average query duration, in microseconds.
218
219       avg_wait_time
220              Time spent by clients waiting for a server, in microseconds (av‐
221              erage per second).
222
223   SHOW STATS_TOTALS
224       Subset of SHOW STATS showing the total values (total_).
225
226   SHOW STATS_AVERAGES
227       Subset of SHOW STATS showing the average values (avg_).
228
229   SHOW TOTALS
230       Like SHOW STATS but aggregated across all databases.
231
232   SHOW SERVERS
233       type   S, for server.
234
235       user   User name pgbouncer uses to connect to server.
236
237       database
238              Database name.
239
240       state  State of the pgbouncer server connection, one of  active,  idle,
241              used, tested, new.
242
243       addr   IP address of PostgreSQL server.
244
245       port   Port of PostgreSQL server.
246
247       local_addr
248              Connection start address on local machine.
249
250       local_port
251              Connection start port on local machine.
252
253       connect_time
254              When the connection was made.
255
256       request_time
257              When last request was issued.
258
259       wait   Current waiting time in seconds.
260
261       wait_us
262              Microsecond part of the current waiting time.
263
264       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
269       ptr    Address  of internal object for this connection.  Used as unique
270              ID.
271
272       link   Address of client connection the server is paired with.
273
274       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
281       tls    A string with TLS connection information, or empty if not  using
282              TLS.
283
284   SHOW CLIENTS
285       type   C, for client.
286
287       user   Client connected user.
288
289       database
290              Database name.
291
292       state  State of the client connection, one of active or waiting.
293
294       addr   IP address of client.
295
296       port   Source port of client.
297
298       local_addr
299              Connection end address on local machine.
300
301       local_port
302              Connection end port on local machine.
303
304       connect_time
305              Timestamp of connect time.
306
307       request_time
308              Timestamp of latest client request.
309
310       wait   Current waiting time in seconds.
311
312       wait_us
313              Microsecond part of the current waiting time.
314
315       close_needed
316              not used for clients
317
318       ptr    Address  of internal object for this connection.  Used as unique
319              ID.
320
321       link   Address of server connection the client is paired with.
322
323       remote_pid
324              Process ID, in case client connects over Unix socket and OS sup‐
325              ports getting it.
326
327       tls    A  string with TLS connection information, or empty if not using
328              TLS.
329
330   SHOW POOLS
331       A new pool entry is made for each couple of (database, user).
332
333       database
334              Database name.
335
336       user   User name.
337
338       cl_active
339              Client connections that are linked to server connection and  can
340              process queries.
341
342       cl_waiting
343              Client connections that have sent queries but have not yet got a
344              server connection.
345
346       cl_cancel_req
347              Client connections that have not forwarded  query  cancellations
348              to the server yet.
349
350       sv_active
351              Server connections that are linked to a client.
352
353       sv_idle
354              Server  connections  that  are unused and immediately usable for
355              client queries.
356
357       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
362       sv_tested
363              Server connections that are currently running either  server_re‐
364              set_query or server_check_query.
365
366       sv_login
367              Server connections currently in the process of logging in.
368
369       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
376       maxwait_us
377              Microsecond part of the maximum waiting time.
378
379       pool_mode
380              The pooling mode in use.
381
382   SHOW LISTS
383       Show following internal information, in columns (not rows):
384
385       databases
386              Count of databases.
387
388       users  Count of users.
389
390       pools  Count of pools.
391
392       free_clients
393              Count of free clients.
394
395       used_clients
396              Count of used clients.
397
398       login_clients
399              Count of clients in login state.
400
401       free_servers
402              Count of free servers.
403
404       used_servers
405              Count of used servers.
406
407       dns_names
408              Count of DNS names in the cache.
409
410       dns_zones
411              Count of DNS zones in the cache.
412
413       dns_queries
414              Count of in-flight DNS queries.
415
416       dns_pending
417              not used
418
419   SHOW USERS
420       name   The user name
421
422       pool_mode
423              The  user’s  override  pool_mode, or NULL if the default will be
424              used instead.
425
426   SHOW DATABASES
427       name   Name of configured database entry.
428
429       host   Host pgbouncer connects to.
430
431       port   Port pgbouncer connects to.
432
433       database
434              Actual database name pgbouncer connects to.
435
436       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
441       pool_size
442              Maximum number of server connections.
443
444       min_pool_size
445              Minimum number of server connections.
446
447       reserve_pool
448              Maximum number of additional connections for this database.
449
450       pool_mode
451              The database’s override pool_mode, or NULL if the  default  will
452              be used instead.
453
454       max_connections
455              Maximum  number of allowed connections for this database, as set
456              by max_db_connections, either globally or per database.
457
458       current_connections
459              Current number of connections for this database.
460
461       paused 1 if this database is currently paused, else 0.
462
463       disabled
464              1 if this database is currently disabled, else 0.
465
466   SHOW FDS
467       Internal command - shows list of file descriptors in use with  internal
468       state attached to them.
469
470       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
475       This command also blocks the internal event loop, so it should  not  be
476       used while PgBouncer is in use.
477
478       fd     File descriptor numeric value.
479
480       task   One of pooler, client or server.
481
482       user   User of the connection using the FD.
483
484       database
485              Database of the connection using the FD.
486
487       addr   IP address of the connection using the FD, unix if a Unix socket
488              is used.
489
490       port   Port used by the connection using the FD.
491
492       cancel Cancel key for this connection.
493
494       link   fd for corresponding server/client.  NULL if idle.
495
496   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
501   SHOW CONFIG
502       Show the current configuration settings, one per row, with the  follow‐
503       ing columns:
504
505       key    Configuration variable name
506
507       value  Configuration value
508
509       default
510              Configuration default value
511
512       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
517   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
521   SHOW DNS_HOSTS
522       Show host names in DNS cache.
523
524       hostname
525              Host name.
526
527       ttl    How many seconds until next lookup.
528
529       addrs  Comma separated list of addresses.
530
531   SHOW DNS_ZONES
532       Show DNS zones in cache.
533
534       zonename
535              Zone name.
536
537       serial Current serial.
538
539       count  Host names belonging to this zone.
540
541   SHOW VERSION
542       Show the PgBouncer version string.
543
544   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
550       If database name is given, only that database will be paused.
551
552       New client connections to a paused database will wait until  RESUME  is
553       called.
554
555   DISABLE db
556       Reject all new client connections on the given database.
557
558   ENABLE db
559       Allow new client connections after a previous DISABLE command.
560
561   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
568       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
576       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
585   KILL db
586       Immediately drop all client and server connections on given database.
587
588       New client connections to a killed database will wait until  RESUME  is
589       called.
590
591   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
596       New  client  connections to a suspended database will wait until RESUME
597       is called.
598
599   RESUME [db]
600       Resume work from previous KILL, PAUSE, or SUSPEND command.
601
602   SHUTDOWN
603       The PgBouncer process will exit.
604
605   RELOAD
606       The PgBouncer process will reload its  configuration  file  and  update
607       changeable settings.
608
609       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
615   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
622   Other commands
623   SET key = arg
624       Changes a configuration setting (see also SHOW CONFIG).  For example:
625
626              SET log_connections = 1;
627              SET server_check_query = 'select 2';
628
629       (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
633   Signals
634       SIGHUP Reload  config.   Same as issuing the command RELOAD on the con‐
635              sole.
636
637       SIGINT Safe shutdown.  Same as issuing PAUSE and SHUTDOWN on  the  con‐
638              sole.
639
640       SIGTERM
641              Immediate shutdown.  Same as issuing SHUTDOWN on the console.
642
643       SIGUSR1
644              Same as issuing PAUSE on the console.
645
646       SIGUSR2
647              Same as issuing RESUME on the console.
648
649   Libevent settings
650       From the Libevent documentation:
651
652              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
657              By setting the environment variable EVENT_SHOW_METHOD,  libevent
658              displays the kernel notification method that it uses.
659

SEE ALSO

661       pgbouncer(5) - man page of configuration settings descriptions
662
663       <https://www.pgbouncer.org/>
664
665
666
6671.16.1                                                            PGBOUNCER(1)
Impressum