PgBouncer是一个轻量级的PostgreSQL数据库连接池，可作为应用服务器与数据库服务器之间的数据库中间件使用。
测试环境
| 主机名 | IP地址 | 角色 | 数据名 | 数据库版本 |
| sht-sgmhadoopcm-01 | 172.16.101.54 | Proxy(安装Pgbouncer) | pgbouncer(虚拟的管理数据库) | V9.6.4（可以安装，也可以不安装） |
| sht-sgmhadoopdn-01 | 172.16.101.58 | 后端数据库服务器 | edbstore | V9.6.4 |
| sht-sgmhadoopdn-02 | 172.16.101.59 | 后端数据库服务器 | testdb | V9.6.4 |
软件准备：
PostgreSQL v9.6.4
https://ftp.postgresql.org/pub/source/v9.6.4/postgresql-9.6.4.tar.gz
PgBouncer 1.12
https://www.pgbouncer.org/downloads/files/1.12.0/pgbouncer-1.12.0.tar.gz
一. PostgreSQL数据库安装，略
二. Pgbouncer安装
1. 安装依赖包
# yum -y install libevent* flex bison
2. 安装PgBouncer
$ wget http://www.pgbouncer.org/downloads/files/1.12.0/pgbouncer-1.12.0.tar.gz $ tar -zxf pgbouncer-1.12.0.tar.gz $ cd pgbouncer-1.12.0 $ ./configure --prefix=/usr/local/pgsql/plugin/pgbouncer $ make ; make install
安装完后
PgBouncer的可执行文件在/usr/local/pgsql/bin/pgbouncer
PgBouncer默认的配置文件模板在/usr/local/pgsql/share/doc/pgbouncer/pgbouncer.ini
关于pgBouncer的使用文档都可以通过"man 1 pgbouncer"、"man 5 pgbouncer"来寻求帮助。
3. 后端数据库服务器准备测试数据库和用户
sht-sgmhadoopdn-01
create database edbstore; create user edbstore with password 'edbstore'; alter database edbstore owner to edbstore;
sht-sgmhadoopdn-02
create database testdb; create user user01 with password 'mypna123'; alter database testdb owner to user01 ;
4. PgBouncer的配置文件pgbouncer.ini如下所示
在PostgresqL的data目录新建PgBouncer的配置文件，配置文件的路径没有特别要求，这里为了方便管理。
[databases] edbstore= host='sht-sgmhadoopdn-01' port=5432 dbname='edbstore' connect_query='select 1' testdb= host='sht-sgmhadoopdn-02' port=5432 dbname='testdb' connect_query='select 1' [pgbouncer] logfile = /usr/local/pgsql/data/pg_log/pgbouncer.log pidfile = /usr/local/pgsql/data/pgbouncer.pid listen_addr = * listen_port = 6432 user = postgres unix_socket_dir = /tmp unix_socket_mode = 0777 unix_socket_group = auth_type = md5 auth_file = /usr/local/pgsql/data/userlist.txt admin_users = dbadmin pool_mode = session server_reset_query = DISCARD ALL server_reset_query_always = 0 server_check_query = select 1 server_check_delay = 30 server_fast_close = 0 max_client_conn = 300 default_pool_size = 30 min_pool_size = 0 reserve_pool_size = 3 reserve_pool_timeout = 5 max_db_connections = 0 max_user_connections = 0 log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 log_stats = 1 verbose = 0 server_lifetime = 3600 server_idle_timeout = 600 server_connect_timeout = 30 server_login_retry = 15 query_timeout = 0 query_wait_timeout = 0 client_idle_timeout = 0 client_login_timeout = 60 autodb_idle_timeout = 3600 idle_transaction_timeout = 0 suspend_timeout = 10
5. 新建认证文件userlist.txt，文件内容如下
"edbstore" "md54993cd9884e1ea380278ca1f857b2e4e" "dbadmin" "md5ec501027bbce0564559892fb69883fab" "user01" "md5db4cd59c0c9e22471df03e0a0c2353be"
注意，该文件的每行第一个字符是访问PgBouncer的账户名，第二个加密字符为该账户对应的md5加密密码，这些账户可以通过后端数据库服务器中的pg_shadow系统表获取。
6. 启动PgBouncer
$ pgbouncer -d pgbouncer.ini $ ps aux |grep pgbouncer postgres 9024 0.0 0.0 112716 992 pts/4 S+ 21:58 0:00 grep --color=auto pgbouncer postgres 25696 0.0 0.0 45756 1948 ? S 16:58 0:03 pgbouncer -d pgbouncer.ini $ netstat -antlp |grep 6432 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 25696/pgbouncer tcp6 0 0 :::6432 :::* LISTEN 25696/pgbouncer
7. 测试PgBouncer，从第三方应用服务器客户端登录PgBouncer.
$ export PGPASSWORD=edbstore $ psql -h 172.16.101.54 -p 6432 -U edbstore edbstore psql (9.6.4) Type "help" for help. edbstore=> create schema schema1; CREATE SCHEMA edbstore=> create table schema1.tb1(id int, name varchar(32)); CREATE TABLE edbstore=> insert into schema1.tb1 values(1, 'Cris'); INSERT 0 1 edbstore=> insert into schema1.tb1 values(2, 'James'); INSERT 0 1 edbstore=> select * from schema1.tb1; id | name ----+------- 1 | Cris 2 | James (2 rows)
从后端数据库服务器本地登录查看新建的数据
$ psql edbstore psql (9.6.4) Type "help" for help. edbstore=# dn List of schemas Name | Owner ----------+---------- edbstore | edbstore public | postgres schema1 | edbstore (3 rows) edbstore=# select * from schema1.tb1; id | name ----+------- 1 | Cris 2 | James (2 rows)
三. PgBouncer的日常管理
PgBouncer提供了一个虚拟的数据库"pgbouncer"，我们可以通过连接该数据库进行日常管理
登录该虚拟数据库的用户名和密码为userlist.txt文件中的"dbadmin"用户，因为我们在pgbouncer.ini配置文件中定义了该用户为superuser
$ export PGPASSWORD=mypna123 $ psql -h sht-sgmhadoopcm-01 -p 6432 -U dbadmin pgbouncer psql (9.6.4, server 1.12.0/bouncer) Type "help" for help.
查看PgBouncer帮助信息
pgbouncer=# show help; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SHOW DNS_HOSTS|DNS_ZONES SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS SET key = arg RELOAD PAUSE [<db>] RESUME [<db>] DISABLE <db> ENABLE <db> RECONNECT [<db>] KILL <db> SUSPEND SHUTDOWN SHOW
查看PgBouncer中定义的数据库名称
pgbouncer=# show databases; name | host | port | database | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled -----------+--------------------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+---------- edbstore | sht-sgmhadoopdn-01 | 5432 | edbstore | edbstore | 20 | 3 | | 0 | 1 | 0 | 0 pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 | 0 | statement | 0 | 0 | 0 | 0 testdb | sht-sgmhadoopdn-02 | 5432 | testdb | user01 | 20 | 3 | | 0 | 0 | 0 | 0 (3 rows)
查看当前PgBouncer客户端连接信息
pgbouncer=# show clients; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_ne eded | ptr | link | remote_pid | tls ------+----------+-----------+--------+----------------+-------+---------------+------------+-------------------------+-------------------------+------+---------+--------- -----+-----------+-----------+------------+----- C | edbstore | edbstore | active | 10.189.101.160 | 26482 | 172.16.101.54 | 6432 | 2020-03-13 22:01:47 CST | 2020-03-13 22:04:03 CST | 0 | 0 | 0 | 0x17ac9f0 | 0x17a5cf0 | 0 | C | dbadmin | pgbouncer | active | 172.16.101.54 | 22873 | 172.16.101.54 | 6432 | 2020-03-13 22:11:19 CST | 2020-03-13 22:17:50 CST | 332 | 811006 | 0 | 0x17acc08 | | 0 | (2 rows)
查看当前PgBouncer连接后端数据服务器信息
pgbouncer=# show servers; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_neede d | ptr | link | remote_pid | tls ------+----------+----------+--------+---------------+------+---------------+------------+-------------------------+-------------------------+------+---------+------------ --+-----------+-----------+------------+----- S | edbstore | edbstore | active | 172.16.101.58 | 5432 | 172.16.101.54 | 35809 | 2020-03-13 22:01:58 CST | 2020-03-13 22:04:03 CST | 0 | 0 | 0 | 0x17a5cf0 | 0x17ac9f0 | 5735 | S | user01 | testdb | active | 172.16.101.59 | 5432 | 172.16.101.54 | 30909 | 2020-03-13 22:20:46 CST | 2020-03-13 22:20:46 CST | 0 | 0 | 0 | 0x17a5f08 | 0x17ac5c0 | 15355 | (2 rows)
PgBouncer中间件重启可以通过kill 进程号实现。
将PgBouncer集成为CentOS7系统服务
[Unit] Description=Lightweight connection pooler for PostgreSQL Documentation=https://www.pgbouncer.org/config.html After=syslog.target After=network.target After=pgsql.service Wants=network-online.target [Service] Type=forking User=postgres Group=dba Restart=always LimitNOFILE=65536 # Disable OOM kill on the postmaster OOMScoreAdjust=-1000 Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj Environment=PG_OOM_ADJUST_VALUE=0 ExecStart=/usr/local/pgsql/plugin/pgbouncer/bin/pgbouncer -d /usr/local/pgsql/data/pgbouncer.ini ExecReload=/bin/kill -SIGHUP $MAINPID KillMode=mixed KillSignal=SIGINT [Install] WantedBy=multi-user.target