# 1. 安装PgBouncer
$ sudo yum install pgbouncer

# 2. 配置PgBouncer
$ sudo vi /etc/pgbouncer/pgbouncer.ini

[databases]
* = host=192.168.1.100 port=5432

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 5.0

# 3. 配置用户列表
$ sudo vi /etc/pgbouncer/userlist.txt
“fgedu” “password”
“postgres” “password”

# 4. 启动PgBouncer
$ sudo systemctl start pgbouncer
$ sudo systemctl enable pgbouncer

# 5. 验证PgBouncer状态
$ psql -U fgedu -h fgedu.localhost -p 6432 -d fgedudb -c “SELECT 1;”

# 1. 安装PgPool-II
$ sudo yum install pgpool-II-14

# 2. 配置PgPool-II
$ sudo vi /etc/pgpool-II/pgpool.conf

listen_addresses = ‘*’
port = 9999

backend_hostname0 = ‘192.168.1.100’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/postgresql/fgdata’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘192.168.1.101’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/postgresql/fgdata’
backend_flag1 = ‘ALLOW_TO_FAILOVER’

load_balance_mode = on
auto_failover = on
failover_command = ‘/etc/pgpool-II/failover.sh’

# 3. 配置pgpool_hba.conf
$ sudo vi /etc/pgpool-II/pgpool_hba.conf
host all all 192.168.1.0/24 md5

# 4. 启动PgPool-II
$ sudo systemctl start pgpool-II
$ sudo systemctl enable pgpool-II

# 5. 验证PgPool-II状态
$ psql -U fgedu -h fgedu.localhost -p 9999 -d fgedudb -c “SELECT 1;”

# 1. PgBouncer参数优化
$ sudo vi /etc/pgbouncer/pgbouncer.ini

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 20
reserve_pool_timeout = 5.0
max_db_connections = 100
max_user_connections = 500

# 2. PgPool-II参数优化
$ sudo vi /etc/pgpool-II/pgpool.conf

# 连接配置
max_connections = 2000
child_max_connections = 200
max_pool = 8
connection_cache = on

# 负载均衡配置
load_balance_mode = on
fgapplication_name = ‘pgpool’

# 健康检查配置
health_check_period = 5
health_check_timeout = 3
health_check_user = ‘postgres’
health_check_password = ‘password’

# 故障转移配置
auto_failover = on
failover_command = ‘/etc/pgpool-II/failover.sh’

# 3. 重启中间件
$ sudo systemctl restart pgbouncer
$ sudo systemctl restart pgpool-II

# 4. 验证配置
$ pgbouncer -C show config
$ pgpool -C show pool_status

# 1. PgBouncer监控
$ psql -U fgedu -h fgedu.localhost -p 6432 -d pgbouncer -c “SHOW POOLS;”

# 2. PgPool-II监控
$ pgpool -C status
$ pgpool -C pool_status

# 3. 使用Prometheus监控中间件
$ sudo vi /etc/prometheus/prometheus.yml
– job_name: ‘pgbouncer’
static_configs:
– targets: [‘fgedu.localhost:9127’]

– job_name: ‘pgpool’
static_configs:
– targets: [‘fgedu.localhost:9699’]

# 4. 配置Grafana面板
# 导入PgBouncer和PgPool-II监控面板

# 5. 设置告警规则
$ sudo vi /etc/prometheus/rules/middleware_alerts.yml
groups:
– name: middleware_alerts
rules:
– alert: PgBouncerDown
expr: pgbouncer_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “PgBouncer down on {{ $labels.instance }}”
description: “PgBouncer instance {{ $labels.instance }} has been down for 5 minutes”

– alert: PgPoolDown
expr: pgpool_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “PgPool down on {{ $labels.instance }}”
description: “PgPool instance {{ $labels.instance }} has been down for 5 minutes”

# 6. 验证监控
$ curl http://fgedu.localhost:9090/targets