PostgreSQL轻量级数据库连接池工具PGBouncer

小白学信创

于 2024-08-11 21:52:56 发布

阅读量1k

点赞数 22

分类专栏： pg-collector PostgreSQL 文章标签：数据库 postgresql oracle

本文链接：https://blog.csdn.net/loveshenyang/article/details/141112616

版权

pg-collector 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

PostgreSQL

2 篇文章 0 订阅

订阅专栏

一、pgbouncer简介

1.1、功能简介

PGBouncer是一个轻量级的PostgreSQL数据库连接池，主要用于减少数据库服务器的连接压力，提高数据库的处理能力。它支持三种连接池模式：会话连接池、事务连接池和语句连接池，适用于不同的使用场景。PGBouncer的内存需求较低（默认为每个连接2KB），因为它不需要一次性接收完整的数据包。此外，PGBouncer不与单个后端服务器绑定，目标数据库可以位于不同的主机上。它支持在线重新配置和在线重启/升级，而不会断开客户端的连接。PGBouncer只支持协议V3版本，因此后端版本必须大于等于7.4。

1.2、PGBouncer主要特点

连接池：维护到PostgreSQL数据库的连接池，允许多个客户端复用已建立的连接，减少连接和断开连接的开销。
轻量级：占用资源极少，可以在几乎不影响系统性能的情况下提供连接池服务。
配置灵活：支持不同的池化模式，以适应不同的使用场景。
高性能：减少了连接建立的开销，提高应用程序的响应速度和吞吐量。
安全性：支持TLS/SSL连接，确保数据传输过程中的安全。
易于管理：提供简单的管理接口，便于监控和管理连接池状态。

1.3、PGBouncer的使用场景

高并发应用：处理大量并发连接的应用程序，可以显著提高性能。
微服务架构：多个服务可能需要访问同一个数据库，PGBouncer可以有效地管理这些连接。
资源受限的环境：在内存较小的环境下，直接连接数据库可能会导致资源快速耗尽，PGBouncer通过复用连接来减少资源消耗。

二、Pgbouncer部署

IP地址	PostgreSQL版本	PGBouncer版本
192.168.1.101	16.2	1.23.0

2.1、安装依赖

PgBouncer 的编译依赖于以下几点：

GNU Make 3.81+
Libevent 2.0+
pkg-config
OpenSSL 1.0.1+ for TLS support
(optional) c-ares as alternative to Libevent’s evdns
(optional) PAM libraries

依赖安装脚本如下：

yum install libevent -y
yum install libevent-devel -y

2.2、安装PostgreSQL

说明：本文不再讲解PostgreSQL的部署流程，如需了解，请移步到以下链接：
https://www.modb.pro/db/1794403072704860160

2.3、在线下载安装包

说明：
官网地址：https://www.pgbouncer.org
安装包：https://www.pgbouncer.org/downloads/files/1.23.1/pgbouncer-1.23.1.tar.gz

代码如下：

[postgres@Node1 ~]$ wget http://www.pgbouncer.org/downloads/files/1.23.0/pgbouncer-1.23.0.tar.gz
--2024-08-04 16:52:15--  http://www.pgbouncer.org/downloads/files/1.23.0/pgbouncer-1.23.0.tar.gz
Resolving www.pgbouncer.org (www.pgbouncer.org)... 185.199.110.153, 185.199.108.153, 185.199.109.153, ...
Connecting to www.pgbouncer.org (www.pgbouncer.org)|185.199.110.153|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 694845 (679K) [application/gzip]
Saving to: ‘pgbouncer-1.23.0.tar.gz’

pgbouncer-1.23.0.tar.gz     100%[=========================================>] 678.56K  9.63KB/s    in 6m 1s

2024-08-04 23:18:18 (1.88 KB/s) - ‘pgbouncer-1.23.0.tar.gz’ saved [694845/694845]

[postgres@Node1 ~]$

2.4、解压安装包

代码如下：

[postgres@Node1 ~]$
[postgres@Node1 ~]$ tar xzvf pgbouncer-1.23.0.tar.gz
pgbouncer-1.23.0/
pgbouncer-1.23.0/requirements.txt
pgbouncer-1.23.0/uthash/
pgbouncer-1.23.0/uthash/LICENSE
pgbouncer-1.23.0/uthash/src/
pgbouncer-1.23.0/uthash/src/uthash.h
pgbouncer-1.23.0/doc/
pgbouncer-1.23.0/doc/pgbouncer.5
pgbouncer-1.23.0/doc/config.md
pgbouncer-1.23.0/doc/frag-usage-man.md
pgbouncer-1.23.0/doc/usage.md
pgbouncer-1.23.0/doc/Makefile
pgbouncer-1.23.0/doc/frag-config-man.md
pgbouncer-1.23.0/doc/pgbouncer.1
pgbouncer-1.23.0/doc/filter.py
pgbouncer-1.23.0/include/
..............................................
pgbouncer-1.23.0/test/test_cancel.py
pgbouncer-1.23.0/test/asynctest.c
pgbouncer-1.23.0/test/test_copy.py
pgbouncer-1.23.0/test/conntest.sh
pgbouncer-1.23.0/test/Makefile
pgbouncer-1.23.0/test/test.ini
pgbouncer-1.23.0/test/test_no_database.py
pgbouncer-1.23.0/test/stress.py
pgbouncer-1.23.0/test/utils.py
pgbouncer-1.23.0/test/hba_test.rules
pgbouncer-1.23.0/test/test_misc.py
pgbouncer-1.23.0/test/test_ssl.py
pgbouncer-1.23.0/test/hba_test.eval
[postgres@Node1 ~]$
[postgres@Node1 ~]$ cd pgbouncer-1.23.0/
[postgres@Node1 pgbouncer-1.23.0]$
[postgres@Node1 pgbouncer-1.23.0]$ mkdir -p /postgres/pgbouncer
[postgres@Node1 pgbouncer-1.23.0]$ ./configure --prefix=/postgres/pgbouncer/

2.5、切换目录进行编译

代码如下：

[postgres@Node1 pgbouncer-1.23.0]$ ./configure --prefix=/postgres/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 the compiler supports GNU C... yes
checking whether gcc accepts -g... yes
checking for gcc option to enable C11 features... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for grep that handles long lines and -e... /usr/bin/grep
checking for a race-free mkdir -p... /usr/bin/mkdir -p
checking how to run the C preprocessor... gcc -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... /usr/bin/grep -E
checking for gawk... gawk
checking for a sed that does not truncate output... /usr/bin/sed
checking for strip... strip
checking for ranlib... ranlib
checking for ar... ar
checking for pkg-config... /usr/bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking for pandoc... no
checking for python3... python3
checking for strip... (cached) strip
checking for stdio.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for strings.h... yes
checking for sys/stat.h... yes
checking for sys/types.h... yes
checking for unistd.h... yes
checking for inttypes.h... (cached) yes
checking for stdbool.h... yes
checking for unistd.h... (cached) yes
checking for sys/time.h... yes
checking for sys/socket.h... yes
checking for poll.h... yes
checking for sys/un.h... yes
checking for arpa/inet.h... yes
checking for netinet/in.h... yes
checking for netinet/tcp.h... yes
checking for sys/param.h... yes
checking for sys/uio.h... yes
checking for pwd.h... yes
checking for grp.h... yes
checking for sys/wait.h... yes
checking for sys/mman.h... yes
checking for syslog.h... yes
checking for netdb.h... yes
checking for dlfcn.h... yes
checking for err.h... yes
checking for pthread.h... yes
checking for endian.h... yes
checking for sys/endian.h... no
checking for byteswap.h... yes
checking for malloc.h... yes
checking for regex.h... yes
checking for getopt.h... yes
checking for fnmatch.h... yes
checking for langinfo.h... yes
checking for xlocale.h... no
checking for linux/random.h... yes
checking for ucred.h... no
checking for sys/ucred.h... no
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 gcc options needed to detect all undeclared functions... none needed
checking whether strerror_r is declared... 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... yes
checking for memset_s... no
checking for reallocarray... yes
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... yes
checking for getrandom... yes
checking for library containing WSAGetLastError... no
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 libcares >= 1.9.0... 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/pki/tls/certs/ca-bundle.crt
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
config.status: lib/usual/config.h is unchanged

Results:
  adns    = evdns2
  pam     = no
  systemd = no
  tls     = yes

[postgres@Node1 pgbouncer-1.23.0]$

2.6、编译安装

代码如下：

[postgres@Node1 pgbouncer-1.23.0]$
[postgres@Node1 pgbouncer-1.23.0]$ make
     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/messages.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/prepare.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/bool.c
     CC       src/common/pgstrcasecmp.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/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
[postgres@Node1 pgbouncer-1.23.0]$

2.7、编译安装

代码如下：

[postgres@Node1 pgbouncer-1.23.0]$
[postgres@Node1 pgbouncer-1.23.0]$ make install
     INSTALL  pgbouncer /postgres/pgbouncer/bin
     INSTALL  README.md /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  NEWS.md /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  etc/pgbouncer-minimal.ini /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  etc/pgbouncer.ini /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  etc/pgbouncer.service /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  etc/pgbouncer.socket /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  etc/userlist.txt /postgres/pgbouncer/share/doc/pgbouncer
     INSTALL  doc/pgbouncer.1 /postgres/pgbouncer/share/man/man1
     INSTALL  doc/pgbouncer.5 /postgres/pgbouncer/share/man/man5
[postgres@Node1 pgbouncer-1.23.0]$

2.8、修改配置文件

说明：需修改databases、pgbouncer的信息。

代码如下：

vi /postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini


[databases]
postgres = host=192.168.1.101 port=5432 user=postgres password=SCRAM-SHA-256$4096:b/47oK1NNkwjc9xMkIA8kg==$y3MS/Y/dMPYhp+FBc35ukK8kC1TEpZLDniVXC+G6/m0=:KIpuME/LaBCLprStLqApe3OIDNsYuod8xQ+cTaF7A2g= client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

[pgbouncer]

logfile = /postgres/pgbouncer/log/pgbouncer.log
pidfile = /postgres/pgbouncer/pgbouncer.pid

listen_addr = 192.168.1.101
listen_port = 6432

auth_type = scram-sha-256
auth_file = /postgres/pgbouncer/share/doc/pgbouncer/userlist.txt

admin_users = postgres, sy, someadmin, otheradmin

stats_users = stats, postgres, sy

pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100

default_pool_size = 20

##### 增加内容，防止外部链接出错
ignore_startup_parameters = extra_float_digits

2.9、修改配置文件

说明：从PostgreSQL导出用户信息。

代码如下：

copy (select usename, passwd from pg_shadow order by 1) to '/postgres/pgbouncer/share/doc/pgbouncer/userlist1';






/install/pgbouncer-1.23.1/etc/mkauth.py /postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini 
"host=192.168.1.101 port=5432 user=postgres password=123"

2.10、拷贝用户信息到userlist1文件

说明：查询用户表信息，将用户信息拷贝到userlist1’文件。

代码如下：

postgres=#
postgres=# copy (select usename, passwd from pg_shadow order by 1) to '/postgres/pgbouncer/share/doc/pgbouncer/userlist1';
COPY 4
postgres=#
postgres=#

2.11、查看userlist1文件内容

说明：查看userlist1文件内容，文件中包含用户名、密码。

代码如下：

[postgres@Node1 ~]$ cat /postgres/pgbouncer/share/doc/pgbouncer/userlist1
pgautofailover_monitor  SCRAM-SHA-256$4096:ek9wjE/qeBjqWwqRNs9iwQ==$N+nE+G9q+0P60ZeH+Z+I+EDcue0xloZSJUbYit1/f+k=:Rpf81TQ4AfQLUjohlstk7LnFzUwSY+7Dx1/o6nWJbVU=
pgautofailover_replicator       \N
postgres        SCRAM-SHA-256$4096:b/47oK1NNkwjc9xMkIA8kg==$y3MS/Y/dMPYhp+FBc35ukK8kC1TEpZLDniVXC+G6/m0=:KIpuME/LaBCLprStLqApe3OIDNsYuod8xQ+cTaF7A2g=
sy      SCRAM-SHA-256$4096:ls4YwV5qsReheBSdmScDMw==$WimBYKkW24xDnYMEutjGwuvKB98EATwiN/T0ia5lYyQ=:BwfYe/oADlAmagHNwYZNOSeaXfi6PFjxVLtGH3WJ3n4=
[postgres@Node1 ~]$

2.12、修改userlist.txt文件

说明：将导出的userlist1文件的用户内容添加到userlist.txt。
userlist.txt内容格式： “用户” “密码” 。

代码如下：

[postgres@Node1 ~]$
[postgres@Node1 ~]$ cd /postgres/pgbouncer/share/doc/pgbouncer/
[postgres@Node1 pgbouncer]$ vi userlist.txt

修改内容：

"postgres"        "SCRAM-SHA-256$4096:b/47oK1NNkwjc9xMkIA8kg==$y3MS/Y/dMPYhp+FBc35ukK8kC1TEpZLDniVXC+G6/m0=:KIpuME/LaBCLprStLqApe3OIDNsYuod8xQ+cTaF7A2g="
"sy"      "SCRAM-SHA-256$4096:ls4YwV5qsReheBSdmScDMw==$WimBYKkW24xDnYMEutjGwuvKB98EATwiN/T0ia5lYyQ=:BwfYe/oADlAmagHNwYZNOSeaXfi6PFjxVLtGH3WJ3n4="

2.13、启动pgbouncer

说明：启动pgbouncer时，需指定pgbouncer.ini。

代码如下：

[postgres@Node1 ~]$ /postgres/pgbouncer/bin/pgbouncer -d -v /postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
2024-08-04 23:48:11.321 CST [1895] DEBUG parse_ini_file: [databases]
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'postgres' = 'host=192.168.1.101 port=5432 user=postgres password=SCRAM-SHA-256$4096:b/47oK1NNkwjc9xMkIA8kg==$y3MS/Y/dMPYhp+FBc35ukK8kC1TEpZLDniVXC+G6/m0=:KIpuME/LaBCLprStLqApe3OIDNsYuod8xQ+cTaF7A2g= client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1''
2024-08-04 23:48:11.322 CST [1895] DEBUG pktbuf_dynamic(128): 0x91a120
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'postgres' = 'host=192.168.1.101 port=5432 user=postgres password=SCRAM-SHA-256$4096:b/47oK1NNkwjc9xMkIA8kg==$y3MS/Y/dMPYhp+FBc35ukK8kC1TEpZLDniVXC+G6/m0=:KIpuME/LaBCLprStLqApe3OIDNsYuod8xQ+cTaF7A2g= client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: [users]
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: [pgbouncer]
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'logfile' = '/postgres/pgbouncer/log/pgbouncer.log'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'logfile' = '/postgres/pgbouncer/log/pgbouncer.log' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'pidfile' = '/postgres/pgbouncer/pgbouncer.pid'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'pidfile' = '/postgres/pgbouncer/pgbouncer.pid' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'listen_addr' = '192.168.1.101'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'listen_addr' = '192.168.1.101' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'listen_port' = '6432'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'listen_port' = '6432' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'unix_socket_dir' = '/tmp'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'unix_socket_dir' = '/tmp' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'auth_type' = 'scram-sha-256'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'auth_type' = 'scram-sha-256' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'auth_file' = '/postgres/pgbouncer/share/doc/pgbouncer/userlist.txt'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'auth_file' = '/postgres/pgbouncer/share/doc/pgbouncer/userlist.txt' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'admin_users' = 'postgres, sy, someadmin, otheradmin'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'admin_users' = 'postgres, sy, someadmin, otheradmin' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'stats_users' = 'stats, postgres, sy'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'stats_users' = 'stats, postgres, sy' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'pool_mode' = 'session'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'pool_mode' = 'session' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'server_reset_query' = 'DISCARD ALL'
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'server_reset_query' = 'DISCARD ALL' ok:1
2024-08-04 23:48:11.322 CST [1895] DEBUG parse_ini_file: 'max_client_conn' = '100'
2024-08-04 23:48:11.323 CST [1895] DEBUG parse_ini_file: 'max_client_conn' = '100' ok:1
2024-08-04 23:48:11.323 CST [1895] DEBUG parse_ini_file: 'default_pool_size' = '20'
2024-08-04 23:48:11.323 CST [1895] DEBUG parse_ini_file: 'default_pool_size' = '20' ok:1
2024-08-04 23:48:11.323 CST [1895] DEBUG parse_ini_file: 'ignore_startup_parameters' = 'extra_float_digits'
2024-08-04 23:48:11.323 CST [1895] DEBUG parse_ini_file: 'ignore_startup_parameters' = 'extra_float_digits' ok:1
2024-08-04 23:48:11.323 CST [1895] DEBUG loading auth_file: "/postgres/pgbouncer/share/doc/pgbouncer/userlist.txt"
2024-08-04 23:48:11.325 CST [1895] DEBUG pktbuf_dynamic(128): 0x977d40
2024-08-04 23:48:11.325 CST [1895] DEBUG make_room(0x977d40, 9): realloc newlen=256
2024-08-04 23:48:11.325 CST [1895] DEBUG pktbuf_dynamic(128): 0x977f20

三、Pgbouncer使用

3.1、登录psql

说明：登录语法：psql -p 端口号 pgbouncer -U 用户名 -h ip地址

代码如下：

[postgres@Node1 ~]$
[postgres@Node1 ~]$ psql -p 6432 pgbouncer -U sy -h192.168.1.101
Password for user sy:
psql (16.2, server 1.23.1/bouncer)
WARNING: psql major version 16, server major version 1.23.
         Some psql features might not work.
Type "help" for help.

pgbouncer=#

3.2、登录DBeaver

说明：
1、输入服务器ip地址、用户名、密码，端口即可远程。
2、端口是pgbouncer的端口

四、问题汇总

4.1、使用DBeaver连接数据库

> 报错提示：unsupported startup parameter: extra_float_digits

如下图：

解决方案：

修改配置文件pgbouncer.ini，在[pgbouncer]下面新增一行

ignore_startup_parameters = extra_float_digits
重启pgbouncer，再次查看日志，该参数已被忽略，测试数据正常。

五、总结

PGBouncer是一个强大的工具，能够显著提升大型和高并发的PostgreSQL数据库环境中的性能和资源利用率。正确配置和管理PGBouncer，可以使它成为数据库架构中不可或缺的一部分。

小白学信创

关注

22
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL轻量级数据库连接池工具PGBouncer

PGBouncer是一个强大的工具，能够显著提升大型和高并发的PostgreSQL数据库环境中的性能和资源利用率。正确配置和管理PGBouncer，可以使它成为数据库架构中不可或缺的一部分。
复制链接

扫一扫

专栏目录