PostgreSQL 连接配置规范

核心概念

PostgreSQL连接配置是确保数据库客户端能够安全、高效连接到数据库服务器的重要配置。连接配置主要涉及以下核心概念：

连接参数：控制客户端与服务器之间连接的各种参数
连接池：管理和复用数据库连接，提高连接利用率
认证方式：控制客户端如何认证到数据库服务器
连接限制：控制同时连接到数据库的客户端数量
安全连接：使用SSL/TLS加密客户端与服务器之间的通信
连接监控：监控连接状态和性能

连接参数配置

1. 基本连接参数

sql

-- 查看连接相关参数
SELECT 
    name,
    setting,
    unit,
    short_desc
FROM 
    pg_settings
WHERE 
    name IN ('listen_addresses', 'port', 'max_connections', 'tcp_keepalives_idle', 'tcp_keepalives_interval', 'tcp_keepalives_count');

-- 修改连接参数
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET listen_addresses = '*';
-- 重新加载配置
SELECT pg_reload_conf();

参数说明：

参数	说明	推荐值
listen_addresses	监听的IP地址，'*'表示所有IP	'*' 或特定IP
port	监听的端口号	5432（默认）
max_connections	最大并发连接数	100-500（根据硬件资源调整）
tcp_keepalives_idle	TCP连接空闲时间（秒）	600
tcp_keepalives_interval	TCP keepalive发送间隔（秒）	15
tcp_keepalives_count	TCP keepalive重试次数	5

2. 连接超时参数

sql

-- 查看超时相关参数
SELECT 
    name,
    setting,
    unit,
    short_desc
FROM 
    pg_settings
WHERE 
    name IN ('connect_timeout', 'idle_in_transaction_session_timeout', 'statement_timeout', 'lock_timeout');

-- 修改超时参数
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

参数说明：

参数	说明	推荐值
connect_timeout	客户端连接超时时间（秒）	10
idle_in_transaction_session_timeout	事务空闲超时时间	300s
statement_timeout	语句执行超时时间	30s（根据业务需求调整）
lock_timeout	锁等待超时时间	5s

连接池配置

1. PgBouncer连接池配置

ini

# PgBouncer配置文件示例 (/etc/pgbouncer/pgbouncer.ini)

[databases]
* = host=localhost port=5432 pool_mode=transaction

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5.0
max_db_connections = 100
max_user_connections = 50

启动PgBouncer：

bash

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

2. Pgpool-II连接池配置

txt

# Pgpool-II配置文件示例 (/etc/pgpool2/pgpool.conf)

listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/15/data'
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

启动Pgpool-II：

bash

sudo systemctl start pgpool2
sudo systemctl enable pgpool2

安全连接配置

1. SSL/TLS配置

sql

-- 查看SSL配置
SELECT 
    name,
    setting,
    short_desc
FROM 
    pg_settings
WHERE 
    name LIKE '%ssl%';

-- 启用SSL
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';
ALTER SYSTEM SET ssl_ca_file = 'root.crt';
-- 需要重启PostgreSQL服务

生成SSL证书：

bash

# 创建证书目录
mkdir -p /data/postgres/ssl
cd /data/postgres/ssl

# 生成根证书
openssl req -new -x509 -days 3650 -nodes -text -out root.crt -keyout root.key -subj "/CN=root-ca"
chmod 0600 root.key

# 生成服务器证书
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=db-server"
openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
chmod 0600 server.key

# 生成客户端证书
openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=db-client"
openssl x509 -req -in client.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out client.crt
chmod 0600 client.key

# 设置证书权限
chown -R postgres:postgres /data/postgres/ssl

2. 认证配置

sql

-- 查看pg_hba.conf文件
-- Linux: /data/postgres/pg_hba.conf
-- Windows: C:\Program Files\PostgreSQL\15\data\pg_hba.conf

-- 示例pg_hba.conf配置
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 本地连接
local   all             all                                     peer
# IPv4本地连接
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6本地连接
host    all             all             ::1/128                 scram-sha-256
# 允许特定IP段连接
host    all             all             192.168.1.0/24          scram-sha-256
# 允许所有IP连接（使用SSL）
hostssl all             all             0.0.0.0/0               scram-sha-256

认证方法说明：

方法	说明	安全性
trust	信任所有连接	低
peer	使用操作系统用户名认证	中
md5	使用MD5加密密码认证	中
scram-sha-256	使用SCRAM-SHA-256加密密码认证	高
cert	使用SSL证书认证	高

连接监控

1. 连接状态监控

sql

-- 查看当前连接状态
SELECT 
    state,
    count(*) AS count,
    application_name
FROM 
    pg_stat_activity
WHERE 
    backend_type = 'client backend'
GROUP BY 
    state, application_name
ORDER BY 
    count DESC;

-- 查看长时间运行的查询
SELECT 
    pid,
    usename,
    datname,
    now() - query_start AS duration,
    state,
    substr(query, 1, 100) AS query_sample
FROM 
    pg_stat_activity
WHERE 
    state = 'active'
ORDER BY 
    duration DESC
LIMIT 5;

-- 查看空闲事务
SELECT 
    pid,
    usename,
    datname,
    now() - xact_start AS xact_duration,
    now() - query_start AS query_duration,
    state,
    substr(query, 1, 100) AS query_sample
FROM 
    pg_stat_activity
WHERE 
    state = 'idle in transaction'
ORDER BY 
    xact_duration DESC
LIMIT 5;

2. 连接统计监控

sql

-- 查看连接统计信息
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM 
    pg_stat_database
ORDER BY 
    numbackends DESC;

连接池最佳实践

1. PgBouncer使用建议

选择合适的池模式：
- session：每个客户端连接独占一个服务器连接，适合需要会话级状态的应用
- transaction：每个事务使用一个服务器连接，适合OLTP应用
- statement：每个语句使用一个服务器连接，适合无状态应用

合理设置连接池大小：

default_pool_size = min(CPU核心数 * 2, 50)
max_client_conn = 1000-5000（根据应用需求调整）

启用连接复用：

ini

server_reset_query = DISCARD ALL
server_reset_query_always = 1

2. 应用层连接池配置

Java应用（HikariCP）：

java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/testdb");
config.setUsername("testuser");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
HikariDataSource dataSource = new HikariDataSource(config);

Python应用（psycopg2-pool）：

python

from psycopg2 import pool

# 创建连接池
postgreSQL_pool = pool.SimpleConnectionPool(
    5,  # 最小连接数
    20,  # 最大连接数
    host="localhost",
    database="testdb",
    user="testuser",
    password="password",
    port="5432"
)

# 获取连接
conn = postgreSQL_pool.getconn()
cursor = conn.cursor()

# 使用连接
cursor.execute("SELECT * FROM test_table")

# 归还连接
postgreSQL_pool.putconn(conn)

连接配置最佳实践

1. 性能优化

合理设置max_connections：根据硬件资源调整，避免设置过大导致内存不足
使用连接池：减少连接建立和关闭的开销
优化TCP参数：调整tcp_keepalives相关参数，避免连接泄漏
设置合理的超时参数：避免长时间占用连接资源

2. 安全最佳实践

限制监听地址：只监听必要的IP地址
使用强密码认证：优先使用scram-sha-256认证
启用SSL/TLS：加密客户端与服务器之间的通信
限制连接来源：在pg_hba.conf中限制允许连接的IP地址
定期轮换密码：定期更新数据库用户密码

3. 监控最佳实践

监控连接数：设置连接数告警阈值
监控空闲事务：及时发现和处理长时间空闲的事务
监控连接池状态：监控连接池的使用率和等待队列
记录连接日志：配置log_connections和log_disconnections参数

常见问题处理

1. 连接数已满

问题症状：

FATAL:  sorry, too many clients already

解决方法：

sql

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看最大连接数
SHOW max_connections;

-- 调整最大连接数
ALTER SYSTEM SET max_connections = 300;
-- 需要重启PostgreSQL服务

-- 或者终止空闲连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND now() - query_start > interval '10 minutes';

2. 连接被拒绝

问题症状：

FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "testuser", database "testdb", SSL off

解决方法：

bash

# 修改pg_hba.conf文件，添加允许的IP地址
echo "host    testdb    testuser    192.168.1.100/32    scram-sha-256" >> /data/postgres/pg_hba.conf

# 重新加载配置
psql -c "SELECT pg_reload_conf();"

3. SSL连接失败

问题症状：

FATAL:  connection requires a valid client certificate

解决方法：

bash

# 确保客户端使用正确的SSL证书
psql "sslmode=require sslcert=client.crt sslkey=client.key sslrootcert=root.crt host=db-server dbname=testdb user=testuser"

# 或者在pg_hba.conf中调整认证方式
echo "hostssl    testdb    testuser    0.0.0.0/0    scram-sha-256" >> /data/postgres/pg_hba.conf

常见问题（FAQ）

Q1：如何查看当前连接数？

A1：可以使用以下命令查看：

sql

-- 查看当前总连接数
SELECT count(*) FROM pg_stat_activity;

-- 按状态分组查看
SELECT state, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY state;

Q2：如何优化连接性能？

A2：可以从以下几个方面优化：

使用连接池管理连接
合理设置max_connections参数
优化TCP keepalive参数
设置合理的超时参数
减少连接建立和关闭的频率

Q3：如何处理连接泄漏？

A3：可以通过以下方法处理：

设置合理的idle_in_transaction_session_timeout参数
定期监控和终止空闲连接
使用连接池管理连接，确保连接正确归还
检查应用代码，确保连接正确关闭

Q4：如何配置SSL连接？

A4：配置SSL连接需要以下步骤：

生成SSL证书和密钥
配置postgresql.conf文件中的SSL参数
在pg_hba.conf中启用hostssl认证
重启PostgreSQL服务
客户端使用SSL连接字符串

Q5：如何选择合适的连接池？

A5：选择连接池需要考虑以下因素：

应用类型：OLTP应用适合使用事务级连接池，OLAP应用适合使用会话级连接池
并发需求：高并发应用需要支持大量连接的连接池
性能要求：选择性能优异的连接池，如HikariCP、PgBouncer
功能需求：根据需要选择支持读写分离、故障切换等功能的连接池
易用性：选择易于配置和管理的连接池

连接配置变更管理

1. 变更流程

评估：分析变更的必要性和影响范围
测试：在测试环境验证变更效果
计划：制定详细的变更计划，包括回滚方案
实施：在业务低峰期实施变更
验证：验证变更效果和系统稳定性
监控：密切监控变更后的连接状态和性能

2. 变更注意事项

避免在业务高峰期实施变更
提前通知相关团队和用户
准备详细的回滚方案
实施变更后密切监控系统性能
记录变更过程和结果

3. 常见变更场景

调整max_connections参数
更改认证方式
启用SSL/TLS
调整连接超时参数
配置连接池

通过遵循上述连接配置规范，可以确保PostgreSQL数据库连接的安全性、可靠性和高性能，为应用提供稳定的数据库服务。

PostgreSQL 连接配置规范 ​

核心概念 ​

连接参数配置 ​

1. 基本连接参数 ​

2. 连接超时参数 ​

连接池配置 ​

1. PgBouncer连接池配置 ​

2. Pgpool-II连接池配置 ​

安全连接配置 ​

1. SSL/TLS配置 ​

2. 认证配置 ​

连接监控 ​

1. 连接状态监控 ​

2. 连接统计监控 ​

连接池最佳实践 ​

1. PgBouncer使用建议 ​

2. 应用层连接池配置 ​

连接配置最佳实践 ​

1. 性能优化 ​

2. 安全最佳实践 ​

3. 监控最佳实践 ​

常见问题处理 ​

1. 连接数已满 ​

2. 连接被拒绝 ​

3. SSL连接失败 ​

常见问题（FAQ） ​

Q1：如何查看当前连接数？ ​

Q2：如何优化连接性能？ ​

Q3：如何处理连接泄漏？ ​

Q4：如何配置SSL连接？ ​

Q5：如何选择合适的连接池？ ​

连接配置变更管理 ​

1. 变更流程 ​

2. 变更注意事项 ​

3. 常见变更场景 ​

PostgreSQL 连接配置规范

核心概念

连接参数配置

1. 基本连接参数

2. 连接超时参数

连接池配置

1. PgBouncer连接池配置

2. Pgpool-II连接池配置

安全连接配置

1. SSL/TLS配置

2. 认证配置

连接监控

1. 连接状态监控

2. 连接统计监控

连接池最佳实践

1. PgBouncer使用建议

2. 应用层连接池配置

连接配置最佳实践

1. 性能优化

2. 安全最佳实践

3. 监控最佳实践

常见问题处理

1. 连接数已满

2. 连接被拒绝

3. SSL连接失败

常见问题（FAQ）

Q1：如何查看当前连接数？

Q2：如何优化连接性能？

Q3：如何处理连接泄漏？

Q4：如何配置SSL连接？

Q5：如何选择合适的连接池？

连接配置变更管理

1. 变更流程

2. 变更注意事项

3. 常见变更场景