files/gitlab-cookbooks/pgbouncer/templates/default/pgbouncer.ini.erb

;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
; foodb over unix socket
;foodb =

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

; access to dest database will go 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 pool sizes
;nondefaultdb = pool_size=50 reserve_pool_size=10

; fallback connect string
;* = host=testserver

;; Configuration section
[pgbouncer]

;;;
;;; Administrative settings
;;;

<% if @logfile %>
logfile = <%= @logfile %>
<% end %>

;;;
;;; Where to wait for clients
;;;

; ip address or * which means all ip-s
listen_addr = <%= @listen_addr %>
listen_port = <%= @listen_port %>

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
unix_socket_dir = <%= @unix_socket_dir %>
unix_socket_mode = <%= @unix_socket_mode %>
<% if @unix_socket_group %>
unix_socket_group = <%= @unix_socket_group %>
<% end %>

;;;
;;; TLS settings for accepting clients
;;;

;; disable, allow, require, verify-ca, verify-full
client_tls_sslmode = <%= @client_tls_sslmode %>

;; Path to file that contains trusted CA certs
<% if @client_tls_ca_file %>
client_tls_ca_file = <%= @client_tls_ca_file %>
<% end %>

;; Private key and cert to present to clients.
;; Required for accepting TLS connections from clients.
<% if @client_tls_key_file %>
client_tls_key_file = <%= @client_tls_key_file %>
<% end %>
<% if @client_tls_cert_file %>
client_tls_cert_file = <%= @client_tls_cert_file %>
<% end %>

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

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
client_tls_protocols = <%= @client_tls_protocols %>

;; none, auto, legacy
client_tls_dheparams = <%= @client_tls_dheparams %>

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

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

;; disable, allow, require, verify-ca, verify-full
server_tls_sslmode = <%= @server_tls_sslmode %>

;; Path to that contains trusted CA certs
<% if @server_tls_ca_file %>
server_tls_ca_file = <%= @server_tls_ca_file %>
<% end %>

;; Private key and cert to present to backend.
;; Needed only if backend server require client cert.
<% if @server_tls_key_file %>
server_tls_key_file = <%= @server_tls_key_file %>
<% end %>
<% if @server_tls_cert_file %>
server_tls_cert_file = <%= @server_tls_cert_file %>
<% end %>

;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2
server_tls_protocols = <%= @server_tls_protocols %>

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

;;;
;;; Authentication settings
;;;

; any, trust, plain, crypt, md5
auth_type = <%= @auth_type %>
;auth_file = /8.0/main/global/pg_auth
auth_file = <%= @data_directory %>/pg_auth

<% unless @auth_hba_file.nil? %>
;; Path to HBA-style auth config
auth_hba_file = <%= @auth_hba_file %>

<% end %>
;; Query to use to fetch password from database.  Result
;; must have 2 columns - username and password hash.
auth_query = <%= @auth_query %>

<% if @auth_dbname %>
; Authentication database that can be set globally to run "auth_query".
auth_dbname = <%= @auth_dbname %>
<% end %>

;;;
;;; Users allowed into database 'pgbouncer'
;;;

; comma-separated list of users, who are allowed to change settings
admin_users = <%= @admin_users.join(', ') %>

; comma-separated list of users who are just allowed to use SHOW command
stats_users = <%= @stats_users.join(', ') %>

;;;
;;; 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 = <%= @pool_mode %>

; Number of prepared statements to cache on a server connection (zero value
; disables support of prepared statements).
max_prepared_statements = <%= @max_prepared_statements %>

;
; Query for cleaning connection immediately after releasing from client.
; No need to put ROLLBACK here, pgbouncer does not reuse connections
; where transaction is left open.
;
; Query for 8.3+:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction pooling is in use.
;
server_reset_query = <%= @server_reset_query %>


; Whether server_reset_query should run in all pooling modes.
; If it is off, server_reset_query is used only for session-pooling.
server_reset_query_always = <%= @server_reset_query_always %>

; Comma-separated list of parameters to track per client. The Postgres 
; parameters listed here will be cached per client by pgbouncer and 
; restored in server everytime the client runs a query. 
track_extra_parameters = <%= @track_extra_parameters.join(', ') %>

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

;
; When taking idle server into use, this query is ran first.
;   SELECT 1
;
server_check_query = <%= @server_check_query %>

; 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 = <%= @server_check_delay %>

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

;;;
;;; Connection limits
;;;

; total number of clients that can connect
max_client_conn = <%= @max_client_conn %>

; default pool size.  20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
default_pool_size = <%= @default_pool_size %>

;; Minimum number of server connections to keep in pool.
min_pool_size = <%= @min_pool_size %>

; how many additional connection to allow in case of trouble
reserve_pool_size = <%= @reserve_pool_size %>

; if a clients needs to wait more than this many seconds, use reserve pool
reserve_pool_timeout = <%= @reserve_pool_timeout %>

; how many total connections to a single database to allow from all pools
<% if @max_db_connections %>
max_db_connections = <%= @max_db_connections %>
<% end %>
<% if @max_user_connections %>
max_user_connections = <%= @max_user_connections %>
<% end %>

; If off, then server connections are reused in LIFO manner
server_round_robin = <%= @server_round_robin %>

;;;
;;; Logging
;;;

;; Syslog settings
syslog = <%= @syslog %>
syslog_facility = <%= @syslog_facility %>
syslog_ident = <%= @syslog_ident %>

; log if client connects or server connection is made
log_connections = <%= @log_connections %>

; log if and why connection was closed
log_disconnections = <%= @log_disconnections %>

; log error messages pooler sends to clients
log_pooler_errors = <%= @log_pooler_errors %>

;; Period for writing aggregated stats into log.
stats_period = <%= @stats_period %>

;; Logging verbosity.  Same as -v switch on command line.
verbose = <%= @verbose %>

;;;
;;; Timeouts
;;;

;; Close server connection if its been connected longer.
server_lifetime = <%= @server_lifetime %>

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

;; Cancel connection attempt if server does not answer takes longer.
server_connect_timeout = <%= @server_connect_timeout %>

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
server_login_retry = <%= @server_login_retry %>

;; 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 = <%= @query_timeout %>

;; 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 = <%= @query_wait_timeout %>

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

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
client_login_timeout = <%= @client_login_timeout %>

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

;; How long SUSPEND/-R waits for buffer flush before closing connection.
suspend_timeout = <%= @suspend_timeout %>

;; Close connections which are in "IDLE in transaction" state longer than
;; this many seconds.
idle_transaction_timeout = <%= @idle_transaction_timeout %>

;; Dangerous.  Client connection is closed if the cancellation request
;; is not assigned to a server in this time.  Should be used to limit
;; the time a client application blocks on a queued cancel request in
;; case of a database or network failure. (default: 10)
cancel_wait_timeout = <%= @cancel_wait_timeout %>

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

;; buffer for streaming packets
pkt_buf = <%= @pkt_buf %>

;; man 2 listen
listen_backlog = <%= @listen_backlog %>

;; Max number pkt_buf to process in one event loop.
sbuf_loopcnt = <%= @sbuf_loopcnt %>

;; Maximum Postgres protocol packet size.
max_packet_size = <%= @max_packet_size %>

;; Set SO_REUSEPORT socket option
so_reuseport = <%= @so_reuseport %>

;; 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 = <%= @tcp_defer_accept %>

;; In-kernel buffer size (Linux default: 4096)
tcp_socket_buffer = <%= @tcp_socket_buffer %>

;; whether tcp keepalive should be turned on (0/1)
tcp_keepalive = <%= @tcp_keepalive %>

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

;; count of keepaliva packets
tcp_keepcnt = <%= @tcp_keepcnt %>

;; how long the connection can be idle,
;; before sending keepalive packets
tcp_keepidle = <%= @tcp_keepidle %>

;; The time between individual keepalive probes.
tcp_keepintvl = <%= @tcp_keepintvl %>

;; DNS lookup caching time
dns_max_ttl = <%= @dns_max_ttl %>

;; DNS zone SOA lookup period
dns_zone_check_period = <%= @dns_zone_check_period %>

;; DNS negative result caching time
dns_nxdomain_ttl = <%= @dns_nxdomain_ttl %>

;;;
;;; Random stuff
;;;

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

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

;;;
;;; Peers
;;;
[peers]
<% @peers.each do |peer_id, settings| %>
<%= peer_id %> = <%= settings.map{ |k,v| "#{k}=#{v}" }.join(' ') %>
<% end %>

;; Read additional config from the /etc/pgbouncer/pgbouncer-other.ini file
;%include /etc/pgbouncer/pgbouncer-other.ini
%include <%= @databases_ini %>