印刻数据库

外观

PostgreSQL 连接控制 ​

连接控制是 PostgreSQL 性能优化和安全管理的重要组成部分，通过合理配置连接参数和管理连接资源，可以提高数据库的性能和可靠性。

连接控制概述 ​

什么是连接控制 ​

连接控制是管理数据库连接的一组规则和配置，包括连接数量限制、连接超时、连接资源限制等，用于优化数据库性能和安全性。

连接控制的重要性 ​

  • 防止连接耗尽导致服务不可用
  • 优化资源使用，提高数据库性能
  • 防止连接泄漏和资源浪费
  • 提高系统的可靠性和稳定性
  • 满足合规和安全要求

PostgreSQL 连接控制的组成部分 ​

  • 连接数量限制：控制并发连接的最大数量
  • 连接超时配置：控制连接的超时时间
  • 连接资源限制：限制单个连接使用的资源
  • 连接池管理：优化连接的创建和复用
  • 连接监控：监控连接状态和资源使用
  • 连接安全：限制连接来源和认证方式

连接限制配置 ​

max_connections 参数 ​

功能说明 ​

max_connections 参数控制 PostgreSQL 实例可以接受的最大并发连接数，默认值为 100。

配置示例 ​

sql
-- 查看当前配置
SHOW max_connections;

-- 修改配置（需要重启 PostgreSQL）
ALTER SYSTEM SET max_connections = 200;

配置建议 ​

  • 根据系统资源（内存、CPU）调整
  • 考虑连接池的使用
  • 预留足够的连接给超级用户
  • 监控连接使用情况，避免设置过大导致资源耗尽

superuser_reserved_connections 参数 ​

功能说明 ​

superuser_reserved_connections 参数为超级用户预留的连接数，默认值为 3。

配置示例 ​

sql
-- 查看当前配置
SHOW superuser_reserved_connections;

-- 修改配置（需要重启 PostgreSQL）
ALTER SYSTEM SET superuser_reserved_connections = 5;

连接限制的影响 ​

  • 内存使用：每个连接会占用一定的内存，连接数过多会导致内存不足
  • CPU 使用：连接数过多会增加上下文切换开销
  • 磁盘 I/O：连接数过多会增加磁盘 I/O 压力
  • 锁竞争：连接数过多会增加锁竞争

连接超时配置 ​

connect_timeout 参数 ​

功能说明 ​

connect_timeout 参数控制客户端连接超时时间，默认值为 10 秒。

配置示例 ​

sql
-- 查看当前配置
SHOW connect_timeout;

-- 修改配置
ALTER SYSTEM SET connect_timeout = 30;
SELECT pg_reload_conf();

idle_in_transaction_session_timeout 参数 ​

功能说明 ​

idle_in_transaction_session_timeout 参数控制空闲事务会话的超时时间，默认值为 0（不超时）。

配置示例 ​

sql
-- 查看当前配置
SHOW idle_in_transaction_session_timeout;

-- 修改配置，设置为 10 分钟
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

idle_session_timeout 参数 ​

功能说明 ​

idle_session_timeout 参数控制空闲会话的超时时间，默认值为 0（不超时）。

配置示例 ​

sql
-- 查看当前配置
SHOW idle_session_timeout;

-- 修改配置，设置为 1 小时
ALTER SYSTEM SET idle_session_timeout = '1h';
SELECT pg_reload_conf();

statement_timeout 参数 ​

功能说明 ​

statement_timeout 参数控制单个 SQL 语句的执行超时时间，默认值为 0（不超时）。

配置示例 ​

sql
-- 查看当前配置
SHOW statement_timeout;

-- 修改配置，设置为 5 分钟
ALTER SYSTEM SET statement_timeout = '5min';
SELECT pg_reload_conf();

-- 为特定用户设置
ALTER USER db_user SET statement_timeout = '10min';

lock_timeout 参数 ​

功能说明 ​

lock_timeout 参数控制获取锁的超时时间，默认值为 0（不超时）。

配置示例 ​

sql
-- 查看当前配置
SHOW lock_timeout;

-- 修改配置，设置为 30 秒
ALTER SYSTEM SET lock_timeout = '30s';
SELECT pg_reload_conf();

连接资源限制 ​

work_mem 参数 ​

功能说明 ​

work_mem 参数控制单个查询操作使用的内存量，默认值为 4MB。

配置示例 ​

sql
-- 查看当前配置
SHOW work_mem;

-- 修改配置
ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

maintenance_work_mem 参数 ​

功能说明 ​

maintenance_work_mem 参数控制维护操作（如 VACUUM、CREATE INDEX）使用的内存量，默认值为 64MB。

配置示例 ​

sql
-- 查看当前配置
SHOW maintenance_work_mem;

-- 修改配置
ALTER SYSTEM SET maintenance_work_mem = '128MB';
SELECT pg_reload_conf();

temp_file_limit 参数 ​

功能说明 ​

temp_file_limit 参数控制单个会话可以使用的临时文件大小，默认值为 -1（无限制）。

配置示例 ​

sql
-- 查看当前配置
SHOW temp_file_limit;

-- 修改配置，设置为 1GB
ALTER SYSTEM SET temp_file_limit = '1GB';
SELECT pg_reload_conf();

连接池管理 ​

连接池概述 ​

什么是连接池 ​

连接池是一种管理数据库连接的技术，通过复用已建立的连接，减少连接建立和关闭的开销，提高数据库性能。

连接池的优势 ​

  • 减少连接建立和关闭的开销
  • 控制并发连接数
  • 优化资源使用
  • 提高应用程序的响应速度
  • 支持连接复用

常用连接池工具 ​

PgBouncer ​

特点 ​
  • 轻量级，资源占用少
  • 支持三种连接池模式：会话级、事务级、语句级
  • 支持多种认证方式
  • 配置简单，易于使用
配置示例 ​
ini
# pgbouncer.ini 示例配置
[databases]
* = host=localhost port=5432

[pgbouncer]
listen_addr = 0.0.0.0
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 = 5
reserve_pool_timeout = 5
启动和管理 ​
bash
# 启动 PgBouncer
systemctl start pgbouncer

# 连接到 PgBouncer 管理界面
psql -h localhost -p 6432 -U postgres pgbouncer

# 查看连接池状态
SHOW pools;

# 查看客户端连接
SHOW clients;

# 查看服务器连接
SHOW servers;

pgpool-II ​

特点 ​
  • 支持连接池、负载均衡、自动故障切换
  • 支持读写分离
  • 支持并行查询
  • 功能丰富，配置复杂
配置示例 ​
ini
# pgpool.conf 示例配置
listen_addresses = '*'
port = 9999
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/15/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

# 连接池配置
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
启动和管理 ​
bash
# 启动 pgpool-II
systemctl start pgpool2

# 连接到 pgpool-II
psql -h localhost -p 9999 -U postgres

# 查看 pgpool-II 状态
pgpool -m status

连接池最佳实践 ​

  • 选择合适的连接池模式（事务级或语句级，根据应用需求）
  • 合理设置连接池大小，避免过大导致资源耗尽
  • 监控连接池状态，及时调整配置
  • 使用连接池的管理界面，查看连接使用情况
  • 考虑连接池的高可用性

连接监控 ​

查看当前连接状态 ​

使用 pg_stat_activity 视图 ​

sql
-- 查看所有连接
SELECT * FROM pg_stat_activity;

-- 查看活跃连接
SELECT * FROM pg_stat_activity WHERE state <> 'idle';

-- 查看空闲连接
SELECT * FROM pg_stat_activity WHERE state = 'idle';

-- 查看空闲事务连接
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';

-- 查看长时间运行的查询
SELECT pid, datname, usename, application_name, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

使用 ps 命令 ​

bash
# 查看 PostgreSQL 进程数
ps aux | grep postgres | wc -l

# 查看 PostgreSQL 连接数
ss -tuln | grep 5432

识别和处理异常连接 ​

终止空闲事务连接 ​

sql
-- 终止单个连接
SELECT pg_terminate_backend(12345);

-- 终止所有空闲事务连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - xact_start > INTERVAL '10 minutes';

终止长时间运行的查询 ​

sql
-- 终止长时间运行的查询
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > INTERVAL '30 minutes';

连接安全 ​

使用 SSL 连接 ​

配置 SSL ​

sql
-- 查看 SSL 配置
SHOW ssl;

-- 启用 SSL（需要重启 PostgreSQL）
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = '/var/lib/postgresql/15/main/ssl/server.crt';
ALTER SYSTEM SET ssl_key_file = '/var/lib/postgresql/15/main/ssl/server.key';
ALTER SYSTEM SET ssl_ca_file = '/var/lib/postgresql/15/main/ssl/ca.crt';

强制使用 SSL 连接 ​

# 在 pg_hba.conf 中配置
hostssl all all 0.0.0.0/0 scram-sha-256

限制连接来源 ​

使用 pg_hba.conf 配置 ​

# 只允许特定 IP 连接
host all all 192.168.1.0/24 scram-sha-256

# 拒绝特定 IP 连接
host all all 10.0.0.0/8 reject

使用防火墙限制连接 ​

配置 iptables ​

bash
# 允许特定 IP 访问 5432 端口
iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 5432 -j ACCEPT

# 拒绝其他 IP 访问 5432 端口
iptables -A INPUT -p tcp --dport 5432 -j DROP

配置 firewalld ​

bash
# 允许特定 IP 访问 5432 端口
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'

# 重新加载配置
firewall-cmd --reload

最佳实践 ​

1. 根据系统资源调整连接数 ​

  • 考虑内存使用：每个连接大约占用 10-15MB 内存
  • 考虑 CPU 核心数：并发连接数不应远大于 CPU 核心数
  • 监控系统资源使用，及时调整 max_connections

2. 使用连接池 ​

  • 生产环境建议使用连接池
  • 选择合适的连接池工具（PgBouncer 或 pgpool-II）
  • 合理配置连接池大小和模式

3. 配置合理的超时参数 ​

  • 配置 idle_in_transaction_session_timeout 防止长事务
  • 配置 idle_session_timeout 防止空闲连接占用资源
  • 配置 statement_timeout 防止查询执行过长

4. 监控连接状态 ​

  • 定期查看 pg_stat_activity 视图
  • 监控连接数、空闲连接数、长事务等指标
  • 配置告警，及时发现异常连接

5. 限制连接来源 ​

  • 使用 pg_hba.conf 限制连接来源
  • 使用 SSL 加密连接
  • 使用防火墙限制访问端口

6. 优化应用连接管理 ​

  • 避免连接泄漏，确保应用正确关闭连接
  • 使用连接池管理连接
  • 合理设置应用的连接超时参数

7. 定期维护 ​

  • 定期清理异常连接
  • 定期检查连接池状态
  • 定期调整连接参数

常见问题与解决方案 ​

1. 连接数耗尽 ​

问题症状 ​

  • 应用无法连接数据库，报错 "FATAL: sorry, too many clients already"
  • 数据库响应缓慢

解决方案 ​

  • 增加 max_connections 参数
  • 使用连接池
  • 关闭空闲连接
  • 优化应用连接管理，避免连接泄漏

临时解决方法 ​

sql
-- 终止空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > INTERVAL '1 hour';

2. 空闲连接过多 ​

问题症状 ​

  • 大量连接处于 idle 状态
  • 内存使用过高

解决方案 ​

  • 配置 idle_session_timeout
  • 配置连接池的客户端空闲超时
  • 优化应用连接管理

3. 连接泄漏 ​

问题症状 ​

  • 连接数持续增加
  • 应用关闭后连接仍存在

解决方案 ​

  • 检查应用代码，确保正确关闭连接
  • 使用连接池管理连接
  • 配置合理的超时参数

4. 长事务占用连接 ​

问题症状 ​

  • 大量连接处于 idle in transaction 状态
  • 锁竞争加剧
  • 数据库性能下降

解决方案 ​

  • 配置 idle_in_transaction_session_timeout
  • 监控长事务，及时终止
  • 优化应用代码，减少长事务

5. 连接池连接数不足 ​

问题症状 ​

  • 应用等待连接可用
  • 连接池队列长度增加

解决方案 ​

  • 增加连接池大小
  • 优化应用查询，减少连接占用时间
  • 考虑使用读写分离，分散连接压力

版本差异 ​

PostgreSQL 9.0+ 版本 ​

  • 支持基本的连接控制参数
  • 支持 pg_stat_activity 视图

PostgreSQL 9.6+ 版本 ​

  • 引入 idle_in_transaction_session_timeout 参数
  • 增强了 pg_stat_activity 视图

PostgreSQL 10+ 版本 ​

  • 引入 idle_session_timeout 参数
  • 支持 parallel_worker_count 参数，影响连接数

PostgreSQL 13+ 版本 ​

  • 增强了连接池支持
  • 优化了连接管理的性能

PostgreSQL 14+ 版本 ​

  • 引入 connection_limits 配置，简化连接限制管理
  • 增强了连接监控功能

PostgreSQL 15+ 版本 ​

  • 默认启用 ssl_min_protocol_version = 'TLSv1.2'
  • 增强了连接安全功能

总结 ​

连接控制是 PostgreSQL 性能优化和安全管理的重要组成部分，通过合理配置连接参数、使用连接池、监控连接状态，可以提高数据库的性能和可靠性。

在实施连接控制时，DBA 需要根据系统资源和应用需求，选择合适的配置参数和工具，定期监控和调整连接配置，确保数据库的正常运行。

通过有效的连接控制，可以防止连接耗尽、资源浪费和性能下降，提高数据库的整体可用性和安全性，为业务提供可靠的数据库服务。