PostgreSQL之Pgbouncer连接池安装

PostgreSQL之Pgbouncer连接池安装

(1) Pgbouncer功能介绍

        PgBouncer作为PostgreSQL数据库的连接池中间件，PgBouncer仅作为一个连接池和代理层为PostgreSQL和应用之间提供服务。

     PgBouncer能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。

     PgBouncer能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。

     PgBouncer对客户端连接进行限制，预防过多或恶意的连接请求。

• Pgbouncer官网 : http://www.pgbouncer.org/ 

(2) Pgbouncer编译需要安装的依赖包

• GNU Make 3.81+
• Libevent 2.0+(需要编译安装)
• pkg-config
• OpenSSL 1.0.1+ for TLS support
• (optional) c-ares as alternative to Libevent’s evdns
• (optional) PAM libraries

yum install  libevent libevent-devel -y

(3) 指定lib库

su - postgres

cat >> ~/.bash_profile << EOF
export LD_LIBRARY_PATH=/usr/local/libevent/lib:\$LD_LIBRARY_PATH
EOF

source ~/.bash_profile

(4) 编译安装Pgbouncer

tar zxvf pgbouncer-1.19.0.tar.gz
cd pgbouncer-1.19.0
./configure --prefix=/opt/apppgbouncer
make && make install

(5) 修改环境变量

sed -i "s;:\$PATH:;:/opt/apppgbouncer/bin:\$PATH:;g" ~/.bash_profile
source ~/.bash_profile
cat ~/.bash_profile
pgbouncer -V

(6) 复制配置文件模板 

mkdir -p /opt/apppgbouncer/conf/
cp /opt/apppgbouncer/share/doc/pgbouncer/pgbouncer.ini /opt/apppgbouncer/conf/

(6) 配置启动service

• 创建/etc/systemd/system/pgbouncer.service文件，通过systemctl管理pgbouncer服务。

su - root

cat /etc/systemd/system/pgbouncer.service

cat  > /etc/systemd/system/pgbouncer.service <<-EOFFFFFFFFFFFFFFFFFFF
[Unit]
Description=pgBouncer connection pooling for PostgreSQL
After=syslog.target network.target

[Service]
Type=forking
User=pgsql
Group=pgsql
PermissionsStartOnly=true
ExecStart= /opt/apppgbouncer/bin/pgbouncer -d /opt/apppgbouncer/conf/pgbouncer.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/opt/apppgbouncer/pgbouncer.pid
LimitNOFILE=100000
[Install]
WantedBy=multi-user.target

EOFFFFFFFFFFFFFFFFFFF


## 生效配置

systemctl daemon-reload

(7) 测试启动

systemctl enable pgbouncer
systemctl start pgbouncer
systemctl status pgbouncer