# 数据库统计信息监控

## 查看数据库统计信息
$ psql -c ”
SELECT
datname,
numbackends AS current_connections,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted
FROM pg_stat_fgedudb
WHERE datname = ‘fgedudb’;
”
datname | current_connections | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
———-+———————+————-+—————+———–+———-+————–+————-+————–+————-+————-
fgedudb | 15 | 1234567 | 123 | 1234567 | 12345678 | 123456789 | 12345678 | 1234567 | 123456 | 12345
(1 行记录)

## 查看缓存命中率
$ psql -c ”
SELECT
datname,
blks_read,
blks_hit,
ROUND(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS cache_hit_ratio
FROM pg_stat_fgedudb
WHERE datname = ‘fgedudb’;
”
datname | blks_read | blks_hit | cache_hit_ratio
———-+———–+———-+—————–
fgedudb | 1234567 | 12345678 | 90.91
(1 行记录)

## 查看事务提交率
$ psql -c ”
SELECT
datname,
xact_commit,
xact_rollback,
ROUND(xact_commit::numeric / (xact_commit + xact_rollback) * 100, 2) AS commit_ratio
FROM pg_stat_fgedudb
WHERE datname = ‘fgedudb’;
”
datname | xact_commit | xact_rollback | commit_ratio
———-+————-+—————+————–
fgedudb | 1234567 | 123 | 99.99
(1 行记录)

## 查看表统计信息
$ psql -c ”
SELECT
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd
FROM pg_stat_fgedu_tables
ORDER BY seq_scan DESC
LIMIT 10;
”
schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd
————+—————–+———-+————–+———-+—————+———–+———–+———–+—————
public | fgedu_orders | 12345 | 123456789 | 123456 | 12345678 | 123456 | 12345 | 1234 | 10000
public | fgedu_fgedus | 5678 | 12345678 | 56789 | 5678901 | 56789 | 5678 | 567 | 5000
(2 行记录)

## 查看索引使用率
$ psql -c ”
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
ORDER BY idx_scan DESC
LIMIT 10;
”
schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
————+—————+——————–+———-+————–+—————
public | fgedu_fgedus | idx_fgedu_fgedus_id | 123456 | 1234567 | 1234567
public | fgedu_orders | idx_fgedu_orders_id| 100000 | 1000000 | 1000000
(2 行记录)

## 查看未使用的索引
$ psql -c ”
SELECT
schemaname,
relname,
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_fgedu_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
”
schemaname | relname | indexrelname | index_size
————+————-+————–+————
public | fgedu_test | idx_test | 128 kB
(1 行记录)

# 后台进程监控

## 查看后台写进程统计
$ psql -c “SELECT * FROM pg_stat_bgwriter;”
-[ RECORD 1 ]———+——————————
checkpoints_timed | 1234
checkpoints_req | 56
checkpoint_write_time | 12345678
checkpoint_sync_time | 123456
buffers_checkpoint | 1234567
buffers_clean | 123456
maxwritten_clean | 1234
buffers_backend | 12345
buffers_backend_fsync | 12
buffers_alloc | 1234567
stats_reset | 2026-01-01 00:00:00+08

## 查看归档进程统计
$ psql -c “SELECT * FROM pg_stat_archiver;”
-[ RECORD 1 ]——+——————————
archived_count | 123456
last_archived_wal | 000000010000000000000123
last_archived_time | 2026-04-02 10:00:00+08
failed_count | 0
last_failed_wal |
last_failed_time |
stats_reset | 2026-01-01 00:00:00+08

## 查看WAL统计
$ psql -c “SELECT * FROM pg_stat_wal;”
-[ RECORD 1 ]—-+——————————
wal_records | 12345678
wal_fpi | 123456
wal_bytes | 1234567890123
wal_buffers_full | 1234
wal_write | 123456
wal_sync | 12345
wal_write_time | 12345.67
wal_sync_time | 1234.56
stats_reset | 2026-01-01 00:00:00+08

## 查看VACUUM进度
$ psql -c “SELECT * FROM pg_stat_progress_vacuum;”
-[ RECORD 1 ]——+——————————
pid | 12345
datid | 16384
datname | fgedudb
relid | 12345
phase | scanning heap
heap_blks_total | 123456
heap_blks_scanned | 50000
heap_blks_vacuumed| 0
index_vacuum_count| 0
max_dead_tuples | 12345678
num_dead_tuples | 1234567

## 查看创建索引进度
$ psql -c “SELECT * FROM pg_stat_progress_create_index;”
-[ RECORD 1 ]—–+——————————
pid | 12345
datid | 16384
datname | fgedudb
relid | 12345
index_relid | 0
command | CREATE INDEX
phase | building index
lockers_total | 0
lockers_done | 0
current_locker_pid| 0
blocks_total | 123456
blocks_done | 50000
tuples_total | 1234567
tuples_done | 500000
partitions_total | 0
partitions_done | 0

# 创建连接数监控脚本
$ cat > /postgresql/scripts/pg_connection_monitor.sh << 'EOF' #!/bin/bash # pg_connection_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: http://www.fgedu.net.cn LOG_FILE=/postgresql/log/connection_monitor.log ALERT_THRESHOLD=80 CRITICAL_THRESHOLD=90 # 获取当前连接数和最大连接数 CURRENT_CONN=$(psql -t -c "SELECT count(*) FROM pg_stat_activity;") MAX_CONN=$(psql -t -c "SELECT setting::int FROM pg_settings WHERE name = 'max_connections';") # 计算使用率 USAGE_PERCENT=$(echo "scale=2; $CURRENT_CONN / $MAX_CONN * 100" | bc) # 记录日志 echo "$(date '+%Y-%m-%d %H:%M:%S') - 当前连接数: $CURRENT_CONN, 最大连接数: $MAX_CONN, 使用率: ${USAGE_PERCENT}%" >> $LOG_FILE

# 判断是否告警
if (( $(echo “$USAGE_PERCENT >= $CRITICAL_THRESHOLD” | bc -l) )); then
echo “[CRITICAL] 连接数使用率 ${USAGE_PERCENT}% 超过临界阈值 ${CRITICAL_THRESHOLD}%”
# 发送告警（可集成邮件、短信等）
# mail -s “PostgreSQL连接数告警” admin@fgedu.net.cn <<< "连接数使用率: ${USAGE_PERCENT}%" elif (( $(echo "$USAGE_PERCENT >= $ALERT_THRESHOLD” | bc -l) )); then
echo “[WARNING] 连接数使用率 ${USAGE_PERCENT}% 超过警告阈值 ${ALERT_THRESHOLD}%”
fi

# 显示空闲连接
IDLE_CONN=$(psql -t -c “SELECT count(*) FROM pg_stat_activity WHERE state = ‘idle’;”)
echo “空闲连接数: $IDLE_CONN”

# 显示长时间空闲连接
LONG_IDLE=$(psql -t -c ”
SELECT count(*) FROM pg_stat_activity
WHERE state = ‘idle’
AND now() – state_change > interval ’30 minutes’;
“)
if [ “$LONG_IDLE” -gt 0 ]; then
echo “[WARNING] 长时间空闲连接数: $LONG_IDLE”
fi
EOF

chmod +x /postgresql/scripts/pg_connection_monitor.sh

# 执行监控
$ /postgresql/scripts/pg_connection_monitor.sh
[WARNING] 连接数使用率 85.50% 超过警告阈值 80%
空闲连接数: 150
[WARNING] 长时间空闲连接数: 25

# 创建锁监控脚本
$ cat > /postgresql/scripts/pg_lock_monitor.sh << 'EOF' #!/bin/bash # pg_lock_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: http://www.fgedu.net.cn LOG_FILE=/postgresql/log/lock_monitor.log echo "========================================" >> $LOG_FILE
echo “锁监控检查 – $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE

# 检查锁等待
LOCK_WAIT=$(psql -t -c ”
SELECT count(*) FROM pg_locks l1
JOIN pg_locks l2 ON l1.locktype = l2.locktype
AND l1.fgedudb IS NOT DISTINCT FROM l2.fgedudb
AND l1.relation IS NOT DISTINCT FROM l2.relation
AND l1.page IS NOT DISTINCT FROM l2.page
AND l1.tuple IS NOT DISTINCT FROM l2.tuple
AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
AND l1.classid IS NOT DISTINCT FROM l2.classid
AND l1.objid IS NOT DISTINCT FROM l2.objid
AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
AND l1.pid != l2.pid
WHERE NOT l1.granted AND l2.granted;
“)

if [ “$LOCK_WAIT” -gt 0 ]; then
echo “[WARNING] 发现 $LOCK_WAIT 个锁等待” >> $LOG_FILE

# 显示锁等待详情
psql -c ”
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_fgedu,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_fgedu,
blocked.query AS blocked_query,
now() – blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.fgedudb IS NOT DISTINCT FROM blocked_locks.fgedudb
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted AND blocking_locks.granted;
” >> $LOG_FILE
else
echo “[OK] 无锁等待” >> $LOG_FILE
fi

# 检查长时间持有锁的会话
LONG_LOCK=$(psql -t -c ”
SELECT count(*) FROM pg_stat_activity
WHERE wait_event_type = ‘Lock’
AND now() – query_start > interval ‘5 minutes’;
“)

if [ “$LONG_LOCK” -gt 0 ]; then
echo “[WARNING] 发现 $LONG_LOCK 个长时间锁等待” >> $LOG_FILE
fi

echo “锁监控检查完成” >> $LOG_FILE
EOF

chmod +x /postgresql/scripts/pg_lock_monitor.sh

# 执行监控
$ /postgresql/scripts/pg_lock_monitor.sh
[WARNING] 发现 2 个锁等待
blocked_pid | blocked_fgedu | blocking_pid | blocking_fgedu | blocked_query | wait_duration
————-+————–+————–+—————+————————————–+—————
12600 | fgapp_fgedu | 12500 | fgapp_fgedu | UPDATE fgedu_fgedus SET name=’test’…| 00:02:30
12601 | fgapp_fgedu | 12500 | fgapp_fgedu | UPDATE fgedu_fgedus SET name=’new’… | 00:01:45
(2 行记录)

# 创建表空间监控脚本
$ cat > /postgresql/scripts/pg_fgedutbs_monitor.sh << 'EOF' #!/bin/bash # pg_fgedutbs_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: http://www.fgedu.net.cn LOG_FILE=/postgresql/log/fgedutbs_monitor.log ALERT_THRESHOLD=80 CRITICAL_THRESHOLD=90 echo "========================================" >> $LOG_FILE
echo “表空间监控检查 – $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE

# 获取表空间使用情况
psql -c ”
SELECT
spcname AS fgedutbs,
pg_size_pretty(pg_fgedutbs_size(oid)) AS used_size,
pg_size_pretty(pg_fgedutbs_location(oid)) AS location
FROM pg_fgedutbs;
” >> $LOG_FILE

# 检查文件系统使用率
for dir in $(psql -t -c “SELECT pg_fgedutbs_location(oid) FROM pg_fgedutbs WHERE pg_fgedutbs_location(oid) IS NOT NULL;”); do
if [ -d “$dir” ]; then
USAGE=$(df -h “$dir” | tail -1 | awk ‘{print $5}’ | tr -d ‘%’)
MOUNT=$(df -h “$dir” | tail -1 | awk ‘{print $6}’)

if [ “$USAGE” -ge $CRITICAL_THRESHOLD ]; then
echo “[CRITICAL] 表空间目录 $dir (挂载点: $MOUNT) 使用率 ${USAGE}%” >> $LOG_FILE
elif [ “$USAGE” -ge $ALERT_THRESHOLD ]; then
echo “[WARNING] 表空间目录 $dir (挂载点: $MOUNT) 使用率 ${USAGE}%” >> $LOG_FILE
else
echo “[OK] 表空间目录 $dir (挂载点: $MOUNT) 使用率 ${USAGE}%” >> $LOG_FILE
fi
fi
done

# 检查数据目录使用率
DATA_USAGE=$(df -h $PGDATA | tail -1 | awk ‘{print $5}’ | tr -d ‘%’)
if [ “$DATA_USAGE” -ge $CRITICAL_THRESHOLD ]; then
echo “[CRITICAL] 数据目录使用率 ${DATA_USAGE}%” >> $LOG_FILE
elif [ “$DATA_USAGE” -ge $ALERT_THRESHOLD ]; then
echo “[WARNING] 数据目录使用率 ${DATA_USAGE}%” >> $LOG_FILE
else
echo “[OK] 数据目录使用率 ${DATA_USAGE}%” >> $LOG_FILE
fi

# 检查WAL目录使用率
WAL_DIR=$PGDATA/pg_wal
WAL_SIZE=$(du -sh $WAL_DIR | awk ‘{print $1}’)
echo “[INFO] WAL目录大小: $WAL_SIZE” >> $LOG_FILE

echo “表空间监控检查完成” >> $LOG_FILE
EOF

chmod +x /postgresql/scripts/pg_fgedutbs_monitor.sh

# 执行监控
$ /postgresql/scripts/pg_fgedutbs_monitor.sh
========================================
表空间监控检查 – Thu Apr 2 10:00:00 CST 2026
========================================
表空间 | 使用大小 | 位置
—————-+———-+————–
pg_default | 15 GB |
pg_global | 560 kB |
fgedu_data | 50 GB | /postgresql/fgedutbss/fgedu_data
fgedu_index | 10 GB | /postgresql/fgedutbss/fgedu_index
(4 行记录)

[OK] 表空间目录 /postgresql/fgedutbss/fgedu_data (挂载点: /postgresql) 使用率 65%
[OK] 表空间目录 /postgresql/fgedutbss/fgedu_index (挂载点: /postgresql) 使用率 65%
[OK] 数据目录使用率 65%
[INFO] WAL目录大小: 2.5G
表空间监控检查完成