PostgreSQL教程FG077-PG服务监控：基础命令（pg_stat*）使用

本文档风哥主要介绍PostgreSQL的服务监控，重点介绍基础命令（pg_stat*）的使用，包括系统视图的查询和分析，以及生产环境中的实战案例和最佳实践。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

from oracle:www.itpux.com

Part01-基础概念与理论知识

1.1 PostgreSQL pg_stat*视图的概念

PostgreSQL pg_stat*视图是一组系统视图，用于提供数据库运行状态的统计信息。这些视图包含了数据库的各种运行指标，如连接数、查询执行情况、表和索引的使用情况等。更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

PostgreSQL pg_stat*视图的特点：

提供实时的数据库运行状态
包含多种监控指标
可以通过SQL查询获取信息
是数据库监控的重要工具
部分视图需要超级用户权限

1.2 PostgreSQL监控指标

PostgreSQL监控指标主要包括以下几类：

# 连接指标
– 活跃连接数
– 空闲连接数
– 连接来源
– 连接状态

# 数据库指标
– 数据库大小
– 事务数量
– 提交和回滚数量
– 缓存命中率
– 后台写入

# 表指标
– 表大小
– 扫描次数
– 插入/更新/删除次数
– 索引使用情况
– 真空操作情况

# 索引指标
– 索引大小
– 索引扫描次数
– 索引命中率
– 索引使用效率

# 系统指标
– CPU使用率
– 内存使用率
– 磁盘I/O
– 网络流量

1.3 PostgreSQL监控级别

PostgreSQL监控可以分为以下几个级别：

PostgreSQL监控级别：

基础监控：连接数、数据库状态、基本性能指标
中级监控：表和索引使用情况、查询性能、资源使用
高级监控：详细的性能分析、执行计划、锁情况
专家级监控：内核级别的监控、详细的统计信息

Part02-生产环境规划与建议

2.1 PostgreSQL监控规划

PostgreSQL监控规划要点：

# 监控目标
– 确保数据库服务正常运行
– 及时发现和解决性能问题
– 预测资源需求
– 优化数据库性能
– 确保数据安全

# 监控范围
– 数据库实例
– 数据库对象（表、索引等）
– SQL查询
– 系统资源
– 网络连接

# 监控工具
– 内置pg_stat*视图
– 第三方监控工具
– 自定义监控脚本
– 监控系统（Prometheus、Zabbix等）

# 监控告警
– 设置合理的告警阈值
– 配置告警通知方式
– 建立告警处理流程

2.2 监控指标选择

监控指标选择要点：

# 核心监控指标
– 连接数：活跃连接数、最大连接数
– 数据库大小：总大小、增长趋势
– 查询性能：慢查询、查询执行时间
– 资源使用：CPU、内存、磁盘I/O
– 缓存命中率：shared_buffers命中率
– 真空操作：自动真空执行情况

# 扩展监控指标
– 索引使用情况：索引扫描次数、命中率
– 表访问情况：扫描次数、修改次数
– 锁情况：锁等待、死锁
– WAL写入：WAL生成量、写入速度
– 后台进程：进程状态、资源使用

# 业务相关指标
– 特定表的访问频率
– 关键查询的执行时间
– 业务操作的响应时间
– 数据变更频率

2.3 监控频率建议

监控频率建议：

# 实时监控（1-5分钟）
– 连接数
– 活跃查询
– 系统资源使用
– 关键性能指标

# 定期监控（15-30分钟）
– 数据库大小
– 表和索引使用情况
– 缓存命中率
– 真空操作情况

# 每日监控
– 慢查询分析
– 数据库增长趋势
– 性能报告
– 备份状态

# 每周监控
– 全面性能评估
– 索引优化建议
– 存储空间规划
– 安全审计

风哥提示：合理的监控规划对于数据库的稳定运行和性能优化至关重要，需要根据业务需求和系统特点进行详细规划。

Part03-生产环境项目实施方案

3.1 pg_stat_activity视图使用

3.1.1 查看连接状态

# 以超级用户登录
$ psql -U pgsql

# 查看所有连接
postgres=# SELECT * FROM pg_stat_activity;

# 查看活跃连接
postgres=# SELECT
postgres-# pid,
postgres-# usename,
postgres-# datname,
postgres-# fgapplication_name,
postgres-# client_addr,
postgres-# state,
postgres-# query
postgres-# FROM pg_stat_activity
postgres-# WHERE state = ‘active’;

# 查看空闲连接
postgres=# SELECT
postgres-# pid,
postgres-# usename,
postgres-# datname,
postgres-# client_addr,
postgres-# state,
postgres-# query_start,
postgres-# now() – query_start as idle_time
postgres-# FROM pg_stat_activity
postgres-# WHERE state = ‘idle’
postgres-# ORDER BY idle_time DESC;

# 查看长时间运行的查询
postgres=# SELECT
postgres-# pid,
postgres-# usename,
postgres-# datname,
postgres-# state,
postgres-# query,
postgres-# now() – query_start as duration
postgres-# FROM pg_stat_activity
postgres-# WHERE state = ‘active’
postgres-# ORDER BY duration DESC
postgres-# LIMIT 5;

3.1.2 分析连接来源

# 查看连接来源分布
postgres=# SELECT
postgres-# client_addr,
postgres-# count(*) as connection_count
postgres-# FROM pg_stat_activity
postgres-# WHERE client_addr IS NOT NULL
postgres-# GROUP BY client_addr
postgres-# ORDER BY connection_count DESC;

# 查看用户连接分布
postgres=# SELECT
postgres-# usename,
postgres-# count(*) as connection_count
postgres-# FROM pg_stat_activity
postgres-# GROUP BY usename
postgres-# ORDER BY connection_count DESC;

# 查看数据库连接分布
postgres=# SELECT
postgres-# datname,
postgres-# count(*) as connection_count
postgres-# FROM pg_stat_activity
postgres-# GROUP BY datname
postgres-# ORDER BY connection_count DESC;

3.2 pg_stat_fgedudb视图使用

3.2.1 查看数据库统计信息

# 查看所有数据库的统计信息
postgres=# SELECT * FROM pg_stat_fgedudb;

# 查看特定数据库的统计信息
postgres=# SELECT
postgres-# datname,
postgres-# numbackends,
postgres-# xact_commit,
postgres-# xact_rollback,
postgres-# blks_read,
postgres-# blks_hit,
postgres-# tup_returned,
postgres-# tup_fetched,
postgres-# tup_inserted,
postgres-# tup_updated,
postgres-# tup_deleted
postgres-# FROM pg_stat_fgedudb
postgres-# WHERE datname = ‘fgedu_business’;

# 计算缓存命中率
postgres=# SELECT
postgres-# datname,
postgres-# blks_read,
postgres-# blks_hit,
postgres-# round((blks_hit * 100.0) / (blks_read + blks_hit), 2) as cache_hit_ratio
postgres-# FROM pg_stat_fgedudb
postgres-# WHERE datname = ‘fgedu_business’;

3.2.2 分析数据库性能

# 查看数据库事务情况
postgres=# SELECT
postgres-# datname,
postgres-# xact_commit,
postgres-# xact_rollback,
postgres-# round((xact_rollback * 100.0) / (xact_commit + xact_rollback), 2) as rollback_ratio
postgres-# FROM pg_stat_fgedudb
postgres-# WHERE datname = ‘fgedu_business’;

# 查看数据库读写情况
postgres=# SELECT
postgres-# datname,
postgres-# tup_inserted,
postgres-# tup_updated,
postgres-# tup_deleted,
postgres-# tup_returned,
postgres-# tup_fetched
postgres-# FROM pg_stat_fgedudb
postgres-# WHERE datname = ‘fgedu_business’;

3.3 pg_stat_fgedu_tables视图使用

3.3.1 查看表统计信息

# 查看用户表的统计信息
postgres=# SELECT * FROM pg_stat_fgedu_tables;

# 查看特定表的统计信息
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# seq_scan,
postgres-# seq_tup_read,
postgres-# idx_scan,
postgres-# idx_tup_fetch,
postgres-# n_tup_ins,
postgres-# n_tup_upd,
postgres-# n_tup_del,
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# last_analyze
postgres-# FROM pg_stat_fgedu_tables
postgres-# WHERE relname = ‘fgedu_fgedus’;

# 查看需要真空操作的表
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# round((n_dead_tup * 100.0) / nullif(n_live_tup, 0), 2) as dead_tuple_ratio
postgres-# FROM pg_stat_fgedu_tables
postgres-# WHERE n_dead_tup > 0
postgres-# ORDER BY dead_tuple_ratio DESC
postgres-# LIMIT 10;

3.3.2 分析表访问情况

# 查看表扫描情况
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# seq_scan,
postgres-# idx_scan,
postgres-# round((idx_scan * 100.0) / nullif(seq_scan + idx_scan, 0), 2) as index_scan_ratio
postgres-# FROM pg_stat_fgedu_tables
postgres-# ORDER BY seq_scan + idx_scan DESC
postgres-# LIMIT 10;

# 查看表修改情况
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# n_tup_ins,
postgres-# n_tup_upd,
postgres-# n_tup_del,
postgres-# n_tup_ins + n_tup_upd + n_tup_del as total_modifications
postgres-# FROM pg_stat_fgedu_tables
postgres-# ORDER BY total_modifications DESC
postgres-# LIMIT 10;

3.4 pg_stat_fgedu_indexes视图使用

3.4.1 查看索引统计信息

# 查看用户索引的统计信息
postgres=# SELECT * FROM pg_stat_fgedu_indexes;

# 查看特定表的索引统计信息
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# indexrelname,
postgres-# idx_scan,
postgres-# idx_tup_read,
postgres-# idx_tup_fetch
postgres-# FROM pg_stat_fgedu_indexes
postgres-# WHERE relname = ‘fgedu_fgedus’;

# 查看未使用的索引
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# indexrelname,
postgres-# idx_scan
postgres-# FROM pg_stat_fgedu_indexes
postgres-# WHERE idx_scan = 0
postgres-# ORDER BY relname, indexrelname;

3.4.2 分析索引使用情况

# 查看索引使用频率
postgres=# SELECT
postgres-# schemaname,
postgres-# relname,
postgres-# indexrelname,
postgres-# idx_scan,
postgres-# idx_tup_read,
postgres-# idx_tup_fetch
postgres-# FROM pg_stat_fgedu_indexes
postgres-# ORDER BY idx_scan DESC
postgres-# LIMIT 10;

# 结合pg_stat_fgedu_tables查看索引效率
postgres=# SELECT
postgres-# t.schemaname,
postgres-# t.relname,
postgres-# t.seq_scan,
postgres-# t.idx_scan,
postgres-# round((t.idx_scan * 100.0) / nullif(t.seq_scan + t.idx_scan, 0), 2) as index_scan_ratio
postgres-# FROM pg_stat_fgedu_tables t
postgres-# WHERE t.seq_scan + t.idx_scan > 0
postgres-# ORDER BY index_scan_ratio ASC
postgres-# LIMIT 10;

风哥提示：定期分析pg_stat*视图的统计信息，可以帮助发现数据库性能问题，优化查询和索引，提高系统效率。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 连接监控案例

4.1.1 案例：监控连接数和状态

# 场景：监控数据库连接数，确保系统正常运行

# 1. 创建连接监控脚本
$ vi /postgresql/scripts/monitor_connections.sh

#!/bin/bash
# 连接监控脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: http://www.fgedu.net.cn

timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
connection_count=$(psql -U pgsql -c “SELECT count(*) FROM pg_stat_activity” -t)
active_count=$(psql -U pgsql -c “SELECT count(*) FROM pg_stat_activity WHERE state = ‘active'” -t)
idle_count=$(psql -U pgsql -c “SELECT count(*) FROM pg_stat_activity WHERE state = ‘idle'” -t)

# 输出监控结果
echo “[$timestamp] 总连接数: $connection_count, 活跃连接数: $active_count, 空闲连接数: $idle_count”

# 检查连接数是否超过阈值
if [ $connection_count -gt 150 ]; then
echo “警告：连接数超过阈值（150）”
# 可以添加告警逻辑
fi

# 检查长时间空闲的连接
long_idle=$(psql -U pgsql -c “SELECT count(*) FROM pg_stat_activity WHERE state = ‘idle’ AND now() – query_start > interval ’30 minutes'” -t)
if [ $long_idle -gt 10 ]; then
echo “警告：存在 $long_idle 个超过30分钟的空闲连接”
# 可以添加清理逻辑
fi

# 2. 设置定时任务
$ crontab -e

# 添加以下内容（每5分钟执行一次）
*/5 * * * * /postgresql/scripts/monitor_connections.sh >> /postgresql/logs/connection_monitor.log 2>&1

# 3. 查看监控日志
$ tail -f /postgresql/logs/connection_monitor.log

[2026-04-02 10:00:00] 总连接数: 50, 活跃连接数: 10, 空闲连接数: 40
[2026-04-02 10:05:00] 总连接数: 52, 活跃连接数: 12, 空闲连接数: 40
[2026-04-02 10:10:00] 总连接数: 55, 活跃连接数: 15, 空闲连接数: 40

4.2 数据库监控案例

4.2.1 案例：监控数据库性能

# 场景：监控数据库性能指标，确保系统高效运行

# 1. 创建数据库性能监控脚本
$ vi /postgresql/scripts/monitor_fgedudb.sh

#!/bin/bash
# 数据库性能监控脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: http://www.fgedu.net.cn

timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
fgedudb=”fgedu_business”

# 获取数据库统计信息
stats=$(psql -U pgsql -c ”
SELECT
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted
FROM pg_stat_fgedudb
WHERE datname = ‘$fgedudb’
” -t)

# 计算缓存命中率
cache_hit_ratio=$(psql -U pgsql -c ”
SELECT
round((blks_hit * 100.0) / (blks_read + blks_hit), 2)
FROM pg_stat_fgedudb
WHERE datname = ‘$fgedudb’
” -t)

# 计算回滚率
rollback_ratio=$(psql -U pgsql -c ”
SELECT
round((xact_rollback * 100.0) / nullif(xact_commit + xact_rollback, 0), 2)
FROM pg_stat_fgedudb
WHERE datname = ‘$fgedudb’
” -t)

# 输出监控结果
echo “[$timestamp] 数据库: $fgedudb”
echo ” 事务提交: $(echo $stats | awk ‘{print $1}’)”
echo ” 事务回滚: $(echo $stats | awk ‘{print $2}’)”
echo ” 块读取: $(echo $stats | awk ‘{print $3}’)”
echo ” 块命中: $(echo $stats | awk ‘{print $4}’)”
echo ” 缓存命中率: $cache_hit_ratio%”
echo ” 回滚率: $rollback_ratio%”
echo ” 元组返回: $(echo $stats | awk ‘{print $5}’)”
echo ” 元组获取: $(echo $stats | awk ‘{print $6}’)”
echo ” 元组插入: $(echo $stats | awk ‘{print $7}’)”
echo ” 元组更新: $(echo $stats | awk ‘{print $8}’)”
echo ” 元组删除: $(echo $stats | awk ‘{print $9}’)”

# 检查缓存命中率
if (( $(echo “$cache_hit_ratio < 90" | bc -l) )); then echo "警告：缓存命中率低于90%" fi # 检查回滚率 if (( $(echo "$rollback_ratio > 5″ | bc -l) )); then
echo “警告：回滚率高于5%”
fi

# 2. 设置定时任务
$ crontab -e

# 添加以下内容（每15分钟执行一次）
*/15 * * * * /postgresql/scripts/monitor_fgedudb.sh >> /postgresql/logs/fgedudb_monitor.log 2>&1

# 3. 查看监控日志
$ tail -f /postgresql/logs/fgedudb_monitor.log

[2026-04-02 10:00:00] 数据库: fgedu_business
事务提交: 1000
事务回滚: 10
块读取: 100
块命中: 900
缓存命中率: 90.00%
回滚率: 1.00%
元组返回: 5000
元组获取: 4500
元组插入: 100
元组更新: 50
元组删除: 10

4.3 性能监控案例

4.3.1 案例：监控表和索引性能

# 场景：监控表和索引的使用情况，优化数据库性能

# 1. 创建表和索引监控脚本
$ vi /postgresql/scripts/monitor_tables_indexes.sh

#!/bin/bash
# 表和索引监控脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: http://www.fgedu.net.cn

timestamp=$(date +”%Y-%m-%d %H:%M:%S”)

# 输出监控结果
echo “[$timestamp] 表使用情况监控”
echo “———————————-”

# 查看需要真空操作的表
psql -U pgsql -c ”
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
round((n_dead_tup * 100.0) / nullif(n_live_tup, 0), 2) as dead_tuple_ratio
FROM pg_stat_fgedu_tables
WHERE n_dead_tup > 0
ORDER BY dead_tuple_ratio DESC
LIMIT 10;
”

echo ”
[$timestamp] 索引使用情况监控”
echo “———————————-”

# 查看未使用的索引
psql -U pgsql -c ”
SELECT
schemaname,
relname,
indexrelname,
idx_scan
FROM pg_stat_fgedu_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;
”

echo ”
[$timestamp] 表扫描情况监控”
echo “———————————-”

# 查看表扫描情况
psql -U pgsql -c ”
SELECT
schemaname,
relname,
seq_scan,
idx_scan,
round((idx_scan * 100.0) / nullif(seq_scan + idx_scan, 0), 2) as index_scan_ratio
FROM pg_stat_fgedu_tables
WHERE seq_scan + idx_scan > 0
ORDER BY index_scan_ratio ASC
LIMIT 10;
”

# 2. 设置定时任务
$ crontab -e

# 添加以下内容（每天执行一次）
0 1 * * * /postgresql/scripts/monitor_tables_indexes.sh >> /postgresql/logs/tables_indexes_monitor.log 2>&1

# 3. 查看监控日志
$ tail -f /postgresql/logs/tables_indexes_monitor.log

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议定期执行监控脚本，及时发现和解决性能问题。同时，结合监控工具如Prometheus和Grafana，实现更全面的监控和可视化。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL监控最佳实践

PostgreSQL监控最佳实践：

基础监控：

定期查询pg_stat_activity视图，监控连接状态
定期查询pg_stat_fgedudb视图，监控数据库性能
定期查询pg_stat_fgedu_tables视图，监控表使用情况
定期查询pg_stat_fgedu_indexes视图，监控索引使用情况

监控频率：

实时监控：1-5分钟
定期监控：15-30分钟
每日监控：一次
每周监控：一次

监控工具：

使用内置pg_stat*视图
使用第三方监控工具（如pgBadger）
使用监控系统（如Prometheus、Zabbix）
创建自定义监控脚本

告警机制：

设置合理的告警阈值
配置告警通知方式
建立告警处理流程
定期审查告警规则

5.2 PostgreSQL监控常见问题

PostgreSQL监控常见问题及解决方案：

监控数据不准确：定期运行ANALYZE，更新统计信息
监控开销过大：合理设置监控频率，避免过于频繁的查询
告警过多：调整告警阈值，减少误报
监控覆盖不全：确保监控所有关键指标
历史数据缺失：保存监控数据，建立趋势分析

5.3 PostgreSQL监控工具推荐

PostgreSQL监控常用工具：

内置工具：pg_stat*视图、psql命令行工具
第三方工具：

pgBadger：日志分析工具
pg_stat_statements：查询性能分析
pgAdmin4：图形化管理工具

监控系统：

Prometheus + Grafana：监控和可视化
Zabbix：综合监控系统
Nagios：网络监控系统
Datadog：云监控平台

自定义脚本：根据业务需求编写监控脚本

风哥提示：PostgreSQL的监控是数据库运维的重要组成部分，需要建立规范的监控体系和流程。定期分析监控数据，及时发现和解决性能问题，确保系统的稳定运行和高效性能。from PostgreSQL:www.itpux.com

持续改进：监控体系是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议建立定期审查机制，持续改进监控策略。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG077-PG服务监控：基础命令（pg_stat*）使用

Part01-基础概念与理论知识

1.1 PostgreSQL pg_stat*视图的概念

1.2 PostgreSQL监控指标

1.3 PostgreSQL监控级别

Part02-生产环境规划与建议

2.1 PostgreSQL监控规划

2.2 监控指标选择

2.3 监控频率建议

Part03-生产环境项目实施方案

3.1 pg_stat_activity视图使用

3.1.1 查看连接状态

3.1.2 分析连接来源

3.2 pg_stat_fgedudb视图使用

3.2.1 查看数据库统计信息

3.2.2 分析数据库性能

3.3 pg_stat_fgedu_tables视图使用

3.3.1 查看表统计信息

3.3.2 分析表访问情况

3.4 pg_stat_fgedu_indexes视图使用

3.4.1 查看索引统计信息

3.4.2 分析索引使用情况

Part04-生产案例与实战讲解

4.1 连接监控案例

4.1.1 案例：监控连接数和状态

4.2 数据库监控案例

4.2.1 案例：监控数据库性能

4.3 性能监控案例

4.3.1 案例：监控表和索引性能

Part05-风哥经验总结与分享

5.1 PostgreSQL监控最佳实践

5.2 PostgreSQL监控常见问题

5.3 PostgreSQL监控工具推荐

相关推荐

联系我们