从卡顿到丝滑：PostgreSQL性能诊断神器pg_activity全面实战指南
项目地址: https://gitcode.com/gh_mirrors/pg/pg_activity 引言：你还在为PostgreSQL性能问题焦头烂额吗？
作为数据库管理员或开发人员，你是否曾经历过这些场景：
- 生产环境数据库突然卡顿，大量查询堆积却找不到瓶颈
- 服务器CPU/内存使用率飙升，但无法定位具体是哪些SQL导致
- 面对阻塞查询，只能手动执行
pg_stat_activity查询和分析 - 尝试优化数据库性能，却缺乏直观的实时监控工具
如果你正被这些问题困扰，本文将彻底改变你的PostgreSQL性能诊断方式。pg_activity——这款被业界称为"PostgreSQL的htop"的开源工具，能让你实时可视化数据库活动，精准定位性能瓶颈，轻松解决各类数据库性能问题。
读完本文后，你将掌握：
- pg_activity的安装配置与基础使用方法
- 高级监控技巧：筛选、排序与自定义视图
- 交互式操作与问题诊断流程
- 生产环境最佳实践与性能优化建议
- 常见故障排查与解决方案
什么是pg_activity？
pg_activity是一款专为PostgreSQL设计的命令行活动监控工具，采用类htop的交互式界面，实时展示数据库连接、查询执行、资源占用等关键指标。作为Dalibo公司的开源项目，它已成为PostgreSQL DBA的必备工具之一。
核心功能亮点
| 功能 | 描述 | 价值 |
|---|---|---|
| 实时活动监控 | 展示所有数据库连接及其执行的SQL语句 | 快速定位长事务和资源密集型查询 |
| 多维度性能指标 | CPU、内存、I/O等系统资源使用情况 | 全面了解数据库服务器负载状态 |
| 阻塞查询可视化 | 直观显示查询之间的阻塞关系 | 快速解决锁竞争问题 |
| 自定义视图配置 | 支持按需求显示/隐藏列、调整布局 | 适配不同监控场景和屏幕尺寸 |
| 交互式操作 | 支持排序、筛选、进程终止等操作 | 提高故障处理效率 |
| 跨版本兼容 | 支持PostgreSQL 9.2及以上版本 | 适配各类生产环境 |
工作原理
pg_activity通过查询PostgreSQL系统视图（主要是pg_stat_activity）获取实时活动数据，并结合操作系统级别的资源监控，提供全方位的数据库性能视图。其数据采集流程如下：
根据PostgreSQL版本的不同，pg_activity会自动选择对应的SQL查询模板。例如，对于PostgreSQL 13及以上版本，它使用get_pg_activity_post_130000.sql查询文件，该文件包含如下核心SQL片段：
SELECT
a.pid AS pid,
a.backend_xmin AS xmin,
a.application_name AS application_name,
a.datname AS database,
a.client_addr AS client,
EXTRACT(epoch FROM (NOW() - a.{duration_column})) AS duration,
a.wait_event AS wait,
a.usename AS user,
a.state AS state,
a.query AS query,
pg_catalog.pg_encoding_to_char(b.encoding) AS encoding,
coalesce(a.leader_pid, a.pid) AS query_leader_pid,
a.backend_type = 'parallel worker' AS is_parallel_worker
FROM
pg_stat_activity a
LEFT OUTER JOIN pg_database b ON a.datid = b.oid
WHERE
a.state <> 'idle'
AND a.pid <> pg_catalog.pg_backend_pid()
-- 更多条件...
ORDER BY
EXTRACT(epoch FROM (NOW() - a.{duration_column})) DESC;
而对于旧版本（如9.2之前），则使用get_pg_activity_oldest.sql，适配不同版本的系统视图结构差异。
安装与配置
支持环境
pg_activity支持以下环境：
- 操作系统：Linux或类Unix系统（不支持Windows）
- Python版本：3.8及以上
- PostgreSQL版本：9.2及以上
- 依赖库：psycopg2/psycopg、blessed、psutil等
安装方法
1. 系统包管理器（推荐）
对于Debian/Ubuntu系统：
sudo apt install pg-activity
对于RHEL/CentOS系统（需配置PGDG仓库）：
sudo yum install pg_activity
2. 使用PyPI安装
# 基础安装
pip install pg_activity
# 带psycopg依赖安装（推荐）
pip install "pg_activity[psycopg]"
# 使用pipx安装（隔离环境）
pipx install "pg_activity[psycopg]"
3. 从源码安装
# 克隆仓库
git clone https://gitcode.com/gh_mirrors/pg/pg_activity
cd pg_activity
# 创建虚拟环境
python3 -m venv .venv
source .venv/bin/activate
# 安装依赖与程序
pip install ".[dev]"
验证安装
pg_activity --version
# 输出示例：pg_activity 3.6.1
配置文件
pg_activity支持通过配置文件自定义界面显示。配置文件可以放在以下位置：
- 系统级：
/etc/pg_activity.conf或/etc/pg_activity/<profile>.conf - 用户级：
~/.config/pg_activity.conf或~/.config/pg_activity/<profile>.conf
项目内置了三个配置模板：
minimal.conf：极简模式，隐藏大部分头部信息和列narrow.conf：窄屏模式，仅显示关键列wide.conf：宽屏模式，显示所有可用列
例如，minimal.conf的内容如下：
[header]
show_instance = no
show_system = no
show_workers = no
[xmin]
hidden = yes
[database]
hidden = yes
[user]
hidden = yes
[client]
hidden = yes
[cpu]
hidden = yes
[mem]
hidden = yes
[read]
hidden = yes
[write]
hidden = yes
[appname]
hidden = yes
可以通过--profile参数指定使用的配置文件：
pg_activity --profile minimal
基础使用指南
连接数据库
pg_activity支持多种连接方式，最常用的包括：
- 使用连接字符串
pg_activity "host=localhost port=5432 user=postgres dbname=mydb"
- 使用命令行参数
pg_activity -h localhost -p 5432 -U postgres -d mydb
- 使用环境变量
export PGHOME=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
pg_activity
注意：连接数据库的用户需要适当权限，建议使用超级用户或具有
pg_monitor角色的用户，以获取完整的性能数据。
基本界面介绍
成功连接后，pg_activity将显示如下界面布局：
┌─────────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL 13.7 on localhost - postgres@localhost:5432/postgres Ref.: 2s │
│ * Global: 2 days, 12:34:56 uptime │
│ 1.2GiB dbs size - 0B/s growth │
│ 99.8% cache hit ratio - 0.0% rollback ratio │
│ Sessions: 15/100 total - 2 active - 13 idle - 0 idle in txn - 0 idle in txn abrt
│ Activity: 0 tps - 0 insert/s - 0 update/s - 0 delete/s - 0 tuples returned/s
│ Worker processes: 3/8 total - 0/4 logical workers - 0/4 parallel workers │
│ Other processes & info: 0/3 autovacuum workers - 0/10 wal senders - 0 wal receivers - 0/10 repl. slots
│ PAUSE │
├─────┬─────────┬────────┬──────────┬──────┬──────┬────────┬────────┬────────┬───────┬─────────┤
│ PID │ USER │ DATABASE│ CLIENT │ CPU% │ MEM% │ READ/s │ WRITE/s│ TIME+ │ W │ QUERY │
├─────┼─────────┼────────┼──────────┼──────┼──────┼────────┼────────┼────────┼───────┼─────────┤
│ 123 │ postgres│ postgres│ 127.0.0.1│ 0.0 │ 0.1 │ 0B │ 0B │ 00:00 │ │ SELECT *│
│ │ │ │ │ │ │ │ │ │ │ FROM ta │
└─────┴─────────┴────────┴──────────┴──────┴──────┴────────┴────────┴────────┴───────┴─────────┘
F1/1 Running F2/2 Waiting F3/3 Blocking Space Pause q Quit h Help
界面主要分为四部分：
- 头部信息区：显示数据库实例信息、系统资源使用、会话统计等
- 模式标题区：显示当前模式（运行中/等待中/阻塞中）或暂停状态
- 进程表格区：显示数据库连接/查询详情，各列可自定义
- 底部状态栏：显示功能键提示
常用命令行选项
# 基本连接
pg_activity -U postgres -d mydb -h dbserver -p 5432
# 使用RDS模式（隐藏rdsadmin数据库，禁用临时文件统计）
pg_activity --rds -U rds_superuser -h mydb.xxxx.us-east-1.rds.amazonaws.com
# 设置刷新间隔为1秒
pg_activity --refresh 1
# 仅显示持续时间超过5秒的查询
pg_activity --min-duration 5
# 按数据库名筛选
pg_activity --filter dbname:mydb
# 使用narrow配置文件
pg_activity --profile narrow
# 将查询输出到CSV文件
pg_activity --output queries.csv
# 禁用临时文件统计
pg_activity --no-tempfiles
核心功能详解
视图模式
pg_activity提供三种主要视图模式，可通过功能键切换：
- 运行中查询模式（F1/1）：默认模式，显示所有活跃查询
- 等待中查询模式（F2/2）：仅显示处于等待状态的查询
- 阻塞查询模式（F3/3）：显示阻塞其他查询的会话
模式切换示例：
# 在界面中按F1或1键切换到运行中查询模式
# 按F2或2键切换到等待中查询模式
# 按F3或3键切换到阻塞查询模式
交互式操作
pg_activity提供丰富的键盘交互功能：
导航与排序
- ↑/↓ 或 k/j：上下滚动进程列表
- PageUp/PageDown：翻页滚动
- Home/End：跳转到首尾
- r：按READ/s降序排序
- w：按WRITE/s降序排序
- c：按CPU%降序排序
- m：按MEM%降序排序
- t：按TIME+降序排序
查询控制
- Space：暂停/恢复刷新
- v：切换查询显示模式（完整/缩进/截断）
- y：复制当前选中查询到剪贴板（需终端支持OSC 52）
- +/-：增加/减少刷新时间（0.5-5秒）
进程管理（需权限）
- K：终止当前选中的后端进程
- C：取消当前选中的查询
- Space：标记/取消标记进程（可批量操作）
显示控制
- s：切换显示系统信息
- i：切换显示实例信息
- o：切换显示工作进程信息
- D：强制刷新数据库大小
自定义显示
1. 使用配置文件自定义
创建~/.config/pg_activity.conf文件，示例配置：
[header]
show_instance = yes
show_system = yes
show_workers = no
[client]
hidden = no
width = 15
[database]
hidden = no
width = 12
[cpu]
color = red
[mem]
color = yellow
2. 使用命令行选项控制列显示
# 隐藏CPU和内存列
pg_activity --no-cpu --no-mem
# 显示XMIN列，隐藏应用名列
pg_activity --xmin --no-app-name
# 启用查询换行显示
pg_activity -w
3. 使用配置文件切换显示模式
# 使用最小化配置
pg_activity --profile minimal
# 使用宽屏配置
pg_activity --profile wide
高级筛选与过滤
按数据库名筛选
# 仅显示mydb数据库的活动
pg_activity --filter dbname:mydb
# 使用正则表达式筛选
pg_activity --filter dbname:'mydb|testdb'
按查询持续时间筛选
# 仅显示持续时间超过10秒的查询
pg_activity --min-duration 10
持续时间模式切换
# 按查询持续时间（默认）
pg_activity --duration-mode 1
# 按事务持续时间
pg_activity --duration-mode 2
# 按后端连接持续时间
pg_activity --duration-mode 3
# 在界面中按T键切换模式
实战场景
场景1：快速定位资源占用最高的查询
步骤：
- 启动pg_activity：
pg_activity -U postgres - 按c键按CPU使用率排序，或按m键按内存使用率排序
- 找到资源占用最高的查询，观察其PID、数据库名和客户端信息
- 按Enter进入详细视图，查看完整查询文本
- 如需终止，按K键并确认
示例输出（按CPU排序）：
├─────┬─────────┬────────┬──────────┬──────┬──────┬────────┬────────┬────────┬───────┬─────────┤
│ PID │ USER │ DATABASE│ CLIENT │ CPU% │ MEM% │ READ/s │ WRITE/s│ TIME+ │ W │ QUERY │
├─────┼─────────┼────────┼──────────┼──────┼──────┼────────┼────────┼────────┼───────┼─────────┤
│ 456 │ appuser │ mydb │ 10.0.0.5 │ 99.8 │ 5.2 │ 0B │ 128KB │ 05:32 │ │ UPDATE │
│ │ │ │ │ │ │ │ │ │ │ users │
│ │ │ │ │ │ │ │ │ │ │ SET age=│
└─────┴─────────┴────────┴──────────┴──────┴──────┴────────┴────────┴────────┴───────┴─────────┘
场景2：诊断查询阻塞问题
步骤：
- 启动pg_activity并按F3或3进入阻塞查询模式
- 查看阻塞关系：BLOCKER列显示阻塞者PID，BLOCKED列显示被阻塞者数量
- 分析阻塞者的查询内容，判断是否为长事务或锁竞争
- 选择阻塞者PID，按C取消查询或K终止后端
阻塞模式界面示例：
┌─────────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL 13.7 on localhost - postgres@localhost:5432/postgres Ref.: 2s │
│ ... │
│ BLOCKING QUERIES │
├─────────┬─────────┬────────┬──────────┬────────┬────────┬────────┬──────────┤
│ BLOCKER │ USER │ DATABASE│ CLIENT │ TIME+ │ BLOCKED│ WAIT │ QUERY │
├─────────┼─────────┼────────┼──────────┼────────┼────────┼────────┼──────────┤
│ 789 │ appuser │ mydb │ 10.0.0.6 │ 12:45 │ 3 │ │ BEGIN; │
│ │ │ │ │ │ │ │ SELECT *│
│ │ │ │ │ │ │ │ FROM ord│
└─────────┴─────────┴────────┴──────────┴────────┴────────┴────────┴──────────┘
F1/1 Running F2/2 Waiting F3/3 Blocking Space Pause q Quit h Help
场景3：监控AWS RDS实例
AWS RDS环境需要特殊配置，因为部分系统视图和功能受限：
# RDS专用模式
pg_activity --rds -U myuser -h mydb.xxxx.us-east-1.rds.amazonaws.com
# 禁用不必要的统计项（RDS不支持）
pg_activity --rds --no-tempfiles --no-walreceiver
--rds选项会自动：
- 禁用临时文件统计（RDS不允许访问相关目录）
- 过滤掉rdsadmin数据库（避免权限问题）
- 调整部分查询以适配RDS的权限限制
场景4：导出查询数据用于分析
# 将查询导出到CSV文件
pg_activity --output /tmp/pg_queries.csv
# 结合筛选条件导出特定查询
pg_activity --filter dbname:mydb --min-duration 5 --output /tmp/slow_queries.csv
CSV文件格式示例：
"timestamp","pid","database","user","client","duration","query"
"2025-09-06 10:15:30",456,"mydb","appuser","10.0.0.5",325,"UPDATE users SET age=age+1 WHERE id=123"
场景5：自定义监控面板
为不同场景创建自定义配置文件，例如：
- 创建慢查询监控配置：
~/.config/pg_activity/slow_queries.conf
[header]
show_instance = yes
show_system = no
show_workers = no
[duration]
min_width = 10
[query]
wrap = yes
[cpu]
hidden = yes
[mem]
hidden = yes
- 使用自定义配置：
pg_activity --profile slow_queries --min-duration 10
性能优化建议
pg_activity自身优化
- 调整刷新间隔：对于高负载服务器，增大刷新间隔（如5秒）
pg_activity --refresh 5
- 使用筛选减少数据量：只监控关注的数据库
pg_activity --filter dbname:production
- 精简显示列：减少不必要的列，降低CPU占用
pg_activity --profile minimal
PostgreSQL配置优化
为获得更准确的监控数据，建议调整以下PostgreSQL参数：
- 增加查询文本长度限制：
ALTER SYSTEM SET track_activity_query_size = '4096';
-- 需重启PostgreSQL生效
- 启用等待事件监控（9.6+）：
ALTER SYSTEM SET track_wait_events = on;
-- 需重启PostgreSQL生效
- 增加统计采样频率：
ALTER SYSTEM SET stats_fetch_interval = 100;
-- 需重启PostgreSQL生效
常见问题与解决方案
问题1：查询显示不完整或被截断
原因：track_activity_query_size参数限制了存储的查询文本长度。
解决方案：
-- 临时调整（立即生效）
SET track_activity_query_size = 4096;
-- 永久调整（需重启）
ALTER SYSTEM SET track_activity_query_size = 4096;
问题2：无法看到系统资源使用情况
原因：运行pg_activity的用户权限不足，无法读取PostgreSQL进程信息。
解决方案：
- 使用postgres用户运行pg_activity：
sudo -u postgres pg_activity
- 或授予适当权限给监控用户。
问题3：连接失败，提示权限不足
解决方案：
- 确保数据库用户具有足够权限：
GRANT pg_monitor TO monitoring_user;
- 使用正确的连接参数：
pg_activity -U monitoring_user -h dbhost -d postgres
问题4：界面乱码或显示异常
解决方案：
- 确保终端支持UTF-8编码
- 更新blessed库到最新版本：
pip install --upgrade blessed
- 尝试不同的终端模拟器（推荐Alacritty、Kitty或GNOME Terminal）
问题5：无法终止查询或进程
原因：pg_activity用户缺乏pg_terminate_backend权限。
解决方案：
GRANT pg_signal_backend TO monitoring_user;
高级技巧与最佳实践
结合其他工具使用
- 与pg_stat_statements配合：识别历史慢查询
# 先安装pg_stat_statements扩展
# 然后结合pg_activity实时监控和pg_stat_statements历史分析
- 与tmux分屏使用：同时监控多个实例
# 在tmux中水平分屏
tmux split-window -h
# 左侧监控主库
pg_activity -h primary
# 右侧监控备库
pg_activity -h standby
- 与日志分析工具结合：
# 实时监控并记录慢查询
pg_activity --output slow_queries.csv &
# 同时分析PostgreSQL日志
tail -f /var/log/postgresql/postgresql-13-main.log | grep -i 'duration:'
自动化监控脚本
创建简单的监控脚本monitor.sh：
#!/bin/bash
OUTPUT_DIR="/var/log/pg_activity"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mkdir -p $OUTPUT_DIR
# 监控30分钟，每5秒刷新一次
pg_activity --refresh 5 --output $OUTPUT_DIR/queries_$TIMESTAMP.csv --duration-mode 2
# 可选：发送邮件通知（需配置mail）
if [ $(wc -l < $OUTPUT_DIR/queries_$TIMESTAMP.csv) -gt 100 ]; then
echo "发现大量慢查询，请查看附件" | mail -a $OUTPUT_DIR/queries_$TIMESTAMP.csv -s "PostgreSQL慢查询告警" admin@example.com
fi
设置定时任务：
# 每天凌晨2点运行
0 2 * * * /path/to/monitor.sh
生产环境最佳实践
- 限制监控用户权限：仅授予必要权限，如
pg_monitor和pg_signal_backend - 避免在高负载时段使用高频率刷新：防止监控本身影响性能
- 定期备份配置文件：确保自定义配置不会丢失
- 监控pg_activity自身资源占用：避免监控工具成为性能瓶颈
- 结合自动化告警：将pg_activity数据与Prometheus+Grafana等结合，实现持续监控
总结与展望
pg_activity作为一款轻量级但功能强大的PostgreSQL监控工具，通过直观的界面和丰富的交互功能，帮助数据库管理员快速定位和解决性能问题。其主要优势包括：
- 实时性：低延迟展示数据库活动状态
- 易用性：命令行界面，无需复杂配置即可上手
- 灵活性：高度可定制的显示选项，适应不同场景
- 轻量级：资源占用低，对生产环境影响小
未来使用建议：
- 关注项目更新，及时获取新功能和bug修复
- 参与社区贡献，提交bug报告或功能建议
- 结合其他工具构建完整的监控体系
- 定期回顾和优化监控策略，适应业务变化
通过熟练掌握pg_activity，你将能够显著提升PostgreSQL性能问题的诊断效率，确保数据库系统稳定高效运行。
附录：快捷键速查表
| 快捷键 | 功能描述 |
|---|---|
| F1/1 | 切换到运行中查询模式 |
| F2/2 | 切换到等待中查询模式 |
| F3/3 | 切换到阻塞查询模式 |
| ↑/↓ 或 k/j | 上下滚动进程列表 |
| PageUp/PageDown | 翻页滚动 |
| Home/End | 跳转到列表首尾 |
| r | 按READ/s降序排序 |
| w | 按WRITE/s降序排序 |
| c | 按CPU%降序排序 |
| m | 按MEM%降序排序 |
| t | 按TIME+降序排序 |
| Space | 暂停/恢复刷新或标记进程 |
| v | 切换查询显示模式 |
| y | 复制当前查询到剪贴板 |
| + | 增加刷新间隔 |
| - | 减少刷新间隔 |
| K | 终止选中进程 |
| C | 取消选中查询 |
| s | 切换显示系统信息 |
| i | 切换显示实例信息 |
| o | 切换显示工作进程信息 |
| D | 强制刷新数据库大小 |
| h | 显示帮助页面 |
| q | 退出pg_activity |
希望本文能帮助你充分利用pg_activity提升PostgreSQL管理效率。如有任何问题或建议，欢迎在项目仓库提交issue或PR。
请点赞、收藏并关注，获取更多PostgreSQL性能优化技巧！
创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
