# 1. 安装pg_stat_statements扩展
$ psql -U fgedu -d fgedudb -c “CREATE EXTENSION pg_stat_statements;”

# 2. 安装pg_top
$ sudo yum install pg_top

# 3. 安装pgBadger
$ sudo yum install perl-ExtUtils-MakeMaker
$ wget https://github.com/darold/pgbadger/archive/master.zip
$ unzip master.zip
$ cd pgbadger-master
$ perl Makefile.PL
$ make
$ sudo make install

# 4. 安装Prometheus和Grafana
$ sudo yum install prometheus grafana

# 5. 安装PostgreSQL exporters
$ wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
$ tar -xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
$ cd postgres_exporter-0.15.0.linux-amd64
$ sudo cp postgres_exporter /usr/local/bin/

# 6. 配置PostgreSQL exporters
$ sudo vi /etc/postgres_exporter.yml
data_source_name: “postgresql://fgedu:password@fgedu.localhost:5432/fgedudb?sslmode=disable”

# 7. 启动PostgreSQL exporters
$ sudo systemctl start postgres_exporter
$ sudo systemctl enable postgres_exporter

# 8. 验证工具安装
$ pg_top -h fgedu.localhost -U fgedu -d fgedudb
$ pgbadger –help
$ curl http://fgedu.localhost:9187/metrics

# 1. 编写日常巡检脚本
$ sudo vi /usr/local/bin/postgresql_daily_check.sh

#!/bin/bash

# PostgreSQL日常巡检脚本

# 设置变量
HOST=”fgedu.localhost”
PORT=”5432″
USER=”fgedu”
DB=”fgedudb”
DATE=$(date +”%Y-%m-%d”)
LOG_DIR=”/var/log/postgresql/check”
REPORT_DIR=”/var/report/postgresql/check”

# 创建目录
mkdir -p $LOG_DIR
mkdir -p $REPORT_DIR

# 开始巡检
echo “开始日常巡检: $(date)” >> $LOG_DIR/daily_check_$DATE.log

# 检查数据库状态
echo “检查数据库状态” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT pg_is_in_recovery();” >> $LOG_DIR/daily_check_$DATE.log

# 检查连接数
echo “检查连接数” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT count(*) FROM pg_stat_activity;” >> $LOG_DIR/daily_check_$DATE.log

# 检查慢查询
echo “检查慢查询” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT query, duration FROM pg_stat_statements ORDER BY duration DESC LIMIT 10;” >> $LOG_DIR/daily_check_$DATE.log

# 检查表空间使用情况
echo “检查表空间使用情况” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;” >> $LOG_DIR/daily_check_$DATE.log

# 检查数据库大小
echo “检查数据库大小” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;” >> $LOG_DIR/daily_check_$DATE.log

# 检查WAL归档
echo “检查WAL归档” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT * FROM pg_stat_archiver;” >> $LOG_DIR/daily_check_$DATE.log

# 检查索引使用情况
echo “检查索引使用情况” >> $LOG_DIR/daily_check_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_stat_user_tables ON pg_stat_user_indexes.relid = pg_stat_user_tables.relid ORDER BY idx_scan DESC LIMIT 10;” >> $LOG_DIR/daily_check_$DATE.log

# 结束巡检
echo “结束日常巡检: $(date)” >> $LOG_DIR/daily_check_$DATE.log

# 生成报告
cat $LOG_DIR/daily_check_$DATE.log > $REPORT_DIR/daily_check_$DATE.report

# 2. 赋予脚本执行权限
$ sudo chmod +x /usr/local/bin/postgresql_daily_check.sh

# 3. 测试脚本执行
$ /usr/local/bin/postgresql_daily_check.sh

# 4. 设置定时任务
$ sudo crontab -e
# 每天凌晨2点执行日常巡检
0 2 * * * /usr/local/bin/postgresql_daily_check.sh

# 1. 配置pg_stat_statements
$ sudo vi /postgresql/fgdata/postgresql.conf

# 启用pg_stat_statements扩展
shared_preload_libraries = ‘pg_stat_statements’

# 配置pg_stat_statements参数
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

# 2. 配置日志参数
log_min_duration_statement = 1000 # 记录执行时间超过1秒的语句
log_statement = ‘all’ # 记录所有语句
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,fgapp=%a,client=%h ‘

# 3. 配置自动清理
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 10min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

# 4. 重启PostgreSQL
$ sudo systemctl restart postgresql

# 5. 验证配置
$ psql -U fgedu -d fgedudb -c “SHOW shared_preload_libraries;”
$ psql -U fgedu -d fgedudb -c “SHOW log_min_duration_statement;”
$ psql -U fgedu -d fgedudb -c “SHOW autovacuum;”

# 1. 配置Prometheus
$ sudo vi /etc/prometheus/prometheus.yml

scrape_configs:
– job_name: ‘postgres’
static_configs:
– targets: [‘fgedu.localhost:9187’]
labels:
instance: ‘postgres’

# 2. 配置Grafana
# 登录Grafana界面：http://fgedu.localhost:3000
# 导入PostgreSQL监控面板

# 3. 配置告警规则
$ sudo vi /etc/prometheus/rules/postgresql_alerts.yml
groups:
– name: postgresql_alerts
rules:
– alert: PostgresqlDown
expr: pg_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “PostgreSQL down on {{ $labels.instance }}”
description: “PostgreSQL instance {{ $labels.instance }} has been down for 5 minutes”

– alert: HighConnectionCount
expr: pg_stat_activity_count > 100
for: 5m
labels:
severity: warning
annotations:
summary: “High connection count on {{ $labels.instance }}”
description: “Connection count is {{ $value }}”

– alert: HighTableSpaceUsage
expr: (pg_tablespace_size_bytes / pg_tablespace_size_max_bytes) * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: “High tablespace usage on {{ $labels.instance }}”
description: “Tablespace usage is {{ $value }}%”

# 4. 配置告警通知
$ sudo vi /etc/prometheus/alertmanager.yml

global:
smtp_smarthost: ‘smtp.fgedu.net.cn:587’
smtp_from: ‘alertmanager@fgedu.net.cn’
smtp_auth_username: ‘alertmanager’
smtp_auth_password: ‘password’

route:
group_by: [‘alertname’]
group_wait: 30s
group_interval: 5m
repeat_interval: 1h
receiver: ’email’

receivers:
– name: ’email’
email_configs:
– to: ‘dba@fgedu.net.cn’

# 5. 验证监控
$ curl http://fgedu.localhost:9090/targets
$ curl http://fgedu.localhost:3000