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

目录

• PostgreSQL 查询历史最大进程数方法
• 一 使用统计收集器数据
• 1.1 查看当前进程zNHxjiBzaV信息
• 1.2 查询历史峰值（需要统计收集器开启）
• 二 使用 pg_stat_activity 历史快照
• 2.1 创建扩展记录历史数据
• 2.2 查询记录的历史峰值
• 三 使用日志分析
• 3.1 配置日志记录连接信息
• 3.2 使用pgBadger分析日志
• 3.3 手动分析日志
• 四 使用监控系统数据
• 4.1 Prometheus + Grafana
• 4.2 pgMonitor (Crunchy Data)
• 五 使用系统视图组合查询
• 5.1 综合查询方法
• 5.2 跟踪连接变化（需要定期执行）
• 六 使用 pg_controldata 工具
• 最佳实践建议

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

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

一 使用统计收集器数据

1.1 查看当前进程信息

SELECT setting as max_connections, 
       (SELECT setting FROM pg_settings WHERE name = 'superuser_reserved_connections') AS reserved_connections
FROM pg_settings 
WHERE name = 'max_connections';

输出示例：

 max_connections | reserved_connections 
-----------------+----------------------
 100             | 3
(1 row)

1.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;

输出示例：

white=# SELECT max(numbackends) AS max_connections_ever
white-# FROM pg_stat_database;
 max_connections_ever 
----------------------
                    2
(1 row)

white=# 
white=# SELECT datname, 
white-#        max(numbackends) AS max_connections
white-# FROM pg_stat_database
white-# GROUP BY datname
white-# ORDER BY max_connections DESC;
  datname  | max_connections 
-----------+-----------------
 white     |               2
 white3    |               0
 white2    |               0
 postgres  |               0
 white1    |               0
 template0 |               0
           |               0
 template1 |               0
(8 rows)

white=# 

二 使用 pg_stat_activity 历史快照

2.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;

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

2.2 查询记录的历史峰值

SELECT max(connection_count) AS historiandroidcal_max_connections
FROM connectionjs_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;

三 使用日志分析

3.1 配置日志记录连接信息

在 postgresql.conf 中设置：

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

3.2 使用pgBadger分析日志

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

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

3.3 手动分析日志

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

四 使用监控系统数据

4.1 Prometheus + Grafana

如果使用Prometheus监控：

-- 查询过去30天最大连接数
max_over_time(pg_sta编程客栈t_activity_count[30d])

4.2 pgMonitor (Crunchy Data)

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

五 使用系统视图组合编程客栈查询

5.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;

5.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 参数

以上就是PostgreSQL查询历史最大进程数的多种方法的详细内容，更多关于PostgreSQL查询最大进程数的资料请关注编程客栈(www.devze.com)其它相关文章！