PostgreSQL教程FG097-PG数据库健康检查：核心指标与校验方法
本文档风哥主要介绍PostgreSQL数据库的健康检查，包括核心指标和校验方法。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 PostgreSQL健康检查的概念
PostgreSQL健康检查是指对PostgreSQL数据库的运行状态、性能指标和数据完整性进行定期检查，以确保数据库的健康运行。健康检查是数据库管理的重要组成部分，可以帮助DBA及时发现和解决潜在问题，预防故障的发生。更多视频教程www.fgedu.net.cn
- 确保数据库的正常运行
- 预防和发现潜在问题
- 评估数据库性能
- 保证数据的完整性和安全性
- 优化数据库配置
1.2 PostgreSQL健康检查指标
PostgreSQL健康检查指标包括：
– 数据库状态指标：
– 连接数：当前连接数、最大连接数
– 事务状态：活跃事务数、长时间运行的事务
– 锁状态：锁等待、死锁
– 后台进程：后台进程状态
– 性能指标：
– 查询性能：慢查询、执行计划
– 缓存命中率：共享缓冲区命中率
– IO性能：磁盘读写速度、IO等待时间
– CPU使用率：数据库进程CPU使用率
– 内存使用：数据库内存使用情况
– 存储指标：
– 磁盘空间：数据目录空间使用情况
– 表大小：表和索引的大小
– 表膨胀：表和索引的膨胀情况
– TOAST使用：TOAST表使用情况
– 数据完整性指标：
– 表完整性：表数据完整性
– 索引完整性：索引完整性
– 约束完整性：约束有效性
– 复制状态：主从复制状态
– 安全指标：
– 权限设置：用户权限设置
– 密码策略：密码强度和过期时间
– 审计日志：审计日志配置
– 网络安全：网络访问控制
1.3 PostgreSQL健康检查的重要性
PostgreSQL健康检查的重要性包括：
学习交流加群风哥微信: itpux-com
- 故障预防：通过定期健康检查，可以提前发现和解决潜在问题，预防故障的发生
- 性能优化：通过分析健康检查结果，可以识别性能瓶颈，优化数据库性能
- 数据安全：通过检查数据完整性和安全配置，可以确保数据的安全性和可靠性
- 合规要求：满足企业的合规要求，如数据保留和安全审计
- 成本控制：通过优化资源使用，降低运维成本
Part02-生产环境规划与建议
2.1 PostgreSQL健康检查规划
PostgreSQL健康检查规划要点：
1. 确定检查范围：根据数据库环境确定需要检查的范围
2. 选择检查指标：根据业务需求选择需要检查的指标
3. 制定检查 schedule：确定健康检查的执行频率和时间
4. 分配检查责任：明确健康检查的责任人
5. 建立检查流程：建立标准化的健康检查流程
6. 分析检查结果：分析健康检查结果，制定改进措施
# 健康检查频率
– 日常检查：每天执行，检查核心指标
– 周检查：每周执行，检查详细指标
– 月检查：每月执行，全面检查
# 健康检查文档
– 检查指标清单：详细列出需要检查的指标
– 检查 schedule：健康检查的执行时间和频率
– 检查流程：健康检查的执行步骤和标准
– 检查责任人：每个检查任务的责任人
– 检查记录：健康检查的执行记录和结果
2.2 健康检查任务调度
PostgreSQL健康检查任务调度建议：
– 日常检查：每天执行，选择业务低峰期
– 周检查：每周执行，选择周末或业务低峰期
– 月检查：每月执行，选择月末或业务低峰期
# 健康检查执行时间
– 日常检查：每天凌晨 1:00-2:00
– 周检查：每周日凌晨 1:00-3:00
– 月检查：每月最后一个周日凌晨 1:00-5:00
# 健康检查自动化
– 使用 cron 定时执行健康检查任务
– 使用监控工具自动触发健康检查任务
– 使用脚本自动化健康检查流程
– 使用版本控制系统管理健康检查脚本
2.3 健康检查工具建议
PostgreSQL健康检查工具建议：
- 内置工具：psql、pg_isready、pg_stat_* 系统视图
- 第三方工具：pgAdmin、pgBadger、Prometheus、Grafana
- 自定义脚本：Shell脚本、Python脚本
- 监控系统：Nagios、Zabbix、Datadog
Part03-生产环境项目实施方案
3.1 核心指标检查
3.1.1 数据库状态检查
$ sudo -u pgsql psql -c “SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_fgedudb;”
# 检查连接状态
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC;”
# 检查锁状态
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;”
# 检查后台进程状态
$ ps aux | grep pgsql
3.1.2 性能指标检查
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”
# 检查缓存命中率
$ sudo -u pgsql psql -c “SELECT
sum(blks_hit) as total_hits,
sum(blks_read) as total_read,
sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read))::float as hit_ratio
FROM pg_stat_fgedudb;”
# 检查IO性能
$ iostat -x 1
# 检查CPU使用率
$ top -b -n 1 | grep pgsql # 检查内存使用
$ free -h
3.1.3 存储指标检查
$ df -h
# 检查数据目录空间
$ du -sh /postgresql/data
# 检查表大小
$ sudo -u pgsql psql -c “SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN (‘pg_catalog’, ‘information_schema’) ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;”
# 检查表膨胀
$ sudo -u pgsql psql -c “SELECT schemaname, tablename, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_tup_pct FROM pg_stat_fgedu_tables WHERE n_live_tup + n_dead_tup > 0 ORDER BY dead_tup_pct DESC;”
3.2 数据库完整性检查
3.2.1 表完整性检查
$ sudo -u pgsql psql -d fgedu_production -c “VACUUM FULL VERBOSE fgedu_employees;”
# 检查索引完整性
$ sudo -u pgsql psql -d fgedu_production -c “REINDEX TABLE fgedu_employees;”
# 检查约束完整性
$ sudo -u pgsql psql -d fgedu_production -c “SELECT conname, conrelid::regclass, contype, condeferrable, condeferred FROM pg_constraint WHERE conrelid = ‘fgedu_employees’::regclass;”
# 检查外键约束
$ sudo -u pgsql psql -d fgedu_production -c “SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE conrelid = ‘fgedu_employees’::regclass AND contype = ‘f’;”
3.2.2 复制状态检查
$ sudo -u pgsql psql -c “SELECT slot_name, plugin, slot_type, active, xmin, catalog_xmin, restart_lsn FROM pg_replication_slots;”
# 检查复制延迟
$ sudo -u pgsql psql -c “SELECT fgapplication_name, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag FROM pg_stat_replication;”
# 检查WAL发送状态
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_wal;”
3.3 性能检查
3.3.1 查询性能检查
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”
# 检查索引使用情况
$ sudo -u pgsql psql -c “SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_fgedu_indexes JOIN pg_index ON pg_index.indexrelid = pg_stat_fgedu_indexes.indexrelid WHERE NOT indisunique ORDER BY idx_scan DESC;”
# 检查统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT schemaname, tablename, last_analyze, analyze_count FROM pg_stat_fgedu_tables ORDER BY last_analyze NULLS LAST;”
3.3.2 系统性能检查
$ uptime
# 检查磁盘IO
$ iostat -x 1
# 检查网络状态
$ netstat -tuln
# 检查系统日志
$ sudo tail -n 100 /var/log/syslog
Part04-生产案例与实战讲解
4.1 健康检查脚本案例
4.1.1 案例描述
场景：一个生产环境的PostgreSQL数据库，需要创建一个健康检查脚本，定期检查数据库的核心指标。
from oracle:www.itpux.com
4.1.2 实施方案
$ sudo vi /postgresql/scripts/health_check.sh
#!/bin/bash
# PostgreSQL健康检查脚本
# 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
PG_HOME=”/postgresql”
PG_DATA=”/postgresql/data”
PG_USER=”pgsql”
LOG_DIR=”/postgresql/logs”
HEALTH_CHECK_DIR=”/postgresql/health_check”
log_message() {
local timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
echo “[$timestamp] $1”
echo “[$timestamp] $1” >> “$LOG_DIR/health_check.log”
}
create_report_dir() {
local report_dir=”$HEALTH_CHECK_DIR/$(date +%Y%m%d)”
if [ ! -d “$report_dir” ]; then
mkdir -p “$report_dir”
fi
echo “$report_dir”
}
log_message “开始执行健康检查…”
report_dir=$(create_report_dir)
# 检查数据库状态
log_message “检查数据库状态…”
sudo -u $PG_USER psql -c “SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_fgedudb;” > “$report_dir/fgedudb_status.txt”
# 检查连接状态
log_message “检查连接状态…”
sudo -u $PG_USER psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC;” > “$report_dir/connection_status.txt”
# 检查锁状态
log_message “检查锁状态…”
sudo -u $PG_USER psql -c “SELECT pid, usename, datname, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;” > “$report_dir/lock_status.txt”
# 检查慢查询
log_message “检查慢查询…”
sudo -u $PG_USER psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;” > “$report_dir/slow_queries.txt”
# 检查缓存命中率
log_message “检查缓存命中率…”
sudo -u $PG_USER psql -c “SELECT sum(blks_hit) as total_hits, sum(blks_read) as total_read, sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read))::float as hit_ratio FROM pg_stat_fgedudb;” > “$report_dir/cache_hit_ratio.txt”
# 检查表大小
log_message “检查表大小…”
sudo -u $PG_USER psql -c “SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN (‘pg_catalog’, ‘information_schema’) ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;” > “$report_dir/table_sizes.txt”
# 检查表膨胀
log_message “检查表膨胀…”
sudo -u $PG_USER psql -c “SELECT schemaname, tablename, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_tup_pct FROM pg_stat_fgedu_tables WHERE n_live_tup + n_dead_tup > 0 ORDER BY dead_tup_pct DESC;” > “$report_dir/table_bloat.txt”
# 检查系统资源
log_message “检查系统资源…”
uptime > “$report_dir/system_load.txt”
df -h > “$report_dir/disk_space.txt”
free -h > “$report_dir/memory_usage.txt”
iostat -x 1 5 > “$report_dir/disk_io.txt”
top -b -n 1 | grep pgsql > “$report_dir/cpu_usage.txt”
log_message “健康检查执行完成，报告保存到 $report_dir”
# 2. 设置定时执行
$ sudo crontab -e
# 添加以下内容
0 1 * * * /postgresql/scripts/health_check.sh
# 3. 验证健康检查脚本执行
$ sudo tail -f /postgresql/logs/health_check.log
$ ls -la /postgresql/health_check/$(date +%Y%m%d)
4.2 数据库完整性检查案例
4.2.1 案例描述
场景：一个生产环境的PostgreSQL数据库，需要执行数据库完整性检查，确保数据的完整性和一致性。
4.2.2 实施方案
$ sudo vi /postgresql/scripts/integrity_check.sh
#!/bin/bash
# PostgreSQL数据库完整性检查脚本
# 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
PG_HOME=”/postgresql”
PG_DATA=”/postgresql/data”
PG_USER=”pgsql”
LOG_DIR=”/postgresql/logs”
INTEGRITY_CHECK_DIR=”/postgresql/integrity_check”
log_message() {
local timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
echo “[$timestamp] $1”
echo “[$timestamp] $1” >> “$LOG_DIR/integrity_check.log”
}
create_report_dir() {
local report_dir=”$INTEGRITY_CHECK_DIR/$(date +%Y%m%d)”
if [ ! -d “$report_dir” ]; then
mkdir -p “$report_dir”
fi
echo “$report_dir”
}
log_message “开始执行数据库完整性检查…”
report_dir=$(create_report_dir)
# 检查表完整性
log_message “检查表完整性…”
tables=$(sudo -u $PG_USER psql -d fgedu_production -c “SELECT tablename FROM pg_tables WHERE schemaname = ‘public’;” -t)
for table in $tables; do
log_message “检查 table: $table”
sudo -u $PG_USER psql -d fgedu_production -c “VACUUM FULL VERBOSE $table;” >> “$report_dir/table_integrity.txt”
sudo -u $PG_USER psql -d fgedu_production -c “REINDEX TABLE $table;” >> “$report_dir/table_integrity.txt”
done
# 检查约束完整性
log_message “检查约束完整性…”
sudo -u $PG_USER psql -d fgedu_production -c “SELECT conname, conrelid::regclass, contype, condeferrable, condeferred FROM pg_constraint WHERE conrelid IN (SELECT oid FROM pg_class WHERE relnamespace = ‘public’::regnamespace);” > “$report_dir/constraint_integrity.txt”
# 检查外键约束
log_message “检查外键约束…”
sudo -u $PG_USER psql -d fgedu_production -c “SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE conrelid IN (SELECT oid FROM pg_class WHERE relnamespace = ‘public’::regnamespace) AND contype = ‘f’;” > “$report_dir/foreign_key_integrity.txt”
# 检查索引完整性
log_message “检查索引完整性…”
sudo -u $PG_USER psql -d fgedu_production -c “SELECT schemaname, tablename, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_fgedu_indexes WHERE schemaname = ‘public’ ORDER BY idx_scan DESC;” > “$report_dir/index_integrity.txt”
log_message “数据库完整性检查执行完成，报告保存到 $report_dir”
# 2. 设置定时执行
$ sudo crontab -e
# 添加以下内容
0 2 * * 0 /postgresql/scripts/integrity_check.sh
# 3. 验证数据库完整性检查脚本执行
$ sudo tail -f /postgresql/logs/integrity_check.log
$ ls -la /postgresql/integrity_check/$(date +%Y%m%d)
4.3 性能检查案例
4.3.1 案例描述
场景：一个生产环境的PostgreSQL数据库，需要执行性能检查，识别性能瓶颈并进行优化。
4.3.2 实施方案
$ sudo vi /postgresql/scripts/performance_check.sh
#!/bin/bash
# PostgreSQL性能检查脚本
# 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
PG_HOME=”/postgresql”
PG_DATA=”/postgresql/data”
PG_USER=”pgsql”
LOG_DIR=”/postgresql/logs”
PERFORMANCE_CHECK_DIR=”/postgresql/performance_check”
log_message() {
local timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
echo “[$timestamp] $1”
echo “[$timestamp] $1” >> “$LOG_DIR/performance_check.log”
}
create_report_dir() {
local report_dir=”$PERFORMANCE_CHECK_DIR/$(date +%Y%m%d)”
if [ ! -d “$report_dir” ]; then
mkdir -p “$report_dir”
fi
echo “$report_dir”
}
log_message “开始执行性能检查…”
report_dir=$(create_report_dir)
# 检查慢查询
log_message “检查慢查询…”
sudo -u $PG_USER psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;” > “$report_dir/slow_queries.txt”
# 检查查询执行计划
log_message “检查查询执行计划…”
sudo -u $PG_USER psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;” > “$report_dir/execution_plan.txt”
# 检查索引使用情况
log_message “检查索引使用情况…”
sudo -u $PG_USER psql -c “SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_fgedu_indexes JOIN pg_index ON pg_index.indexrelid = pg_stat_fgedu_indexes.indexrelid WHERE NOT indisunique ORDER BY idx_scan DESC;” > “$report_dir/index_usage.txt”
# 检查缓存命中率
log_message “检查缓存命中率…”
sudo -u $PG_USER psql -c “SELECT sum(blks_hit) as total_hits, sum(blks_read) as total_read, sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read))::float as hit_ratio FROM pg_stat_fgedudb;” > “$report_dir/cache_hit_ratio.txt”
# 检查统计信息
log_message “检查统计信息…”
sudo -u $PG_USER psql -d fgedu_production -c “SELECT schemaname, tablename, last_analyze, analyze_count FROM pg_stat_fgedu_tables ORDER BY last_analyze NULLS LAST;” > “$report_dir/statistics.txt”
# 检查系统性能
log_message “检查系统性能…”
uptime > “$report_dir/system_load.txt”
df -h > “$report_dir/disk_space.txt”
free -h > “$report_dir/memory_usage.txt”
iostat -x 1 5 > “$report_dir/disk_io.txt”
top -b -n 1 | grep pgsql > “$report_dir/cpu_usage.txt”
# 检查配置参数
log_message “检查配置参数…”
sudo -u $PG_USER psql -c “SHOW shared_buffers;” > “$report_dir/config_params.txt”
sudo -u $PG_USER psql -c “SHOW work_mem;” >> “$report_dir/config_params.txt”
sudo -u $PG_USER psql -c “SHOW maintenance_work_mem;” >> “$report_dir/config_params.txt”
sudo -u $PG_USER psql -c “SHOW effective_cache_size;” >> “$report_dir/config_params.txt”
log_message “性能检查执行完成，报告保存到 $report_dir”
# 2. 设置定时执行
$ sudo crontab -e
# 添加以下内容
0 3 * * 0 /postgresql/scripts/performance_check.sh
# 3. 验证性能检查脚本执行
$ sudo tail -f /postgresql/logs/performance_check.log
$ ls -la /postgresql/performance_check/$(date +%Y%m%d)
Part05-风哥经验总结与分享
5.1 PostgreSQL健康检查最佳实践
PostgreSQL健康检查最佳实践：
- 制定合理的健康检查计划：根据数据库环境和业务需求，制定合理的健康检查计划
- 自动化健康检查：使用脚本和定时任务，自动化健康检查流程
- 全面检查：检查数据库的各个方面，包括状态、性能、存储和完整性
- 定期检查：定期执行健康检查，及时发现和解决问题
- 分析检查结果：分析健康检查结果，制定改进措施
- 文档记录：记录健康检查的执行情况和结果，便于后续参考
- 持续改进：根据检查结果，持续改进数据库配置和管理
- 团队协作：明确健康检查的责任，加强团队协作
5.2 健康检查常见问题
健康检查常见问题及解决方案：
- 检查任务执行失败：解决方案：检查脚本权限、数据库连接和系统资源
- 检查结果不准确：解决方案：确保检查脚本的正确性，定期更新检查指标
- 检查时间过长：解决方案：优化检查脚本，选择业务低峰期执行
- 磁盘空间不足：解决方案：清理旧检查报告，监控磁盘使用情况
- 系统资源不足：解决方案：调整检查频率，优化检查脚本
- 检查脚本错误：解决方案：测试脚本，检查语法和逻辑错误
- 报告管理困难：解决方案：建立报告管理系统，定期归档报告
- 检查结果分析困难：解决方案：使用可视化工具，便于分析检查结果
5.3 健康检查技巧
健康检查技巧：
- 检查时间选择：选择业务低峰期执行健康检查，减少对业务的影响
- 检查脚本测试：在测试环境中测试健康检查脚本，确保脚本的正确性
- 检查指标选择：根据数据库环境和业务需求，选择合适的检查指标
- 监控告警：设置监控告警，及时发现和解决问题
- 性能基准：建立性能基准，评估健康检查效果
- 文档管理：建立健康检查文档，记录检查结果和改进措施
- 团队培训：加强团队培训，提高健康检查的技能和意识
- 持续学习：关注PostgreSQL的最新特性和最佳实践，持续学习和改进
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
