1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
|
pgbouncer
#########
Synopsis
========
::
pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
pgbouncer -V|-h
On Windows computers, the options are::
pgbouncer.exe [-v][-u user] <pgbouncer.ini>
pgbouncer.exe -V|-h
Additional options for setting up a Windows service::
pgbouncer.exe --regservice <pgbouncer.ini>
pgbouncer.exe --unregservice <pgbouncer.ini>
DESCRIPTION
===========
**pgbouncer** is a PostgreSQL connection pooler. Any target application
can be connected to **pgbouncer** as if it were a PostgreSQL server,
and **pgbouncer** will create a connection to the actual server, or it
will reuse one of its existing connections.
The aim of **pgbouncer** is to lower the performance impact of opening
new connections to PostgreSQL.
In order not to compromise transaction semantics for connection
pooling, **pgbouncer** supports several types of pooling when
rotating connections:
Session pooling
Most polite method. When client connects, a server connection will
be assigned to it for the whole duration the client stays connected. When
the client disconnects, the server connection will be put back into the pool.
This is the default method.
Transaction pooling
A server connection is assigned to client only during a transaction.
When PgBouncer notices that transaction is over, the server connection
will be put back into the pool.
Statement pooling
Most aggressive method. The server connection will be put back into
pool immediately after a query completes. Multi-statement
transactions are disallowed in this mode as they would break.
The administration interface of **pgbouncer** consists of some new
``SHOW`` commands available when connected to a special 'virtual'
database **pgbouncer**.
Quick-start
===========
Basic setup and usage as following.
1. Create a pgbouncer.ini file. Details in **pgbouncer(5)**. Simple example::
[databases]
template1 = host=127.0.0.1 port=5432 dbname=template1
[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
2. Create ``users.txt`` file that contains users allowed in::
"someuser" "same_password_as_in_server"
3. Launch **pgbouncer**::
$ pgbouncer -d pgbouncer.ini
4. Have your application (or the **psql** client) connect to
**pgbouncer** instead of directly to PostgreSQL server::
$ psql -p 6543 -U someuser template1
5. Manage **pgbouncer** by connecting to the special administration
database **pgbouncer** and issuing ``show help;`` to begin::
$ psql -p 6543 -U someuser pgbouncer
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION]
SET key = arg
RELOAD
PAUSE
SUSPEND
RESUME
SHUTDOWN
6. If you made changes to the pgbouncer.ini file, you can reload it with::
pgbouncer=# RELOAD;
Command line switches
=====================
-d
Run in background. Without it the process will run in foreground.
Note: Does not work on Windows, **pgbouncer** need to run as service there.
-R
Do an online restart. That means connecting to the running process,
loading the open sockets from it, and then using them. If there
is no active process, boot normally.
Note: Works only if OS supports Unix sockets and the `unix_socket_dir`
is not disabled in config. Does not work on Windows machines.
Does not work with TLS connections, they are dropped.
-u user
Switch to the given user on startup.
-v
Increase verbosity. Can be used multiple times.
-q
Be quiet - do not log to stdout. Note this does not affect
logging verbosity, only that stdout is not to be used.
For use in init.d scripts.
-V
Show version.
-h
Show short help.
--regservice
Win32: Register pgbouncer to run as Windows service. The **service_name**
config parameter value is used as name to register under.
--unregservice
Win32: Unregister Windows service.
Admin console
=============
The console is available by connecting as normal to the
database **pgbouncer**::
$ psql -p 6543 pgbouncer
Only users listed in configuration parameters **admin_users** or **stats_users**
are allowed to login to the console. (Except when `auth_mode=any`, then
any user is allowed in as a stats_user.)
Additionally, the username **pgbouncer** is allowed to log in without password,
if the login comes via Unix socket and the client has same Unix user uid
as the running process.
Show commands
~~~~~~~~~~~~~
The **SHOW** commands output information. Each command is described below.
SHOW STATS;
-----------
Shows statistics.
database
Statistics are presented per database.
total_requests
Total number of SQL requests pooled by **pgbouncer**.
total_received
Total volume in bytes of network traffic received by **pgbouncer**.
total_sent
Total volume in bytes of network traffic sent by **pgbouncer**.
total_query_time
Total number of microseconds spent by **pgbouncer** when actively
connected to PostgreSQL.
avg_req
Average requests per second in last stat period.
avg_recv
Average received (from clients) bytes per second.
avg_sent
Average sent (to clients) bytes per second.
avg_query
Average query duration in microseconds.
SHOW SERVERS;
-------------
type
S, for server.
user
Username **pgbouncer** uses to connect to server.
database
Database name.
state
State of the pgbouncer server connection, one of **active**, **used** or
**idle**.
addr
IP address of PostgreSQL server.
port
Port of PostgreSQL server.
local_addr
Connection start address on local machine.
local_port
Connection start port on local machine.
connect_time
When the connection was made.
request_time
When last request was issued.
ptr
Address of internal object for this connection.
Used as unique ID.
link
Address of client connection the server is paired with.
remote_pid
Pid of backend server process. In case connection is made over
unix socket and OS supports getting process ID info, it's
OS pid. Otherwise it's extracted from cancel packet server sent,
which should be PID in case server is Postgres, but it's a random
number in case server it another PgBouncer.
SHOW CLIENTS;
-------------
type
C, for client.
user
Client connected user.
database
Database name.
state
State of the client connection, one of **active**, **used**, **waiting**
or **idle**.
addr
IP address of client.
port
Port client is connected to.
local_addr
Connection end address on local machine.
local_port
Connection end port on local machine.
connect_time
Timestamp of connect time.
request_time
Timestamp of latest client request.
ptr
Address of internal object for this connection.
Used as unique ID.
link
Address of server connection the client is paired with.
remote_pid
Process ID, in case client connects over UNIX socket
and OS supports getting it.
SHOW POOLS;
-----------
A new pool entry is made for each couple of (database, user).
database
Database name.
user
User name.
cl_active
Client connections that are linked to server connection and can process queries.
cl_waiting
Client connections have sent queries but have not yet got a server connection.
sv_active
Server connections that linked to client.
sv_idle
Server connections that unused and immediately usable for client queries.
sv_used
Server connections that have been idle more than `server_check_delay`,
so they needs `server_check_query` to run on it before it can be used.
sv_tested
Server connections that are currently running either `server_reset_query`
or `server_check_query`.
sv_login
Server connections currently in logging in process.
maxwait
How long the first (oldest) client in queue has waited, in seconds.
If this starts increasing, then the current pool of servers does
not handle requests quick enough. Reason may be either overloaded
server or just too small of a **pool_size** setting.
pool_mode
The pooling mode in use.
SHOW LISTS;
-----------
Show following internal information, in columns (not rows):
databases
Count of databases.
users
Count of users.
pools
Count of pools.
free_clients
Count of free clients.
used_clients
Count of used clients.
login_clients
Count of clients in **login** state.
free_servers
Count of free servers.
used_servers
Count of used servers.
SHOW USERS;
-----------
name
The user name
pool_mode
The user's override pool_mode, or NULL if the default will be used instead.
SHOW DATABASES;
---------------
name
Name of configured database entry.
host
Host pgbouncer connects to.
port
Port pgbouncer connects to.
database
Actual database name pgbouncer connects to.
force_user
When user is part of the connection string, the connection between
pgbouncer and PostgreSQL is forced to the given user, whatever the
client user.
pool_size
Maximum number of server connections.
pool_mode
The database's override pool_mode, or NULL if the default will be used instead.
SHOW FDS;
---------
Internal command - shows list of fds in use with internal state attached to them.
When the connected user has username "pgbouncer", connects through Unix socket
and has same UID as running process, the actual fds are passed over the connection.
This mechanism is used to do an online restart.
Note: This does not work on Windows machines.
This command also blocks internal event loop, so it should not be used
while PgBouncer is in use.
fd
File descriptor numeric value.
task
One of **pooler**, **client** or **server**.
user
User of the connection using the FD.
database
Database of the connection using the FD.
addr
IP address of the connection using the FD, **unix** if a unix socket
is used.
port
Port used by the connection using the FD.
cancel
Cancel key for this connection.
link
fd for corresponding server/client. NULL if idle.
SHOW CONFIG;
------------
Show the current configuration settings, one per row, with following
columns:
key
Configuration variable name
value
Configuration value
changeable
Either **yes** or **no**, shows if the variable can be changed while running.
If **no**, the variable can be changed only boot-time.
SHOW DNS_HOSTS;
---------------
Show hostnames in DNS cache.
hostname
Host name.
ttl
How meny seconds until next lookup.
addrs
Comma separated list of addresses.
SHOW DNS_ZONES
--------------
Show DNS zones in cache.
zonename
Zone name.
serial
Current serial.
count
Hostnames belonging to this zone.
Process controlling commands
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PAUSE [db];
-----------
PgBouncer tries to disconnect from all servers, first waiting for all queries
to complete. The command will not return before all queries are finished. To be used
at the time of database restart.
If database name is given, only that database will be paused.
DISABLE db;
-----------
Reject all new client connections on the given database.
ENABLE db;
----------
Allow new client connections after a previous **DISABLE** command.
KILL db;
--------
Immediately drop all client and server connections on given database.
SUSPEND;
--------
All socket buffers are flushed and PgBouncer stops listening for data on them.
The command will not return before all buffers are empty. To be used at the time
of PgBouncer online reboot.
RESUME [db];
------------
Resume work from previous **PAUSE** or **SUSPEND** command.
SHUTDOWN;
---------
The PgBouncer process will exit.
RELOAD;
-------
The PgBouncer process will reload its configuration file and update
changeable settings.
Signals
~~~~~~~
SIGHUP
Reload config. Same as issuing command **RELOAD;** on console.
SIGINT
Safe shutdown. Same as issuing **PAUSE;** and **SHUTDOWN;** on console.
SIGTERM
Immediate shutdown. Same as issuing **SHUTDOWN;** on console.
Libevent settings
~~~~~~~~~~~~~~~~~
From libevent docs::
It is possible to disable support for epoll, kqueue, devpoll, poll
or select by setting the environment variable EVENT_NOEPOLL,
EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or EVENT_NOSELECT,
respectively.
By setting the environment variable EVENT_SHOW_METHOD, libevent
displays the kernel notification method that it uses.
See also
========
pgbouncer(5) - manpage of configuration settings descriptions.
https://pgbouncer.github.io/
https://wiki.postgresql.org/wiki/PgBouncer
|