1PGBOUNCER(5) [FIXME: manual] PGBOUNCER(5)
2
6 pgbouncer - Lightweight connection pooler for PostgreSQL.
7
9 [databases]
10 db = ...
11 [pgbouncer]
13 ...
14
16 Config file is in "ini" format. Section names are between " and ".
17 Lines starting with ";" or "" are taken as comments and ignored. The
18 characters ";" and "" are not recognized when they appear later in the
19 line.
20
22 Generic settings
23 logfile
24 Specifies log file. Log file is kept open so after rotation kill
25 -HUP or on console RELOAD; should be done. Note: On Windows
26 machines, the service must be stopped and started.
27 Default: not set.
29 pidfile
31 Specifies the pid file. Without a pidfile, daemonization is not
32 allowed.
33 Default: not set.
35 listen_addr
37 Specifies IPv4 address, where to listen for TCP connections. You
38 may also use * meaning "listen on all addresses". When not set,
39 only Unix socket connections are allowed.
40 Default: not set
42 listen_port
44 Which port to listen on. Applies to both TCP and Unix sockets.
45 Default: 6432
47 unix_socket_dir
49 Specifies location for Unix sockets. Applies to both listening
50 socket and server connections. If set to an empty string, Unix
51 sockets are disabled. Note: Not supported on Windows machines.
52 Default: /tmp
54 user
56 If set, specifies the Unix user to change to after startup. Works
57 only if PgBouncer is started as root or if user is the same as the
58 current user. Note: Not supported on Windows machines.
59 Default: not set
61 auth_file
63 The name of the file to load user names and passwords from. The
64 file format is the same as the PostgreSQL pg_auth/pg_pwd file, so
65 this setting can be pointed directly to one of those backend files.
66 Default: not set.
68 auth_type
70 How to authenticate users.
71 md5: Use MD5-based password check. auth_file may contain both
73 MD5-encrypted or plain-text passwords. This is the default
74 authentication method.
75 crypt
77 Use crypt(3) based password check. auth_file must contain
78 plain-text passwords.
79 plain
81 Clear-text password is sent over wire.
82 trust
84 No authentication is done. Username must still exist in
85 auth_file.
86 any
88 Like the trust method, but the username given is ignored.
89 Requires that all databases are configured to log in as
90 specific user. Additionally, the console database allows any
91 user to log in as admin.
92 pool_mode
94 Specifies when a server connection can be reused by other clients.
95 session
97 Server is released back to pool after client disconnects.
98 Default.
99 transaction
101 Server is released back to pool after transaction finishes.
102 statement
104 Server is released back to pool after query finishes. Long
105 transactions spanning multiple statements are disallowed in
106 this mode.
107 max_client_conn
109 Maximum number of client connections allowed. When increased then
110 the file descriptor limits should also be increased. Note that
111 actual number of file descriptors used is more than
112 max_client_conn. Theoretical maximum used is:
113 max_client_conn + (max_pool_size * total_databases * total_users)
115 if each user connects under its own username to server. If a
118 database user is specified in connect string (all users connect
119 under same username), the theoretical maximum is:
120 max_client_conn + (max_pool_size * total_databases)
122 The theoretical maximum should be never reached, unless somebody
125 deliberately crafts special load for it. Still, it means you should
126 set the number of file descriptors to a safely high number.
127 Search for ulimit in your favourite shell man page. Note: ulimit
129 does not apply in a Windows environment.
130 Default: 100
132 default_pool_size
134 How many server connections to allow per user/database pair. Can be
135 overriden in the per-database configuration.
136 Default: 20
138 reserve_pool_size
140 How many additional connections to allow to a pool. 0 disables.
141 Default: 0 (disabled)
143 reserve_pool_timeout
145 If a client has not been serviced in this many seconds, pgbouncer
146 enables use of additional connections from reserve pool. 0
147 disables.
148 Default: 5
150 server_round_robin
152 By default, pgbouncer reuses server connections in LIFO (last-in,
153 first-out) manner, so that few connections get the most load. This
154 gives best performance if you have a single server serving a
155 database. But if there is TCP round-robin behind a database IP,
156 then it is better if pgbouncer also uses connections in that
157 manner, thus achieving uniform load.
158 Default: 0
160 ignore_startup_parameters
162 By default, PgBouncer allows only parameters it can keep track of
163 in startup packets - client_encoding, datestyle, timezone and
164 standard_conforming_strings.
165 All others parameters will raise an error. To allow others
167 parameters, they can be specified here, so that pgbouncer knows
168 that they are handled by admin and it can ignore them.
169 Default: empty
171 Log settings
173 syslog
174 Toggles syslog on/off As for windows environment, eventlog is used
175 instead.
176 Default: 0
178 syslog_facility
180 Under what facility to send logs to syslog. Possibilities: auth,
181 authpriv, daemon, user, local0-7
182 Default: daemon
184 log_connections
186 Log successful logins.
187 Default: 1
189 log_disconnections
191 Log disconnections with reasons.
192 Default: 1
194 log_pooler_errors
196 Log error messages pooler sends to clients.
197 Default: 1
199 Console access control
201 admin_users
202 Comma-separated list of database users that are allowed to connect
203 and run all commands on console. Ignored when auth_mode=any, in
204 which case any username is allowed in as admin.
205 Default: empty
207 stats_users
209 Comma-separated list of database users that are allowed to connect
210 and run read-only queries on console. Thats means all SHOW commands
211 except SHOW FDS.
212 Default: empty.
214 Connection sanity checks, timeouts
216 server_reset_query
217 Query sent to server on connection release, before making it
218 available to other clients. At that moment no transaction is in
219 progress so it should not include ABORT or ROLLBACK.
220 Good choice for Postgres 8.2 and below is:
222 server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;
224 for 8.3 and above its enough to do:
227 server_reset_query = DISCARD ALL;
229 server_check_delay
231 How long to keep released connections available for immidiate
232 re-use, without running sanity-check queries on it. If 0 then the
233 query is ran always.
234 Default: 30
236 server_check_query
238 Simple do-nothing query to check if the server connection is alive.
239 If an empty string, then sanity checking is disabled.
241 Default: SELECT 1;
243 server_lifetime
245 The pooler will try to close server connections that have been
246 connected longer than this. Setting it to 0 means the connection is
247 to be used only once, then closed. [seconds]
248 Default: 3600
250 server_idle_timeout
252 If a server connection has been idle more than this many seconds,
253 and there are too many connections in the pool, this one can be
254 dropped. [seconds]
255 Default: 600
257 server_connect_timeout
259 If connection and login won’t finish in this amount of time, the
260 connection will be closed. [seconds]
261 Default: 15
263 server_login_retry
265 If login failed, because of failure from connect() or
266 authentication that pooler waits this much before retrying to
267 connect. [seconds]
268 Default: 15
270 client_login_timeout
272 If a client connects but does not manage to login in this amount of
273 time, it will be disconnected. Mainly needed to avoid dead
274 connections stalling SUSPEND and thus online restart. [seconds]
275 Default: 60
277 autodb_idle_timeout
279 If the automatically created (via "*") database pools have been
280 unused this many seconds, they are freed. The negative aspect of
281 that is that their statistics are also forgotten. [seconds]
282 Default: 3600
284 Dangerous timeouts
286 Setting following timeouts cause unexpected errors.
287 query_timeout
289 Queries running longer than that are canceled. This should be used
290 only with slightly smaller server-side statement_timeout, to apply
291 only for network problems. [seconds]
292 Default: 0 (disabled)
294 query_wait_timeout
296 Maximum time queries are allowed to spend waiting for execution. If
297 the query is not assigned to a server during that time, the client
298 is disconnected. This is used to prevent unresponsive servers from
299 grabbing up connections. [seconds]
300 Default: 0 (disabled)
302 client_idle_timeout
304 Client connections idling longer than this many seconds are closed.
305 This should be larger than the client-side connection lifetime
306 settings, and only used for network problems. [seconds]
307 Default: 0 (disabled)
309 Low-level network settings
311 pkt_buf
312 Internal buffer size for packets. Affects size of TCP packets sent
313 and general memory usage. Actual libpq packets can be larger than
314 this so, no need to set it large.
315 Default: 2048
317 listen_backlog
319 Backlog argument for listen(2). Determines how many new unanswered
320 connection attempts are kept in queue. When queue is full, futher
321 new connections are dropped.
322 Default: 128
324 sbuf_loopcnt
326 How many times to process data on one connection, before
327 proceeding. Without this limit, one connection with a big resultset
328 can stall PgBouncer for a long time. One loop processes one pkt_buf
329 amount of data. 0 means no limit.
330 Default: 5
332 tcp_defer_accept
334 For details on this and other tcp options, please see man 7 tcp.
335 Default: 45 on Linux, otherwise 0
337 tcp_socket_buffer
339 Default: not set
340 tcp_keepalive
342 Default: not set
343 tcp_keepcnt
345 Default: not set
346 tcp_keepidle
348 Default: not set
349 tcp_keepintvl
351 Default: not set
352
354 This contains key=value pairs where key will be taken as a database
355 name and value as a libpq connect-string style list of key=value pairs.
356 As actual libpq is not used, so not all features from libpq can be used
357 (service=, .pgpass).
358 Database name can contain characters [0-9A-Za-z_.-] without quoting.
360 Names that contain other chars need to be quoted with standard SQL
361 ident quoting: double quotes where "" is taken as single quote.
362 "\*" acts as fallback database: if the exact name does not exist, its
364 value is taken as connect string for requested database. Such
365 automatically created database entries are cleaned up if they stay idle
366 longer then the time specified in autodb_idle_timeout parameter.
367 Location parameters
369 dbname
370 Destination database name.
371 Default: same as client-side database name.
373 host
375 IP address to connect to.
376 Default: not set, meaning to use a Unix socket.
378 port
380 Default: 5432
381 user, password
383 If user= is set, all connections to the destination database will
384 be done with the specified user, meaning that there will be only
385 one pool for this database.
386 Otherwise PgBouncer tries to log into the destination database with
388 client username, meaning that there will be one pool per user.
389 Pool configuration
391 pool_size
392 Set maximum size of pools for this database. If not set, the
393 default_pool_size is used.
394 connect_query
396 Query to be executed after a connection is established, but before
397 allowingf the connection to be used by any clients. If the query
398 raises errors, they are logged but ignored otherwise.
399 Extra parameters
401 They allow setting default parameters on server connection.
402 Note that since version 1.1 PgBouncer tracks client changes for their
404 values, so their use in pgbouncer.ini is deprecated now.
405 client_encoding
407 Ask specific client_encoding from server.
408 datestyle
410 Ask specific datestyle from server.
411 timezone
413 Ask specific timezone from server.
414
416 PgBouncer needs its own user database. The users are loaded from a text
417 file that should be in same format as PostgreSQL’s pg_auth/pg_pwd file.
418 "username1" "password" ...
420 "username2" "md5abcdef012342345" ...
421 There shoud be at least 2 fields, surrounded by double quotes. The
424 first field is the username and the second is either a plain-text or a
425 MD5-hashed password. PgBouncer ignores the rest of the line.
426 This file format allows you to directly use the existing PostgreSQL
428 authentication files in the Postgres data directory.
429
431 Minimal config
432 [databases]
433 template1 = host=127.0.0.1 dbname=template1
434 [pgbouncer]
436 pool_mode = session
437 listen_port = 6543
438 listen_addr = 127.0.0.1
439 auth_type = md5
440 auth_file = users.txt
441 logfile = pgbouncer.log
442 pidfile = pgbouncer.pid
443 admin_users = someuser
444 stats_users = stat_collector
445 Database defaults
447 [databases]
448 ; foodb over unix socket
450 foodb =
451 ; redirect bardb to bazdb on localhost
453 bardb = host=127.0.0.1 dbname=bazdb
454 ; access to destination database will go with single user
456 forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
457
459 pgbouncer(1) - manpage for general usage, console commands.
460 http://pgbouncer.projects.postgresql.org/doc/
462 https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
464[FIXME: source] 04/23/2010 PGBOUNCER(5)