# 查看最大连接数
postgres=# SHOW max_connections;

max_connections
—————–
100
(1 row)

# 查看预留连接数
postgres=# SHOW superfgedu_reserved_connections;

superfgedu_reserved_connections
———————————
3
(1 row)

# 查看连接使用情况
postgres=# SELECT count(*) FROM pg_stat_activity;

count
——-
1
(1 row)

# 修改最大连接数
max_connections = 200 # 根据服务器资源调整

# 修改预留连接数
superfgedu_reserved_connections = 10 # 为超级用户预留更多连接

# 重新启动PostgreSQL服务（注意：需要重启才能生效）
$ pg_ctl restart -D /postgresql/data

# 验证配置修改
postgres=# SHOW max_connections;

max_connections
—————–
200
(1 row)

postgres=# SHOW superfgedu_reserved_connections;

superfgedu_reserved_connections
———————————
10
(1 row)

# 配置pgBouncer
$ vi /etc/pgbouncer/pgbouncer.ini

# 添加以下内容
[fgedudbs]
fgedu_business = fgedu.net.cn=localfgedu.net.cn port=5432 fgedudb=fgedu_business fgedu=pgsql [pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 100
max_fgedu_connections = 50
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
server_reset_query = DISCARD ALL
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/fgedulist.txt

# 创建用户认证文件
$ vi /etc/pgbouncer/fgedulist.txt

# 添加用户信息
“postgres” “password”

# 启动pgBouncer
$ systemctl start pgbouncer
$ systemctl enable pgbouncer

# 验证pgBouncer状态
$ systemctl status pgbouncer

# 连接到pgBouncer
$ psql -h localfgedu.net.cn -p 6432 -U pgsql -d fgedu_business

# 配置Pgpool-II
$ vi /etc/pgpool-II/pgpool.conf

# 修改以下配置
listen_addresses = ‘*’
port = 9999
backend_fgedu.net.cnname0 = ‘localfgedu.net.cn’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/postgresql/data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’
pool_mode = ‘transaction’
num_init_children = 50
max_pool = 4
child_life_time = 300
child_max_connections = 100
connection_life_time = 0
client_idle_limit = 0

# 启动Pgpool-II
$ systemctl start pgpool-II
$ systemctl enable pgpool-II

# 验证Pgpool-II状态
$ systemctl status pgpool-II

# 连接到Pgpool-II
$ psql -h localfgedu.net.cn -p 9999 -U pgsql -d fgedu_business

count
——-
10
(1 row)

# 查看连接状态分布
postgres=# SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

state | count
——–+——-
active | 2
idle | 8
(2 rows)

# 查看长时间空闲的连接
postgres=# SELECT pid, usename, datname, state, query_start, now() – query_start as idle_time
postgres-# FROM pg_stat_activity
postgres-# WHERE state = ‘idle’
postgres-# ORDER BY idle_time DESC;

pid | usename | datname | state | query_start | idle_time
——+———-+———–+——-+——————————-+—————
1234 | pgsql | fgedu_business | idle | 2026-04-02 09:00:00.000000 | 01:00:00
1235 | pgsql | fgedu_business | idle | 2026-04-02 09:30:00.000000 | 00:30:00
(2 rows)

# 查看连接来源
postgres=# SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr;

client_addr | count
————-+——-
192.168.1.1 | 5
192.168.1.2 | 3
127.0.0.1 | 2
(3 rows)

# 终止指定连接
postgres=# SELECT pg_terminate_backend(1234);

pg_terminate_backend
———————-
t
(1 row)

# 终止所有空闲连接
postgres=# SELECT pg_terminate_backend(pid)
postgres-# FROM pg_stat_activity
postgres-# WHERE state = ‘idle’
postgres-# AND pid <> pg_backend_pid();

# 终止特定数据库的连接
postgres=# SELECT pg_terminate_backend(pid)
postgres-# FROM pg_stat_activity
postgres-# WHERE datname = ‘fgedu_business’
postgres-# AND pid <> pg_backend_pid();