# CPU和内存检查

# 1. 检查CPU使用率
$ top -bn1 | head -20

top – 10:00:00 up 30 days, 2:30, 2 users, load average: 0.52, 0.58, 0.59
Tasks: 156 total, 1 running, 155 sleeping, 0 stopped, 0 zombie
%Cpu(s): 5.2 us, 2.1 sy, 0.0 ni, 92.3 id, 0.0 wa, 0.4 hi, 0.0 si
MiB Mem : 65536.0 total, 12345.6 free, 45000.0 used, 8190.4 buff/cache
MiB Swap: 8192.0 total, 8000.0 free, 192.0 used. 20000.0 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12345 pgsql 20 0 50.0g 45.0g 2.0g S 15.2 70.0 1200:00 postgres
12346 pgsql 20 0 50.0g 45.0g 2.0g S 5.2 70.0 800:00 postgres

# 2. 检查内存使用情况
$ free -h

total used free shared buff/cache available
Mem: 64Gi 45Gi 12Gi 2.0Gi 8.0Gi 20Gi
Swap: 8.0Gi 192Mi 7.8Gi

# 3. 检查大页内存
$ cat /proc/meminfo | grep Huge

HugePages_Total: 16384
HugePages_Free: 12000
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

# 4. 检查NUMA状态
$ numactl –hardware

available: 2 nodes (0-1)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32768 MB
node 0 free: 8192 MB
node 1 cpus: 8 9 10 11 12 13 14 15
node 1 size: 32768 MB
node 1 free: 8192 MB
node distances:
node 0 1
0: 10 21
1: 21 10

# 5. 检查进程状态
$ ps -ef | grep postgres | head -10

pgsql 12345 1 0 Apr01 ? 00:20:00 /postgresql/fgapp/bin/postgres -D /postgresql/fgdata
pgsql 12346 12345 0 Apr01 ? 00:15:00 postgres: checkpointer
pgsql 12347 12345 0 Apr01 ? 00:10:00 postgres: background writer
pgsql 12348 12345 0 Apr01 ? 00:05:00 postgres: walwriter
pgsql 12349 12345 0 Apr01 ? 00:08:00 postgres: autovacuum launcher
pgsql 12350 12345 0 Apr01 ? 00:02:00 postgres: stats collector
pgsql 12351 12345 0 Apr01 ? 00:01:00 postgres: logical replication launcher

# 磁盘和I/O检查

# 1. 检查磁盘空间
$ df -h

Filesystem Size Used Avail Use% Mounted on
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 2.0G 30G 7% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 500G 200G 300G 40% /
/dev/sdb1 2T 800G 1.2T 40% /postgresql
/dev/sdc1 1T 500G 500G 50% /postgresql/backup

# 2. 检查数据目录大小
$ du -sh /postgresql/fgdata/*

120G /postgresql/fgdata/base
50G /postgresql/fgdata/global
10G /postgresql/fgdata/pg_wal
5G /postgresql/fgdata/pg_xact
…

# 3. 检查I/O性能
$ iostat -x 1 5

Linux 5.14.0-284.11.1.el9_2.x86_64 (fgedu.net.cn) 04/07/2026 _x86_64_ (16 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
5.20 0.00 2.10 0.50 0.00 92.20

Device r/s w/s rMB/s wMB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
sda 50.00 30.00 2.50 1.50 0.00 5.00 0.00 14.29 1.00 2.00 0.10 51.20 51.20 1.00 8.00
sdb 100.00 80.00 5.00 4.00 0.00 10.00 0.00 11.11 0.50 1.00 0.15 51.20 51.20 0.50 9.00

# 4. 检查磁盘调度算法
$ cat /sys/block/sdb/queue/scheduler

[mq-deadline] none

# 5. 检查文件系统类型
$ mount | grep postgresql

/dev/sdb1 on /postgresql type xfs (rw,noatime,nodiratime,attr2,inode64,logbufs=8,logbsize=256k)

# 数据库状态检查

# 1. 检查数据库服务状态
$ pg_isready -h fgedu.localhost -p 5432 -U fgedu -d fgedudb

fgedu.localhost:5432 – accepting connections

# 2. 检查数据库版本
$ psql -U fgedu -d fgedudb -c “SELECT version();”

version
—————————————————————————————————————-
PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-2), 64-bit
(1 row)

# 3. 检查数据库启动时间
$ psql -U fgedu -d fgedudb -c “SELECT pg_postmaster_start_time();”

pg_postmaster_start_time
——————————-
2026-04-01 10:00:00.123456+08
(1 row)

# 4. 检查数据库列表
$ psql -U fgedu -d fgedudb -c “\l”

List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
———–+———-+———-+————-+————-+————+—————–+———————–
fgedudb | fgedu | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | pgsql | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | pgsql | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/pgsql + pgsql=CTc/pgsql
template1 | pgsql | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/pgsql + pgsql=CTc/pgsql
(4 rows)

# 5. 检查数据库大小
$ psql -U fgedu -d fgedudb -c ”
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) as db_size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;
”

datname | db_size
———-+———
fgedudb | 200 GB
(1 row)

# 连接和会话检查

# 1. 检查当前连接数
$ psql -U fgedu -d fgedudb -c ”
SELECT
count(*) as total_connections,
count(*) FILTER (WHERE state = ‘active’) as active,
count(*) FILTER (WHERE state = ‘idle’) as idle,
count(*) FILTER (WHERE state = ‘idle in transaction’) as idle_in_transaction
FROM pg_stat_activity;
”

total_connections | active | idle | idle_in_transaction
——————-+——–+——+———————
50 | 10 | 35 | 5
(1 row)

# 2. 检查最大连接数配置
$ psql -U fgedu -d fgedudb -c ”
SELECT setting::int as max_connections,
(SELECT count(*) FROM pg_stat_activity) as current_connections,
ROUND((SELECT count(*) FROM pg_stat_activity)::numeric / setting::int * 100, 2) as usage_percent
FROM pg_settings
WHERE name = ‘max_connections’;
”

max_connections | current_connections | usage_percent
—————–+———————+—————
300 | 50 | 16.67
(1 row)

# 3. 检查长时间运行的查询
$ psql -U fgedu -d fgedudb -c ”
SELECT
pid,
usename,
fgapplication_name,
client_addr,
state,
now() – query_start as duration,
query
FROM pg_stat_activity
WHERE state = ‘active’
AND now() – query_start > interval ‘5 minutes’
ORDER BY duration DESC;
”

pid | usename | fgapplication_name | client_addr | state | duration | query
——+———+——————+—————+——–+—————–+———————————-
8765 | fgapp_usr | fgedu_fgapp | 192.168.1.50 | active | 00:10:30.123456 | SELECT * FROM fgedu_orders WHERE…
(1 row)

# 4. 检查空闲事务
$ psql -U fgedu -d fgedudb -c ”
SELECT
pid,
usename,
fgapplication_name,
client_addr,
now() – state_change as idle_duration,
query
FROM pg_stat_activity
WHERE state = ‘idle in transaction’
AND now() – state_change > interval ‘5 minutes’
ORDER BY idle_duration DESC;
”

pid | usename | fgapplication_name | client_addr | idle_duration | query
——+———+——————+—————+—————-+—————————
9876 | fgapp_usr | fgedu_fgapp | 192.168.1.51 | 00:15:00.12345 | UPDATE fgedu_orders SET…
(1 row)

# 5. 检查锁等待
$ psql -U fgedu -d fgedudb -c ”
SELECT
blocked.pid as blocked_pid,
blocked.query as blocked_query,
blocking.pid as blocking_pid,
blocking.query as blocking_query,
blocked.mode as blocked_mode
FROM pg_locks blocked
JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid
JOIN pg_locks blocking ON blocked.locktype = blocking.locktype
AND blocked.database IS NOT DISTINCT FROM blocking.database
AND blocked.relation IS NOT DISTINCT FROM blocking.relation
AND blocked.page IS NOT DISTINCT FROM blocking.page
AND blocked.tuple IS NOT DISTINCT FROM blocking.tuple
AND blocked.pid != blocking.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking.pid
WHERE NOT blocked.granted;
”

blocked_pid | blocked_query | blocking_pid | blocking_query | blocked_mode
————-+——————————————+————–+—————————————+————–
12345 | UPDATE fgedu_orders SET status = ‘…’ | 12346 | UPDATE fgedu_orders SET status = ‘…’ | RowExclusive
(1 row)

#!/bin/bash
# pg_daily_inspection.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# PostgreSQL日常巡检脚本

PGHOME=/postgresql/fgapp
PGHOST=fgedu.localhost
PGPORT=5432
PGUSER=fgedu
PGDATABASE=fgedudb
REPORT_DIR=/postgresql/scripts/reports
DATE=$(date +%Y%m%d)
REPORT_FILE=${REPORT_DIR}/inspection_${DATE}.log

mkdir -p ${REPORT_DIR}

log_message() {
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] $1” | tee -a ${REPORT_FILE}
}

log_message “==========================================”
log_message “PostgreSQL数据库日常巡检报告”
log_message “巡检时间: $(date)”
log_message “数据库主机: $(hostname)”
log_message “==========================================”

# 1. 系统资源检查
log_message “”
log_message “=== 系统资源检查 ===”

# CPU使用率
CPU_USAGE=$(top -bn1 | grep “Cpu(s)” | awk ‘{print $2}’ | cut -d’%’ -f1)
log_message “CPU使用率: ${CPU_USAGE}%”

# 内存使用率
MEM_USAGE=$(free | grep Mem | awk ‘{printf “%.2f”, $3/$2 * 100}’)
log_message “内存使用率: ${MEM_USAGE}%”

# 磁盘使用率
log_message “磁盘使用情况:”
df -h | grep -E “^/dev|Filesystem” | tee -a ${REPORT_FILE}

# 2. 数据库状态检查
log_message “”
log_message “=== 数据库状态检查 ===”

# 数据库服务状态
DB_STATUS=$(${PGHOME}/bin/pg_isready -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} 2>&1)
log_message “数据库服务状态: ${DB_STATUS}”

# 数据库版本
DB_VERSION=$(${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -t -c “SELECT version();” 2>/dev/null | head -1)
log_message “数据库版本: ${DB_VERSION}”

# 3. 连接数检查
log_message “”
log_message “=== 连接数检查 ===”

CONN_INFO=$(${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -t -c ”
SELECT ‘当前连接: ‘ || count(*) || ‘, 最大连接: ‘ || setting ||
‘, 使用率: ‘ || ROUND(count(*)::numeric / setting::numeric * 100, 2) || ‘%’
FROM pg_stat_activity, pg_settings
WHERE name = ‘max_connections’;
” 2>/dev/null)
log_message “${CONN_INFO}”

# 4. 复制状态检查
log_message “”
log_message “=== 复制状态检查 ===”

IS_PRIMARY=$(${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -t -c “SELECT NOT pg_is_in_recovery();” 2>/dev/null | tr -d ‘ ‘)

if [ “${IS_PRIMARY}” = “t” ]; then
log_message “角色: 主库”

REPL_INFO=$(${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -t -c ”
SELECT ‘备库数量: ‘ || count(*) || ‘, 最大延迟: ‘ || COALESCE(max(pg_wal_lsn_diff(sent_lsn, replay_lsn)), 0) || ‘ bytes’
FROM pg_stat_replication;
” 2>/dev/null)
log_message “${REPL_INFO}”
else
log_message “角色: 备库”

REPL_LAG=$(${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -t -c ”
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());
” 2>/dev/null | tr -d ‘ ‘)
log_message “复制延迟: ${REPL_LAG} bytes”
fi

# 5. 表空间检查
log_message “”
log_message “=== 表空间检查 ===”

${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -c ”
SELECT
spcname as tablespace,
pg_size_pretty(pg_tablespace_size(oid)) as size
FROM pg_tablespace
ORDER BY pg_tablespace_size(oid) DESC;
” 2>/dev/null | tee -a ${REPORT_FILE}

# 6. 表大小检查
log_message “”
log_message “=== 表大小TOP10 ===”

${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -c ”
SELECT
schemaname || ‘.’ || relname as table_name,
pg_size_pretty(pg_total_relation_size(schemaname || ‘.’ || relname)) as total_size,
n_live_tup as live_tuples,
n_dead_tup as dead_tuples
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || ‘.’ || relname) DESC
LIMIT 10;
” 2>/dev/null | tee -a ${REPORT_FILE}

# 7. 索引检查
log_message “”
log_message “=== 未使用索引检查 ===”

${PGHOME}/bin/psql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d ${PGDATABASE} -c ”
SELECT
schemaname || ‘.’ || relname as table_name,
indexrelname as index_name,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE ‘%_pkey’
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
” 2>/dev/null | tee -a ${REPORT_FILE}

log_message “”
log_message “==========================================”
log_message “巡检完成”
log_message “报告文件: ${REPORT_FILE}”
log_message “==========================================”