PostgreSQL 查询历史最大进程数方法

PostgreSQL 查询历史最大进程数方法

PostgreSQL 提供了多种方式来查询数据库的历史最大进程数（连接数）。以下是几种有效的方法：

一、使用统计收集器数据

1. 查看当前统计信息

SELECT max_connections, 
       (SELECT setting FROM pg_settings WHERE name = 'superuser_reserved_connections') AS reserved_connections,
       max_connections - (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections') AS user_max_connections
FROM pg_settings 
WHERE name = 'max_connections';

2. 查询历史峰值（需要统计收集器开启）

-- 查看所有时间最大连接数（自统计重置后）
SELECT max(numbackends) AS max_connections_ever
FROM pg_stat_database;

-- 按数据库查看历史峰值
SELECT datname, 
       max(numbackends) AS max_connections
FROM pg_stat_database
GROUP BY datname
ORDER BY max_connections DESC;

二、使用 pg_stat_activity 历史快照

1. 创建扩展记录历史数据

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;  -- 更高级的监控

-- 创建历史记录表
CREATE TABLE connection_history AS
SELECT now() AS sample_time, 
       count(*) AS connection_count
FROM pg_stat_activity
WITH NO DATA;

-- 设置定时任务（如pgAgent或cron）定期执行
INSERT INTO connection_history
SELECT now(), count(*) FROM pg_stat_activity;

2. 查询记录的历史峰值

SELECT max(connection_count) AS historical_max_connections
FROM connection_history;

-- 按时间范围查询
SELECT date_trunc('hour', sample_time) AS hour,
       max(connection_count) AS max_connections_per_hour
FROM connection_history
WHERE sample_time > now() - interval '7 days'
GROUP BY 1
ORDER BY 1;

三、使用日志分析

1. 配置日志记录连接信息

在 postgresql.conf 中设置：

log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d '

2. 使用pgBadger分析日志

# 生成连接数报告
pgbadger -j 8 /var/log/postgresql/postgresql-*.log -o pgbadger_report.html

# 然后查看报告中的"Connections"部分

3. 手动分析日志

# 统计每日最大连接数
grep "connection authorized" /var/log/postgresql/postgresql-15-main.log | \
  awk '{print $1}' | \
  sort | uniq -c | sort -n

四、使用监控系统数据

1. Prometheus + Grafana

如果使用Prometheus监控：

-- 查询过去30天最大连接数
max_over_time(pg_stat_activity_count[30d])

2. pgMonitor (Crunchy Data)

-- 使用预置的监控视图
SELECT * FROM monitor.pg_connection_history
ORDER BY max_connections DESC
LIMIT 10;

五、使用系统视图组合查询

1. 综合查询方法

WITH connection_stats AS (
  SELECT 
    count(*) AS current_connections,
    (SELECT setting FROM pg_settings WHERE name = 'max_connections')::int AS max_allowed_connections
  FROM pg_stat_activity
)
SELECT 
  current_connections,
  max_allowed_connections,
  round(current_connections * 100.0 / max_allowed_connections, 2) AS percentage_used,
  (SELECT max(numbackends) FROM pg_stat_database) AS historical_max
FROM connection_stats;

2. 跟踪连接变化（需要定期执行）

-- 创建跟踪表
CREATE TABLE IF NOT EXISTS connection_tracking (
  ts timestamp PRIMARY KEY,
  connection_count integer,
  max_since_reset integer
);

-- 更新函数
CREATE OR REPLACE FUNCTION update_connection_stats() RETURNS void AS $$
DECLARE
  current_count integer;
  historical_max integer;
BEGIN
  SELECT count(*) INTO current_count FROM pg_stat_activity;
  SELECT max(numbackends) INTO historical_max FROM pg_stat_database;
  
  INSERT INTO connection_tracking 
  VALUES (now(), current_count, historical_max)
  ON CONFLICT (ts) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

-- 设置定时执行（如每分钟）
-- 可以通过pg_cron扩展或外部cron设置

六、使用 pg_controldata 工具

对于紧急情况分析：

# 查看数据库控制文件信息（包含一些历史统计）
pg_controldata /var/lib/postgresql/15/main

# 查找以下行：
"Maximum data alignment:               
"Database block size:                  
"Blocks per segment of large relation: 
"WAL block size:                       
"Latest checkpoint's MAXIMUM CONNECTIONS:"

最佳实践建议

1. 长期监控：设置定期记录机制（如每分钟记录连接数）
2. 警报阈值：当连接数接近 max_connections 的80%时触发警报
3. 连接池：考虑使用pgBouncer或pgPool-II管理连接
4. 定期审查：每月分析连接趋势，调整 max_connections 参数