## 背景
– 业务需求：企业需要一个自动化的PostgreSQL日常巡检脚本
– 数据库环境：PostgreSQL 18.3，10个数据库实例
– 巡检频率：每天执行一次

## 实施过程

### 1. 编写日常巡检脚本
$ vi /postgresql/scripts/daily_check.sh

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

# 定义变量
DATE=$(date +”%Y-%m-%d”)
LOG_DIR=”/postgresql/logs/check”
LOG_FILE=”${LOG_DIR}/daily_check_${DATE}.log”

# 创建日志目录
mkdir -p ${LOG_DIR}

# 开始日志
echo “PostgreSQL日常巡检开始: $(date)” > ${LOG_FILE}
echo “====================================” >> ${LOG_FILE}

# 1. 检查数据库状态
echo “1. 检查数据库状态” >> ${LOG_FILE}
pg_isready -h fgedu.localhost -p 5432 >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 2. 检查数据库连接数
echo “2. 检查数据库连接数” >> ${LOG_FILE}
psql -U postgres -c “SELECT count(*) FROM pg_stat_activity;” >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 3. 检查数据库性能
echo “3. 检查数据库性能” >> ${LOG_FILE}
psql -U postgres -c “SELECT now() – query_start as duration, state, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC LIMIT 5;” >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 4. 检查表空间使用情况
echo “4. 检查表空间使用情况” >> ${LOG_FILE}
psql -U postgres -c “SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size FROM pg_tablespace;” >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 5. 检查备份状态
echo “5. 检查备份状态” >> ${LOG_FILE}
# 检查最近24小时的备份
psql -U postgres -c “SELECT * FROM pg_stat_archiver WHERE last_archived_wal IS NOT NULL ORDER BY last_archived_time DESC LIMIT 1;” >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 6. 检查日志
echo “6. 检查日志” >> ${LOG_FILE}
grep -i “error” /postgresql/logs/postgresql-*.log | tail -10 >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 7. 检查系统资源
echo “7. 检查系统资源” >> ${LOG_FILE}
# 检查CPU使用情况
top -b -n 1 | head -20 >> ${LOG_FILE}
echo “” >> ${LOG_FILE}
# 检查内存使用情况
free -h >> ${LOG_FILE}
echo “” >> ${LOG_FILE}
# 检查磁盘使用情况
df -h >> ${LOG_FILE}
echo “” >> ${LOG_FILE}

# 结束日志
echo “====================================” >> ${LOG_FILE}
echo “PostgreSQL日常巡检结束: $(date)” >> ${LOG_FILE}

# 发送邮件通知
mail -s “PostgreSQL日常巡检报告” admin@fgedu.net.cn < ${LOG_FILE} ### 2. 配置定时任务 $ crontab -e # 每天凌晨1点执行日常巡检 0 1 * * * /postgresql/scripts/daily_check.sh ### 3. 执行脚本测试 $ /postgresql/scripts/daily_check.sh ### 4. 查看巡检结果 $ cat /postgresql/logs/check/daily_check_2026-04-06.log PostgreSQL日常巡检开始: 2026-04-06 10:00:00 ==================================== 1. 检查数据库状态 /var/run/postgresql:5432 - accepting connections 2. 检查数据库连接数 count ------- 10 (1 row) 3. 检查数据库性能 duration | state | query ----------+--------+------------------------------------------------ | active | SELECT * FROM fgedu_users WHERE id = 1; (1 row) 4. 检查表空间使用情况 spcname | size ------------+-------- pg_default | 10 GB pg_global | 10 MB fgedutbs | 5 GB (3 rows) 5. 检查备份状态 archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | archived_cleanup_count | last_archived_cleanup_time ----------------+-------------------+------------------------+--------------+-----------------------------------+------------------------+------------------------+---------------------------- 100 | 00000001000000000000000A | 2026-04-06 09:00:00 | 0 | | | 0 | (1 row) 6. 检查日志 2026-04-06 08:00:00 UTC [12345]: [1-1] ERROR: syntax error at or near "SELECT" 7. 检查系统资源 top - 10:00:00 up 10 days, 2:00, 1 user, load average: 0.50, 0.40, 0.30 Tasks: 100 total, 1 running, 99 sleeping, 0 stopped, 0 zombie %Cpu(s): 5.0 us, 1.0 sy, 0.0 ni, 94.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st MiB Mem : 16384.0 total, 8192.0 free, 4096.0 used, 4096.0 buff/cache MiB Swap: 4096.0 total, 4096.0 free, 0.0 used. 12288.0 avail Mem total used free shared buff/cache available Mem: 16G 4.0G 8.0G 100M 4.0G 12G Swap: 4.0G 0B 4.0G Filesystem Size Used Avail Use% Mounted on /dev/sda1 50G 20G 30G 40% / /dev/sdb1 200G 50G 150G 25% /postgresql ==================================== PostgreSQL日常巡检结束: 2026-04-06 10:00:00

## 背景
– 业务需求：企业需要一个自动化的PostgreSQL巡检系统，实现实时监控和告警
– 数据库环境：PostgreSQL 18.3，20个数据库实例
– 监控需求：实时监控，及时告警

## 实施过程

### 1. 安装Prometheus+Grafana
– 安装PostgreSQL Exporter
– 安装Prometheus
– 安装Grafana

### 2. 配置Prometheus
$ vi /usr/local/prometheus/prometheus.yml

global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
– job_name: ‘postgres’
static_configs:
– targets: [‘192.168.1.10:9187’, ‘192.168.1.11:9187’, ‘192.168.1.12:9187’]

### 3. 配置Grafana
– 添加Prometheus数据源
– 导入PostgreSQL仪表盘
– 配置仪表盘变量

### 4. 配置告警规则
$ vi /usr/local/prometheus/rules/postgres_alerts.yml

groups:
– name: postgres_alerts
rules:
– alert: PostgresDown
expr: pg_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “PostgreSQL down”
description: “PostgreSQL instance {{ $labels.instance }} is down”

– alert: PostgresHighConnections
expr: pg_stat_activity_count > 800
for: 5m
labels:
severity: warning
annotations:
summary: “PostgreSQL high connections”
description: “PostgreSQL instance {{ $labels.instance }} has {{ $value }} connections”

– alert: PostgresSlowQueries
expr: pg_stat_statements_total_time / pg_stat_statements_calls > 1000
for: 5m
labels:
severity: warning
annotations:
summary: “PostgreSQL slow queries”
description: “PostgreSQL instance {{ $labels.instance }} has slow queries”

– alert: PostgresHighTableSpace
expr: pg_tablespace_size_bytes / 1024 / 1024 / 1024 > 90
for: 5m
labels:
severity: warning
annotations:
summary: “PostgreSQL high tablespace usage”
description: “PostgreSQL tablespace {{ $labels.tablespace }} on {{ $labels.instance }} is {{ $value }} GB”

### 5. 配置告警通知
– 配置Email通知
– 配置Slack通知
– 配置短信通知

### 6. 测试告警
– 模拟数据库故障
– 验证告警是否触发
– 验证告警通知是否发送

## 实施效果
– 实时监控：实时监控数据库状态和性能
– 及时告警：发现问题及时告警
– 可视化：通过Grafana实现数据可视化
– 自动化：减少人工干预，提高效率

## 背景
– 业务需求：企业需要对巡检结果进行分析和处理，及时解决问题
– 数据库环境：PostgreSQL 18.3，15个数据库实例
– 巡检频率：每天执行一次

## 实施过程

### 1. 收集巡检结果
– 收集日常巡检脚本的输出
– 收集Prometheus告警信息
– 收集数据库日志

### 2. 分析巡检结果
– 分析数据库状态：检查数据库是否正常运行
– 分析性能指标：检查是否存在性能瓶颈
– 分析资源使用：检查系统资源是否充足
– 分析安全配置：检查是否存在安全隐患
– 分析备份状态：检查备份是否正常执行

### 3. 处理发现的问题
– 性能问题：优化查询，调整参数
– 资源问题：增加资源，优化配置
– 安全问题：加强安全配置，修复漏洞
– 备份问题：修复备份故障，确保备份正常
– 其他问题：根据具体情况进行处理

### 4. 案例分析

#### 案例1：连接数过高
– 现象：巡检发现数据库连接数达到800，接近最大连接数
– 分析：应用程序连接管理不当，导致连接泄漏
– 处理：
1. 检查应用程序连接池配置
2. 优化应用程序连接管理
3. 调整数据库最大连接数
4. 监控连接使用情况

#### 案例2：表空间使用率高
– 现象：巡检发现表空间使用率达到90%
– 分析：数据增长过快，未及时清理过期数据
– 处理：
1. 清理过期数据
2. 归档历史数据
3. 增加表空间容量
4. 监控表空间使用情况

#### 案例3：慢查询
– 现象：巡检发现存在慢查询
– 分析：SQL语句未优化，缺少索引
– 处理：
1. 分析慢查询日志
2. 优化SQL语句
3. 添加适当的索引
4. 监控查询性能

## 实施效果
– 问题及时解决：发现问题及时处理，避免系统故障
– 性能优化：通过分析巡检结果，优化系统性能
– 安全增强：加强安全配置，提高系统安全性
– 经验积累：通过处理问题，积累运维经验