1PGBOUNCER.INI(5) Databases PGBOUNCER.INI(5)
2
6 pgbouncer.ini - configuration file for pgbouncer
7
9 The configuration file is in "ini" format. Section names are between
10 "[" and "]". Lines starting with ";" or "#" are taken as comments and
11 ignored. The characters ";" and "#" are not recognized when they appear
12 later in the line.
13
15 logfile
16 Specifies log file. Log file is kept open so after rotation kill -HUP
17 or on console RELOAD; should be done. Note: On Windows machines, the
18 service must be stopped and started.
19 Default: not set.
21 pidfile
23 Specifies the pid file. Without a pidfile, daemonization is not
24 allowed.
25 Default: not set.
27 listen_addr
29 Specifies list of addresses, where to listen for TCP connections. You
30 may also use * meaning "listen on all addresses". When not set, only
31 Unix socket connections are allowed.
32 Addresses can be specified numerically (IPv4/IPv6) or by name.
34 Default: not set
36 listen_port
38 Which port to listen on. Applies to both TCP and Unix sockets.
39 Default: 6432
41 unix_socket_dir
43 Specifies location for Unix sockets. Applies to both listening socket
44 and server connections. If set to an empty string, Unix sockets are
45 disabled. Required for online reboot (-R) to work. Note: Not sup‐
46 ported on Windows machines.
47 Default: /tmp
49 unix_socket_mode
51 File system mode for Unix socket.
52 Default: 0777
54 unix_socket_group
56 Group name to use for Unix socket.
57 Default: not set
59 user
61 If set, specifies the Unix user to change to after startup. Works only
62 if PgBouncer is started as root or if it's already running as given
63 user.
64 Note: Not supported on Windows machines.
66 Default: not set
68 auth_file
70 The name of the file to load user names and passwords from. The file
71 format is the same as the PostgreSQL 8.x pg_auth/pg_pwd file, so this
72 setting can be pointed directly to one of those backend files. Since
73 version 9.0, PostgreSQL does not use such text file, so it must be gen‐
74 erated manually. See section Authentication file format below about
75 details.
76 Default: not set.
78 auth_hba_file
80 HBA configuration file to use when auth_type is hba. Supported from
81 version 1.7 onwards.
82 Default: not set
84 auth_type
86 How to authenticate users.
87 pam PAM is used to authenticate users, auth_file is ignored. This
89 method is not compatible with databases using auth_user option.
90 Service name reported to PAM is "pgbouncer". Also, pam is still
91 not supported in HBA configuration file.
92 hba Actual auth type is loaded from auth_hba_file. This allows dif‐
94 ferent authentication methods different access paths. Example:
95 connection over Unix socket use peer auth method, connection
96 over TCP must use TLS. Supported from version 1.7 onwards.
97 cert Client must connect over TLS connection with valid client cert.
99 Username is then taken from CommonName field from certificate.
100 md5 Use MD5-based password check. auth_file may contain both
102 MD5-encrypted or plain-text passwords. This is the default
103 authentication method.
104 plain Clear-text password is sent over wire. Deprecated.
106 trust No authentication is done. Username must still exist in
108 auth_file.
109 any Like the trust method, but the username given is ignored.
111 Requires that all databases are configured to log in as specific
112 user. Additionally, the console database allows any user to log
113 in as admin.
114 auth_query
116 Query to load user's password from database.
117 Direct access to pg_shadow requires admin rights. It's preferable to
119 use non-admin user that calls SECURITY DEFINER function instead.
120 Note that the query is run inside target database, so if a function is
122 used it needs to be installed into each database.
123 Default: SELECT usename, passwd FROM pg_shadow WHERE usename=$1
125 auth_user
127 If auth_user is set, any user not specified in auth_file will be
128 queried through the auth_query query from pg_shadow in the database
129 using auth_user. Auth_user's password will be taken from auth_file.
130 Direct access to pg_shadow requires admin rights. It's preferable to
132 use non-admin user that calls SECURITY DEFINER function instead.
133 Default: not set.
135 pool_mode
137 Specifies when a server connection can be reused by other clients.
138 session
140 Server is released back to pool after client disconnects.
141 Default.
142 transaction
144 Server is released back to pool after transaction finishes.
145 statement
147 Server is released back to pool after query finishes. Long
148 transactions spanning multiple statements are disallowed in this
149 mode.
150 max_client_conn
152 Maximum number of client connections allowed. When increased then the
153 file descriptor limits should also be increased. Note that actual num‐
154 ber of file descriptors used is more than max_client_conn. Theoretical
155 maximum used is:
156 max_client_conn + (max_pool_size * total_databases * total_users)
158 if each user connects under its own username to server. If a database
160 user is specified in connect string (all users connect under same user‐
161 name), the theoretical maximum is:
162 max_client_conn + (max_pool_size * total_databases)
164 The theoretical maximum should be never reached, unless somebody delib‐
166 erately crafts special load for it. Still, it means you should set the
167 number of file descriptors to a safely high number.
168 Search for ulimit in your favorite shell man page. Note: ulimit does
170 not apply in a Windows environment.
171 Default: 100
173 default_pool_size
175 How many server connections to allow per user/database pair. Can be
176 overridden in the per-database configuration.
177 Default: 20
179 min_pool_size
181 Add more server connections to pool if below this number. Improves
182 behavior when usual load comes suddenly back after period of total
183 inactivity.
184 Default: 0 (disabled)
186 reserve_pool_size
188 How many additional connections to allow to a pool. 0 disables.
189 Default: 0 (disabled)
191 reserve_pool_timeout
193 If a client has not been serviced in this many seconds, pgbouncer
194 enables use of additional connections from reserve pool. 0 disables.
195 Default: 5.0
197 max_db_connections
199 Do not allow more than this many connections per-database (regardless
200 of pool - i.e. user). It should be noted that when you hit the limit,
201 closing a client connection to one pool will not immediately allow a
202 server connection to be established for another pool, because the
203 server connection for the first pool is still open. Once the server
204 connection closes (due to idle timeout), a new server connection will
205 immediately be opened for the waiting pool.
206 Default: unlimited
208 max_user_connections
210 Do not allow more than this many connections per-user (regardless of
211 pool - i.e. user). It should be noted that when you hit the limit,
212 closing a client connection to one pool will not immediately allow a
213 server connection to be established for another pool, because the
214 server connection for the first pool is still open. Once the server
215 connection closes (due to idle timeout), a new server connection will
216 immediately be opened for the waiting pool.
217 server_round_robin
219 By default, pgbouncer reuses server connections in LIFO (last-in,
220 first-out) manner, so that few connections get the most load. This
221 gives best performance if you have a single server serving a database.
222 But if there is TCP round-robin behind a database IP, then it is better
223 if pgbouncer also uses connections in that manner, thus achieving uni‐
224 form load.
225 Default: 0
227 ignore_startup_parameters
229 By default, PgBouncer allows only parameters it can keep track of in
230 startup packets - client_encoding, datestyle, timezone and stan‐
231 dard_conforming_strings.
232 All others parameters will raise an error. To allow others parameters,
234 they can be specified here, so that pgbouncer knows that they are han‐
235 dled by admin and it can ignore them.
236 Default: empty
238 disable_pqexec
240 Disable Simple Query protocol (PQexec). Unlike Extended Query proto‐
241 col, Simple Query allows multiple queries in one packet, which allows
242 some classes of SQL-injection attacks. Disabling it can improve secu‐
243 rity. Obviously this means only clients that exclusively use Extended
244 Query protocol will stay working.
245 Default: 0
247 application_name_add_host
249 Add the client host address and port to the application name setting
250 set on connection start. This helps in identifying the source of bad
251 queries etc. This logic applies only on start of connection, if appli‐
252 cation_name is later changed with SET, pgbouncer does not change it
253 again.
254 Default: 0
256 conffile
258 Show location of current config file. Changing it will make PgBouncer
259 use another config file for next RELOAD / SIGHUP.
260 Default: file from command line.
262 service_name
264 Used on win32 service registration.
265 Default: pgbouncer
267 job_name
269 Alias for service_name.
270
272 syslog
273 Toggles syslog on/off As for windows environment, eventlog is used
274 instead.
275 Default: 0
277 syslog_ident
279 Under what name to send logs to syslog.
280 Default: pgbouncer (program name)
282 syslog_facility
284 Under what facility to send logs to syslog. Possibilities: auth, auth‐
285 priv, daemon, user, local0-7.
286 Default: daemon
288 log_connections
290 Log successful logins.
291 Default: 1
293 log_disconnections
295 Log disconnections with reasons.
296 Default: 1
298 log_pooler_errors
300 Log error messages pooler sends to clients.
301 Default: 1
303 stats_period
305 Period for writing aggregated stats into log.
306 Default: 60
308 verbose
310 Increase verbosity. Mirrors "-v" switch on command line. Using "-v
311 -v" on command line is same as verbose=2 in config.
312 Default: 0
314
316 admin_users
317 Comma-separated list of database users that are allowed to connect and
318 run all commands on console. Ignored when auth_type is any, in which
319 case any username is allowed in as admin.
320 Default: empty
322 stats_users
324 Comma-separated list of database users that are allowed to connect and
325 run read-only queries on console. That means all SHOW commands except
326 SHOW FDS.
327 Default: empty.
329
331 server_reset_query
332 Query sent to server on connection release, before making it available
333 to other clients. At that moment no transaction is in progress so it
334 should not include ABORT or ROLLBACK.
335 The query is supposed to clean any changes made to database session so
337 that next client gets connection in well-defined state. Default is
338 DISCARD ALL which cleans everything, but that leaves next client no
339 pre-cached state. It can be made lighter, e.g. DEALLOCATE ALL to just
340 drop prepared statements, if application does not break when some state
341 is kept around.
342 When transaction pooling is used, the server_reset_query is not used,
344 as clients must not use any session-based features as each transaction
345 ends up in different connection and thus gets different session state.
346 Default: DISCARD ALL
348 server_reset_query_always
350 Whether server_reset_query should be run in all pooling modes. When
351 this setting is off (default), the server_reset_query will be run only
352 in pools that are in sessions-pooling mode. Connections in transac‐
353 tion-pooling mode should not have any need for reset query.
354 It is workaround for broken setups that run apps that use session fea‐
356 tures over transaction-pooled pgbouncer. Is changes non-deterministic
357 breakage to deterministic breakage - client always lose their state
358 after each transaction.
359 Default: 0
361 server_check_delay
363 How long to keep released connections available for immediate re-use,
364 without running sanity-check queries on it. If 0 then the query is ran
365 always.
366 Default: 30.0
368 server_check_query
370 Simple do-nothing query to check if the server connection is alive.
371 If an empty string, then sanity checking is disabled.
373 Default: SELECT 1;
375 server_lifetime
377 The pooler will try to close server connections that have been con‐
378 nected longer than this. Setting it to 0 means the connection is to be
379 used only once, then closed. [seconds]
380 Default: 3600.0
382 server_idle_timeout
384 If a server connection has been idle more than this many seconds it
385 will be dropped. If 0 then timeout is disabled. [seconds]
386 Default: 600.0
388 server_connect_timeout
390 If connection and login won't finish in this amount of time, the con‐
391 nection will be closed. [seconds]
392 Default: 15.0
394 server_login_retry
396 If login failed, because of failure from connect() or authentication
397 that pooler waits this much before retrying to connect. [seconds]
398 Default: 15.0
400 client_login_timeout
402 If a client connects but does not manage to login in this amount of
403 time, it will be disconnected. Mainly needed to avoid dead connections
404 stalling SUSPEND and thus online restart. [seconds]
405 Default: 60.0
407 autodb_idle_timeout
409 If the automatically created (via "*") database pools have been unused
410 this many seconds, they are freed. The negative aspect of that is that
411 their statistics are also forgotten. [seconds]
412 Default: 3600.0
414 dns_max_ttl
416 How long the DNS lookups can be cached. If a DNS lookup returns sev‐
417 eral answers, pgbouncer will robin-between them in the meantime.
418 Actual DNS TTL is ignored. [seconds]
419 Default: 15.0
421 dns_nxdomain_ttl
423 How long error and NXDOMAIN DNS lookups can be cached. [seconds]
424 Default: 15.0
426 dns_zone_check_period
428 Period to check if zone serial has changed.
429 PgBouncer can collect DNS zones from host names (everything after first
431 dot) and then periodically check if zone serial changes. If it notices
432 changes, all host names under that zone are looked up again. If any
433 host IP changes, its connections are invalidated.
434 Works only with UDNS and c-ares backends (--with-udns or --with-cares
436 to configure).
437 Default: 0.0 (disabled)
439
441 client_tls_sslmode
442 TLS mode to use for connections from clients. TLS connections are dis‐
443 abled by default. When enabled, client_tls_key_file and
444 client_tls_cert_file must be also configured to set up key and cert
445 PgBouncer uses to accept client connections.
446 disable
448 Plain TCP. If client requests TLS, it's ignored. Default.
449 allow If client requests TLS, it is used. If not, plain TCP is used.
451 If client uses client-certificate, it is not validated.
452 prefer Same as allow.
454 require
456 Client must use TLS. If not, client connection is rejected. If
457 client uses client-certificate, it is not validated.
458 verify-ca
460 Client must use TLS with valid client certificate.
461 verify-full
463 Same as verify-ca.
464 client_tls_key_file
466 Private key for PgBouncer to accept client connections.
467 Default: not set.
469 client_tls_cert_file
471 Certificate for private key. Clients can validate it.
472 Default: not set.
474 client_tls_ca_file
476 Root certificate file to validate client certificates.
477 Default: unset.
479 client_tls_protocols
481 Which TLS protocol versions are allowed. Allowed values: tlsv1.0,
482 tlsv1.1, tlsv1.2. Shortcuts: all (tlsv1.0,tlsv1.1,tlsv1.2), secure
483 (tlsv1.2), legacy (all).
484 Default: all
486 client_tls_ciphers
488 Default: fast
489 client_tls_ecdhcurve
491 Elliptic Curve name to use for ECDH key exchanges.
492 Allowed values: none (DH is disabled), auto (256-bit ECDH), curve name.
494 Default: auto
496 client_tls_dheparams
498 DHE key exchange type.
499 Allowed values: none (DH is disabled), auto (2048-bit DH), legacy
501 (1024-bit DH).
502 Default: auto
504 server_tls_sslmode
506 TLS mode to use for connections to PostgreSQL servers. TLS connections
507 are disabled by default.
508 disable
510 Plain TCP. TCP is not event requested from server. Default.
511 allow FIXME: if server rejects plain, try TLS?
513 prefer TLS connection is always requested first from PostgreSQL, when
515 refused connection will be established over plain TCP. Server
516 certificate is not validated.
517 require
519 Connection must go over TLS. If server rejects it, plain TCP is
520 not attempted. Server certificate is not validated.
521 verify-ca
523 Connection must go over TLS and server certificate must be valid
524 according to server_tls_ca_file. Server host name is not
525 checked against certificate.
526 verify-full
528 Connection must go over TLS and server certificate must be valid
529 according to server_tls_ca_file. Server host name must match
530 certificate info.
531 server_tls_ca_file
533 Root certificate file to validate PostgreSQL server certificates.
534 Default: unset.
536 server_tls_key_file
538 Private key for PgBouncer to authenticate against PostgreSQL server.
539 Default: not set.
541 server_tls_cert_file
543 Certificate for private key. PostgreSQL server can validate it.
544 Default: not set.
546 server_tls_protocols
548 Which TLS protocol versions are allowed. Allowed values: tlsv1.0,
549 tlsv1.1, tlsv1.2. Shortcuts: all (tlsv1.0,tlsv1.1,tlsv1.2), secure
550 (tlsv1.2), legacy (all).
551 Default: all
553 server_tls_ciphers
555 Default: fast
556
558 Setting following timeouts cause unexpected errors.
559 query_timeout
561 Queries running longer than that are canceled. This should be used only
562 with slightly smaller server-side statement_timeout, to apply only for
563 network problems. [seconds]
564 Default: 0.0 (disabled)
566 query_wait_timeout
568 Maximum time queries are allowed to spend waiting for execution. If the
569 query is not assigned to a server during that time, the client is dis‐
570 connected. This is used to prevent unresponsive servers from grabbing
571 up connections. [seconds]
572 It also helps when server is down or database rejects connections for
574 any reason. If this is disabled, clients will be queued infinitely.
575 Default: 120
577 client_idle_timeout
579 Client connections idling longer than this many seconds are closed.
580 This should be larger than the client-side connection lifetime set‐
581 tings, and only used for network problems. [seconds]
582 Default: 0.0 (disabled)
584 idle_transaction_timeout
586 If client has been in "idle in transaction" state longer, it will be
587 disconnected. [seconds]
588 Default: 0.0 (disabled)
590
592 pkt_buf
593 Internal buffer size for packets. Affects size of TCP packets sent and
594 general memory usage. Actual libpq packets can be larger than this so,
595 no need to set it large.
596 Default: 4096
598 max_packet_size
600 Maximum size for PostgreSQL packets that PgBouncer allows through. One
601 packet is either one query or one result set row. Full result set can
602 be larger.
603 Default: 2147483647
605 listen_backlog
607 Backlog argument for listen(2). Determines how many new unanswered
608 connection attempts are kept in queue. When queue is full, further new
609 connections are dropped.
610 Default: 128
612 sbuf_loopcnt
614 How many times to process data on one connection, before proceeding.
615 Without this limit, one connection with a big result set can stall
616 PgBouncer for a long time. One loop processes one pkt_buf amount of
617 data. 0 means no limit.
618 Default: 5
620 suspend_timeout
622 How many seconds to wait for buffer flush during SUSPEND or reboot
623 (-R). Connection is dropped if flush does not succeed.
624 Default: 10
626 tcp_defer_accept
628 For details on this and other tcp options, please see man 7 tcp.
629 Default: 45 on Linux, otherwise 0
631 tcp_socket_buffer
633 Default: not set
634 tcp_keepalive
636 Turns on basic keepalive with OS defaults.
637 On Linux, the system defaults are tcp_keepidle=7200, tcp_keepintvl=75,
639 tcp_keepcnt=9. They are probably similar on other OS-es.
640 Default: 1
642 tcp_keepcnt
644 Default: not set
645 tcp_keepidle
647 Default: not set
648 tcp_keepintvl
650 Default: not set
651
653 This contains key=value pairs where key will be taken as a database
654 name and value as a libpq connect-string style list of key=value pairs.
655 As actual libpq is not used, so not all features from libpq can be used
656 (service=, .pgpass).
657 Database name can contain characters _0-9A-Za-z without quoting. Names
659 that contain other chars need to be quoted with standard SQL ident
660 quoting: double quotes where "" is taken as single quote.
661 "*" acts as fallback database: if the exact name does not exist, its
663 value is taken as connect string for requested database. Such automat‐
664 ically created database entries are cleaned up if they stay idle longer
665 then the time specified in autodb_idle_timeout parameter.
666 dbname
668 Destination database name.
669 Default: same as client-side database name.
671 host
673 Host name or IP address to connect to. Host names are resolved on con‐
674 nect time, the result is cached per dns_max_ttl parameter. If DNS
675 returns several results, they are used in round-robin manner.
676 Default: not set, meaning to use a Unix socket.
678 port
680 Default: 5432
681 user, password
683 If user= is set, all connections to the destination database will be
684 done with the specified user, meaning that there will be only one pool
685 for this database.
686 Otherwise PgBouncer tries to log into the destination database with
688 client username, meaning that there will be one pool per user.
689 The length for password is limited to 128 characters maximum.
691 auth_user
693 Override of the global auth_user setting, if specified.
694 pool_size
696 Set maximum size of pools for this database. If not set, the
697 default_pool_size is used.
698 connect_query
700 Query to be executed after a connection is established, but before
701 allowing the connection to be used by any clients. If the query raises
702 errors, they are logged but ignored otherwise.
703 pool_mode
705 Set the pool mode specific to this database. If not set, the default
706 pool_mode is used.
707 max_db_connections
709 Configure a database-wide maximum (i.e. all pools within the database
710 will not have more than this many server connections).
711 client_encoding
713 Ask specific client_encoding from server.
714 datestyle
716 Ask specific datestyle from server.
717 timezone
719 Ask specific timezone from server.
720
722 This contains key=value pairs where key will be taken as a user name
723 and value as a libpq connect-string style list of key=value pairs. As
724 actual libpq is not used, so not all features from libpq can be used.
725 pool_mode
727 Set the pool mode to be used for all connections from this user. If not
728 set, the database or default pool_mode is used.
729
731 The PgBouncer config file can contain include directives, which specify
732 another config file to read and process. This allows for splitting the
733 configuration file into physically separate parts. The include direc‐
734 tives look like this:
735 %include filename
737 If the file name is not absolute path it is taken as relative to cur‐
739 rent working directory.
740
742 PgBouncer needs its own user database. The users are loaded from a text
743 file in following format:
744 "username1" "password" ...
746 "username2" "md5abcdef012342345" ...
747 There should be at least 2 fields, surrounded by double quotes. The
749 first field is the username and the second is either a plain-text or a
750 MD5-hidden password. PgBouncer ignores the rest of the line.
751 This file format is equivalent to text files used by PostgreSQL 8.x for
753 authentication info, thus allowing PgBouncer to work directly on Post‐
754 greSQL authentication files in data directory.
755 Since PostgreSQL 9.0, the text files are not used anymore. Thus the
757 auth file needs to be generated. See ./etc/mkauth.py for sample
758 script to generate auth file from pg_shadow table.
759 PostgreSQL MD5-hidden password format:
761 "md5" + md5(password + username)
763 So user admin with password 1234 will have MD5-hidden password
765 md545f2603610af569b6155c45067268c6b.
766
768 It follows the format of PostgreSQL pg_hba.conf file -
769 http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html
770 There are following differences:
772 · Supported record types: local, host, hostssl, hostnossl.
774 · Database field: Supports all, sameuser, @file, multiple names. Not
776 supported: replication, samerole, samegroup.
777 · Username field: Supports all, @file, multiple names. Not supported:
779 +groupname.
780 · Address field: Supported IPv4, IPv6. Not supported: DNS names,
782 domain prefixes.
783 · Auth-method field: Supported methods: trust, reject, md5, password,
785 peer, cert. Not supported: gss, sspi, ident, ldap, radius, pam.
786 Also username map (map=) parameter is not supported.
787
789 Minimal config:
790 [databases]
792 template1 = host=127.0.0.1 dbname=template1 auth_user=someuser
793 [pgbouncer]
795 pool_mode = session
796 listen_port = 6543
797 listen_addr = 127.0.0.1
798 auth_type = md5
799 auth_file = users.txt
800 logfile = pgbouncer.log
801 pidfile = pgbouncer.pid
802 admin_users = someuser
803 stats_users = stat_collector
804 Database defaults:
806 [databases]
808 ; foodb over Unix socket
810 foodb =
811 ; redirect bardb to bazdb on localhost
813 bardb = host=127.0.0.1 dbname=bazdb
814 ; access to destination database will go with single user
816 forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
817 Example of secure function for auth_query:
819 CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
821 RETURNS record AS $$
822 BEGIN
823 SELECT usename, passwd FROM pg_catalog.pg_shadow
824 WHERE usename = i_username INTO uname, phash;
825 RETURN;
826 END;
827 $$ LANGUAGE plpgsql SECURITY DEFINER;
828 REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
829 GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
830
832 pgbouncer(1) - man page for general usage, console commands.
833 https://pgbouncer.github.io/
835 https://wiki.postgresql.org/wiki/PgBouncer
8371.8.1 2017-12-20 PGBOUNCER.INI(5)