部署postgresql_exporter监控pgsql

• 部署exporter
• 配置监控job
• 配置告警规则

一键部署脚本

#!/bin/bash# 定义变量
PG_HOST="xx.ap-southeast-1.rds.amazonaws.com"
PG_PORT="5432"
PG_PASSWORD="bagayalu321"
PG_USER="monitor_user"
EXPORTER_VERSION="0.16.0"  # PostgreSQL exporter版本# 安装PostgreSQL客户端
echo "正在安装PostgreSQL客户端..."
apt-get update
apt-get install -y postgresql-client# 测试连接到PostgreSQL服务器
echo "测试PostgreSQL连接..."
PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER -c "SELECT version();"# 创建授权用户（这部分您已经完成，但为完整性保留）
cat > /tmp/create_user.sql << EOF
CREATE USER $PG_USER WITH PASSWORD '$PG_PASSWORD';
GRANT pg_monitor TO $PG_USER;
GRANT SELECT ON pg_stat_database TO $PG_USER;
EOFecho "注意：用户创建部分仅供参考，您已经完成此步骤"# 下载并安装Prometheus PostgreSQL exporter
echo "安装Prometheus PostgreSQL exporter..."# 创建用户
useradd --no-create-home --shell /bin/false postgres_exporter# 下载Prometheus PostgreSQL exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v$EXPORTER_VERSION/postgres_exporter-$EXPORTER_VERSION.linux-amd64.tar.gz
tar -xzf postgres_exporter-$EXPORTER_VERSION.linux-amd64.tar.gz
cp postgres_exporter-$EXPORTER_VERSION.linux-amd64/postgres_exporter /usr/local/bin/
chown postgres_exporter:postgres_exporter /usr/local/bin/postgres_exporter# 创建配置
mkdir -p /etc/postgres_exporter
cat > /etc/postgres_exporter/postgres_exporter.env << EOF
DATA_SOURCE_NAME="postgresql://$PG_USER:$PG_PASSWORD@$PG_HOST:$PG_PORT/postgres?sslmode=disable"
EOFchown -R postgres_exporter:postgres_exporter /etc/postgres_exporter# 创建systemd服务
cat > /etc/systemd/system/postgres_exporter.service << EOF
[Unit]
Description=Prometheus PostgreSQL Exporter
Wants=network-online.target
After=network-online.target[Service]
User=postgres_exporter
Group=postgres_exporter
Type=simple
EnvironmentFile=/etc/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter
Restart=always[Install]
WantedBy=multi-user.target
EOF# 启动服务
systemctl daemon-reload
systemctl enable postgres_exporter
systemctl start postgres_exporter
systemctl status postgres_exporter# 清理下载文件
rm -rf postgres_exporter-$EXPORTER_VERSION.linux-amd64*echo "PostgreSQL配置和PostgreSQL exporter安装完成！"
echo "Exporter端口: 9187"
echo "请确保防火墙允许访问此端口"

配置job

  - job_name: 'xx-xx-pgsql'static_configs:- targets: ['xxxx:9187']relabel_configs:- source_labels: [__address__]target_label: __param_target- source_labels: [__param_target]target_label: instancereplacement: xx-xx-pgsql-aws

配置最基础的探活告警

# pgsql_rule.yml 
groups:
- name: PostgreSQLStatsAlertrules:# PostgreSQL 实例宕机- alert: PostgreSQL database is downexpr: pg_up == 0for: 1mlabels:severity: criticalservice: postgresqlannotations:description: "Instance {{ $labels.instance }} PostgreSQL is down"summary: "PostgreSQL database is down. This requires immediate action!"