data.gov.uk pgbouncer config /etc/pgbouncer/pgbouncer.ini

pgbouncer.ini

;; database name = connect string
[databases]
ckan = host=127.0.0.1 dbname=ckan user=dgu password=REDACTED pool_size=40
;;dgudatastore = host=127.0.0.1 dbname=dgudatastore user=dgu password=REDACTED pool_size=10
;;dgudatastore_ro = host=127.0.0.1 dbname=dgudatastore user=dguro password=REDACTED pool_size=10
dgucelery = host=127.0.0.1 dbname=dgucelery user=dgu password=redacted pool_size=5

;; Configuation section
;; Configuation section
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /tmp
user = postgres

ignore_startup_parameters = application_name

;;;
;;; Authentication settings
;;;
; any, trust, plain, crypt, md5
auth_type = any

;;;
;;; Users allowed into database 'pgbouncer'
;;;
admin_users = postgres
stats_users = stats, root

;;;
;;; Pooler personality questions
;;;

; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
pool_mode = transaction
server_reset_query =
; 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 immidiately.
server_check_delay = 10

;;;
;;; Connection limits
;;;
; total number of clients that can connect
max_client_conn = 10000
default_pool_size = 10

; how many additional connection to allow in case of trouble
reserve_pool_size = 5

; if a clients needs to wait more than this many seconds, use reserve pool
reserve_pool_timeout = 3
log_connections = 0
log_disconnections = 0

; log error messages pooler sends to clients
log_pooler_errors = 1

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

;;;
;;; Timeouts
;;;

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

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

;; Cancel connection attepmt 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: 0)
;query_wait_timeout = 0

;; 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

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

;; buffer for streaming packets
;pkt_buf = 2048

;; man 2 listen
;listen_backlog = 128

;; 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 = 0

;; following options are linux-specific.
;; they also require tcp_keepalive=1

;; count of keepaliva 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