外观
PostgreSQL 连接相关参数
PostgreSQL 采用基于进程的架构，每个客户端连接对应一个后端进程。连接管理是 PostgreSQL 性能优化的重要组成部分，合理配置连接相关参数可以提高系统的并发处理能力，减少资源消耗，提高系统稳定性。
核心连接参数
1. listen_addresses
listen_addresses 控制 PostgreSQL 服务器监听的 IP 地址。默认值为 'localhost'，只允许本地连接。如果需要允许远程连接，需要设置为 '*' 或特定的 IP 地址。
配置原则：
- 生产环境建议只监听特定的 IP 地址，不建议使用 '*'
- 对于需要远程连接的系统，可以监听内网 IP 地址
- 对于容器化部署，可以监听 '0.0.0.0' 允许所有连接
配置示例：
ini
# 只允许本地连接
listen_addresses = 'localhost'
# 允许所有连接
listen_addresses = '*'
# 只允许特定 IP 连接
listen_addresses = '127.0.0.1,192.168.1.100'
# 监听 IPv4 和 IPv6 地址
listen_addresses = '0.0.0.0,::'2. port
port 参数指定 PostgreSQL 服务器监听的端口，默认值为 5432。
配置原则：
- 建议使用默认端口 5432，便于管理
- 对于多实例部署，需要使用不同的端口
- 考虑使用非标准端口提高安全性（可选）
配置示例：
ini
# 默认端口
port = 5432
# 多实例部署，使用不同端口
port = 54333. max_connections
max_connections 是 PostgreSQL 允许的最大并发连接数，默认值为 100。
配置原则：
- 根据系统资源和应用需求调整
- 对于 OLTP 系统，建议设置为 200-500
- 对于 OLAP 系统，建议设置为 50-100
- 考虑使用连接池减少实际连接数
- 注意：每个连接会消耗内存，过多连接会导致内存不足
配置示例：
ini
# 小型系统
max_connections = 100
# 中型系统
max_connections = 200
# 大型系统
max_connections = 500
# 超大型系统
max_connections = 10004. superuser_reserved_connections
superuser_reserved_connections 是为超级用户保留的连接数，默认值为 3。
配置原则：
- 建议设置为 3-5
- 确保在连接数达到上限时，超级用户仍能连接进行管理
配置示例：
ini
superuser_reserved_connections = 35. tcp_keepalives_idle
tcp_keepalives_idle 是 TCP 连接在发送 keepalive 消息前的空闲时间，默认值为 7200 秒（2 小时）。
配置原则：
- 建议设置为 60-300 秒
- 对于不稳定的网络环境，设置较小的值
- 对于稳定的网络环境，设置较大的值
配置示例：
ini
# 不稳定网络环境
tcp_keepalives_idle = 60
# 稳定网络环境
tcp_keepalives_idle = 3006. tcp_keepalives_interval
tcp_keepalives_interval 是 TCP keepalive 消息的发送间隔，默认值为 75 秒。
配置原则：
- 建议设置为 15-60 秒
- 与 tcp_keepalives_idle 配合使用
配置示例：
ini
tcp_keepalives_interval = 307. tcp_keepalives_count
tcp_keepalives_count 是 TCP 发送 keepalive 消息的最大次数，默认值为 9。
配置原则：
- 建议设置为 3-5
- 与 tcp_keepalives_interval 配合使用
配置示例：
ini
tcp_keepalives_count = 38. connection_timeout
connection_timeout 是客户端连接超时时间，默认值为 60 秒。
配置原则：
- 建议设置为 10-30 秒
- 对于不稳定的网络环境，设置较小的值
- 对于复杂的认证过程，设置较大的值
配置示例：
ini
connection_timeout = 159. keepalives_idle
keepalives_idle 是客户端连接的空闲时间，默认值为 0（使用系统默认值）。
配置原则：
- 建议设置为 60-300 秒
- 与 tcp_keepalives_idle 配合使用
配置示例：
ini
keepalives_idle = 12010. keepalives_interval
keepalives_interval 是客户端连接的 keepalive 消息间隔，默认值为 0（使用系统默认值）。
配置原则：
- 建议设置为 15-60 秒
- 与 keepalives_idle 配合使用
配置示例：
ini
keepalives_interval = 30连接池相关参数
1. 使用连接池的必要性
PostgreSQL 每个连接对应一个后端进程，创建和销毁进程的开销较大。使用连接池可以减少进程创建和销毁的开销，提高系统的并发处理能力。
常见的连接池包括：
- PgBouncer
- PgPool-II
- C3P0
- HikariCP（Java 应用）
- DBCP（Java 应用）
2. 连接池配置原则
连接池大小：
- 建议设置为 CPU 核心数的 2-4 倍
- 对于 OLTP 系统，建议设置为 10-50
- 对于 OLAP 系统，建议设置为 5-20
连接池参数：
- 最小空闲连接数：建议设置为最大连接数的 20-30%
- 最大空闲时间：建议设置为 300-900 秒
- 连接超时时间：建议设置为 10-30 秒
- 验证连接查询：建议使用简单的查询，如 'SELECT 1'
3. PgBouncer 配置示例
ini
# pgbouncer.ini
[databases]
app_db = host=localhost port=5432 dbname=app_db
[pgbouncer]
listen_addr = *
listen_port = 6432
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 5
server_reset_query = DISCARD ALL
pool_mode = transaction认证相关参数
1. authentication_timeout
authentication_timeout 是客户端认证超时时间，默认值为 1min。
配置原则：
- 建议设置为 30-60 秒
- 对于复杂的认证过程，设置较大的值
配置示例：
ini
authentication_timeout = 30s2. password_encryption
password_encryption 控制密码的加密方式，默认值为 'md5'。PostgreSQL 13+ 支持 'scram-sha-256'，提供更高的安全性。
配置原则：
- 建议使用 'scram-sha-256' 加密方式
- 对于需要兼容旧版本的系统，可以使用 'md5'
配置示例：
ini
# 使用 MD5 加密
password_encryption = md5
# 使用 SCRAM-SHA-256 加密
password_encryption = scram-sha-2563. ssl
ssl 控制是否启用 SSL/TLS 连接，默认值为 off。
配置原则：
- 生产环境建议启用 SSL/TLS
- 对于内部网络，可以根据安全要求决定是否启用
配置示例：
ini
# 启用 SSL
ssl = on
# SSL 证书配置
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'连接监控与管理
1. 连接状态监控
sql
-- 查看当前连接状态
SELECT
state,
count(*) AS count,
usename,
application_name
FROM pg_stat_activity
GROUP BY state, usename, application_name
ORDER BY count DESC;
-- 查看长时间运行的连接
SELECT
pid,
usename,
application_name,
client_addr,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
AND state != 'idle'
ORDER BY duration DESC;
-- 查看连接来源分布
SELECT
client_addr,
count(*) AS connections,
usename,
application_name
FROM pg_stat_activity
GROUP BY client_addr, usename, application_name
ORDER BY connections DESC
LIMIT 20;2. 连接管理
sql
-- 终止长时间运行的连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE now() - query_start > interval '10 minutes'
AND state != 'idle';
-- 取消长时间运行的查询
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
AND state = 'active';3. 连接池监控
PgBouncer 监控：
bash
# 连接到 PgBouncer 管理数据库
psql -h localhost -p 6432 -U pgbouncer pgbouncer
-- 查看连接池状态
SHOW pools;
-- 查看客户端连接
SHOW clients;
-- 查看服务器连接
SHOW servers;
-- 查看统计信息
SHOW stats;连接优化策略
1. 使用连接池
使用连接池可以减少进程创建和销毁的开销，提高系统的并发处理能力。建议在生产环境中使用连接池，如 PgBouncer 或 PgPool-II。
2. 限制连接数量
合理设置 max_connections 参数，避免过多连接导致系统资源耗尽。建议根据系统资源和应用需求调整连接数量，一般不建议超过 500。
3. 优化 TCP 连接设置
调整 tcp_keepalives_idle、tcp_keepalives_interval 和 tcp_keepalives_count 参数，确保连接能够及时检测到网络故障，避免僵尸连接占用资源。
4. 启用 SSL/TLS
对于需要远程连接的系统，建议启用 SSL/TLS 加密连接，保护数据传输安全。
5. 优化认证过程
使用高效的认证方式，如 SCRAM-SHA-256 或证书认证，避免使用过于复杂的认证过程。
6. 监控连接状态
定期监控连接状态，及时终止长时间运行的连接和查询，避免资源浪费。
常见连接问题排查
1. 无法连接到服务器
症状：客户端连接失败，显示 "could not connect to server: Connection refused"。
解决方案：
- 检查 PostgreSQL 服务是否正在运行
- 检查 listen_addresses 参数是否允许远程连接
- 检查防火墙是否允许 PostgreSQL 端口（默认 5432）
- 检查 pg_hba.conf 文件是否允许客户端连接
2. 连接数达到上限
症状：客户端连接失败，显示 "FATAL: sorry, too many clients already"。
解决方案：
- 增加 max_connections 参数
- 使用连接池减少实际连接数
- 终止长时间运行的连接
- 优化应用代码，减少连接占用时间
3. 连接超时
症状：客户端连接失败，显示 "connection timed out"。
解决方案：
- 检查网络连接是否正常
- 检查 PostgreSQL 服务是否正在运行
- 调整 connection_timeout 参数
- 检查防火墙设置
4. 认证失败
症状：客户端连接失败，显示 "FATAL: password authentication failed for user"。
解决方案：
- 检查用户名和密码是否正确
- 检查 pg_hba.conf 文件的认证规则
- 检查 password_encryption 参数
- 重置用户密码
最佳实践
1. 连接配置
- 只监听必要的 IP 地址，不建议使用 '*'
- 合理设置 max_connections 参数，避免过多连接
- 启用 TCP keepalive 设置，及时检测连接故障
- 生产环境建议启用 SSL/TLS
2. 连接池使用
- 生产环境建议使用连接池
- 连接池大小建议设置为 CPU 核心数的 2-4 倍
- 配置合理的连接池参数，包括最小空闲连接、最大空闲时间等
- 定期监控连接池状态
3. 连接监控
- 定期监控连接状态，及时终止异常连接
- 监控连接来源，发现异常连接及时处理
- 监控连接池状态，确保连接池正常运行
4. 安全配置
- 启用 SSL/TLS 保护数据传输
- 使用强密码加密方式（如 SCRAM-SHA-256）
- 限制连接来源，只允许必要的 IP 连接
- 定期更新证书和密码
常见问题（FAQ）
Q1: 如何确定合适的 max_connections 值？
A1: 合适的 max_connections 值取决于：
- 系统内存大小：每个连接大约需要 10-20MB 内存
- CPU 核心数：连接数过多会导致 CPU 上下文切换开销增大
- 应用类型：OLTP 系统需要更多连接，OLAP 系统需要较少连接
- 是否使用连接池：使用连接池可以减少实际连接数
建议计算公式：
max_connections ≈ (系统内存 * 0.8) / (每个连接内存消耗)例如，对于 16GB 内存系统，每个连接消耗 15MB 内存：
max_connections ≈ (16GB * 0.8) / 15MB ≈ 873Q2: 为什么不建议使用 '*' 作为 listen_addresses？
A2: 使用 '*' 作为 listen_addresses 会让 PostgreSQL 监听所有可用的 IP 地址，增加安全风险。生产环境建议只监听特定的 IP 地址，如 localhost 和内网 IP 地址，减少暴露面，提高系统安全性。
Q3: 如何处理大量空闲连接？
A3: 处理大量空闲连接的方法包括：
- 使用连接池，复用连接
- 调整 idle_in_transaction_session_timeout 参数，终止长时间空闲事务
- 定期终止长时间空闲连接
- 优化应用代码，及时释放连接
Q4: 连接池和 max_connections 的关系是什么？
A4: 连接池的最大连接数应该小于等于 PostgreSQL 的 max_connections。一般建议连接池的最大连接数设置为 max_connections 的 80% 左右，预留一部分连接给超级用户和管理操作。
Q5: 如何监控连接池的性能？
A5: 监控连接池性能的指标包括：
- 连接池使用率：当前使用连接数 / 最大连接数
- 等待队列长度：等待连接的请求数量
- 连接创建和销毁速率：频繁创建和销毁连接说明连接池配置不合理
- 连接空闲时间：空闲时间过长说明连接池过大
- 查询响应时间：连接池性能不佳会导致查询响应时间变长
Q6: SSL/TLS 会影响连接性能吗？
A6: 启用 SSL/TLS 会增加 CPU 开销，因为需要进行加密和解密操作。但在现代硬件上，这种开销通常可以忽略不计。对于需要远程连接的系统，建议启用 SSL/TLS 保护数据传输安全，而不是为了轻微的性能提升而牺牲安全性。