PostgreSQL连接池pgbouncer
pgbouncer是PostgreSQL的轻量级连接池，pgbouncer仅作为一个连接池和代理层为PostgreSQL和应用之间提供服务，数据库连接池在pgbouncer中包括会话连接池、事务连接池、语句连接池三种方式。详见官网
本文将引导您完成从零安装配置pgbouncer会话连接池
环境准备
下载软件包
http://www.pgbouncer.org/downloads/
安装PosgreSQL数据库14.9(略)
安装依赖
yum install -y libevent-devel
[root@pgcm1 ~]# yum install -y libevent-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package libevent-devel.x86_64 0:2.0.21-4.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================================================
Installing:
libevent-devel x86_64 2.0.21-4.el7 base 85 k
Transaction Summary
=============================================================================================================================================================================================================================================================================
Install 1 Package
Total download size: 85 k
Installed size: 357 k
Downloading packages:
libevent-devel-2.0.21-4.el7.x86_64.rpm | 85 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libevent-devel-2.0.21-4.el7.x86_64 1/1
Verifying : libevent-devel-2.0.21-4.el7.x86_64 1/1
Installed:
libevent-devel.x86_64 0:2.0.21-4.el7
Complete!
[root@pgcm1 ~]#
源码编译安装
上传pgbouncer-xxx.tar.gz到/home/postgres目录下，创建安装目录/opt/pgbouncer，源码编译安装
mkdir -p /opt/pgbouncer
tar zxf pgbouncer-1.15.0.tar.gz
cd pgbouncer-1.15.0/
./configure --prefix=/opt/pgbouncer --with-systemd
make && make install
/opt/pgbouncer/bin/pgbouncer --version
[postgres@pgcm1 ~]$ mkdir -p /opt/pgbouncer
[postgres@pgcm1 ~]$ tar zxf pgbouncer-1.15.0.tar.gz
[postgres@pgcm1 ~]$ cd pgbouncer-1.15.0/
[postgres@pgcm1 pgbouncer-1.15.0]$ ./configure --prefix=/opt/pgbouncer
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking target host type... unix
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /bin/install -c
checking for grep that handles long lines and -e... /bin/grep
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gcc option to accept ISO C99... -std=gnu99
checking for gcc -std=gnu99 option to accept ISO Standard C... (cached) -std=gnu99
checking how to run the C preprocessor... gcc -std=gnu99 -E
checking whether compiler supports __func__... yes
checking whether linker supports --as-needed... yes
checking whether compiler supports dependency generation... yes
checking for working warning switches... done
checking whether ln -s works... yes
checking for egrep... /bin/grep -E
checking for gawk... gawk
checking for a sed that does not truncate output... /bin/sed
checking for strip... strip
checking for ranlib... ranlib
checking for ar... ar
checking for pkg-config... /bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking for pandoc... no
checking for python... python
checking for strip... (cached) strip
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for inttypes.h... (cached) yes
checking stdbool.h usability... yes
checking stdbool.h presence... yes
checking for stdbool.h... yes
checking for unistd.h... (cached) yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking sys/socket.h usability... yes
checking sys/socket.h presence... yes
checking for sys/socket.h... yes
checking poll.h usability... yes
checking poll.h presence... yes
checking for poll.h... yes
checking sys/un.h usability... yes
checking sys/un.h presence... yes
checking for sys/un.h... yes
checking arpa/inet.h usability... yes
checking arpa/inet.h presence... yes
checking for arpa/inet.h... yes
checking netinet/in.h usability... yes
checking netinet/in.h presence... yes
checking for netinet/in.h... yes
checking netinet/tcp.h usability... yes
checking netinet/tcp.h presence... yes
checking for netinet/tcp.h... yes
checking sys/param.h usability... yes
checking sys/param.h presence... yes
checking for sys/param.h... yes
checking sys/uio.h usability... yes
checking sys/uio.h presence... yes
checking for sys/uio.h... yes
checking pwd.h usability... yes
checking pwd.h presence... yes
checking for pwd.h... yes
checking grp.h usability... yes
checking grp.h presence... yes
checking for grp.h... yes
checking sys/wait.h usability... yes
checking sys/wait.h presence... yes
checking for sys/wait.h... yes
checking sys/mman.h usability... yes
checking sys/mman.h presence... yes
checking for sys/mman.h... yes
checking syslog.h usability... yes
checking syslog.h presence... yes
checking for syslog.h... yes
checking netdb.h usability... yes
checking netdb.h presence... yes
checking for netdb.h... yes
checking dlfcn.h usability... yes
checking dlfcn.h presence... yes
checking for dlfcn.h... yes
checking err.h usability... yes
checking err.h presence... yes
checking for err.h... yes
checking pthread.h usability... yes
checking pthread.h presence... yes
checking for pthread.h... yes
checking endian.h usability... yes
checking endian.h presence... yes
checking for endian.h... yes
checking sys/endian.h usability... no
checking sys/endian.h presence... no
checking for sys/endian.h... no
checking byteswap.h usability... yes
checking byteswap.h presence... yes
checking for byteswap.h... yes
checking malloc.h usability... yes
checking malloc.h presence... yes
checking for malloc.h... yes
checking regex.h usability... yes
checking regex.h presence... yes
checking for regex.h... yes
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking fnmatch.h usability... yes
checking fnmatch.h presence... yes
checking for fnmatch.h... yes
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking xlocale.h usability... yes
checking xlocale.h presence... yes
checking for xlocale.h... yes
checking linux/random.h usability... yes
checking linux/random.h presence... yes
checking for linux/random.h... yes
checking for ucred.h... no
checking for sys/ucred.h... no
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes
checking for sys/resource.h... yes
checking for sys/wait.h... (cached) yes
checking for inline... inline
checking for C/C++ restrict keyword... __restrict
checking whether byte ordering is bigendian... no
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for pid_t... yes
checking for uid_t in sys/types.h... yes
checking for size_t... yes
checking for uint8_t... yes
checking for uint32_t... yes
checking for uint64_t... yes
checking for strlcpy... no
checking for strlcat... no
checking for strnlen... yes
checking for strsep... yes
checking for getpeereid... no
checking for sigaction... yes
checking for sigqueue... yes
checking for memmem... yes
checking for memrchr... yes
checking for mempcpy... yes
checking for inet_ntop... yes
checking for inet_pton... yes
checking for poll... yes
checking for getline... yes
checking for regcomp... yes
checking for err... yes
checking for errx... yes
checking for warn... yes
checking for warnx... yes
checking for getprogname... no
checking for setprogname... no
checking for posix_memalign... yes
checking for memalign... yes
checking for valloc... yes
checking for explicit_bzero... no
checking for memset_s... no
checking for reallocarray... no
checking for getopt... yes
checking for getopt_long... yes
checking for getopt_long_only... yes
checking for fls... no
checking for flsl... no
checking for flsll... no
checking for ffs... yes
checking for ffsl... yes
checking for ffsll... yes
checking for fnmatch... yes
checking for mbsnrtowcs... yes
checking for nl_langinfo... yes
checking for strtod_l... yes
checking for strtonum... no
checking for asprintf... yes
checking for vasprintf... yes
checking for timegm... yes
checking for localtime_r... yes
checking for gettimeofday... yes
checking for recvmsg... yes
checking for sendmsg... yes
checking for usleep... yes
checking for getrusage... yes
checking for syslog... yes
checking for mmap... yes
checking for getpeerucred... no
checking for arc4random_buf... no
checking for getentropy... no
checking for getrandom... no
checking for library containing WSAGetLastError... no
checking whether strerror_r is declared... yes
checking for strerror_r... yes
checking whether strerror_r returns char *... yes
checking for integer enc/dec functions... not found
checking for library containing clock_gettime... none required
checking for library containing getsockname... none required
checking for library containing gethostbyname... none required
checking for library containing hstrerror... none required
checking for lstat... yes
checking for LIBEVENT... yes
checking whether to build with systemd support... no
checking whether to use c-ares for DNS lookups... auto
checking for CARES... no
checking whether to use libudns... no
checking whether to use libevent for DNS lookups... yes
checking for OpenSSL... found
checking for SSL_CTX_use_certificate_chain_mem... no
checking for SSL_CTX_load_verify_mem... no
checking for asn1_time_parse... no
checking for root CA certs... /etc/ssl/cert.pem
checking whether to build debug binary... yes
checking whether to enable asserts... no
checking whether to fail on warnings... no
configure: creating ./config.status
config.status: creating config.mak
config.status: creating lib/usual/config.h
Results:
adns = evdns2
pam = no
systemd = no
tls = yes
[postgres@pgcm1 pgbouncer-1.15.0]$ make && make install
CC src/admin.c
CC src/client.c
CC src/dnslookup.c
CC src/hba.c
CC src/janitor.c
CC src/loader.c
CC src/main.c
CC src/objects.c
CC src/pam.c
CC src/pktbuf.c
CC src/pooler.c
CC src/proto.c
CC src/sbuf.c
CC src/scram.c
CC src/server.c
CC src/stats.c
CC src/system.c
CC src/takeover.c
CC src/util.c
CC src/varcache.c
CC src/common/base64.c
CC src/common/saslprep.c
CC src/common/scram-common.c
CC src/common/unicode_norm.c
CC src/common/wchar.c
CC lib/usual/aatree.c
CC lib/usual/base.c
CC lib/usual/cbtree.c
CC lib/usual/cfparser.c
CC lib/usual/crypto/chacha.c
CC lib/usual/crypto/csrandom.c
CC lib/usual/crypto/digest.c
CC lib/usual/crypto/entropy.c
CC lib/usual/crypto/keccak.c
CC lib/usual/crypto/keccak_prng.c
CC lib/usual/crypto/md5.c
CC lib/usual/crypto/sha256.c
CC lib/usual/cxalloc.c
CC lib/usual/cxextra.c
CC lib/usual/err.c
CC lib/usual/fileutil.c
CC lib/usual/getopt.c
CC lib/usual/hashing/lookup3.c
CC lib/usual/list.c
CC lib/usual/logging.c
CC lib/usual/mbuf.c
CC lib/usual/mempool.c
CC lib/usual/netdb.c
CC lib/usual/pgutil.c
CC lib/usual/regex.c
CC lib/usual/safeio.c
CC lib/usual/signal.c
CC lib/usual/slab.c
CC lib/usual/socket.c
CC lib/usual/socket_ntop.c
CC lib/usual/socket_pton.c
CC lib/usual/string.c
CC lib/usual/strpool.c
CC lib/usual/time.c
CC lib/usual/tls/tls.c
CC lib/usual/tls/tls_cert.c
CC lib/usual/tls/tls_client.c
CC lib/usual/tls/tls_compat.c
CC lib/usual/tls/tls_config.c
CC lib/usual/tls/tls_conninfo.c
CC lib/usual/tls/tls_ocsp.c
CC lib/usual/tls/tls_peer.c
CC lib/usual/tls/tls_server.c
CC lib/usual/tls/tls_util.c
CC lib/usual/tls/tls_verify.c
CCLD pgbouncer
INSTALL pgbouncer /opt/pgbouncer/bin
INSTALL README.md /opt/pgbouncer/share/doc/pgbouncer
INSTALL NEWS.md /opt/pgbouncer/share/doc/pgbouncer
INSTALL etc/pgbouncer.ini /opt/pgbouncer/share/doc/pgbouncer
INSTALL etc/userlist.txt /opt/pgbouncer/share/doc/pgbouncer
INSTALL doc/pgbouncer.1 /opt/pgbouncer/share/man/man1
INSTALL doc/pgbouncer.5 /opt/pgbouncer/share/man/man5
[postgres@pgcm1 pgbouncer-1.15.0]$/opt/pgbouncer/bin/pgbouncer --version
PgBouncer 1.15.0
libevent 2.0.21-stable
adns: evdns2
tls: OpenSSL 1.0.2k-fips 26 Jan 2017
[postgres@pgcm1 pgbouncer-1.15.0]$
配置pgbouncer.ini
cat > /opt/pgbouncer/bin/pgbouncer.ini << EOF
[databases]
pg14_db1 = host=localhost port=5432 dbname=postgres
pg14_db2 = host=localhost port=5432 dbname=testdb
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /opt/pgbouncer/bin/userlist.txt
logfile = /opt/pgbouncer/bin/pgbouncer.log
pidfile = /opt/pgbouncer/bin/pgbouncer.pid
admin_users = postgres
stats_users = postgres
pool_mode = session
ignore_startup_parameters = extra_float_digits
max_client_conn=1000
default_pool_size=30
EOF
psql
create user sunying password '123456';
alter user sunying with superuser;
psql -Aqt -c "select '\"'||usename||'\" \"'||passwd||'\"' from pg_shadow"
cat > /opt/pgbouncer/bin/userlist.txt << "EOF"
"postgres" "SCRAM-SHA-256$4096:2ECCGFxNLnzSecIYAcH9Ew==$T4oH00/lFN3iBZwuNBAPuuVVMH+dafX0x5swcnp7uOg=:ra4xd3qx2jg2amf3fmL2y5vlOEhLbt+eWblfdAWshk4="
"sunying" "SCRAM-SHA-256$4096:Vm3+FJgWkpmlw5tRNzH54A==$q5I/X6P7jZcbIOpQKyB4qJcDXSV9ea2uAiKe5AXDHus=:tohRHjrpW2JqixRsw7HGNHUuPdfpaQTvHjQRO7Xt6kQ="
EOF
#注意postgresql认证方式pg_hba.conf是host all all 0/0 scram-sha-256
[postgres@pgcm1 pgbouncer]$ cat > /opt/pgbouncer/bin/pgbouncer.ini << EOF
> [databases]
> pg14_db1 = host=localhost port=5432 dbname=postgres
> pg14_db2 = host=localhost port=5432 dbname=testdb
> [pgbouncer]
> listen_port = 6432
> listen_addr = 0.0.0.0
> auth_type = scram-sha-256
> auth_file = /opt/pgbouncer/bin/userlist.txt
> logfile = /opt/pgbouncer/bin/pgbouncer.log
> pidfile = /opt/pgbouncer/bin/pgbouncer.pid
> admin_users = postgres
> stats_users = postgres
> pool_mode = session
> ignore_startup_parameters = extra_float_digits
> max_client_conn=1000
> default_pool_size=30
> EOF
[postgres@pgcm1 pgbouncer]$ psql
Password for user postgres:
psql (14.9)
Type "help" for help.
postgres=# create user sunying password '123456';
CREATE ROLE
postgres=# alter user sunying with superuser;
ALTER ROLE
postgres=# \q
[postgres@pgcm1 pgbouncer]$ psql -Aqt -c "select '\"'||usename||'\" \"'||passwd||'\"' from pg_shadow"
"postgres" "SCRAM-SHA-256$4096:2ECCGFxNLnzSecIYAcH9Ew==$T4oH00/lFN3iBZwuNBAPuuVVMH+dafX0x5swcnp7uOg=:ra4xd3qx2jg2amf3fmL2y5vlOEhLbt+eWblfdAWshk4="
"sunying" "SCRAM-SHA-256$4096:Vm3+FJgWkpmlw5tRNzH54A==$q5I/X6P7jZcbIOpQKyB4qJcDXSV9ea2uAiKe5AXDHus=:tohRHjrpW2JqixRsw7HGNHUuPdfpaQTvHjQRO7Xt6kQ="
[postgres@pgcm1 pgbouncer]$ cat > /opt/pgbouncer/bin/userlist.txt << "EOF"
> "postgres" "SCRAM-SHA-256$4096:2ECCGFxNLnzSecIYAcH9Ew==$T4oH00/lFN3iBZwuNBAPuuVVMH+dafX0x5swcnp7uOg=:ra4xd3qx2jg2amf3fmL2y5vlOEhLbt+eWblfdAWshk4="
> "sunying" "SCRAM-SHA-256$4096:Vm3+FJgWkpmlw5tRNzH54A==$q5I/X6P7jZcbIOpQKyB4qJcDXSV9ea2uAiKe5AXDHus=:tohRHjrpW2JqixRsw7HGNHUuPdfpaQTvHjQRO7Xt6kQ="
> EOF
[postgres@pgcm1 pgbouncer]$ cat /opt/pgbouncer/bin/userlist.txt
"postgres" "SCRAM-SHA-256$4096:2ECCGFxNLnzSecIYAcH9Ew==$T4oH00/lFN3iBZwuNBAPuuVVMH+dafX0x5swcnp7uOg=:ra4xd3qx2jg2amf3fmL2y5vlOEhLbt+eWblfdAWshk4="
"sunying" "SCRAM-SHA-256$4096:Vm3+FJgWkpmlw5tRNzH54A==$q5I/X6P7jZcbIOpQKyB4qJcDXSV9ea2uAiKe5AXDHus=:tohRHjrpW2JqixRsw7HGNHUuPdfpaQTvHjQRO7Xt6kQ="
[postgres@pgcm1 pgbouncer]$
启动pgbouncer
export PATH=/opt/pgbouncer/bin:$PATH
pgbouncer -d /opt/pgbouncer/bin/pgbouncer.ini
#重启
pgbouncer -R -d /opt/pgbouncer/bin/pgbouncer.ini
lsof -i:6432
[postgres@pgcm1 pgbouncer]$ export PATH=/opt/pgbouncer/bin:$PATH
[postgres@pgcm1 pgbouncer]$ pgbouncer -d /opt/pgbouncer/bin/pgbouncer.ini
[postgres@pgcm1 pgbouncer]$ pgbouncer -R -d /opt/pgbouncer/bin/pgbouncer.ini
2023-09-11 11:21:50.099 CST [15022] LOG takeover_init: launching connection
2023-09-11 11:21:50.099 CST [15022] LOG S-0x1929c30: pgbouncer/pgbouncer@unix:6432 new connection to server
2023-09-11 11:21:50.099 CST [15022] LOG S-0x1929c30: pgbouncer/pgbouncer@unix:6432 login OK, sending SUSPEND
2023-09-11 11:21:50.099 CST [15022] LOG SUSPEND finished, sending SHOW FDS
2023-09-11 11:21:50.099 CST [15022] LOG got pooler socket: 0.0.0.0:6432
2023-09-11 11:21:50.099 CST [15022] LOG got pooler socket: unix:6432
2023-09-11 11:21:50.099 CST [15022] LOG SHOW FDS finished
2023-09-11 11:21:50.099 CST [15022] LOG disko over, going background
[postgres@pgcm1 pgbouncer]$ lsof -i:6432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
pgbouncer 10217 postgres 8u IPv4 63397 0t0 TCP *:pgbouncer (LISTEN)
[postgres@pgcm1 pgbouncer]$
连接测试
#创建连接测试库
createdb testdb
psql -h 192.168.17.7 -p 6432 -U sunying -d pg14_db2
psql -h 192.168.17.7 -p 6432 -U postgres -d pg14_db1
#查看连接池运行状态
psql -p 6432 -U pgbouncer -d pgbouncer
show pools;
show clients;
show stats;
show servers;
[postgres@pgcm1 ~]$ createdb testdb
Password:
[postgres@pgcm1 ~]$ psql -h 192.168.17.7 -p 6432 -U sunying -d pg14_db2
Password for user sunying:
psql (14.9)
Type "help" for help.
pg14_db2=# \q
[postgres@pgcm1 ~]$ psql -p 6432 -U postgres -d pg14_db1
Password for user postgres:
psql (14.9)
Type "help" for help.
pg14_db1=# \c pg14_db2
You are now connected to database "pg14_db2" as user "postgres".
pg14_db2=# \q
[postgres@pgcm1 ~]$
[postgres@pgcm1 ~]$ ps f -u postgres
PID TTY STAT TIME COMMAND
10320 pts/1 S 0:00 -bash
10380 pts/1 R+ 0:00 \_ ps f -u postgres
10104 pts/0 S+ 0:00 -bash
10217 ? S 0:00 pgbouncer -R -d /opt/pgbouncer/bin/pgbouncer.ini
10080 ? Ss 0:00 /opt/pg14/bin/postgres -D /opt/pgdata
10083 ? Ss 0:00 \_ postgres: logger
10085 ? Ss 0:00 \_ postgres: checkpointer
10086 ? Ss 0:00 \_ postgres: background writer
10087 ? Ss 0:00 \_ postgres: walwriter
10088 ? Ss 0:00 \_ postgres: autovacuum launcher
10089 ? Ss 0:00 \_ postgres: archiver
10090 ? Ss 0:00 \_ postgres: stats collector
10091 ? Ss 0:00 \_ postgres: logical replication launcher
10238 ? Ss 0:00 \_ postgres: postgres postgres 127.0.0.1(38762) idle
10252 ? Ss 0:00 \_ postgres: sunying testdb 127.0.0.1(38768) idle
[postgres@pgcm1 ~]$ psql -p 6432 -U pgbouncer -d pgbouncer
psql (14.9, server 1.15.0/bouncer)
Type "help" for help.
pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
pg14_db1 | postgres | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | session
pg14_db1 | sunying | 2 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | session
pg14_db2 | sunying | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | session
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
(4 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 | pg14_db1 | active | unix | 6432 | unix | 6432 | 2023-09-11 13:49:29 CST | 2023-09-11 13:49:29 CST | 0 | 0 | 0 | 0x1ded120 | | 10396 |
C | sunying | pg14_db1 | active | 192.168.17.1 | 55850 | 192.168.17.7 | 6432 | 2023-09-11 13:48:34 CST | 2023-09-11 13:48:34 CST | 0 | 0 | 0 | 0x1deccb0 | 0x1de60c0 | 0 |
C | sunying | pg14_db1 | active | 192.168.17.1 | 55851 | 192.168.17.7 | 6432 | 2023-09-11 13:48:34 CST | 2023-09-11 13:48:49 CST | 0 | 0 | 0 | 0x1decee8 | 0x1de62f8 | 0 |
C | pgbouncer | pgbouncer | active | unix | 6432 | unix | 6432 | 2023-09-11 13:56:13 CST | 2023-09-11 13:59:47 CST | 200 | 788994 | 0 | 0x1ded358 | | 10481 |
(4 rows)
pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
pg14_db1 | postgres | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | session
pg14_db1 | sunying | 2 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | session
pg14_db2 | postgres | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | session
pg14_db2 | sunying | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | session
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
(5 rows)
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
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
pg14_db1 | 31 | 31 | 13952 | 119150 | 15310429 | 15310429 | 15293880 | 0 | 0 | 0 | 0 | 0 | 0 | 0
pg14_db2 | 0 | 0 | 0 | 0 | 0 | 0 | 4709 | 0 | 0 | 0 | 0 | 0 | 0 | 0
pgbouncer | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(3 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 | sunying | pg14_db1 | active | 127.0.0.1 | 5432 | 127.0.0.1 | 38770 | 2023-09-11 13:48:31 CST | 2023-09-11 13:48:34 CST | 0 | 0 | 0 | 0x1de60c0 | 0x1deccb0 | 10384 |
S | sunying | pg14_db1 | active | 127.0.0.1 | 5432 | 127.0.0.1 | 38772 | 2023-09-11 13:48:49 CST | 2023-09-11 13:48:49 CST | 0 | 0 | 0 | 0x1de62f8 | 0x1decee8 | 10394 |
S | postgres | pg14_db2 | used | 127.0.0.1 | 5432 | 127.0.0.1 | 38774 | 2023-09-11 14:02:39 CST | 2023-09-11 14:02:39 CST | 0 | 0 | 0 | 0x1de5e88 | | 10587 |
(3 rows)
pgbouncer=# \q
[postgres@pgcm1 ~]$
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
