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

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)

--当前数据库最大连接数（自统计重置后）
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=# 

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;

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;

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

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

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

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

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

-- 使用预置的监控视图
select * from monitor.pg_connection_history
order by max_connections desc
limit 10;

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;

-- 创建跟踪表
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 /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:"