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)

--當前數(shù)據(jù)庫最大連接數(shù)（自統(tǒng)計重置后）
SELECT max(numbackends) AS max_connections_ever
FROM pg_stat_database;

--查看所有數(shù)據(jù)庫最大連接數(shù)
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;  -- 更高級的監(jiān)控

-- 創(chuàng)建歷史記錄表
CREATE TABLE connection_history AS
SELECT now() AS sample_time, 
       count(*) AS connection_count
FROM pg_stat_activity;

-- 設(shè)置定時任務(wù)（如pgAgent或cron）定期執(zhí)行
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 '

# 生成連接數(shù)報告
pgbadger -j 8 /var/log/postgresql/postgresql-*.log -o pgbadger_report.html

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

# 統(tǒng)計每日最大連接數(shù)
grep "connection authorized" /var/log/postgresql/postgresql-15-main.log | \
  awk '{print $1}' | \
  sort | uniq -c | sort -n

-- 查詢過去30天最大連接數(shù)
max_over_time(pg_stat_activity_count[30d])

-- 使用預(yù)置的監(jiān)控視圖
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;

-- 創(chuàng)建跟蹤表
CREATE TABLE IF NOT EXISTS connection_tracking (
  ts timestamp PRIMARY KEY,
  connection_count integer,
  max_since_reset integer
);

-- 更新函數(shù)
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;

-- 設(shè)置定時執(zhí)行（如每分鐘）
-- 可以通過pg_cron擴展或外部cron設(shè)置

# 查看數(shù)據(jù)庫控制文件信息（包含一些歷史統(tǒng)計）
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:"