安装配置PgBouncer for PostgreSQL
发布时间:2020-12-13 16:10:31 所属栏目:百科 来源:网络整理
导读:PgBouncer连接池来自于PostgreSQL社区,它可以为多个数据库管理连接池,并且这些数据库可以位于不同的PostgreSQL后端。PgBouncer会为每一种数据库用户与数据库的组合建立一个池。一个被池化的连接只能被来自于同一个用户和数据库的另一个连接请求重用。 客户
PgBouncer连接池来自于PostgreSQL社区,它可以为多个数据库管理连接池,并且这些数据库可以位于不同的PostgreSQL后端。PgBouncer会为每一种数据库用户与数据库的组合建立一个池。一个被池化的连接只能被来自于同一个用户和数据库的另一个连接请求重用。
客户端应用不需要做软件修改,但是要连接到连接池的主机和端口而不是PostgreSQL的主机和端口。PgBouncer会创建新的数据库连接或者重用一个已有的连接。当客户端断开连接时,该连接会被返回给连接池以备重用。 1、安装pgbouncePgBounce的安装有两种方式,源代码安装和安装rpm二进制软件包。这里使用rpm软件安装,如下: [[email?protected] ~]# yum -y install pgbouncer [[email?protected] ~]# rpm -ql pgbouncer /etc/logrotate.d/pgbouncer /etc/pgbouncer /etc/pgbouncer/mkauth.py /etc/pgbouncer/mkauth.pyc /etc/pgbouncer/mkauth.pyo /etc/pgbouncer/pgbouncer.ini /etc/sysconfig/pgbouncer /usr/bin/pgbouncer /usr/lib/systemd/system/pgbouncer.service /usr/lib/tmpfiles.d/pgbouncer.conf /usr/share/doc/pgbouncer /usr/share/doc/pgbouncer/NEWS.rst /usr/share/doc/pgbouncer/README.rst /usr/share/doc/pgbouncer/pgbouncer.ini /usr/share/doc/pgbouncer/userlist.txt /usr/share/licenses/pgbouncer-1.9.0 /usr/share/licenses/pgbouncer-1.9.0/COPYRIGHT /usr/share/man/man1/pgbouncer.1.gz /usr/share/man/man5/pgbouncer.5.gz /var/run/pgbouncer 2、配置pgbounce编辑配置文件,修改以下内容: [databases] kkdb= host=192.168.120.149 port=5432 user=kkuser password=redhat postgres= host=192.168.120.149 port=5432 user=postgres password=redhat [pgbouncer] logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid listen_addr = * listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt admin_users = postgres,kkuser pool_mode = session server_reset_query = DISCARD ALL ignore_startup_parameters = extra_float_digits max_client_conn = 1000 default_pool_size = 200 上面的认证模式md5,所以指定认证文件的路径,并加入以下内容: [[email?protected] pgbouncer]# vi userlist.txt "postgres" "md5fe4b5cfa57349c59d9b18d6920de5f59" "kkuser" "md5ad9501a851743f17648b943395f791e0" 其中的md5值,可以在数据库中通过md5将密码进行编码,如下: postgres=# SELECT ‘md5‘||md5(‘redhat‘||‘postgres‘); 如果安装的是rpm二进制软件包,则使用下面的方法: [[email?protected] ~]# cd /etc/pgbouncer [[email?protected] pgbouncer]# ./mkauth.py userlist.txt "host=192.168.120.149 port=5432 dbname=kkdb user=kkuser password=redhat" [[email?protected] pgbouncer]# chown pgbouncer:pgbouncer userlist.txt --或者直接查询数据库 postgres=# select usename,passwd from pg_shadow order by 1; 3、启动pgbouncer如果通过源代码安装,只能使用非root用户启动。 [[email?protected] pgbouncer]# systemctl enable pgbouncer [[email?protected] pgbouncer]# systemctl start pgbouncer [[email?protected] pgbouncer]# systemctl status pgbouncer ● pgbouncer.service - A lightweight connection pooler for PostgreSQL Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2018-12-13 17:13:07 CST; 5s ago Process: 21134 ExecStart=/usr/bin/pgbouncer -d -q ${BOUNCERCONF} (code=exited,status=0/SUCCESS) Main PID: 21136 (pgbouncer) CGroup: /system.slice/pgbouncer.service └─21136 /usr/bin/pgbouncer -d -q /etc/pgbouncer/pgbouncer.ini Dec 13 17:13:07 hdp06 systemd[1]: Starting A lightweight connection pooler for PostgreSQL... Dec 13 17:13:07 hdp06 systemd[1]: PID file /var/run/pgbouncer/pgbouncer.pid not readable (yet?) after start. Dec 13 17:13:07 hdp06 systemd[1]: Started A lightweight connection pooler for PostgreSQL. [[email?protected] pgbouncer]# netstat -antpl|grep 6432 tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 21136/pgbouncer tcp6 0 0 :::6432 :::* LISTEN 21136/pgbouncer 4、测试pgbouncer[[email?protected] ~]$ psql -Upostgres -dpostgres -p6432 -h192.168.120.104 Password for user postgres: psql (10.6) Type "help" for help. postgres=# l 5、管理PgBouncerPgBouncer有一个管理控制台,可以登录到pgbouncer虚拟数据库来访问它。该控制台接受类SQL命令,这些命令允许用户监控、重新配置和管理PgBouncer。 [[email?protected] ~]$ psql -Upostgres -dpgbouncer -p6432 -h192.168.120.104 Password for user postgres: psql (10.6,server 1.9.0/bouncer) Type "help" for help. pgbouncer=# show clients; pgbouncer=# show servers; 如果变更了pgbouncer配置文件,不用重启,直接reload下就ok。 pgbouncer=# reload; 更多pgbouncer管理,请参考官方文档。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |