Pgbouncer Quick Start

pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
pgbouncer -V|-h

;; Database name = connection string
;;
;; Connection string parameters:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
;;   auth_user=
[databases]

instanceA = host=10.1.1.1 dbname=core
instanceB = host=102.2.2.2 dbname=payment

; foodb over Unix socket
;foodb =

; Redirect bardb on localhost to bazdb 
;bardb = host=localhost dbname=bazdb

; Access target database with single user
;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

; Use custom connection pool size
;nondefaultdb = pool_size=50 reserve_pool=10

; If user not in auth file, use auth_user as substitute; auth_user must be in auth file
; foodb = auth_user=bar

; Fallback wildcard connection string
;* = host=testserver

;; Pgbouncer configuration section
[pgbouncer]

;;;
;;; Administrative settings
;;;

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;;;
;;; Where to listen for clients
;;;

; IP address to listen on, * means all IPs
listen_addr = *
listen_port = 6432

; Unix socket is also used by -R option
; On Debian this is /var/run/postgresql
;unix_socket_dir = /tmp
;unix_socket_mode = 0777
;unix_socket_group =

;;;
;;; TLS settings
;;;

;; Options: disable, allow, require, verify-ca, verify-full
;client_tls_sslmode = disable

;; Path to trusted CA certificate file
;client_tls_ca_file = <system default>

;; Private key and certificate paths for client representation
;; Required when accepting TLS connections from clients
;client_tls_key_file =
;client_tls_cert_file =

;; fast, normal, secure, legacy, <ciphersuite string>
;client_tls_ciphers = fast

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
;client_tls_protocols = all

;; none, auto, legacy
;client_tls_dheparams = auto

;; none, auto, <curve name>
;client_tls_ecdhcurve = auto

;;;
;;; TLS settings for connecting to backend databases
;;;

;; disable, allow, require, verify-ca, verify-full
;server_tls_sslmode = disable

;; Path to trusted CA certificate file
;server_tls_ca_file = <system default>

;; Private key and certificate for backend representation
;; Only needed when backend server requires client certificates
;server_tls_key_file =
;server_tls_cert_file =

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
;server_tls_protocols = all

;; fast, normal, secure, legacy, <ciphersuite string>
;server_tls_ciphers = fast

;;;
;;; Authentication settings
;;;

; any, trust, plain, crypt, md5, cert, hba, pam
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

;; HBA-style authentication config file
# auth_hba_file = /pg/data/pg_hba.conf

;; Query to get password from database, result must contain two columns: username and password hash
;auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

;;;
;;; Users allowed to access virtual database 'pgbouncer'
;;;

; Users allowed to modify settings, comma-separated list of usernames
admin_users = postgres

; Users allowed to use SHOW commands, comma-separated list of usernames
stats_users = stats, postgres

;;;
;;; Connection pooling settings
;;;

; When server connection is put back to pool? (default is session)
;   session      - session mode, when client disconnects
;   transaction  - transaction mode, when transaction ends  
;   statement    - statement mode, when statement ends
pool_mode = session

; Query for immediately cleaning connections when client releases connection
; Don't put ROLLBACK here, Pgbouncer won't reuse connections when transaction hasn't ended
;
; Query for 8.3 and higher versions:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction-level connection pooling enabled
;
server_reset_query = DISCARD ALL

; Whether server_reset_query needs to execute in any case
; If off (default), server_reset_query only used in session-level connection pooling
;server_reset_query_always = 0

;
; Comma-separated list of parameters to ignore when given
; in startup packet. Newer JDBC versions require the
; extra_float_digits here.
;
;ignore_startup_parameters = extra_float_digits

;
; When taking idle server into use, this query is ran first.
;   SELECT 1
;
;server_check_query = select 1

; If server was used more recently that this many seconds ago,
; skip the check query. Value 0 may or may not run in immediately.
;server_check_delay = 30

; Close servers in session pooling mode after a RECONNECT, RELOAD,
; etc. when they are idle instead of at the end of the session.
;server_fast_close = 0

;; Use <appname - host> as application_name on server.
;application_name_add_host = 0

;;;
;;; Connection limits
;;;

; Maximum allowed connections
max_client_conn = 100

; Default pool size, 20 is appropriate for transaction connection pooling
; For session-level connection pooling, this is max connections you want to handle simultaneously
default_pool_size = 20

;; Minimum reserved connections in pool
;min_pool_size = 0

; How many additional connections allowed when problems occur
;reserve_pool_size = 0

; If client waits longer than this many seconds, use reserve pool
;reserve_pool_timeout = 5

; Maximum connections allowed per single database/user
;max_db_connections = 0
;max_user_connections = 0

; If off, then server connections are reused in LIFO manner
;server_round_robin = 0

;;;
;;; Logging
;;;

;; Syslog settings
;syslog = 0
;syslog_facility = daemon
;syslog_ident = pgbouncer

; log if client connects or server connection is made
;log_connections = 1

; log if and why connection was closed
;log_disconnections = 1

; log error messages pooler sends to clients
;log_pooler_errors = 1

;; Period for writing aggregated stats into log.
;stats_period = 60

;; Logging verbosity. Same as -v switch on command line.
;verbose = 0

;;;
;;; Timeouts
;;;

;; Close server connection if its been connected longer.
;server_lifetime = 3600

;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
;server_idle_timeout = 600

;; Cancel connection attempt if server does not answer takes longer.
;server_connect_timeout = 15

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
;server_login_retry = 15

;; Dangerous. Server connection is closed if query does not return
;; in this time. Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0

;; Dangerous. Client connection is closed if the query is not assigned
;; to a server in this time. Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 120)
;query_wait_timeout = 120

;; Dangerous. Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600

;; How long SUSPEND/-R waits for buffer flush before closing connection.
;suspend_timeout = 10

;; Close connections which are in "IDLE in transaction" state longer than
;; this many seconds.
;idle_transaction_timeout = 0

;;;
;;; Low-level tuning options
;;;

;; buffer for streaming packets
;pkt_buf = 4096

;; man 2 listen
;listen_backlog = 128

;; Max number pkt_buf to process in one event loop.
;sbuf_loopcnt = 5

;; Maximum PostgreSQL protocol packet size.
;max_packet_size = 2147483647

;; networking options, for info: man 7 tcp

;; Linux: notify program about new connection only if there
;; is also data received. (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0

;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1

;; The following options are Linux-specific.
;; They also require tcp_keepalive=1.

;; count of keepalive packets
;tcp_keepcnt = 0

;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0

;; The time between individual keepalive probes.
;tcp_keepintvl = 0

;; DNS lookup caching time
;dns_max_ttl = 15

;; DNS zone SOA lookup period
;dns_zone_check_period = 0

;; DNS negative result caching time
;dns_nxdomain_ttl = 15

;;;
;;; Random stuff
;;;

;; Hackish security feature. Helps against SQL-injection - when PQexec is disabled,
;; multi-statement cannot be made.
;disable_pqexec = 0

;; Config file to use for next RELOAD/SIGHUP.
;; By default contains config file from command line.
;conffile

;; Win32 service name to register as. job_name is alias for service_name,
;; used by some Skytools scripts.
;service_name = pgbouncer
;job_name = pgbouncer

;; Read additional config from the /etc/pgbouncer/pgbouncer-other.ini file
;%include /etc/pgbouncer/pgbouncer-other.ini