pgbouncer.ini参数含义

2019独角兽企业重金招聘Python工程师标准>>>

;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
;user password 如果设置将使用设置的用户名登录数据库,如果不设置将使用客户端的用户名登录,如果使用了user那么对这个数据库只有一个连接池,如果不使用,将对每个用户生成一个连接池.
[databases]

; 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'
vitodb0 = host = 192.168.1.135 port=1960 user=vito password=vito dbname=vitodb pool_size=10
vitodb1 = host = 192.168.1.135 port=1961 user=vito password=vito dbname=vitodb pool_size=10 readonly=yes max_reqs_persec=10 weight=20
vitodb2 = host = 192.168.1.135 port=1962 user=vito password=vito dbname=vitodb pool_size=10 readonly=yes weight=40

; use custom pool sizes
;nondefaultdb = pool_size=50 reserve_pool_size=10

; fallback connect string
;* = host=testserver

;; Configuration section
[pgbouncer]

;;;
;;; Administrative settings
;;;

logfile = /home/pg96/pgbouncer-1.7.2/log/pgbouncer.log
;pgbouncer的log文件路径,日志文件是保持的打开状态的,直到kill -HUP 或者在控制台上RELOAD完成之后。
pidfile = /home/pg96/pgbouncer-1.7.2/pgbouncer.pid
;pgbouncer的pid文件路径，没有pidFfile就不允许成为守护进程
;;;
;;; Where to wait for clients
;;;

; ip address or * which means all ip-s
listen_addr = 127.0.0.1 
;声明一个用于监听TCP连接的IPv4地址,或者*就是意味着监听所有地址,如果不设置那么直允许UNIX_SOCKET连接
listen_port = 6432
;pgbouncer的监听端口,适用于TCP和UNIX socket

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
unix_socket_dir = /home/pg96/pgbouncer-1.7.2
;声明Unix socket的位置,适用于监听socket和服务器连接,如果设置为空,就会关闭socket
unix_socket_mode = 0700

unix_socket_group = postgres

;;;
;;; TLS settings for accepring clients
;;;

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

;; Path to file that contains trusted CA certs
;client_tls_ca_file = <system default>

;; Private key and cert to present to clients.
;; Required for 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 that contains trusted CA certs
;server_tls_ca_file = <system default>

;; Private key and cert to present to backend.
;; Needed only if backend server require client cert.
;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
auth_type = md5
;客户端验证方式
;auth_file = /8.0/main/global/pg_auth
auth_file = /home/pg96/pgbouncer-1.7.2/conf/userlist.txt
;完成客户的验证文件,保存用户名和密码
;md5:基于MD5的密码验证,auth_file文件中需要有普通文本和md5值的两种形式的密码;
;crypt:必须包含文本密码
;plain:明文验证密码
;trust:不进行验证,但auth_file中依然需要保存用户名
;any:不进行验证,auth_file中不需要保存用户名,但在所有数据库必须明确说明用户名进行登录
;auth_file中用户名,密码必须要用双引号,不然报错.
;; 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 = vitone 
;列出哪些用户可以登录pgbouncer进行管理,以逗号进行分隔

; comma-separated list of users who are just allowed to use SHOW command
;stats_users = stats, root  --
;列出哪些用户可以登录pgbouncer进行只读操作,如可以列出服务器状态,访问链接等,但是不能执行reload
;出了SHOW FDS外所有的show命令
;;;
;;; Pooler personality questions
;;;

; When server connection is released back to pool:
;   session      - after client disconnects
;   会话级连接,只有与当客户端的会话结束时,pgbouncer才会回收分配的连接,此时server_reset_query设置为DISCARD ALL
;   transaction  - after transaction finishes
;   事务级连接,事务完成后,pgbouncer才回收分配的连接,客户端只在事务中才独占此链接,在非事务中对数据库的请求是没有独享的链接
;   statement    - after statement finishes
;   语句级连接,任何对数据库的请求完成后,pgbouncer都会回收链接,此种模式下,客户端不能使用事务.
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 = 
;在连接释放到时候,服务器可被其他客户端使用之前,发给服务器的查询,在那个点上,没有正在处理的事务,所以他不应该包括包括ABORT,ROLLBACK;
; 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
;在不执行健康查询的时候,保持已经释放的连接多久处于立即可用状态,如果为0则查询总是运行.
;; 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 = 100
;允许最大客户端连接数,如果增大这个数值,那么最好也增大文件描述符限制,请注意实际的文件描述符数目总是比max_client_conn多,理论上被使用的最大描述符数是max_client_conn+(max_pool_size*total_databases*total_users)
;上面是假设每个用户都在用其用户名连接服务器,如果数据库用户在连接串中声明了(所有用户以同一个用户连接),那么理论的最大值是max_client_conn+(max_pool_size*total_databases)
; 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 = 20
;每个用户/数据库对允许的服务器连接数,可以在数据库相关的配置中覆盖
;; Minimum number of server connections to keep in pool.
;min_pool_size = 0

; 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

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

; If off, then server connections are reused in LIFO manner
;server_round_robin = 0
;缺省时,pgbouncer以LIFO方式复用服务器端的连接,这样大多数负载被少量的连接承担,这样在你只有一个服务器做数据库服务的是后性能最好,但是如果在数据库IP背后有一个TCP轮转的机制,那么pgbouncer最好也使用轮转的方式,这样才能获得一致的负载.
;;;
;;; Logging
;;;

;; Syslog settings
;syslog = 0
;切换syslog的开关
;syslog_facility = daemon
;在何种设施下给syslog发送日志,可能为auto,authopriv,daemon,user,local0-7
;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
;如果因为来自connect()活着认证的登录失败，那么连接池等待这段时间后再重新尝试连接.
;; 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
;运行时长超过这段的查询就取消,这个应该是和小一些的服务器端statement_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 = 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
;如果客户端登录了，但是无法再这段时间内完成登录，那么他将被断开，主要是避免死掉的连接SUSPEND,并且因此导致在线重启.
;; 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
;用于网络包的内部缓冲区大小,会影响发出的TCP包以及一般的内存使用,实际的libpq包可以比这个大,所以没必要设置的太大.
;; 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

 

转载于:https://my.oschina.net/vitofarm/blog/1543034