Pgbouncer 最佳实践：系列四

作者：王志斌，曾获得中国 PostgreSQL 数据库管理工程师（PGCE），是 PostgreSQL 官方认证讲师，盘古云课堂特邀金牌讲师。

最后再来说一下关于 Pgbouncer 的部署形式，包括单应用场景、多应用场景、集群场景还有多实例场景，这些方式都是依据不同的业务场景，没有孰优孰劣，符合的才是对的。其中单应用和多应用场景来源于官方。

单应用场景：

图 9 单应用多连接场景结构图

单应用场景主要具体为短连接较多的场景，频繁进行数据库的连接操作，但操作时间较短，均为短连接，所以将 pgbouncer 于应用服务器部署在同一台服务器，减少应用服务器和 pgbouncer 之间的开销。

配置文件

[databases]

test1 =

test =

[pgbouncer]

listen_port = 6688

listen_addr = 192.168.165.3

auth_type = md5

auth_file = /home/postgres/pgbouncer/bin/userlist.txt

logfile = /home/postgres/pgbouncer/pgbouncer1.log

pidfile =/home/postgres/pgbouncer/pgbouncer1.pid

unixsocketdir = /tmp

;;unixsocketmode = 0777

admin_users = wzb

stats_users = wzb

pool_mode = session

maxclientconn=1000

defaultpoolsize=30

导出数据库中用户名及密码到 userslist.txt

userslist.txt，格式为用户名 密码

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"

"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"

"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动 Pgbouncer

pgbouncer -d pgbouncer.ini

客户端连接方式

psql -dtest1 -Utestuser1 -p6688

多应用场景：

图 10多应用场景结构图

多应用场景，一般指多个应用服务器连接数据库，因此可以选择将 pgbouncer 与数据库服务部署在同一台服务器上，减少 pgbouncer 和数据库之间的开销。

配置 PgBouncer.ini 文件

[databases]

a1 = host=127.0.0.1 port=5432 dbname=test

a2 = host=127.0.0.1 port=5432 dbname=test1

[pgbouncer]

listen_port = 6688

listen_addr = *

auth_type = md5

auth_file = /home/postgres/pgbouncer/bin/userlist.txt

logfile = /home/postgres/pgbouncer/pgbouncer.log

pidfile =/home/postgres/pgbouncer/pgbouncer.pid

admin_users = wzb

stats_users = wzb

pool_mode = session

maxclientconn=1000

defaultpoolsize=30

导出数据库中用户名及密码到 userslist.txt

userslist.txt，格式为用户名 密码

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"

"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"

"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动 Pgbouncer

pgbouncer -d pgbouncer.ini

连接后端数据库

$ psql -p 6688 -U testuser a1

$ psql -p 6688 -U testuser1 a2

连接 pgbouncer 数据库

psql -p 6688 pgbouncer -U wzb

pgbouncer=# show help;

NOTICE: Console usage

DETAIL:

SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION

SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM

SHOW DNSHOSTS|DNSZONES

SHOW STATS|STATSTOTALS|STATSAVERAGES|TOTALS

SET key = arg

RELOAD

PAUSE [<db>]

RESUME [<db>]

DISABLE <db>

ENABLE <db>

RECONNECT [<db>]

KILL <db>

SUSPEND

SHUTDOWN

SHOW

pgbouncer=# show clients;

type| C

user| pgbouncer

database| pgbouncer

state| active

addr| unix

port| 6432

local_addr| unix

local_port| 6432

connect_time| 2020-10-09 20:41:32 CST

request_time| 2020-10-09 20:41:32 CST

wait| 5

wait_us| 483185

close_needed| 0

ptr| 0x9ec340

link|

remote_pid| 23567

tls |

pgbouncer=# show pools;

database| pgbouncer

user| pgbouncer

cl_active| 1

cl_waiting| 0

sv_active|0

sv_idle|0

sv_used|0

sv_tested|0

sv_login|0

maxwait|0

maxwait_us|0

pool_mode| transaction

集群场景（读写分离）:

读写分离场景下 pgbouncer 的配置与前面配置基本一致，主要区别于要针对读和写进行分别部署 pgbouncer，因为 pgbouncer 本身只是数据库连接池，不具备负载均衡，或高可用，IP 漂移等特性，需要结合其他成熟产品进行组合使用。

多实例场景：

图 11 多实例场景结构图

多实例场景主要利用 linux 系统端口重用技术，这个特性依靠 Linux 内核上的支持（Linux3.6 以上版本），并结合 pgbouncer 自身支持（设置 so_reuseport=1）结合起来形成多实例场景下的 pgbouncer 使用，可以认为是 pgbouncer 的高可靠或者高可用，在某一个实例进程故障的情况下，其他实例集成仍然可以处理来自外部的数据库连接请求。从操作系统层面来看，属于多进程共享同一个端口。

实例配置 1

[databases]

a2 = host=127.0.0.1 port=5432 dbname=test1 pool_size=50

;;a1 = host=127.0.0.1 port=5432 dbname=test pool_size=30

[pgbouncer]

listen_port = 6688

listen_addr = 192.168.165.3

auth_type = md5

auth_file = /home/postgres/pgbouncer/bin/userlist.txt

logfile = /home/postgres/pgbouncer/pgbouncer1.log

pidfile =/home/postgres/pgbouncer/pgbouncer1.pid

unixsocketdir = /tmp/pg1

#unixsocketmode = 0777

admin_users = wzb

stats_users = wzb

pool_mode = session

maxclientconn=1000

defaultpoolsize=30

so_reuseport = 1

实例配置 2

[databases]

a2 = host=127.0.0.1 port=5432 dbname=test1 pool_size=50

;;a1 = host=127.0.0.1 port=5432 dbname=test pool_size=30

[pgbouncer]

listen_port = 6688

listen_addr = 192.168.165.3

auth_type = md5

auth_file = /home/postgres/pgbouncer/bin/userlist.txt

logfile = /home/postgres/pgbouncer/pgbouncer2.log

pidfile =/home/postgres/pgbouncer/pgbouncer2.pid

unixsocketdir = /tmp/pg2

#unixsocketmode = 0777

admin_users = wzb

stats_users = wzb

pool_mode = session

maxclientconn=1000

defaultpoolsize=30

so_reuseport = 1

导出数据库中用户名及密码到 userslist.txt

userslist.txt，格式为用户名 密码

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"

"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"

"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动多实例

./pgbouncer pgbouncer.ini

./pgbouncer pgbouncer1.ini

参考

[1]Pgbouncer 官网

[2]PgBouncer Configuration

[3]Tuning PostgreSQL for sysbench-tpcc

[4]understanding-user-management-in-pgbouncer

[5]performance-best-practices-for-using-azure-database-for-postgresql-connection-pooling

[6]guide-using-pgbouncer

[7]azure-database-for-postgresql/connection-handling-best-practice-with-postgresql

[8]steps-to-install-and-setup-pgbouncer-connection-pooling-proxy

[9]pg-phriday-securing-pgbouncer

了解更多 PostgreSQL 热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站

解决更多 PostgreSQL 相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区

下载更多 PostgreSQL 相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站