如何在Ubuntu上监控PostgreSQL

在Ubuntu上监控PostgreSQL的综合方法

一、使用系统自带工具实时监控

Ubuntu的系统工具可直接查看PostgreSQL进程的资源占用情况，适合快速定位系统级瓶颈：

• htop：安装后运行htop，通过F3搜索“postgres”进程，实时查看CPU、内存、线程等占用情况，支持排序和过滤。
• vmstat：运行vmstat 1（间隔1秒刷新），查看系统整体性能（如CPU使用率、内存交换、磁盘IO），重点关注r（运行队列长度）、b（阻塞进程数）、wa（IO等待）等指标。
• iostat：安装sysstat后运行iostat -x 1，查看磁盘IO详情（如%util磁盘利用率、await平均响应时间），识别磁盘瓶颈。

二、通过PostgreSQL内置视图监控

PostgreSQL自带的系统视图提供了详细的性能指标，适合深入分析数据库内部状态：

• pg_stat_activity：查看当前所有连接的活动状态（如查询内容、执行时间、状态）。常用查询：SELECT * FROM pg_stat_activity WHERE state = 'active';（筛选活跃查询）。
• pg_stat_statements：需先启用扩展（CREATE EXTENSION pg_stat_statements;），记录查询的执行次数、总时间、平均时间等。查询示例：SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;（找出最耗时的查询）。
• pg_stat_database：查看每个数据库的统计信息（如连接数、事务数、磁盘读写量）。查询示例：SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;。
• pg_stat_user_tables：查看用户表的访问情况（如扫描次数、插入/更新/删除次数）。查询示例：SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd FROM pg_stat_user_tables;。

三、使用图形化工具（pgAdmin）

pgAdmin是PostgreSQL的官方管理工具，提供直观的图形化监控界面：

1. 安装pgAdmin：运行sudo apt update && sudo apt install pgadmin4，按提示完成安装。
2. 配置PostgreSQL：确保PostgreSQL允许远程连接（修改pg_hba.conf，添加host all all 0.0.0.0/0 md5），并设置用户密码（ALTER USER postgres WITH PASSWORD 'your_password';）。
3. 连接数据库：启动pgAdmin，在左侧“Servers”中右键选择“Create > Server”，填写连接信息（主机localhost、端口5432、用户名postgres、密码），点击“Save”。
4. 监控性能：连接成功后，在左侧对象浏览器中展开服务器→数据库→表，右键选择“Statistics”查看表的访问统计；或通过“Tools > Query Tool”执行SQL查询（如上述pg_stat_statements查询）获取性能数据。

四、使用专业监控解决方案（Zabbix）

Zabbix是企业级开源监控工具，可实现PostgreSQL的全面监控（包括性能、可用性、告警）：

1. 安装Zabbix Agent2：运行wget https://repo.zabbix.com/zabbix/7.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_latest_7.0+ubuntu22.04_all.deb下载仓库包，dpkg -i安装，然后apt update && apt install zabbix-agent2。
2. 安装PostgreSQL监控插件：运行apt install zabbix-agent2-plugin-postgresql，插件会自动部署到/usr/lib/zabbix/agent2/plugins/。
3. 配置PostgreSQL权限：创建监控用户（CREATE USER zbx_monitor WITH PASSWORD 'zbx_monitor' INHERIT;），授予权限（GRANT pg_monitor TO zbx_monitor;），修改pg_hba.conf允许连接（host all zbx_monitor 127.0.0.1/32 trust），重启PostgreSQL。
4. 配置Zabbix：在Zabbix前端添加主机（填写PostgreSQL服务器IP），关联“PostgreSQL by Zabbix agent2”模板，修改模板宏（如{$PG.USER}设为zbx_monitor、{$PG.PASSWORD}设为zbx_monitor），即可自动采集性能指标（如查询时间、连接数、缓存命中率）。

五、使用终端专用工具（pg_activity）

pg_activity是类似top的终端监控工具，实时显示PostgreSQL的活动状态：

1. 安装：运行sudo apt install pg-activity（Ubuntu 22.04及以上可通过bullseye-backports安装）。
2. 使用：运行pg_activity，界面显示当前所有PostgreSQL进程，包括：
   • 查询状态（active/idle/waiting）
   • 执行时间（total/mean）
   • 锁情况（waiting for lock）
   • 资源占用（CPU、内存） 支持快捷键（如F3搜索、q退出），帮助快速定位长时间运行的查询或锁冲突。