centos postgresql监控方法

一、内置监控工具（原生视图与命令）
CentOS上的PostgreSQL自带丰富的监控工具，无需额外安装，适合快速排查基础问题：

• pg_stat_activity：查看当前所有数据库连接的详细信息（如用户、数据库、查询状态、执行时间），是监控活跃连接的核心视图。常用查询：SELECT datname, usename, state, query, now() - query_start AS duration FROM pg_stat_activity WHERE state = 'active';（筛选活跃连接的SQL及耗时）。
• pg_stat_statements：统计SQL语句的执行次数、总耗时、平均耗时等，帮助定位慢查询。需先启用扩展（CREATE EXTENSION pg_stat_statements;），再通过查询获取TOP慢SQL：SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;。
• pg_stat_database：展示数据库级别的统计信息（如事务提交/回滚次数、缓存命中率、磁盘写入量），反映数据库整体负载。关键指标计算：SELECT datname, (blks_hit + blks_read) AS total_reads, blks_hit, (blks_hit::float / (blks_hit + blks_read)) * 100 AS cache_hit_rate FROM pg_stat_database;（缓存命中率≥98%为佳）。
• pg_stat_replication：监控流复制状态（如主备延迟、复制槽状态），确保数据一致性。常用查询：SELECT pid, usename, application_name, client_addr, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;（lag为延迟时间，单位毫秒）。
• 系统命令辅助：systemctl status postgresql（检查服务运行状态）、pg_isready -U postgres（测试连接是否正常）、psql -U postgres -c "SELECT 1;"（验证数据库响应）。

二、日志分析工具
通过分析PostgreSQL日志，可深入了解错误、慢查询及系统行为：

• pgBadger：开源日志分析工具，将PostgreSQL日志（需开启log_statement、log_min_duration_statement）解析为HTML报告，展示慢查询、错误日志、查询频率等信息。安装后运行pgbadger /var/log/postgresql/postgresql-*.log即可生成报告。
• ELK Stack（Elasticsearch+Logstash+Kibana）：收集、存储、可视化PostgreSQL日志，适合大规模环境。通过Logstash配置解析日志格式，Kibana创建 dashboard 实现日志检索与趋势分析。

三、第三方监控工具
针对企业级需求，可选择功能全面的第三方工具，实现实时监控、告警与可视化：

• Prometheus+Grafana：
  • Prometheus：开源时间序列数据库，通过postgres_exporter（PostgreSQL专用exporter）采集指标（如TPS、QPS、缓存命中率、连接数）；
  • Grafana：可视化工具，配置PostgreSQL监控 dashboard（如官方提供的“PostgreSQL Overview”），支持设置告警规则（如连接数超过阈值、复制延迟超过1秒）。安装步骤：先部署Prometheus，再添加postgres_exporter作为target，最后配置Grafana dashboard。
• Zabbix：企业级开源监控平台，支持PostgreSQL监控（通过UserParameter或专用模板）。可监控数据库性能（如TPS、慢查询）、服务器资源（CPU、内存、磁盘）、复制状态等，支持自动发现、告警（邮件、短信）。
• pgAdmin：流行的开源管理工具，提供图形化监控界面（如“Dashboard”“Statistics” tab），支持查看连接数、查询性能、表空间使用率等，适合日常管理。
• Netdata：实时性能监控工具，安装后自动生成PostgreSQL监控 dashboard（如CPU使用率、内存占用、磁盘I/O、查询速率），无需复杂配置，适合快速查看系统状态。

四、自定义脚本与告警
通过编写脚本定期检查关键指标，结合邮件、钉钉等工具发送告警，适合定制化需求：

• 示例脚本（检查空闲连接）：

  #!/bin/bash
  IDLE_CONNS=$(psql -U postgres -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle';")
  THRESHOLD=50  # 阈值
  if [ "$IDLE_CONNS" -gt "$THRESHOLD" ]; then
    echo "警告：PostgreSQL空闲连接数超过阈值！当前值：$IDLE_CONNS" | mail -s "PostgreSQL告警" admin@example.com
  fi
  

  将脚本添加到cron（如每5分钟运行一次），即可实现空闲连接数的自动监控与告警。