PostgreSQL 状态检查命令与脚本

数据库基本状态检查

1. 数据库版本检查

通过以下命令可以查看PostgreSQL的版本信息，包括完整版本、主版本号和版本号数字形式，这些信息对于确认数据库环境、规划升级和排查兼容性问题非常重要。

sql

-- 查看PostgreSQL完整版本信息，包含编译信息和操作系统信息
SELECT version();

-- 查看PostgreSQL主版本号，如 "15.3"
SHOW server_version;

-- 查看PostgreSQL完整版本号（数字形式，如 "150003"），便于版本比较
SHOW server_version_num;

2. 数据库运行状态

检查数据库服务是否正常运行是日常运维的基础，可以通过多种方式验证：

bash

# 使用pg_ctl命令检查指定数据目录的数据库状态
pg_ctl status -D /path/to/data

# 使用systemctl检查PostgreSQL服务状态（适用于systemd系统，需指定具体版本）
systemctl status postgresql-15

# 使用ps命令查看PostgreSQL进程是否存在
ps aux | grep postgres

3. 配置文件位置

PostgreSQL的配置文件位置对于修改配置和排查问题至关重要，通过以下命令可以快速获取：

sql

-- 查看主配置文件postgresql.conf的位置
SHOW config_file;

-- 查看客户端认证配置文件pg_hba.conf的位置
SHOW hba_file;

-- 查看身份映射配置文件pg_ident.conf的位置
SHOW ident_file;

连接状态检查

连接状态是数据库性能和稳定性的重要指标，通过监控连接数、活跃连接和连接来源，可以及时发现异常连接和资源争用问题。

1. 当前连接数

连接数监控可以帮助我们了解数据库的负载情况，避免连接数达到上限导致新连接失败。

sql

-- 查看当前数据库的总连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看不同状态的连接数分布，了解连接使用情况
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- 查看数据库配置的最大连接数
SHOW max_connections;

-- 计算当前连接数使用率，超过80%时应警惕
SELECT 
  count(*)::float / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') * 100 
  AS connection_usage_percent 
FROM pg_stat_activity;

2. 活跃连接详情

活跃连接包含正在执行SQL查询的会话，监控活跃连接可以帮助我们发现长时间运行的查询和性能瓶颈。

sql

-- 查看所有活跃连接的详细信息，包括用户、数据库、客户端信息和执行的查询
SELECT 
  pid, 
  usename, 
  datname, 
  application_name, 
  client_addr, 
  client_port, 
  backend_start, 
  state, 
  query_start, 
  wait_event_type, 
  wait_event, 
  query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY query_start;

-- 查看运行时间超过5分钟的长时间查询，这些查询可能导致性能问题
SELECT 
  pid, 
  usename, 
  datname, 
  query, 
  age(now(), query_start) AS query_duration 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND age(now(), query_start) > interval '5 minutes' 
ORDER BY query_duration DESC;

3. 连接来源统计

按客户端IP、用户名和数据库名统计连接数，可以帮助我们了解连接的分布情况，发现异常连接来源。

sql

-- 按客户端IP地址统计连接数，识别异常IP的大量连接
SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr ORDER BY count(*) DESC;

-- 按用户名统计连接数，了解不同用户的连接情况
SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename ORDER BY count(*) DESC;

-- 按数据库名统计连接数，了解各数据库的负载情况
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname ORDER BY count(*) DESC;

性能状态检查

性能状态是数据库运维的核心关注点，通过监控CPU、内存、缓冲区命中率、事务和锁状态，可以及时发现性能瓶颈并进行优化。

1. CPU和内存使用

CPU和内存是数据库运行的基础资源，监控其使用情况有助于理解数据库负载和资源需求。

sql

-- 使用系统命令查看PostgreSQL进程的内存使用情况，按内存使用率排序，显示前10个进程
-- 此命令需要在操作系统命令行执行，而非SQL查询工具
ps aux --sort=-%mem | grep postgres | head -10

-- 查看PostgreSQL关键内存参数的配置情况，这些参数直接影响数据库性能
SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

2. 缓冲区命中率

缓冲区命中率反映了数据库从内存中读取数据的比例，命中率越高，数据库性能越好。理想情况下，堆缓冲区命中率和索引缓冲区命中率都应高于99%。

sql

-- 计算堆缓冲区命中率，反映表数据的内存访问效率
SELECT 
  sum(heap_blks_hit) as heap_hit, 
  sum(heap_blks_read) as heap_read, 
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::float as heap_hit_ratio
FROM pg_statio_user_tables;

-- 计算索引缓冲区命中率，反映索引的内存访问效率
SELECT 
  sum(idx_blks_hit) as idx_hit, 
  sum(idx_blks_read) as idx_read, 
  sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::float as idx_hit_ratio
FROM pg_statio_user_indexes;

3. 事务状态

事务状态监控可以帮助我们发现长时间运行的事务，这些事务可能导致锁争用、MVCC膨胀和性能下降。

sql

-- 查看当前所有活跃事务的详细信息，包括开始时间和持续时长
SELECT 
  pid, 
  usename, 
  datname, 
  xact_start, 
  age(now(), xact_start) as xact_duration, 
  query 
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
ORDER BY xact_duration DESC;

-- 查看运行时间超过10分钟的长事务，这些事务应重点关注和处理
SELECT 
  pid, 
  usename, 
  datname, 
  xact_start, 
  age(now(), xact_start) as xact_duration, 
  query 
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
  AND age(now(), xact_start) > interval '10 minutes' 
ORDER BY xact_duration DESC;

4. 锁状态检查

锁是数据库并发控制的重要机制，但锁争用可能导致性能问题和阻塞。监控锁状态可以帮助我们发现阻塞的查询和事务。

sql

-- 查看当前数据库中的所有锁信息，包括持有锁的进程和被锁定的对象
SELECT 
  pid, 
  usename, 
  datname, 
  relation::regclass, 
  mode, 
  granted, 
  query 
FROM pg_locks 
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid 
ORDER BY pid;

-- 查看当前被阻塞的锁，识别阻塞链和导致阻塞的查询
SELECT 
  bl.pid AS blocked_pid, 
  a.usename AS blocked_user, 
  kl.pid AS blocking_pid, 
  ka.usename AS blocking_user, 
  a.query AS blocked_query, 
  ka.query AS blocking_query, 
  a.state AS blocked_state
FROM pg_locks bl
JOIN pg_stat_activity a ON bl.pid = a.pid
JOIN pg_locks kl ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;

磁盘空间检查

磁盘空间是数据库稳定运行的重要保障，监控磁盘使用情况可以避免因空间不足导致的数据库故障。磁盘空间检查包括操作系统级别的目录大小检查和数据库内部的空间使用分析。

1. 数据库目录空间

在操作系统级别检查数据库目录大小，可以快速了解数据文件、WAL文件和表空间的磁盘占用情况。

bash

# 查看数据库数据目录的总大小，了解数据库整体磁盘占用
# 将/path/to/data替换为实际的数据目录路径
du -sh /path/to/data

# 查看WAL目录的大小，WAL文件用于崩溃恢复和复制
# WAL文件过大可能影响磁盘空间，需要检查归档是否正常
du -sh /path/to/data/pg_wal

# 查看所有表空间的大小，表空间是PostgreSQL中管理数据存储的重要机制
du -sh /path/to/tablespaces/*

2. 数据库内部空间使用

在数据库内部检查空间使用情况，可以更细致地了解各个数据库、表和索引的磁盘占用，有助于优化存储结构和识别空间浪费。

sql

-- 查看所有数据库的大小，按大小降序排列
-- 可以快速识别占用空间最大的数据库
SELECT 
  datname, 
  pg_size_pretty(pg_database_size(datname)) as database_size
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

-- 查看用户表的详细大小信息，包括总大小、表大小和索引大小
-- 排除系统表（pg_catalog和information_schema）
-- 可以帮助识别大表和索引，评估优化空间
SELECT 
  schemaname, 
  tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) as index_size
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 查看最大的10个用户表，快速定位占用空间最大的表
SELECT 
  schemaname, 
  tablename, 
  pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_stat_user_tables 
ORDER BY pg_total_relation_size(relid) DESC 
LIMIT 10;

3. 临时文件使用

PostgreSQL在执行复杂查询时会生成临时文件，监控临时文件使用情况可以帮助优化查询和调整临时文件配置。

sql

-- 查看各数据库的临时文件使用情况，包括文件数量和总大小
-- 需要超级用户权限才能查看所有数据库的临时文件信息
SELECT 
  datname, 
  temp_files, 
  temp_bytes, 
  pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database 
ORDER BY temp_bytes DESC;

-- 查看临时文件的配置参数
-- temp_file_limit：单个会话可以使用的临时文件总大小上限
-- temp_buffers：每个会话用于临时表的内存缓冲区大小
SHOW temp_file_limit;
SHOW temp_buffers;

复制状态检查

对于使用主从复制的PostgreSQL集群，监控复制状态至关重要。通过检查复制槽、复制连接和复制延迟，可以确保数据一致性和高可用性。

1. 主从复制状态

复制状态检查需要分别在主库和从库上执行不同的命令，以全面了解复制系统的运行情况。

sql

-- 在主库上查看复制槽状态
-- 复制槽用于确保WAL文件不会被过早删除，保证从库能够接收所有必要的WAL
-- 注意：retained_wal列显示了为每个复制槽保留的WAL大小，过大可能导致磁盘空间问题
SELECT 
  slot_name, 
  plugin, 
  slot_type, 
  active, 
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
FROM pg_replication_slots;

-- 在主库上查看复制连接状态
-- 显示从库的连接信息、同步状态和延迟情况
-- sync_state列显示同步状态：async（异步）、sync（同步）、potential（潜在同步）
SELECT 
  usename, 
  application_name, 
  client_addr, 
  state, 
  sync_state, 
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as send_lag,
  pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag,
  pg_wal_lsn_diff(write_lsn, flush_lsn) as flush_lag,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) as replay_lag
FROM pg_stat_replication;

-- 在从库上查看WAL接收状态
-- 显示从库接收和重放WAL的情况
SELECT 
  client_addr, 
  state, 
  sync_state, 
  pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) as replay_lag,
  pg_is_in_recovery() as is_in_recovery, -- 检查是否处于恢复模式
  pg_last_xact_replay_timestamp() as last_replay_time -- 最后一次事务重放时间
FROM pg_stat_wal_receiver;

2. 复制延迟

复制延迟是衡量主从数据一致性的重要指标，延迟过大会影响高可用性和数据安全性。

sql

-- 在从库上查看复制延迟（秒）
-- 计算当前时间与最后一次事务重放时间的差值
SELECT 
  extract(epoch from (now() - pg_last_xact_replay_timestamp())) as replay_delay_seconds
FROM pg_stat_wal_receiver;

-- 在主库上查看复制延迟的详细信息，分解为不同阶段的延迟
-- send_lag：主库已生成但尚未发送的WAL
-- write_lag：已发送但尚未写入从库磁盘的WAL
-- flush_lag：已写入从库磁盘但尚未刷新到WAL缓冲区的WAL
-- replay_lag：已刷新但尚未重放到从库数据库的WAL
SELECT 
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;

真空状态检查

PostgreSQL的VACUUM操作是维护数据库性能和稳定性的重要机制，用于清理死元组、更新统计信息和防止事务ID回卷。监控真空状态可以确保自动真空正常工作，避免性能问题。

1. 自动真空配置

自动真空是PostgreSQL默认启用的重要功能，通过以下命令可以查看其关键配置参数，了解自动真空的工作方式。

sql

-- 查看自动真空是否启用（默认值为on）
SHOW autovacuum;

-- 查看自动真空最大工作线程数（默认值为3）
SHOW autovacuum_max_workers;

-- 查看自动真空检查间隔（默认值为1分钟）
SHOW autovacuum_naptime;

-- 查看自动真空触发阈值（默认值为50个死元组）
SHOW autovacuum_vacuum_threshold;

-- 查看自动分析触发阈值（默认值为50个修改的元组）
SHOW autovacuum_analyze_threshold;

2. 表真空状态

监控各个表的真空状态可以帮助我们发现需要手动干预的表，避免因死元组过多导致的性能下降和磁盘空间浪费。

sql

-- 查看所有包含死元组的用户表，按死元组数量降序排列
-- 显示死元组数量、活元组数量以及手动/自动真空和分析的次数
SELECT 
  schemaname, 
  relname, 
  n_dead_tup, 
  n_live_tup, 
  vacuum_count, 
  autovacuum_count, 
  analyze_count, 
  autoanalyze_count
FROM pg_stat_user_tables 
WHERE n_dead_tup > 0 
ORDER BY n_dead_tup DESC;

-- 计算死元组比例超过阈值的表，按比例降序排列
-- 死元组比例过高会影响查询性能，建议定期监控并处理
SELECT 
  schemaname, 
  relname, 
  n_dead_tup, 
  n_live_tup, 
  round(n_dead_tup::numeric / (n_live_tup + n_dead_tup)::numeric * 100, 2) as dead_tuple_percent
FROM pg_stat_user_tables 
WHERE (n_live_tup + n_dead_tup) > 0 
  AND n_dead_tup > autovacuum_vacuum_threshold
ORDER BY dead_tuple_percent DESC;

WAL状态检查

WAL（Write-Ahead Logging）是PostgreSQL实现事务持久性和崩溃恢复的核心机制，同时也是复制的基础。监控WAL状态可以帮助我们了解数据库的写入负载、复制性能和存储空间使用情况。

1. WAL配置

WAL的配置直接影响数据库的性能、安全性和复制行为。通过以下命令可以查看关键的WAL配置参数：

sql

-- 查看WAL级别（默认值为replica，支持复制）
-- 可选值：minimal, replica, logical
SHOW wal_level;

-- 查看WAL段文件大小（默认值为16MB）
SHOW wal_segment_size;

-- 查看检查点配置
-- checkpoint_timeout：检查点间隔时间（默认值为5分钟）
-- checkpoint_completion_target：检查点完成目标比例（默认值为0.9）
-- max_wal_size：最大WAL大小，决定检查点触发时机（默认值为1GB）
-- min_wal_size：最小WAL大小，用于控制WAL文件回收（默认值为80MB）
SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;
SHOW max_wal_size;
SHOW min_wal_size;

2. WAL生成速率

监控WAL生成速率可以帮助我们了解数据库的写入负载，预测存储需求，并发现异常的写入活动。

sql

-- 估算最近WAL生成速率
-- 注意：此查询依赖于复制连接，仅在主库上有复制从库时有效
SELECT 
  extract(epoch from (now() - pg_stat_file('pg_wal/000000010000000000000001').modification)) as seconds,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as wal_generated
FROM pg_stat_replication 
LIMIT 1;

-- 估算各数据库生成的WAL大小
-- 基于事务提交数和平均事务大小（假设为2KB）进行估算
SELECT 
  datname, 
  pg_size_pretty(xact_commit * 2048) as estimated_wal_size
FROM pg_stat_database 
ORDER BY xact_commit DESC;

常用检查脚本

以下是几个实用的PostgreSQL检查脚本，可以帮助DBA自动化日常巡检工作，及时发现和解决问题。

1. 数据库健康检查脚本

这个脚本用于快速检查PostgreSQL数据库的基本健康状态，包括连接测试、版本信息、服务状态、连接数、长事务、锁状态和磁盘空间。

bash

#!/bin/bash
# PostgreSQL健康检查脚本
# 使用方法：chmod +x pg_health_check.sh && ./pg_health_check.sh

echo "=== PostgreSQL健康检查报告 ==="
echo "检查时间: $(date)"
echo ""

# 1. 数据库连接测试
echo "1. 数据库连接测试:"
# 注意：将localhost和postgres替换为实际的数据库地址和用户名
psql -h localhost -U postgres -c "SELECT 1" > /dev/null 2>&1
if [ $? -eq 0 ]; then
  echo "   ✓ 数据库连接正常"
else
  echo "   ✗ 数据库连接失败"
fi

# 2. 版本检查
echo "2. 数据库版本:"
psql --version

# 3. 服务状态
echo "3. 服务状态:"
# 注意：根据实际情况调整服务名称
if systemctl is-active postgresql-15 > /dev/null 2>&1; then
  echo "   ✓ PostgreSQL服务运行正常"
else
  echo "   ✗ PostgreSQL服务未运行"
fi

# 4. 连接数检查
echo "4. 连接数检查:"
psql -h localhost -U postgres -c "SELECT count(*) as current_connections, setting::int as max_connections FROM pg_stat_activity, pg_settings WHERE name = 'max_connections' GROUP BY setting;"

# 5. 长事务检查
echo "5. 长事务检查（>5分钟）:"
long_tx_count=$(psql -h localhost -U postgres -t -c "SELECT count(*) FROM pg_stat_activity WHERE xact_start IS NOT NULL AND age(now(), xact_start) > interval '5 minutes';")
if [ "$long_tx_count" -gt 0 ]; then
  echo "   ✗ 发现 $long_tx_count 个长事务:"
  psql -h localhost -U postgres -c "SELECT pid, usename, age(now(), xact_start) as duration FROM pg_stat_activity WHERE xact_start IS NOT NULL AND age(now(), xact_start) > interval '5 minutes' ORDER BY duration DESC;"
else
  echo "   ✓ 未发现长事务"
fi

# 6. 锁检查
echo "6. 锁状态检查:"
blocked_locks=$(psql -h localhost -U postgres -t -c "SELECT count(*) FROM pg_locks WHERE NOT granted;")
if [ "$blocked_locks" -gt 0 ]; then
  echo "   ✗ 发现 $blocked_locks 个阻塞的锁:"
  psql -h localhost -U postgres -c "SELECT * FROM pg_locks WHERE NOT granted;"
else
  echo "   ✓ 未发现阻塞的锁"
fi

# 7. 磁盘空间检查
echo "7. 磁盘空间检查:"
# 注意：将/path/to/data替换为实际的数据目录路径
df -h /path/to/data

echo ""
echo "=== 检查完成 ==="

2. 性能监控视图

这个SQL脚本创建一个性能监控视图，聚合了PostgreSQL的关键性能指标，包括连接统计、缓冲区命中率、事务统计、锁统计、WAL统计和长事务信息，便于快速查看数据库整体性能状况。

sql

-- PostgreSQL性能监控视图
-- 使用方法：在psql中执行此脚本创建视图，然后执行 SELECT * FROM pg_performance_metrics;
CREATE OR REPLACE VIEW pg_performance_metrics AS
SELECT
  -- 基本信息
  current_database() AS database_name,
  now() AS check_time,
  
  -- 连接统计
  (SELECT count(*) FROM pg_stat_activity) AS total_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  
  -- 缓冲区命中率
  (SELECT 
     round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric * 100, 2)
   FROM pg_statio_user_tables) AS heap_hit_ratio,
  
  (SELECT 
     round(sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::numeric * 100, 2)
   FROM pg_statio_user_indexes) AS index_hit_ratio,
  
  -- 事务统计
  (SELECT sum(xact_commit + xact_rollback) FROM pg_stat_database) AS total_transactions,
  (SELECT sum(xact_rollback) FROM pg_stat_database) AS rolled_back_transactions,
  
  -- 锁统计
  (SELECT count(*) FROM pg_locks WHERE NOT granted) AS blocked_locks,
  
  -- WAL统计（仅主库有值）
  (SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) 
   FROM pg_stat_replication LIMIT 1) AS wal_lag,
  
  -- 长事务统计
  (SELECT count(*) FROM pg_stat_activity 
   WHERE xact_start IS NOT NULL AND age(now(), xact_start) > interval '10 minutes') AS long_transactions_count;

-- 使用方法示例
-- SELECT * FROM pg_performance_metrics;

3. 日常巡检脚本

这个脚本用于生成详细的日常巡检报告，输出格式为JSON，便于后续处理和展示。报告包含数据库基本信息、连接统计、性能指标、存储信息、复制状态和长事务详情。

bash

#!/bin/bash
# PostgreSQL日常巡检脚本
# 输出格式：JSON，便于后续处理和展示
# 使用方法：chmod +x pg_inspection.sh && ./pg_inspection.sh

OUTPUT_FILE="pg_inspection_$(date +%Y%m%d_%H%M%S).json"

# 执行巡检查询
# 注意：将localhost和postgres替换为实际的数据库地址和用户名
psql -h localhost -U postgres -t -c "
SELECT 
  json_build_object(
    'inspection_time', now(),
    'database_info', json_build_object(
      'name', current_database(),
      'version', version()
    ),
    'connection_stats', json_build_object(
      'current_connections', (SELECT count(*) FROM pg_stat_activity),
      'max_connections', (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'),
      'connection_states', (SELECT json_object_agg(state, count) FROM (SELECT state, count(*) FROM pg_stat_activity GROUP BY state) s)
    ),
    'performance_metrics', json_build_object(
      'heap_hit_ratio', (SELECT round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric * 100, 2) FROM pg_statio_user_tables),
      'index_hit_ratio', (SELECT round(sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::numeric * 100, 2) FROM pg_statio_user_indexes),
      'buffer_cache_hit_ratio', (SELECT round((sum(blks_hit) - sum(idx_blks_hit)) / (sum(blks_hit) - sum(idx_blks_hit) + sum(blks_read) - sum(idx_blks_read))::numeric * 100, 2) FROM pg_statio_user_tables)
    ),
    'storage_info', json_build_object(
      'database_sizes', (SELECT json_agg(json_build_object('name', datname, 'size', pg_size_pretty(pg_database_size(datname)))) FROM pg_database),
      'largest_tables', (SELECT json_agg(json_build_object('schema', schemaname, 'table', tablename, 'size', pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)))) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC LIMIT 10)
    ),
    'replication_status', (SELECT json_agg(json_build_object(
      'slot_name', slot_name,
      'active', active,
      'wal_retained', pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
    )) FROM pg_replication_slots),
    'long_transactions', (SELECT json_agg(json_build_object(
      'pid', pid,
      'user', usename,
      'duration', age(now(), xact_start)::text,
      'query', query
    )) FROM pg_stat_activity WHERE xact_start IS NOT NULL AND age(now(), xact_start) > interval '10 minutes' ORDER BY age(now(), xact_start) DESC)
  )
;
"
 > $OUTPUT_FILE

echo "巡检完成，报告已保存到: $OUTPUT_FILE"
echo "可以使用以下命令查看报告内容: cat $OUTPUT_FILE | jq"

监控告警建议

为了确保PostgreSQL数据库的稳定运行，建议建立完善的监控告警机制。以下是关键监控指标和常用监控工具推荐：

1. 关键监控指标

指标类型	具体指标	告警阈值	监控频率	说明
连接	连接数使用率	>80%	1分钟	连接数接近上限时，新连接可能失败
连接	空闲事务时长	>10分钟	5分钟	长时间运行的空闲事务可能导致锁争用和MVCC膨胀
性能	缓冲区命中率	<95%	5分钟	命中率过低表示内存配置不足，导致频繁磁盘I/O
性能	长查询时长	>5分钟	1分钟	长查询可能导致资源争用和性能下降
存储	WAL磁盘使用率	>80%	5分钟	WAL文件过多可能导致磁盘空间不足
存储	数据目录使用率	>85%	10分钟	数据目录空间不足会导致数据库无法写入
复制	复制延迟	>30秒	1分钟	复制延迟过大影响数据一致性和故障切换
锁	阻塞锁数量	>5	1分钟	大量阻塞锁表示存在严重的并发问题

2. 监控工具集成

根据实际需求和预算，可以选择以下监控工具：

Prometheus + Grafana：推荐的开源监控组合，有成熟的PostgreSQL监控模板，可自定义仪表盘和告警规则
Zabbix：支持PostgreSQL监控，内置多种监控项和告警规则，适合企业级监控
Datadog：商业监控工具，提供全面的PostgreSQL监控和分析，集成简单
pgBadger：日志分析工具，生成详细的性能报告，适合离线分析
Checkmk：支持PostgreSQL监控，提供直观的监控仪表盘，易于部署和使用

常见问题（FAQ）

Q1：如何查看PostgreSQL的实时性能指标？

解决方案：

PostgreSQL提供了丰富的系统视图来查看实时性能指标，最常用的包括pg_stat_activity、pg_stat_database和pg_statio_user_tables。可以使用以下命令快速查看关键性能指标概览：

sql

-- 查看关键性能指标概览
SELECT 
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  (SELECT round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric * 100, 2) FROM pg_statio_user_tables) AS heap_hit_ratio,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE xact_start IS NOT NULL AND age(now(), xact_start) > interval '5 minutes') AS long_transactions;

此外，还可以使用前面提到的pg_performance_metrics视图获取更全面的性能指标。

Q2：如何识别和处理PostgreSQL中的慢查询？

解决方案：

开启慢查询日志：通过配置慢查询日志，可以记录所有超过指定时长的查询，便于后续分析。

sql

ALTER SYSTEM SET log_min_duration_statement = '1000ms'; -- 记录超过1秒的查询
ALTER SYSTEM SET log_statement = 'none';
ALTER SYSTEM SET log_duration = on;
SELECT pg_reload_conf(); -- 无需重启即可生效

使用pg_stat_statements扩展：这是PostgreSQL的官方扩展，可以跟踪所有SQL语句的执行统计信息。

sql

-- 首先确保已安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最耗时的10个查询
SELECT 
  queryid, 
  query, 
  total_exec_time, 
  calls, 
  mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

分析执行计划：使用EXPLAIN ANALYZE命令可以查看查询的详细执行计划，帮助识别性能瓶颈。
sql
```
EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
```

Q3：如何监控PostgreSQL的复制延迟？

解决方案：

在从库上监控：可以通过pg_stat_wal_receiver视图查看复制延迟。

sql

SELECT 
  extract(epoch from (now() - pg_last_xact_replay_timestamp())) as replay_delay_seconds
FROM pg_stat_wal_receiver;

在主库上监控：可以通过pg_stat_replication视图查看所有从库的复制延迟。

sql

SELECT 
  client_addr, 
  application_name, 
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_lag
FROM pg_stat_replication;

使用监控工具：大多数监控工具都提供了专门的复制延迟监控面板，如：
- Prometheus + Grafana：使用pg_stat_replication指标
- Zabbix：使用内置的PostgreSQL复制监控模板
- Datadog：提供专门的复制延迟监控面板

Q4：如何检查PostgreSQL中的锁阻塞问题？

解决方案：

查看当前锁信息：使用以下查询可以查看所有被阻塞的锁和相关的查询。

sql

SELECT 
  bl.pid AS blocked_pid, 
  a.usename AS blocked_user, 
  kl.pid AS blocking_pid, 
  ka.usename AS blocking_user, 
  a.query AS blocked_query, 
  ka.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON bl.pid = a.pid
JOIN pg_locks kl ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;

终止阻塞进程：如果确认阻塞进程是问题的根源，可以使用以下命令终止它。
sql
```
-- 将blocking_pid替换为实际的阻塞进程ID
SELECT pg_terminate_backend(blocking_pid);
```
预防锁阻塞：
- 优化查询，减少锁持有时间
- 使用适当的事务隔离级别（如READ COMMITTED）
- 避免长时间运行的事务
- 实现合理的锁超时机制
- 定期监控锁状态，及时发现和处理问题

Q5：如何查看PostgreSQL的磁盘使用情况？

解决方案：

查看数据库大小：

sql

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

查看表大小：

sql

-- 查看所有用户表的大小（含索引）
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) 
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

查看索引大小：

sql

-- 查看所有用户表的索引大小
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) 
FROM pg_indexes 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY pg_indexes_size(schemaname || '.' || tablename) DESC;

查看WAL文件使用：

sql

-- 在主库上查看WAL使用情况
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as wal_used 
FROM pg_stat_replication 
LIMIT 1;

操作系统级别的磁盘检查：

bash

# 查看数据目录大小
du -sh /path/to/data

# 查看WAL目录大小
du -sh /path/to/data/pg_wal

# 查看磁盘空间使用情况
df -h

PostgreSQL 状态检查命令与脚本 ​

数据库基本状态检查 ​

1. 数据库版本检查 ​

2. 数据库运行状态 ​

3. 配置文件位置 ​

连接状态检查 ​

1. 当前连接数 ​

2. 活跃连接详情 ​

3. 连接来源统计 ​

性能状态检查 ​

1. CPU和内存使用 ​

2. 缓冲区命中率 ​

3. 事务状态 ​

4. 锁状态检查 ​

磁盘空间检查 ​

1. 数据库目录空间 ​

2. 数据库内部空间使用 ​

3. 临时文件使用 ​

复制状态检查 ​

1. 主从复制状态 ​

2. 复制延迟 ​

真空状态检查 ​

1. 自动真空配置 ​

2. 表真空状态 ​

WAL状态检查 ​

1. WAL配置 ​

2. WAL生成速率 ​

常用检查脚本 ​

1. 数据库健康检查脚本 ​

2. 性能监控视图 ​

3. 日常巡检脚本 ​

监控告警建议 ​

1. 关键监控指标 ​

2. 监控工具集成 ​

常见问题（FAQ） ​

Q1：如何查看PostgreSQL的实时性能指标？ ​

Q2：如何识别和处理PostgreSQL中的慢查询？ ​

Q3：如何监控PostgreSQL的复制延迟？ ​

Q4：如何检查PostgreSQL中的锁阻塞问题？ ​

Q5：如何查看PostgreSQL的磁盘使用情况？ ​

PostgreSQL 状态检查命令与脚本

数据库基本状态检查

1. 数据库版本检查

2. 数据库运行状态

3. 配置文件位置

连接状态检查

1. 当前连接数

2. 活跃连接详情

3. 连接来源统计

性能状态检查

1. CPU和内存使用

2. 缓冲区命中率

3. 事务状态

4. 锁状态检查

磁盘空间检查

1. 数据库目录空间

2. 数据库内部空间使用

3. 临时文件使用

复制状态检查

1. 主从复制状态

2. 复制延迟

真空状态检查

1. 自动真空配置

2. 表真空状态

WAL状态检查

1. WAL配置

2. WAL生成速率

常用检查脚本

1. 数据库健康检查脚本

2. 性能监控视图

3. 日常巡检脚本

监控告警建议

1. 关键监控指标

2. 监控工具集成

常见问题（FAQ）

Q1：如何查看PostgreSQL的实时性能指标？

Q2：如何识别和处理PostgreSQL中的慢查询？

Q3：如何监控PostgreSQL的复制延迟？

Q4：如何检查PostgreSQL中的锁阻塞问题？

Q5：如何查看PostgreSQL的磁盘使用情况？