Skip to content

📚 pgbouncer Install

1. Install pgbouncer

  • **1.1 Install
http_proxy="http://10.55.13.139:8080"
export http_proxy
https_proxy="http://10.55.13.139:8080"
export https_proxy

cat << EOF > /etc/yum.repos.d/pgbouncer.repo
[pgdg16]
name=Pgbouncer
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-\$releasever-\$basearch
enabled=1
gpgcheck=0
EOF

yum clean all
yum makecache
yum list |grep pgbouncer
yum install -y pgbouncer
  • **1.2 modify pgbouncer_MESP132.ini
cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer_MESP132.ini
vim /etc/pgbouncer/pgbouncer_MESP132.ini
[databases]
postgres = host=127.0.0.1 port=6432 dbname=postgres auth_user=postgres
MESP132 = host=127.0.0.1 port=6432 dbname=MESP132 auth_user=postgres

[pgbouncer]
listen_addr = *
listen_port = 5432
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo,search_path,replication,lc_monetary
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist_MESP132.txt
auth_user = postgres
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
pool_mode = transaction
server_reset_query = DISCARD ALL
server_reset_query_always = 0
max_client_conn = 5000
default_pool_size = 1000
reserve_pool_size = 30
reserve_pool_timeout = 5
max_db_connections = 5000
pkt_buf = 8192
application_name_add_host = 1
stats_users = stats, postgres
logfile = /var/log/pgbouncer/pgbouncer_MESP132.log
pidfile = /var/run/pgbouncer/pgbouncer_MESP132.pid
max_prepared_statements = 100
server_idle_timeout = 1200
idle_transaction_timeout = 1200

alt text

  • **1.3 modify userlist_MESP132.txt
vim /etc/pgbouncer/userlist_MESP132.txt
# Example for MD5：
"postgres"      "md5127a4b2945924f911888b239e2dcbbb5"
# Example for scram-sha-256 ：
"postgres" "SCRAM-SHA-256$4096:wfH002xStHOzKbZg4N0OiA==$Er7mTVwmJAolWz7fREPSqu+NIOAGWtWgQDkZLdq7EpY=:IbjCX4rztKMbfWebR5RAvGwgZ5AUOi5x4sS3rGmfewY="

alt text

  • **1.4 Config auto start
vim /usr/lib/systemd/system/pgbouncer_MESP132.service
[Unit]
Description=A lightweight connection pooler for PostgreSQL
After=syslog.target
After=network.target

[Service]
Type=notify

User=pgbouncer
Group=pgbouncer

# Path to the init file
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer_MESP132.ini

# Where to send early-startup messages from the server 
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

ExecStart=/usr/bin/pgbouncer ${BOUNCERCONF}
ExecReload=/usr/bin/kill -HUP $MAINPID
KillSignal=SIGINT

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

alt text

systemctl enable pgbouncer_MESP132.service
systemctl start pgbouncer_MESP132.service
systemctl status pgbouncer_MESP132.service
  • **1.5 Login postgres port Database
psql -h 10.41.243.24 -p 6432 -d pgboucer

alt text

  • **1.6 Use pgbouncer port login database
psql -h 10.41.243.24 -p 5432 MIC131Q

alt text

2. introduction ini

通用配置 logfile 指定日志文件，默认值是/var/log/pgbouncer/pgbouncer.log pidfile 指定pid文件位置，默认值是/var/run/pgbouncer/pgbouncer.pid listen_addr 监听的地址，默认值是127.0.0.1，可以使用*号表示监听所有IP地址。 listen_port 监听的端口，默认值是6432 unix_socket_dir 指定unix socket文件的目录，默认为/tmp目录 unix_socket_mode 指定unix socket文件的权限，默认值为0777 unix_socket_group 指定unix socket文件的组，默认无 user 指定启动PgBouncer的用户名，windows系统不支持此设置 auth_type 认证的类型，默认是trust，其他值包括md5，crypt，plain，any。用的较多的是md5 auth_file 认证文件的位置，默认值是/etc/pgbouncer/userlist.txt pool_mode 指定池的模式，默认是session模式，还可以是transaction和statement max_client_conn 允许的最大连接数 default_pool_size 默认的池大小，默认值20 min_pool_size 最小的池大小，每个连接池至少会向后端数据库保持多少个连接 reserve_pool_size 连接池的保留连接数 reserve_pool_timeout 保留连接的超时时间 server_round_robin 负载均衡的方式是否设置为“round robin”，默认为关闭，即后进先出 ignore_startup_parameters 默认情况PgBouncer只会跟踪一些默认参数，并且能检测这些参数的变化，保持这些参数和客户端的一致。在这个配置后面跟的配置后被> PgBouncer忽略，不会被检查。 disable_pqexec 是否禁止简单查询协议，默认值为0。简单查询协议允许一个请求发送多个SQL，容易导致SQL注入攻击。

日志配置项： syslog 是否打开syslog，windows下打开eventlog，默认值为0，表示不打开。 syslog_ident 默认为PgBouncer syslog_facility log_connections 是否记录连接成功的日志，默认值为1，表示记录 log_disconnections 是否记录断开连接的日志，默认值为1，表示记录 log_pooler_errors 连接池法网客户端的错误是否记录在日志中，默认值为1，表示记录 stats_period 把汇总的统计信息写入日志的时间周期，默认是60s

控制界面访问控制配置项： admin_users 管理用户名，默认值是postgres stats_users 允许连接到控制界面，查看连接池只读信息的用户列表。可以执行除“SHOW FDS”以外的其他“SHOW”命令 server_reset_query server_check_delay 空闲的连接多长时间进行一次健康检测，判断连接是否可用。如果设置为0，则立即检测，默认值为30s server_check_query，进行健康检查的SQL语句，如果为0，表示不检测，默认值为“select 1;” server_lifetime 连接的存活时间，连接超过这个时间就会被关闭，默认为3600，设置为0表示只使用一次。 server_idle_timeout 连接的idle时间，超过此时间，连接会被关闭。默认为600 server_connect_timeout 后端数据库的login时间超过这个值就会被关闭。默认为15s server_login_retry 传教到后端数据库的连接失败后，等多长时间后重试，默认为15s client_login_timeout 客户端与PgBouncer建立连接后，如果无法在这段时间内完成登录，那么连接会断开，默认为60s

危险超时配置项指的是为防止一些未知错误或者原因导致系统卡住的针对性配置。 query_timeout 允许超过该时间值的SQL会被断开，应该比SQL实际的执行时间稍长，也需要比数据库的statement_timeout参数值更大。为了应付一些未知的网络问> 题。默认为0.0，禁止使用 query_wait_timeout 请求在队列中等待被执行的最长时间，如果超过该时间还没有分配到连接，就会断开。默认为0，禁止使用。 client_idle_timeout 客户端连接空闲超过该时间，则断开连接。默认值为0，禁止使用 idle_transaction_timeout 客户端启动事务后，超过这个时间没有结束事务，则关闭这个客户端连接。默认值为0，禁止使用

底层网络连接配置 pkt_buf 用于网络包的内部缓冲区大小，会影响发出的TCP包的大小即内存的使用，默认值为2048，一般保持默认值 max_packet_size 通过PgBouncer最大的包大小，包可以是一个SQL，也可以是返回的结果，默认值是2147483647 listen_backlog TCP监听函数listen的Backlog参数，默认值为128 sbuf_loopcnt 处理过程中，每个连接处理多少数据就切换到下一个连接。默认为5，如果设置为0，表示不限制。不限制时，一个连接发送大量数据，另外的连接可能就> 会空闲，导致被结束掉。 tcp_defer_accept linux下，默认为45，其他平台为0。详细解释用man 7 tcp来查看 tcp_socket_buffer 默认没有设置 tcp_keepalive 是否以操作系统的默认值打开基本的keepalive 设置，在linux下，操作系统的keepalive里，默认值时tcp_keepidle=7200, tcp_keepintvl-75,> tcp_keepcnt=9，其他操作系统类型，默认值为1 tcp_keepcnt 默认未设置 tcp_keepidle 默认未设置 tcp_keepintvl 默认未设置