1PGBOUNCER(1) [FIXME: manual] 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 computers, the options are:
14 pgbouncer.exe [-v][-u user] <pgbouncer.ini>
16 pgbouncer.exe -V|-h
17 Additional options for setting up a Windows service:
20 pgbouncer.exe -regservice <pgbouncer.ini>
22 pgbouncer.exe -unregservice <pgbouncer.ini>
23
25 pgbouncer is a PostgreSQL connection pooler. Any target application can
26 be connected to pgbouncer as if it were a PostgreSQL server, and
27 pgbouncer will create a connection to the actual server, or it will
28 reuse one of its existing connections.
29 The aim of pgbouncer is to lower the performance impact of opening new
31 connections to PostgreSQL.
32 In order not to compromise transaction semantics for connection
34 pooling, pgbouncer supports several types of pooling when rotating
35 connections:
36 Session pooling
38 Most polite method. When client connects, a server connection will
39 be assigned to it for the whole duration the client stays
40 connected. When the client disconnects, the server connection will
41 be put back into the pool. This is the default method.
42 Transaction pooling
44 A server connection is assigned to client only during a
45 transaction. When PgBouncer notices that transaction is over, the
46 server connection will be put back into the pool.
47 Statement pooling
49 Most aggressive method. The server connection will be put back into
50 pool immediately after a query completes. Multi-statement
51 transactions are disallowed in this mode as they would break.
52 The administration interface of pgbouncer consists of some new SHOW
54 commands available when connected to a special virtual database
55 pgbouncer.
56
58 Basic setup and usage as following.
59 1. Create a pgbouncer.ini file. Details in pgbouncer(5). Simple
61 example:
62 [databases]
64 template1 = host=127.0.0.1 port=5432 dbname=template1
65 [pgbouncer]
67 listen_port = 6543
68 listen_addr = 127.0.0.1
69 auth_type = md5
70 auth_file = users.txt
71 logfile = pgbouncer.log
72 pidfile = pgbouncer.pid
73 admin_users = someuser
74 2. Create a users.txt file:
76 "someuser" "same_password_as_in_server"
78 3. Launch pgbouncer:
80 $ pgbouncer -d pgbouncer.ini
82 4. Have your application (or the psql client) connect to pgbouncer
84 instead of directly to PostgreSQL server.
85 $ psql -p 6543 -U someuser template1
87 5. Manage pgbouncer by connecting to the special administration
89 database pgbouncer and issuing show help; to begin:
90 $ psql -p 6543 -U someuser pgbouncer
92 pgbouncer=# show help;
93 NOTICE: Console usage
94 DETAIL:
95 SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION]
96 SET key = arg
97 RELOAD
98 PAUSE
99 SUSPEND
100 RESUME
101 SHUTDOWN
102 6. If you made changes to the pgbouncer.ini file, you can reload it
104 with:
105 pgbouncer=# RELOAD;
107
109 -d
110 Run in background. Without it the process will run in foreground.
111 Note: Does not work on Windows, pgbouncer need to run as service
112 there.
113 -R
115 Do an online restart. That means connecting to the running process,
116 loading the open sockets from it, and then using them. If there is
117 no active process, boot normally. Note: Does not work on Windows
118 machines.
119 -u user
121 Switch to the given user on startup.
122 -v
124 Increase verbosity. Can be used multiple times.
125 -q
127 Be quiet - do not log to stdout. Note this does not affect logging
128 verbosity, only that stdout is not to be used. For use in init.d
129 scripts.
130 -V
132 Show version.
133 -h
135 Show short help.
136 -regservice
138 Win32: Register pgbouncer to run as Windows service. The
139 service_name config parameter value is used as name to register
140 under.
141 -unregservice
143 Win32: Unregister Windows service.
144
146 The console is available by connecting as normal to the database
147 pgbouncer
148 $ psql -p 6543 pgbouncer
150 Only users listed in configuration parameters admin_users or
153 stats_users are allowed to login to the console. (Except when
154 auth_mode=any, then any user is allowed in as an admin.)
155 Additionally, the username pgbouncer is allowed to log in without
157 password, if the login comes via Unix socket and the client has same
158 Unix user uid as the running process.
159 SHOW COMMANDS
161 The SHOW commands output information. Each command is described below.
162 SHOW STATS;
164 Shows statistics.
165 database
167 Statistics are presented per database.
168 total_requests
170 Total number of SQL requests pooled by pgbouncer.
171 total_received
173 Total volume in bytes of network traffic received by pgbouncer.
174 total_sent
176 Total volume in bytes of network traffic sent by pgbouncer.
177 total_query_time
179 Total number of microseconds spent by pgbouncer when actively
180 connected to PostgreSQL.
181 avg_req
183 Average requests per second in last stat period.
184 avg_recv
186 Average received (from clients) bytes per second.
187 avg_sent
189 Average sent (to clients) bytes per second.
190 avg_query
192 Average query duration in microseconds.
193 SHOW SERVERS;
195 type
196 S, for server.
197 user
199 Username pgbouncer uses to connect to server.
200 database
202 Database name.
203 state
205 State of the pgbouncer server connection, one of active, used
206 or idle.
207 addr
209 IP address of PostgreSQL server.
210 port
212 Port of PostgreSQL server.
213 local_addr
215 Connection start address on local machine.
216 local_port
218 Connection start port on local machine.
219 connect_time
221 When the connection was made.
222 request_time
224 When last request was issued.
225 ptr
227 Address of internal object for this connection. Used as unique
228 ID.
229 link
231 Address of client connection the server is paired with.
232 SHOW CLIENTS;
234 type
235 C, for client.
236 user
238 Client connected user.
239 database
241 Database name.
242 state
244 State of the client connection, one of active, used, waiting or
245 idle.
246 addr
248 IP address of client.
249 port
251 Port client is connected to.
252 local_addr
254 Connection end address on local machine.
255 local_port
257 Connection end port on local machine.
258 connect_time
260 Timestamp of connect time.
261 request_time
263 Timestamp of latest client request.
264 ptr
266 Address of internal object for this connection. Used as unique
267 ID.
268 link
270 Address of server connection the client is paired with.
271 SHOW POOLS;
273 A new pool entry is made for each couple of (database, user).
274 database
276 Database name.
277 user
279 Username.
280 cl_active
282 Count of currently active client connections.
283 cl_waiting
285 Count of currently waiting client connections.
286 sv_active
288 Count of currently active server connections.
289 sv_idle
291 Count of currently idle server connections.
292 sv_used
294 Count of currently used server connections.
295 sv_tested
297 Count of currently tested server connections.
298 sv_login
300 Count of server connections currently logged in to PostgreSQL.
301 maxwait
303 How long the first (oldest) client in queue has waited, in
304 seconds. If this starts increasing, then the current pool of
305 servers does not handle requests quick enough. Reason may be
306 either overloaded server or just too small of a pool_size
307 setting.
308 SHOW LISTS;
310 Show following internal information, in columns (not rows):
311 databases
313 Count of databases.
314 users
316 Count of users.
317 pools
319 Count of pools.
320 free_clients
322 Count of free clients.
323 used_clients
325 Count of used clients.
326 login_clients
328 Count of clients in login state.
329 free_servers
331 Count of free servers.
332 used_servers
334 Count of used servers.
335 SHOW USERS;
337 Shows one line per user, under the name column name.
338 SHOW DATABASES;
340 name
341 Name of configured database entry.
342 host
344 Host pgbouncer connects to.
345 port
347 Port pgbouncer connects to.
348 database
350 Actual database name pgbouncer connects to.
351 force_user
353 When user is part of the connection string, the connection
354 between pgbouncer and PostgreSQL is forced to the given user,
355 whatever the client user.
356 pool_size
358 Maximum number of server connections.
359 SHOW FDS;
361 Shows list of fds in use. When the connected user has username
362 "pgbouncer", connects through Unix socket and has same UID as
363 running process, the actual fds are passed over the connection.
364 This mechanism is used to do an online restart. Note: This does not
365 work on Windows machines.
366 fd
368 File descriptor numeric value.
369 task
371 One of pooler, client or server.
372 user
374 User of the connection using the FD.
375 database
377 Database of the connection using the FD.
378 addr
380 IP address of the connection using the FD, unix if a unix
381 socket is used.
382 port
384 Port used by the connection using the FD.
385 cancel
387 Cancel key for this connection.
388 link
390 fd for corresponding server/client. NULL if idle.
391 SHOW CONFIG;
393 Show the current configuration settings, one per row, with
394 following columns:
395 key
397 Configuration variable name
398 value
400 Configuration value
401 changeable
403 Either yes or no, shows if the variable can be changed while
404 running. If no, the variable can be changed only boot-time.
405 PROCESS CONTROLLING COMMANDS
407 PAUSE;
408 PgBouncer tries to disconnect from all servers, first waiting for
409 all queries to complete. The command will not return before all
410 queries are finished. To be used at the time of database restart.
411 SUSPEND;
413 All socket buffers are flushed and PgBouncer stops listening for
414 data on them. The command will not return before all buffers are
415 empty. To be used at the time of PgBouncer online reboot.
416 RESUME;
418 Resume work from previous PAUSE or SUSPEND command.
419 SHUTDOWN;
421 The PgBouncer process will exit.
422 RELOAD;
424 The PgBouncer process will reload its configuration file and update
425 changeable settings.
426 SIGNALS
428 SIGHUP
429 Reload config. Same as issuing command RELOAD; on console.
430 SIGINT
432 Safe shutdown. Same as issuing PAUSE; and SHUTDOWN; on console.
433 SIGTERM
435 Immediate shutdown. Same as issuing SHUTDOWN; on console.
436 LIBEVENT SETTINGS
438 From libevent docs:
439 It is possible to disable support for epoll, kqueue, devpoll, poll
441 or select by setting the environment variable EVENT_NOEPOLL,
442 EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or EVENT_NOSELECT,
443 respectively.
444 By setting the environment variable EVENT_SHOW_METHOD, libevent
446 displays the kernel notification method that it uses.
447
449 pgbouncer(5) - manpage of configuration settings descriptions.
450 http://pgbouncer.projects.postgresql.org/doc/
452 https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
454[FIXME: source] 01/15/2010 PGBOUNCER(1)