PostgreSQL_配置pgbouncer连接池

下载地址 PgBouncer Downloads

上传解压

[postgres@localhost ~]$ cd ~
[postgres@localhost ~]$ cd resoruce/
[postgres@localhost resoruce]$ ll
-rw-r--r--. 1 postgres postgres    598294 Jul  9 08:15 pgbouncer-1.17.0.tar.gz
[postgres@localhost resoruce]$ tar -zxvf pgbouncer-1.17.0.tar.gz 

编译安装

[postgres@localhost ~]$ su root
[root@localhost ~]# yum install -y libevent-devel

[postgres@localhost resoruce]$ cd pgbouncer-1.17.0
[postgres@localhost pgbouncer-1.17.0]$ mkdir ~/pgbouncer
[postgres@localhost pgbouncer-1.17.0]$ ./configure --prefix=/home/postgres/pgbouncer/
[postgres@localhost pgbouncer-1.17.0]$ make
[postgres@localhost pgbouncer-1.17.0]$ make install

修改配置文件

[postgres@localhost pgbouncer]$ cd ~/pgbouncer/
[postgres@localhost pgbouncer]$ egrep -v '^\;|^$' /home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini > pgbouncer.ini
[postgres@localhost pgbouncer]$ vi pgbouncer.ini

[databases]
appdb = host = 192.168.144.140 dbname=appdb port=5666
[pgbouncer]
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
listen_addr = *
listen_port = 5766
auth_type = trust
auth_file = /home/postgres/pgbouncer/userlist.txt

修改登录用户列表

[postgres@localhost pgbouncer]$ cp share/doc/pgbouncer/userlist.txt ./
[postgres@localhost pgbouncer]$ vi userlist.txt

"appuser" "1qaz@WSX"

修改环境变量

[postgres@localhost pgbouncer]$ vi ~/.bashrc
[postgres@localhost pgbouncer]$ source ~/.bashrc 

export PATH=${PGHOME}/bin:/home/postgres/pgbouncer/bin:${PATH}

修改pg_hba配置文件 保证appdb可以连接

[postgres@localhost pgbouncer]$ vi $PGDATA/pg_hba.conf

启动pgbouncer

[postgres@localhost pgbouncer]$ pgbouncer -d -v /home/postgres/pgbouncer/pgbouncer.ini

使用pgbouncer连接数据库

[postgres@localhost pgbouncer]$ psql -U appuser -d appdb -p 5766
psql (12.0)
Type "help" for help.

appdb=> show port;
 port 
------
 5666
(1 row)