# 1. 部署Prometheus+Grafana
– 安装PostgreSQL Exporter
– 配置Prometheus
– 配置Grafana
– 导入PostgreSQL仪表盘

# 2. 配置告警规则
$ 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”

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

# 4. 定期检查系统状态
$ vi /postgresql/scripts/check_status.sh

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

# 检查数据库状态
pg_isready -h fgedu.localhost -p 5432

# 检查连接数
psql -U postgres -c “SELECT count(*) FROM pg_stat_activity;”

# 检查表空间使用情况
psql -U postgres -c “SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size FROM pg_tablespace;”

# 检查复制状态
psql -U postgres -c “SELECT * FROM pg_stat_replication;”

# 检查慢查询
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;”

# 5. 配置定时任务
$ crontab -e

# 每小时执行一次状态检查
0 * * * * /postgresql/scripts/check_status.sh

# 1. 故障响应流程
– 接收告警：接收监控系统的告警
– 确认故障：确认故障是否真实存在
– 诊断故障：分析故障原因
– 处理故障：采取故障处理措施
– 验证恢复：验证系统是否恢复正常
– 记录故障：记录故障处理过程

# 2. 常见故障处理方法

## 数据库无法启动
– 检查日志：查看数据库日志，了解启动失败原因
– 检查配置：检查postgresql.conf和pg_hba.conf配置
– 检查权限：检查数据目录权限
– 检查磁盘空间：检查磁盘空间是否充足
– 检查内存：检查内存是否充足

## 数据库性能下降
– 分析慢查询：使用pg_stat_statements分析慢查询
– 检查系统资源：检查CPU、内存、磁盘I/O使用情况
– 检查连接数：检查数据库连接数
– 检查锁：检查数据库锁情况
– 优化SQL：优化慢查询SQL

## 数据库连接故障
– 检查网络：检查网络连接是否正常
– 检查防火墙：检查防火墙设置
– 检查pg_hba.conf：检查认证配置
– 检查连接数：检查最大连接数设置
– 检查PostgreSQL服务：检查PostgreSQL服务是否正常运行

## 数据损坏故障
– 检查备份：使用备份恢复数据
– 使用pg_resetxlog：修复WAL日志
– 使用pg_dump：导出可恢复的数据
– 重建数据库：重建数据库并恢复数据

# 3. 故障处理文档
$ vi /postgresql/docs/troubleshooting.md

# PostgreSQL故障处理文档

## 故障类型
– 启动故障
– 性能故障
– 连接故障
– 数据故障
– 复制故障

## 故障处理流程
1. 接收告警
2. 确认故障
3. 诊断故障
4. 处理故障
5. 验证恢复
6. 记录故障

## 故障处理方法
– 数据库无法启动：检查日志、配置、权限、磁盘空间、内存
– 数据库性能下降：分析慢查询、检查系统资源、检查连接数、检查锁、优化SQL
– 数据库连接故障：检查网络、防火墙、pg_hba.conf、连接数、PostgreSQL服务
– 数据损坏故障：检查备份、使用pg_resetxlog、使用pg_dump、重建数据库

# 1. 安装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

# 2. 配置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

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

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

global:
scrape_interval: 15s
evaluation_interval: 15s

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

# 5. 启动Prometheus
$ systemctl start prometheus
$ systemctl enable prometheus

# 6. 配置Grafana
# 访问 http://fgedu.localhost:3000
# 添加Prometheus数据源
# 导入PostgreSQL仪表盘

# 1. 配置定期备份
$ vi /postgresql/scripts/backup.sh

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

# 定义变量
DATE=$(date +”%Y-%m-%d”)
BACKUP_DIR=”/postgresql/backup”
BACKUP_FILE=”${BACKUP_DIR}/fgedudb_${DATE}.backup”

# 创建备份目录
mkdir -p ${BACKUP_DIR}

# 执行备份
pg_dump -U postgres -d fgedudb -F c -f ${BACKUP_FILE}

# 清理过期备份
find ${BACKUP_DIR} -name “*.backup” -mtime +7 -delete

# 2. 配置定时任务
$ crontab -e

# 每天凌晨2点执行备份
0 2 * * * /postgresql/scripts/backup.sh

# 3. 恢复演练
$ vi /postgresql/scripts/recovery_test.sh

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

# 定义变量
DATE=$(date +”%Y-%m-%d”)
BACKUP_DIR=”/postgresql/backup”
LATEST_BACKUP=$(ls -t ${BACKUP_DIR}/*.backup | head -1)
TEST_DB=”fgedudb_test”

# 创建测试数据库
psql -U postgres -c “CREATE DATABASE ${TEST_DB};”

# 恢复数据
pg_restore -U postgres -d ${TEST_DB} ${LATEST_BACKUP}

# 验证数据
psql -U postgres -d ${TEST_DB} -c “SELECT count(*) FROM fgedu_users;”

# 删除测试数据库
psql -U postgres -c “DROP DATABASE ${TEST_DB};”

# 4. 配置定时任务
$ crontab -e

# 每周日凌晨3点执行恢复演练
0 3 * * 0 /postgresql/scripts/recovery_test.sh