这个需求来自于我们这边的数据仓库，可能是连接的用户太多了，大家都在上面执行SQL，导致大家的查询都很慢。
所以通过连接池来限制并发的SQL请求是比较靠谱的方法。
允许用户连接上来，并且执行SQL，超出配置的请求放入队列,等有空闲的数据库端连接的时候再执行。而不是直接返回错误,这一点很重要。
pgbouncer和PostgreSQL之间连接的复用，pgpool和PostgreSQL之间连接的复用不在这里讨论。可以参考手册。
pgbouncer 可以限制每一对用户+数据库 到PostgreSQL Cluster的总连接数。
pgpool 可以限制pgpool到PostgreSQL cluster的总连接数。
结合起来可以干什么呢？
可以设置PostgreSQL Cluster全局的连接限制。如图
假设有客户端的需求最大有2万个连接，我们的需求是限制这2万个连接里面并发提交给数据库的请求是100个。超出100的SQL请求全部排队。
一个简单的配置如下:
pgbouncer.config
[databases]
db1 = host=pgpool_host_ip dbname=db1 port=9999 pool_size=1000
db2 = host=pgpool_host_ip dbname=db2 port=9999 pool_size=1000
[pgbouncer]
pool_mode = transaction # 配置为事务级是比较适合我们前面提到的场景的。
listen_port = 1999
unix_socket_dir = /opt/pgbouncer/etc
listen_addr = *
auth_type = md5
auth_file = /opt/pgbouncer/etc/users.txt
logfile = /dev/null
pidfile = /opt/pgbouncer/etc/pgbouncer.pid
max_client_conn = 20000
reserve_pool_timeout = 0
server_reset_query = DISCARD ALL;
admin_users = pgbouncer_admin
stats_users = pgbouncer_guest
ignore_startup_parameters = extra_float_digits
server_idle_timeout = 1
server_round_robin = 1
pgbouncer.user
"username" "password"
启动pgbouncer
pgbouncer -d -u nonroot_user /opt/pgbouncer/etc/config.ini
pgpool:
pcp.conf
# USERID:MD5PASSWD
pgpool.conf
listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 100 # num_init_children*max_pool = 限制的连接数
max_pool = 1
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/tmp'
pid_file_name = '/var/run/pgpool/pgpool.pid'
replication_mode = false
load_balance_mode = false
replication_stop_on_mismatch = false
failover_if_affected_tuples_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; DISCARD ALL'
white_function_list = ''
black_function_list = 'nextval,setval'
print_timestamp = true
master_slave_mode = false
master_slave_sub_mode = 'slony'
delay_threshold = 0
log_standby_delay = 'none'
connection_cache = true
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
fail_over_on_backend_error = true
insert_lock = true
ignore_leading_white_space = true
log_statement = false
log_per_node_statement = false
log_connections = false
log_hostname = false
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = ''
backend_hostname0 = 'real_database_ip'
backend_port0 = real_database_port
backend_weight0 = 1
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
lobj_lock_table = ''
ssl = false
debug_level = 0
启动pgpool
pgpool -c -f /opt/pgpool/etc/pgpool.conf -F /opt/pgpool/etc/pcp.conf
客户端连接配置:
ip : pgbouncer_host_ip
port : pgbouncer_listener_port
dbname : pgbouncer_configed_dbname
user : pgbouncer_configed_name equal to real db's username
password : pgbouncer_configed_password equal to real db's password
【参考】
pgbouncer/doc
pgbouncer/share/man
pgpool/doc
pgpool/share/man