# 1. 安装Prometheus
$ wget https://github.com/prometheus/prometheus/releases/download/v2.40.0/prometheus-2.40.0.linux-amd64.tar.gz
$ tar -xzf prometheus-2.40.0.linux-amd64.tar.gz
$ mv prometheus-2.40.0.linux-amd64 /usr/local/prometheus

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

global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
– job_name: ‘postgresql’
static_configs:
– targets: [‘192.168.1.10:9187’]

# 3. 安装PostgreSQL Exporter
$ wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.0/postgres_exporter-0.13.0.linux-amd64.tar.gz
$ tar -xzf postgres_exporter-0.13.0.linux-amd64.tar.gz
$ mv postgres_exporter-0.13.0.linux-amd64 /usr/local/postgres_exporter

# 4. 配置PostgreSQL Exporter
$ vi /etc/systemd/system/postgres_exporter.service

[Unit]
Description=PostgreSQL Exporter
After=network.target

[Service]
Type=simple
User=postgres
Environment=DATA_SOURCE_NAME=”postgresql://postgres:postgres_pass@fgedu.localhost:5432/postgres?sslmode=disable”
ExecStart=/usr/local/postgres_exporter/postgres_exporter
Restart=always

[Install]
WantedBy=multi-user.target

# 5. 启动PostgreSQL Exporter
$ systemctl daemon-reload
$ systemctl start postgres_exporter
$ systemctl enable postgres_exporter

# 6. 安装Grafana
$ wget https://dl.grafana.com/oss/release/grafana-9.3.6-1.x86_64.rpm
$ yum install grafana-9.3.6-1.x86_64.rpm

# 7. 启动Grafana
$ systemctl start grafana-server
$ systemctl enable grafana-server

# 8. 访问Grafana
# 打开浏览器，访问 http://fgedu.localhost:3000
# 添加Prometheus数据源，导入PostgreSQL仪表盘

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

# 巡检日志文件
LOG_FILE=”/postgresql/logs/daily_check_$(date +%Y%m%d).log”

# 记录开始时间
echo “===== PostgreSQL日常巡检开始 =====” > $LOG_FILE
echo “巡检时间: $(date)” >> $LOG_FILE
echo “” >> $LOG_FILE

# 1. 检查数据库状态
echo “1. 检查数据库状态” >> $LOG_FILE
psql -U postgres -c “SELECT pg_is_in_recovery(), pg_postmaster_start_time();” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

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

# 3. 检查表空间使用情况
echo “3. 检查表空间使用情况” >> $LOG_FILE
psql -U postgres -c “SELECT
spcname AS tablespace,
pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace;” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 4. 检查缓存命中率
echo “4. 检查缓存命中率” >> $LOG_FILE
psql -U postgres -c “SELECT
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as hit_ratio
FROM pg_stat_database;” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 5. 检查慢查询
echo “5. 检查慢查询” >> $LOG_FILE
psql -U postgres -c “SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 6. 检查备份状态
echo “6. 检查备份状态” >> $LOG_FILE
ls -la /postgresql/backup/ >> $LOG_FILE
echo “” >> $LOG_FILE

# 记录结束时间
echo “===== PostgreSQL日常巡检结束 =====” >> $LOG_FILE
echo “巡检完成时间: $(date)” >> $LOG_FILE

# 发送邮件通知
mail -s “PostgreSQL日常巡检报告” admin@fgedu.net.cn < $LOG_FILE

# 编辑crontab
$ crontab -e

# 每天凌晨2点执行日常巡检
0 2 * * * /postgresql/scripts/daily_check.sh

# 每周日凌晨3点执行周巡检
0 3 * * 0 /postgresql/scripts/weekly_check.sh

# 每月1日凌晨4点执行月巡检
0 4 1 * * /postgresql/scripts/monthly_check.sh

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

# 报告文件
REPORT_FILE=”/postgresql/reports/pg_check_report_$(date +%Y%m%d).html”

# 生成HTML报告
cat > $REPORT_FILE << EOF