1、下载pgbouncer程序包和libevent依赖包
wget https://github.com/libevent/libevent/releases/download/release-2.1.11-stable/libevent-2.1.11-stable.tar.gz //pgbouncer需要用到libevent依赖包 wget http://www.pgbouncer.org/downloads/files/1.12.0/pgbouncer-1.12.0.tar.gz
2、安装编译所需的环境依赖
yum install gcc gcc-c++ make openssl-devel
3、解压并编译安装
tar -xf libevent-2.1.11-stable.tar.gz tar -xf pgbouncer-1.12.0.tar.gz
4、编译安装libevent
./configure --prefix=/usr/local/libevent make -j 2 make install
5、配置环境变量
echo "export PKG_CONFIG_PATH=/usr/local/libevent/lib/pkgconfig" >> ~/.bashrc cat ~/.bashrc source ~/.bashrc
9、授权postgres用户访问libevent-2.1.so.7权限
setfacl -Rm u:postgres:rwx /usr/
10、创建软连接
ln -s /usr/local/libevent/lib/libevent-2.1.so.7 /usr/lib64/libevent-2.1.so.7
11、编译安装pgbouncer
./configure --prefix=/usr/local/pgbouncer/ make -j 2 make install
12、创建pgbouncer.ini配置文件
cat /etc/pgbouncer/pgbouncer.ini [databases] postgres = host=127.0.0.1 port=5432 dbname=postgres [pgbouncer] logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid listen_addr = 10.15.150.41 listen_port = 6432 unix_socket_dir = /var/run/postgresql auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt admin_users = postgres ignore_startup_parameters = extra_float_digits,geqo pool_mode = session server_reset_query = DISCARD ALL max_client_conn = 10000 default_pool_size = 1000 reserve_pool_size = 30 reserve_pool_timeout = 5 max_db_connections = 5000 pkt_buf = 8192
13、创建用户名及密码认证文件，确保仅postgres用户可以访问，权限600
cat /etc/pgbouncer/userlist.txt "postgres" "P2poRLYZ8"
14、配置开机自启服务,由systemctl管理，创建pgbouncer.service文件
cat /etc/systemd/system/pgbouncer.service [Unit] Description=pgBouncer connection pooling for PostgreSQL After=syslog.target network.target [Service] Type=forking User=postgres Group=postgres PermissionsStartOnly=true ExecStartPre=-/bin/mkdir -p /var/run/pgbouncer /var/log/pgbouncer ExecStartPre=/bin/chown -R postgres:postgres /var/run/pgbouncer /var/log/pgbouncer ExecStart=/usr/local/pgbouncer/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini ExecReload=/bin/kill -SIGHUP $MAINPID PIDFile=/var/run/pgbouncer/pgbouncer.pid LimitNOFILE=100000 [Install] WantedBy=multi-user.target
15、填写可执行权限
chmod +x /etc/systemd/system/pgbouncer.service
16、启动服务
systemctl start pgbouncer
17、查询启动状态systemctl status pgbouncer
18、加入开机启动项
systemctl enable pgbouncer