kenvac · November 7, 2017 09:50 · litnimax · Jun 13, 2019 · jguerriat · Jun 20, 2019
diff --git a/pgbouncer.ini b/pgbouncer.ini
 ;; database name = connect string
 ;;
 ;; connect string params:
 ;;   dbname= host= port= user= password=
 ;;   client_encoding= datestyle= timezone=
 ;;   pool_size= connect_query=
 [databases]
 odoo1 = host=127.0.0.1 auth_user=odoo dbname=odoo1 port=5432
 odoo2 = host=127.0.0.1 auth_user=odoo dbname=odoo2 port=5432
 odoo3 = host=127.0.0.1 auth_user=odoo port=5432 dbname=odoo3
 odoo4 = host=127.0.0.1 auth_user=odoo dbname=odoo4 port=5433 # odoo - postgres 10 test
 postgres = port=5432 host=127.0.0.1 auth_user=postgres

 [pgbouncer]

 ;;;
 ;;; Administrative settings
 ;;;

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

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

 ; ip address or * which means all ip-s
 listen_addr = 127.0.0.1
 listen_port = 6432

 ; unix socket is also used for -R.
 ; On debian it should be /var/run/postgresql
 ;unix_socket_dir = /tmp
 ;unix_socket_mode = 0777
 ;unix_socket_group =
 unix_socket_dir = /var/run/postgresql

 auth_file = /etc/pgbouncer/userlist.txt

 ;; Path to HBA-style auth config
 ;auth_hba_file =

 ;; Query to use to fetch password from database.  Result
 ;; must have 2 columns - username and password hash.
 auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

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

 ; comma-separated list of users, who are allowed to change settings
 ;admin_users = user2, someadmin, otheradmin

 ; comma-separated list of users who are just allowed to use SHOW command
 ;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

 ;
 ; 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 = DISCARD ALL


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

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

 ;;;
 ;;; Connection limits
 ;;;

 ; total number of clients that can connect
 max_client_conn = 500

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

 ;; Minimum number of server connections to keep in pool.
 min_pool_size = 2

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

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

 ; how many total connections to a single database to allow from all pools
 ;max_db_connections = 0
 ;max_user_connections = 50

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

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

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

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

 ;; 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
	;; database name = connect string
	;;
	;; connect string params:
	;; dbname= host= port= user= password=
	;; client_encoding= datestyle= timezone=
	;; pool_size= connect_query=
	[databases]
	odoo1 = host=127.0.0.1 auth_user=odoo dbname=odoo1 port=5432
	odoo2 = host=127.0.0.1 auth_user=odoo dbname=odoo2 port=5432
	odoo3 = host=127.0.0.1 auth_user=odoo port=5432 dbname=odoo3
	odoo4 = host=127.0.0.1 auth_user=odoo dbname=odoo4 port=5433 # odoo - postgres 10 test
	postgres = port=5432 host=127.0.0.1 auth_user=postgres

	[pgbouncer]

	;;;
	;;; Administrative settings
	;;;

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

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

	; ip address or * which means all ip-s
	listen_addr = 127.0.0.1
	listen_port = 6432

	; unix socket is also used for -R.
	; On debian it should be /var/run/postgresql
	;unix_socket_dir = /tmp
	;unix_socket_mode = 0777
	;unix_socket_group =
	unix_socket_dir = /var/run/postgresql

	auth_file = /etc/pgbouncer/userlist.txt

	;; Path to HBA-style auth config
	;auth_hba_file =

	;; Query to use to fetch password from database. Result
	;; must have 2 columns - username and password hash.
	auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

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

	; comma-separated list of users, who are allowed to change settings
	;admin_users = user2, someadmin, otheradmin

	; comma-separated list of users who are just allowed to use SHOW command
	;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

	;
	; 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 = DISCARD ALL


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

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

	;;;
	;;; Connection limits
	;;;

	; total number of clients that can connect
	max_client_conn = 500

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

	;; Minimum number of server connections to keep in pool.
	min_pool_size = 2

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

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

	; how many total connections to a single database to allow from all pools
	;max_db_connections = 0
	;max_user_connections = 50

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

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

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

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

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