postgres高可用学习篇二：通过pgbouncer连接池工具来管理postgres连接

最新推荐文章于 2024-08-20 09:31:56 发布

andou2693

最新推荐文章于 2024-08-20 09:31:56 发布

阅读量498

点赞数

文章标签：数据库运维

原文链接：http://www.cnblogs.com/caidingyu/p/11567033.html

版权

本文详细介绍如何在三台节点上安装和配置pgbouncer连接池，包括通过yum安装pgbouncer，编辑pgbouncer.ini配置文件，设置监听地址、端口、认证方式等参数，以及如何创建systemd服务文件以便于服务管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

安装pgbouncer

 yum -y install pgbouncer

node1：编辑pgbouncer.ini配置文件

cat /etc/pgbouncer/pgbouncer.ini 
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres 

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 192.168.216.130
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo
 
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 10
reserve_pool_size = 1
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192


# Documentation https://pgbouncer.github.io/config.html

配置postgres管理账户和密码

cat /etc/pgbouncer/userlist.txt
"postgres" "postgres-pass"

node2：编辑pgbouncer.ini配置文件

cat /etc/pgbouncer/pgbouncer.ini 
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres 

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 192.168.216.132
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo
 
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 10
reserve_pool_size = 1
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192


# Documentation https://pgbouncer.github.io/config.html

配置postgres管理账户和密码

cat /etc/pgbouncer/userlist.txt
"postgres" "postgres-pass"

node3：编辑pgbouncer.ini配置文件

cat /etc/pgbouncer/pgbouncer.ini 
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres 

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 192.168.216.134
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo
 
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 10
reserve_pool_size = 1
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192


# Documentation https://pgbouncer.github.io/config.html

配置postgres管理账户和密码

cat /etc/pgbouncer/userlist.txt
"postgres" "postgres-pass"

创建/etc/systemd/system/pgbouncer.service文件,通过systemctl管理pgbouncer服务

cat /etc/systemd/system/pgbouncer.service
[Unit]
Description=pgBouncer connection pooling for PostgreSQL
After=syslog.target network.target

[Service]
Type=forking

User=postgres
Group=postgres

PermissionsStartOnly=true
ExecStartPre=-/bin/mkdir -p /var/run/pgbouncer /var/log/pgbouncer
ExecStartPre=/bin/chown -R postgres:postgres /var/run/pgbouncer /var/log/pgbouncer
ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/var/run/pgbouncer/pgbouncer.pid

LimitNOFILE=100000

[Install]
WantedBy=multi-user.target