部署postgresql_exporter监控pgsql

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

一键部署脚本

#!/bin/bash # 定义变量 PG_HOST="xx.ap-southeast-1.rds.amazonaws " 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; EOF echo "注意：用户创建部分仅供参考，您已经完成此步骤" # 下载并安装Prometheus PostgreSQL exporter echo "安装Prometheus PostgreSQL exporter..." # 创建用户 useradd --no-create-home --shell /bin/false postgres_exporter # 下载Prometheus PostgreSQL exporter wget github /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" EOF chown -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: instance replacement: xx-xx-pgsql-aws

配置最基础的探活告警

# pgsql_rule.yml groups: - name: PostgreSQLStatsAlert rules: # PostgreSQL 实例宕机 - alert: PostgreSQL database is down expr: pg_up == 0 for: 1m labels: severity: critical service: postgresql annotations: description: "Instance {{ $labels.instance }} PostgreSQL is down" summary: "PostgreSQL database is down. This requires immediate action!"