PG连接池工具之Pgbouncer
通常，连接池的目的是降低打开新的PostgreSQL（或其他）数据库连接的性能影响。使用连接池能够降低服务器的内存开销，并且有效复用数据库连接，提供了良好的数据库连接性能管理。
在我们进行Postgresql入门的时候，通常会看到这段介绍“PostgreSQL服务器可以处理来自客户端的多个并发连接。为此，它为每个连接启动（“fork”）新进程，从那时起，客户端和新的服务器进程进行通信，而无需原始postgres进程进行干预。因此，主服务器进程始终在运行，等待客户端连接，而客户端及关联的服务器进程来来往往。”但是，这意味着每个新连接都会分叉一个新进程，保留在内存中，并可能在多个会话中变得过分繁忙。在业务量较小的情况下，这种方式基本可以满足要求，但是当业务量迅速激增，我们可能就需要不断去更改max_connections来满足客户端的需求。当时同样也带来了很大的问题，如频繁的关闭和创建连接造成的内存开销，管理已产生的大量连接等等，最终导致服务器响应缓慢而无法对外提供数据库服务。在这样一个背景下，数据库连接池就被提出来了，对于使用Postgresql数据库来说，一般分为客户端连接池，比如c3p0、druid等等；另外一种则是服务器端连接池，例如pgbouncer、odyssey、pgpoolII等。
Pgbouncer简介
https://pgbouncer.github.io/install.html
https://pgbouncer.github.io/usage.html
https://pgbouncer.github.io/config.html
https://github.com/pgbouncer/pgbouncer
Pgbouncer是一个针对PostgreSQL数据库的轻量级连接池，
任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接，
然后pgbouncer 会处理与服务器连接，或者是重用已存在的连接。
pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。
PgBouncer的作用
- PgBouncer可以在后端数据库和前端应用间建立连接桥梁，由PgBouncer去处理和后端数据库的连接关系。
- 对客户端连接进行限制，预防过多或者恶意的连接请求。
- 主要工作还是每一个数据节点对数据的存取，pgbouncer只是提供给客户端数据节点的分配和链接以及限制客户端连接的数量。整体上实现了对数据的负载均衡。
- 能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。
- 能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
轻量级体现在：使用libevent进行socket通信、C语言编写，效率高，每个连接仅消耗2kB内存 。
PgBouncer的优点
内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包
可以把不同的数据库连接到一个机器上，而对客户端保持透明
支持在线的重新配置而无须重启
pgbouncer 可以限制每一对用户+数据库 到PostgreSQL Cluster的总连接数
PgBouncer的缺点
仅支持V3协议，因此后端版本须>=7.4
并没有真正实现数据库的负载均衡，如果单一的对一个数据库操作会使某个数据节点读写量增大
当某个数据库down掉后，就不能通过pgbouncer连接起来，缺少了数据库备份的功能
PgBouncer 支持三种连接池模型
1.session（会话连接池）
会话级连接，在它的连接生命周期内，连接池分配给它一个数据库连接。客户端断开时，数据库连接会放回连接池中。
2.transaction（事务连接池）
事务级别连接，当客户端的每个事务结束时，数据库连接就会重新释放回连接池中，再次执行一个事务时，需要再从连接池中获取一个连接。
3.statement（语句连接池）
每执行完一个SQL时，连接就会重新释放回连接池中，再次执行一个SQL 时，需要再次从连接池中获得连接。这种模式意味着在客户端强制autocomit模式。
连接池模式对比**
从上述对比情况来看，在连接池的选择上，需要依据业务环境特点来进行选择，默认情况下推荐使用事务连接池，它兼顾了执行事务的特性，尤其多语句的支持，并且不会像会话连接池那样，尝尝处于等待状态。当然事务模式并不支持预编译语句。而根据具体业务场景的特殊需要，有些时候需要客户端与服务器端保持连接，或者支持预编译语句，这样只能选择会话池模式。还有一些特例情况，某些业务场景只是单语句执行，那么语句池模式可能更适合。因此对比这三种模式，可以发现从对客户端操作的支持程度来讲，会话池支持度最高，其次是事务池，最后是语句池模式。但是从支持的连接数来讲，可能刚好是相反的顺序。
SQL特性对照表
上表为会话连接池和事务连接池的SQL特性对比情况，可以通过对比具体业务场景与SQL特性的符合度，来对连接池模式进行选型。
下面列举了一些示例场景：
- 有些只运行快速查询，因此在没有事务的情况下可以共享一个会话来处理上百个并发查询。
- 一些角色成员对于会话级并发是安全的，并且总是使用事务。因此，他们可以安全地共享数百个并发事务的多个会话。
- 有些角色过于复杂，无法与其他人共享会话。因此，您对它们使用会话池模式可以避免当所有“插槽”都已占用时连接错误。
- 不要使用它代替HAProxy或其他负载均衡器。尽管pgbouncer具有一些可配置的功能来解决负载均衡器要解决的问题，例如dns_max_ttl，并且可以为其设置DNS配置，但是大多数产品环境都使用HAProxy或其他用于HA的负载均衡器。这是因为HAProxy确实擅长以循环方式在服务器之间实现负载平衡，而不是pgbouncer。尽管pgbouncer对于postgres连接池更好，但最好使用一个小型守护程序来完美地执行一项任务，而不是使用较大的守护程序来完成两项任务，那样效果更糟。
下载安装
http://www.pgbouncer.org/downloads/
http://www.pgbouncer.org/install.html
yum安装
在Linux发行版中，包含已经编译好的PgBouncer，可以直接安装。
RHEL/CentOS平台直接使用yum命令来安装。
Debian/Ubuntu平台下，直接使用apt-get命令安装。
1 2 | yum install -y pgbouncer apt-get install -y pgbouncer |
编译安装
在官网http://www.pgbouncer.org/downloads/上下载源码编译安装，需要先安装libevent-devel这个rpm包。
1 2 3 4 5 6 7 8 9 | wget http://www.pgbouncer.org/downloads/files/1.16.1/pgbouncer-1.16.1.tar.gz tar -zxvf pgbouncer-1.16.1.tar.gz cd pgbouncer-1.16.1 yum install -y libevent-devel ./configure --prefix=/usr/local --with-systemd make make install |
编译安装 默认PgBouncer是安装到 /usr/local/bin 目录下的。
安装完成：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [root@lhrpgall /]# cd /usr/local/bin/ [root@lhrpgall bin]# ll total 1884 -rwxr-xr-x 1 root root 1925408 Jan 18 18:45 pgbouncer [root@lhrpgall ~]# pgbouncer -h pgbouncer is a connection pooler for PostgreSQL. Usage: pgbouncer [OPTION]... CONFIG_FILE Options: -d, --daemon run in background (as a daemon) -q, --quiet run quietly -R, --reboot do an online reboot -u, --user=USERNAME assume identity of USERNAME -v, --verbose increase verbosity -V, --version show version, then exit -h, --help show this help, then exit Report bugs to <https://github.com/pgbouncer/pgbouncer/issues>. PgBouncer home page: <https://www.pgbouncer.org/> |
配置文件
使用系统自带工具（yum或apt-get）安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini
源码安装后，在/usr/local/share/doc/pgbouncer目录下有一个示例的配置文件pgbouncer.ini，可作为配置文件的模板。
默认的配置和含义如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [databases] appdb = host=172.72.6.30 dbname=lhrdb port=5432 [pgbouncer] logfile = /var/log/pgbouncer/pgbouncer.log # 日志文件位置 pidfile = /var/run/pgbouncer/pgbouncer.pid # pid文件位置 listen_addr = 127.0.0.1 # 监听的地址 listen_port = 6432 # 监听的端口 auth_type = trust # 认证方式 auth_file = /etc/pgbouncer/userlist.txt # 认证文件 admin_users = postgres # 管理员用户名 stats_users = stats, postgres # 状态用户？stats和postgres pool_mode = session # 池的模式，默认session级别 server_reset_query = DISCARD ALL # max_client_conn = 100 # 最大连接用户数，客户端到pgbouncer的链接数量 default_pool_size = 20 # 默认池大小，表示建立多少 pool_size = 20 #配置连接池的大小，如果没有配置此项，连接池的大小将使用default_pool_size配置项的值。 |
默认情况下不配置任何数据库信息，从上面还可以看到，配置主要分为两部分：
第一部分是[databases]区域，是用来配置数据库连接相关信息的。
第二部分是[pgbouncer]，是pgbouncer自身的配置。
其中，
1 2 | [databases] appdb = host=172.72.6.30 dbname=lhrdb port=5432 |
本句话说明该pgbouncer创建了针对172.72.6.30的lhrdb的一个连接池，该链接池对调用方的呈现的数据库名称是appdb，它映射到本机的lhrdb数据库上。所有访问pgbouncer上的appdb的请求都会转到lhrdb上完成。
这意味着，客户端通过pgbouncer单次连接时，只能连接到某台主机上的某个数据库。
配置示例
官方配置示例：http://www.pgbouncer.org/config.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | cat > /etc/pgbouncer/pgbouncer.ini <<"EOF" [databases] appdb = host=172.72.6.30 dbname=lhrdb port=5432 [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 ;; resolve: unsupported startup parameter: extra_float_digits ignore_startup_parameters = extra_float_digits EOF -- 若使用源码编译安装，则还需要创建如下目录 mkdir -p /etc/pgbouncer/ mkdir -p /var/log/pgbouncer/ mkdir -p /var/run/pgbouncer/ |
配置密码文件
[pgbouncer]区域使用默认配置，补充说明以下两个配置：
1 2 | auth_type = trust # 认证方式 auth_file = /etc/pgbouncer/userlist.txt # 认证文件 |
第一行是用于配置登录pgbouncer的认证方式，和PostgreSQL认证方式相同，默认是trust，即所有的都信任（不需要密码即可登录），还可以使用md5加密的形式。注意：这里的trust表示只有参数“auth_file”中配置的用户才会trust认证，其它用户登陆均会报错“psql: error: ERROR: "trust" authentication failed”
第二行是用于配置认证用户的，即连接pgbouncer的用户名都保存在该文件中。
当第一行设置为md5加密时，则加密的密码也必须保存在第二行配置的文件中。
如果这个文件不存在，那么登录的时候，无论是哪个用户，都会提示下面的错误：
1 2 3 4 5 6 | -bash-4.2$ psql -p 6432 testdb -h 127.0.0.1 psql: ERROR: No such user: postgres -bash-4.2$ psql -p 6432 testdb -h 127.0.0.1 -U dbuser psql: ERROR: No such user: dbuser |
而这个认证文件默认情况下是没有的，因此需要手动生成。
从PostgreSQL的9.x开始，所有的用户密码都是保存在pg_shadow表里。
PostgreSQL 8.x版本则是保存在数据库目录下，可以直接复制过来使用。
生成这个认证文件有两种方式，如下：
1）SQL语句生成认证文件
之前我们说过，用户密码默认是保存在pg_shadow表里的，如下面所示：
1 2 3 4 5 | appdb=# select usename, passwd from pg_shadow order by 1; usename | passwd ----------+------------------------------------- postgres | md5da3edeb741de62d06ab73785ed222494 (1 row) |
usename和passwd两列里面保存的就是我们需要的账号和密码
我们使用copy命令将它们导出来（或者手动编辑也可以）：
1 2 3 | postgres=# copy (select usename, passwd from pg_shadow order by 1) to '/var/lib/pgsql/9.5/auth_file'; COPY 2 |
我们打开这个auth_file文件，内容如下：
1 | dbuser md5baa6c789c3728a1a449b82005eb54a19 |
里面保存有postgres的账号，一般不要使用这个超级管理员的身份，最好删掉。
然后保留可以连接数据库的用户账号和加密后的密码，将这个文件转移到上面配置项指定的位置。并且文件名称要和上面变量里定义的文件名一致，否则会提示找不到这个文件。
最后还要注意的一点是，默认导出的文件里用户名和密码的格式pgbouncer无法识别，需要用双引号引起来才能正确识别
如下所示：
1 | "dbuser" "md5baa6c789c3728a1a449b82005eb54a19" |
2）使用mkauth.py来生成文件
这个文件是使用python编写的一个脚本，已经赋予了可执行权限。
执行的时候需要两个参数，基本格式是：
/etc/pgbouncer/mkauth.py 用户列表文件 "数据库连接参数"
示例：
1 | /etc/pgbouncer/mkauth.py /etc/pgbouncer/userlist.txt "host=172.72.6.30 dbname=lhrdb port=5432 user=postgres password=lhr" |
这里比较重要的是后面那一段参数，=号两边不能有空格，两个键值对之间要用空格隔开，不能用逗号，否则会报错。用户必须是有查询pg_shadow表权限的用户请记住这里的限制条件。
如果没有错误的话，就会在/etc/pgbouncer/目录下生成userlist.txt文件。
文件内容如下所示：
1 2 | "dbuser" "md5baa6c789c3728a1a449b82005eb54a19" "" "postgres" "" "" |
默认会备份出PostgreSQL数据库的pg_shadow表里的所有数据库，包括postgres用户。所有的用户名和密码都会用双引号引起来，比手动备份更方便。这里唯一麻烦的就是脚本后面的连接字符串。
3）手动生成文件
配置密码文件，参考：http://www.pgbouncer.org/config.html#authentication-file-format ，密码需要以双引号引起来，然后用户名和密码之间使用空格隔开：
1 | echo '"postgres" "lhr"' >> /etc/pgbouncer/userlist.txt |
这样就是明文，不建议。
启动
不能以root启动，会报错：“FATAL PgBouncer should not run as root”。
当用户文件配置好以后，就可以启动pgbouncer来使用了。
使用linux发行版自带的包管理工具安装pgbouncer的时候，它会自动创建一个pgbouncer用户
如果是自己编译的话，则需要手动创建这个用户。创建完成以后。
需要切换到这个用户下来启动pgbouncer，pgbouncer是不允许在root用户下启动的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- 若是编译安装 useradd pgbouncer chown -R pgbouncer.pgbouncer /etc/pgbouncer/ chown -R pgbouncer.pgbouncer /var/log/pgbouncer/ chown -R pgbouncer.pgbouncer /var/run/pgbouncer/ -- 启动，若生产环境使用，那么不建议加-v参数 su - pgbouncer pgbouncer -d -v /etc/pgbouncer/pgbouncer.ini -- 若是yum安装 systemctl start pgbouncer systemctl enable pgbouncer systemctl status pgbouncer [root@lhrpgall /]# netstat -tulnp | grep pgb tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 21140/pgbouncer tcp6 0 0 :::6432 :::* LISTEN 21140/pgbouncer [root@lhrpgall /]# ps -ef|grep pgb pg13 21140 0 0 19:25 ? 00:00:00 pgbouncer -d -v /etc/pgbouncer/pgbouncer.ini pg13 21325 21098 0 19:29 pts/1 00:00:00 tailf /var/log/pgbouncer/pgbouncer.log root 21389 21342 0 19:29 pts/2 00:00:00 grep --color=auto pgb [root@lhrpgall ~]# ps -ef|grep pgb pgbounc+ 2173 1 0 10:06 ? 00:00:00 /usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini root 2207 468 0 10:07 pts/3 00:00:00 grep --color=auto pgb |
停止
可以通过systemctl来操作，也可以通过kill命令来停止。
1 | systemctl stop pgbouncer |
kill命令格式是：
1 | kill `cat /var/run/pgbouncer/pgbouncer.pid` |
或
1 | cat /var/run/pgbouncer/pgbouncer.pid | xargs kill -9 |
登录 pgbouncer虚拟库(管理员终端维护操作）
pgbouncer对外提供了一个虚拟数据库pgbouncer，之所以称为虚拟数据库，是因为它可以提供像PostgreSQL那样的数据库操作界面，但是这个数据库却并不是真实存在的，而是pgbouncer虚拟出来的一个命令行界面。
如果修改了一些配置参数，可以不用重启 pgbouncer 而是 reload 使其生效。
注意：
只有在配置了参数 admin_users 或者 stats_users才会连接到控制台。
登录命令是：
1 | psql -p 6432 -d pgbouncer -U postgres -h 127.0.0.1 |
登录以后可以使用：
1 2 3 4 5 | show help; #查看所有的帮助命令信息 show clients ; #用来查看客户端连接信息 show pools; #用来查看连接池信息 |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | [root@lhrpgall ~]# psql -p 5439 -d pgbouncer -U postgres -h 127.0.0.1 Password for user postgres: psql (13.3, server 1.16.1/bouncer) Type "help" for help. pgbouncer=# show help; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SHOW DNS_HOSTS|DNS_ZONES SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS SET key = arg RELOAD PAUSE [<db>] RESUME [<db>] DISABLE <db> ENABLE <db> RECONNECT [<db>] KILL <db> SUSPEND SHUTDOWN SHOW pgbouncer=# show config; key | value | default | changeable ---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------ admin_users | postgres | | yes application_name_add_host | 0 | 0 | yes auth_file | /etc/pgbouncer/userlist.txt | | yes auth_hba_file | | | yes auth_query | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | yes auth_type | md5 | md5 | yes auth_user | | | yes autodb_idle_timeout | 3600 | 3600 | yes client_idle_timeout | 0 | 0 | yes client_login_timeout | 60 | 60 | yes client_tls_ca_file | | | yes client_tls_cert_file | | | yes client_tls_ciphers | fast | fast | yes client_tls_dheparams | auto | auto | yes client_tls_ecdhcurve | auto | auto | yes client_tls_key_file | | | yes client_tls_protocols | secure | secure | yes client_tls_sslmode | disable | disable | yes conffile | /etc/pgbouncer/pgbouncer.ini | | yes default_pool_size | 20 | 20 | yes disable_pqexec | 0 | 0 | no dns_max_ttl | 15 | 15 | yes dns_nxdomain_ttl | 15 | 15 | yes dns_zone_check_period | 0 | 0 | yes idle_transaction_timeout | 0 | 0 | yes ignore_startup_parameters | extra_float_digits | | yes job_name | pgbouncer | pgbouncer | no listen_addr | * | | no listen_backlog | 128 | 128 | no listen_port | 5439 | 6432 | no log_connections | 1 | 1 | yes log_disconnections | 1 | 1 | yes log_pooler_errors | 1 | 1 | yes log_stats | 1 | 1 | yes logfile | /var/log/pgbouncer/pgbouncer.log | | yes max_client_conn | 100 | 100 | yes max_db_connections | 0 | 0 | yes max_packet_size | 2147483647 | 2147483647 | yes max_user_connections | 0 | 0 | yes min_pool_size | 0 | 0 | yes pidfile | /var/run/pgbouncer/pgbouncer.pid | | no pkt_buf | 4096 | 4096 | no pool_mode | session | session | yes query_timeout | 0 | 0 | yes query_wait_timeout | 120 | 120 | yes reserve_pool_size | 0 | 0 | yes reserve_pool_timeout | 5 | 5 | yes resolv_conf | | | no sbuf_loopcnt | 5 | 5 | yes server_check_delay | 30 | 30 | yes server_check_query | select 1 | select 1 | yes server_connect_timeout | 15 | 15 | yes server_fast_close | 0 | 0 | yes server_idle_timeout | 600 | 600 | yes server_lifetime | 3600 | 3600 | yes server_login_retry | 15 | 15 | yes server_reset_query | DISCARD ALL | DISCARD ALL | yes server_reset_query_always | 0 | 0 | yes server_round_robin | 0 | 0 | yes server_tls_ca_file | | | yes server_tls_cert_file | | | yes server_tls_ciphers | fast | fast | yes server_tls_key_file | | | yes server_tls_protocols | secure | secure | yes server_tls_sslmode | disable | disable | yes so_reuseport | 0 | 0 | no stats_period | 60 | 60 | yes stats_users | | | yes suspend_timeout | 10 | 10 | yes syslog | 0 | 0 | yes syslog_facility | daemon | daemon | yes syslog_ident | pgbouncer | pgbouncer | yes tcp_defer_accept | 1 | | yes tcp_keepalive | 1 | 1 | yes tcp_keepcnt | 0 | 0 | yes tcp_keepidle | 0 | 0 | yes tcp_keepintvl | 0 | 0 | yes tcp_socket_buffer | 0 | 0 | yes tcp_user_timeout | 0 | 0 | yes unix_socket_dir | /tmp | /tmp | no unix_socket_group | | | no unix_socket_mode | 511 | 0777 | no user | | | no verbose | 0 | | yes (84 rows) pgbouncer=# show clients ; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls ------+----------+-----------+--------+---------------+-------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+----- C | postgres | appdb | active | 192.168.66.35 | 61514 | 172.17.0.4 | 5439 | 2022-01-19 10:27:16 CST | 2022-01-19 10:27:16 CST | 0 | 0 | 0 | 0x1740d18 | | 0 | C | postgres | pgbouncer | active | 127.0.0.1 | 47570 | 127.0.0.1 | 5439 | 2022-01-19 10:26:22 CST | 2022-01-19 10:27:24 CST | 60 | 504722 | 0 | 0x1740ae0 | | 0 | (2 rows) pgbouncer=# show pools; database | user | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode -----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+----------- appdb | postgres | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | session pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement (2 rows) pgbouncer=# show databases; name | host | port | database | force_user | pool_size | min_pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled -----------+-------------+------+-----------+------------+-----------+---------------+--------------+-----------+-----------------+---------------------+--------+---------- appdb | 172.72.6.30 | 5432 | lhrdb | | 20 | 0 | 0 | | 0 | 1 | 0 | 0 pgbouncer | | 5439 | pgbouncer | pgbouncer | 2 | 0 | 0 | statement | 0 | 0 | 0 | 0 (2 rows) pgbouncer=# show servers; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls ------+----------+----------+-------+-------------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+----- S | postgres | appdb | used | 172.72.6.30 | 5432 | 172.72.6.2 | 59682 | 2022-01-19 10:27:16 CST | 2022-01-19 10:27:16 CST | 0 | 0 | 0 | 0x174fce0 | | 1889 | (1 row) pgbouncer=# SHOW STATS; database | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time -----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+--------------- appdb | 0 | 0 | 0 | 0 | 0 | 0 | 6452 | 0 | 0 | 0 | 0 | 0 | 0 | 72 pgbouncer | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) |
日志信息
1 | /var/log/pgbouncer/pgbouncer.log |
in B/s：每秒读入字节数。
out B/s：和in B/s一样，表示每秒读出的字节数。
query us：平均每个查询花费的时间，单位微秒（1S（秒）等于1000ms(毫秒)，1ms等于1000us(微秒)。）。
wait us : 等待耗时 微秒
xacts/s： 每秒多少个事务操作
queries/s：每秒多少次请求数
xact us：每个事务耗时多少微秒
登陆验证
本地登陆验证
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [root@lhrpgall /]# psql -U postgres -d appdb -h 127.0.0.1 -p 6432 Password for user postgres: psql (13.3, server 13.4 (Debian 13.4-4.pgdg110+1)) Type "help" for help. appdb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- lhrdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb2 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb3 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (6 rows) appdb=# \c lhrdb FATAL: no such database: lhrdb Previous connection kept appdb=# \c lhrdb2 FATAL: no such database: lhrdb2 Previous connection kept appdb=# \d List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | t1 | table | postgres public | tb_test | table | postgres public | tb_test2 | table | postgres public | tb_test3 | table | postgres public | test | table | postgres (5 rows) |
远程登陆验证
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | C:\Users\lhrxxt>psql -U postgres -d appdb -h 1192.168.66.35 -p 15439 psql: error: could not translate host name "1192.168.66.35" to address: Unknown host C:\Users\lhrxxt>psql -U postgres -d appdb -h 192.168.66.35 -p 15439 Password for user postgres: psql (14.0, server 13.4 (Debian 13.4-4.pgdg110+1)) Type "help" for help. appdb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- lhrdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb2 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb3 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (6 rows) appdb=# appdb=# create database lhdb4; CREATE DATABASE appdb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- lhdb4 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb2 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb3 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (7 rows) appdb=# \c lhrdb4 connection to server at "192.168.66.35", port 15439 failed: FATAL: no such database: lhrdb4 Previous connection kept |
多个客户端登陆
1 | psql -U postgres -d appdb -h 192.168.66.35 -p 15439 |
登陆多个客户端，然后查看数据库服务器的后台进程，发现只有1个（postgres: postgres lhrdb 172.72.6.2(59578) idle）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | root@lhrpg30:/# ps -ef|grep post postgres 1 0 0 Jan14 ? 00:00:43 postgres postgres 27 1 0 Jan14 ? 00:00:04 postgres: checkpointer postgres 28 1 0 Jan14 ? 00:01:25 postgres: background writer postgres 29 1 0 Jan14 ? 00:01:38 postgres: walwriter postgres 30 1 0 Jan14 ? 00:00:56 postgres: autovacuum launcher postgres 31 1 0 Jan14 ? 00:00:56 postgres: stats collector postgres 32 1 0 Jan14 ? 00:00:03 postgres: pglogical supervisor postgres 33 1 0 Jan14 ? 00:00:03 postgres: logical replication launcher postgres 51 1 0 Jan14 ? 00:00:02 postgres: pglogical manager 16384 postgres 105 1 0 Jan14 ? 00:00:24 postgres: walsender postgres 172.72.6.32(41266) idle postgres 3393 1 0 Jan14 ? 00:00:22 postgres: walsender postgres 172.72.6.31(56664) idle postgres 7565 1 0 Jan15 ? 00:00:01 postgres: pglogical manager 16596 postgres 7624 1 0 Jan15 ? 00:00:14 postgres: walsender postgres 172.72.6.32(50340) idle postgres 20193 1 0 Jan17 ? 00:00:05 postgres: walsender postgres 172.72.6.31(33678) idle postgres 29958 1 0 19:51 ? 00:00:00 postgres: postgres lhrdb 192.168.66.64(30547) idle postgres 29959 1 0 19:51 ? 00:00:00 postgres: postgres lhrdb 192.168.66.64(30550) idle postgres 30071 1 0 20:12 ? 00:00:00 postgres: postgres lhrdb 172.72.6.2(59578) idle root 30099 29675 0 20:17 pts/4 00:00:00 grep post |
报错
配置pgbouncer的时候，遇到这个错误“unsupported startup parameter: extra_float_digits”。用数据库客户端图形管理工具（dbeaver）连接的时候，报错；但是，使用navicat连接或使用psql客户端连接却不报错：
解决：配置文件中添加 ignore_startup_parameters = extra_float_digits
然后重启pgbouncer即可。
Navicat的连接：
