pgbouncer就是一连接池。现在有好多模块,plproxy,pgpool什么的,都有这功能。pgbouncer与之相比,最大的好处是简单,小型。
如果不需要负载均衡什么的功能,用pgbouncer就足够了。
PgBouncer的特点
a.内存消耗低(默认为2k/连接),因为Bouncer不需要每次都接受完整的数据包
b.可以把不同的数据库连接到一个机器上,而对客户端保持透明
c.支持在线的重新配置而无须重启
环境:
red hat 6.5
pg9.6.1
pgbouncer 1.7.2
libevent 2.0.21
1、安装过程
安装pgbouncer之前需要先安装libevent
安装libevent
下载地址https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
创建libevent的安装目录:
[postgres@node1 opt]$mkdir /home/postgres/libevent
切换到你的安装包所在的目录下,然后解压安装包:
[postgres@node1 opt]$ tar -zxvf libevent-2.0.21-stable.tar.gz
[postgres@node1 opt]$ cd libevent-2.0.21-stable
[postgres@node1 libevent-2.0.21-stable]$./configure --prefix=/home/postgres/libevent
[postgres@node1 libevent-2.0.21-stable]$make
[postgres@node1 libevent-2.0.21-stable]$make install
安装pgbouncer
下载地址:https://pgbouncer.github.io/downloads/
创建pgbouncer的安装目录
[postgres@node1 opt]$mkdir /home/postgres/pgbouncer
切换到你的安装包所在的目录下,然后解压安装包:
[postgres@node1 opt]$ tar -zxvf pgbouncer-1.7.2.tar.gz
[postgres@node1 opt]$ cd pgbouncer-1.7.2
[postgres@node1 pgbouncer-1.7.2]$ ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/
如果出现以下错误
checking for libevent... configure: error: not found, cannot proceed
指定libevent的安装目录以后再测试。
如果出现以下错误
checking for OpenSSL... configure: error: not found
切换到root用户,使用yum安装:
yum install openssl-devel* -y
切回原来的用户和目录后重新运行
[postgres@node1 pgbouncer-1.7.2]./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/
[postgres@node1 pgbouncer-1.7.2]$ make
[postgres@node1 pgbouncer-1.7.2]$ make install
查看pgbouncer是否安装成功,可以通过查看config.log中最后的返回值exit来确认,0是成功1是失败.
完成后,不要改变目录的情况下。
[postgres@node1 pgbouncer-1.7.2]$ tail -f config.log
#define HAVE_LSTAT 1
#define HAVE_LIBEVENT 1
#define HAVE_EVENT_LOOPBREAK 1
#define HAVE_EVENT_BASE_NEW 1
#define HAVE_EVDNS_BASE_NEW 1
#define USE_EVDNS 1
#define USUAL_LIBSSL_FOR_TLS 1
#define USUAL_TLS_CA_FILE "/etc/ssl/cert.pem"
configure: exit 0
2、配置
配置pgbouncer的cfg文件
[postgres@node1 ]$ mkdir /home/postgres/pgbouncer/config/
[postgres@node1 ]$ cd /home/postgres/pgbouncer/share/doc/pgbouncer
[postgres@node1 pgbouncer]$ ll
-rw-r--r-- 1 postgres postgres 36783 Mar 8 15:13 NEWS.rst
-rw-r--r-- 1 postgres postgres 8598 Mar 8 15:13 pgbouncer.ini
-rw-r--r-- 1 postgres postgres 4638 Mar 8 15:13 README.rst
-rw-r--r-- 1 postgres postgres 56 Mar 8 15:13 userlist.txt
[postgres@node1 pgbouncer]$ cp pgbouncer.ini /home/postgres/pgbouncer/config/
[postgres@node1 pgbouncer]$ cd /home/postgres/pgbouncer/config/
[postgres@node1 pgbouncer]$ vi pgbouncer.ini
[databases]
postgres=host=192.168.1.12 port=5432 user=postgres dbname=postgres pool_size=100
[pgbouncer]
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
auth_file = /home/postgres/pgbouncer/userlist.txt
auth_type = trust
listen_addr = 192.168.1.12
listen_port = 6432
pool_mode = transaction
admin_users = postgres
配置用户密码文件userlist.txt
[postgres@node1 pgbouncer]$ cd /home/postgres/pgbouncer/
[postgres@node1 pgbouncer]$ vi userlist.txt
"postgres" "postgres"
配置环境变量
[postgres@node1 pgbouncer]$ vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
#PATH=$PATH:$HOME/bin
export PGDATA=/home/postgres/pgsql96/data
export LD_LIBRARY_PATH=/opt/psql-9.6/lib:$LD_LIBRARY_PATH:/home/postgres/libevent/lib
export PATH=/opt/psql-9.6/bin:$PATH:/home/postgres/pgbouncer/bin/
[postgres@node1 pgbouncer]$ source ~/.bash_profile
启动pgbouncer
[postgres@node1 pgbouncer]$ /home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/config/pgbouncer.ini
2017-02-05 15:22:23.712 2430 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 210
出现上面信息说明pgbouncer启动成功
连接时报错
[postgres@pg pgbouncer]$ psql -h 192.168.1.12 -p 6432 -U postgres
psql: ERROR: no pg_hba.conf entry for host "192.168.1.12", user "postgres", database "postgres"
[postgres@pg data]$ vi $PGDATA/pg_hba.conf
在ipv4 部分增加一行
host all all 0.0.0.0/0 trust
[postgres@node1 pgbouncer]$ psql -h 192.168.1.12 -p 6432 -U postgres
psql (9.6.1)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | user1
public | emp | table | postgres
(2 rows)
postgres=#
连接pgbouncer本身的DB
[postgres@node1 pgbouncer]$ psql -h 192.168.1.12 -p 6432 -U postgres -d pgbouncer
假如出现以下问题:
psql: ERROR: not allowed
大概是参数文件pgbouncer.ini出现问题,参数没有配置好。确认参数配置好以后,重启pgbouncer。
[postgres@pg config]$ cat /home/postgres/pgbouncer/pgbouncer.pid
6459
[postgres@pg config]$ kill -9 6459
[postgres@pg config]$ /home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/config/pgbouncer.ini
2017-03-09 09:39:35.409 6530 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 210
2017-03-09 09:39:35.410 6530 LOG Stale pidfile, removing
[postgres@pg config]$ psql -h 192.168.1.12 -p 6432 pgbouncer
psql (9.4.4, server 1.7.2/bouncer)
Type "help" for help.
pgbouncer=# \d
ERROR: failure
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Pgbouncer 三中模式:
1.session pooling:一个客户端连接对应一个服务器连接。客户端断开,服务器连接回收到连接池中。是默认的模式,每开启一个进程,DB端也会开启一个新的进程
2.transaction pooling:服务器连接在一个事务里是才给予客户端,事务结束,连接回收回连接池。
3.statement pooling:不允许多语句的事务,最激进的模式。主要给pl/proxy使用。是基于每个查询的,开启此模式不适合执行事务,会报错
Show config; #查看相关配置
Show stats;
Show lists;#查看连接相关信息,如,数据库个数,空闲连接数等等
Show pools;#查看池中连接信息
Show databases;#查看相关数据库
Show clients; #查看连接数
如果修改了配置文件,那么需要reload重新加载
pgbouncer=# reload;
RELOAD
Pgbouncer是一个针对PostgreSQL数据库的轻量级连接池,任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接,然后pgbouncer 会处理与服务器连接,或者是重用已存在的连接。
pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。
pgbouncer安装练习
最新推荐文章于 2024-08-09 14:08:47 发布