PostgreSQL 常用诊断命令

系统状态查询

1. 数据库版本信息

在日常运维中，了解数据库的准确版本信息非常重要，这有助于确定支持的功能、已知问题和适用的补丁。以下是查询PostgreSQL版本的常用命令：

-- 查询完整的PostgreSQL版本信息，包括编译选项和操作系统
SELECT version();

-- 简化版本查询，仅返回主版本号
SHOW server_version;

-- 返回数字形式的版本号，便于比较（例如140005表示14.5版本）
SHOW server_version_num;

2. 数据库状态概览

这些命令可以帮助您快速了解数据库的基本运行状态，包括启动时间、是否处于恢复模式等关键信息：

-- 查看PostgreSQL服务的启动时间
SELECT pg_postmaster_start_time();

-- 检查当前实例是否处于恢复模式（用于判断主从角色）
SELECT pg_is_in_recovery();

-- 获取当前连接的数据库名称
SELECT current_database();

-- 获取当前登录的用户名
SELECT current_user;

-- 获取当前系统时间
SELECT now();

3. 数据库列表

查看服务器上所有数据库的基本信息，包括名称、大小和字符集配置：

-- 使用psql元命令列出所有数据库（仅在psql客户端中可用）
\l

-- 使用SQL查询获取数据库详细信息
SELECT datname, datsize, datcollate, datctype FROM pg_database;

连接与会话管理

1. 当前活动会话

监控数据库连接和会话是数据库运维的重要部分，可以帮助识别连接泄漏、长时间运行的查询和资源争用问题：

-- 查看所有活动连接的详细信息
SELECT pid, usename, datname, application_name, client_addr, client_port, backend_start, state, query FROM pg_stat_activity;

-- 筛选出正在执行查询的活跃会话
SELECT pid, usename, datname, query_start, state, query FROM pg_stat_activity WHERE state = 'active';

-- 查找运行时间超过5分钟的长时间查询，按持续时间降序排列
SELECT pid, usename, datname, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes' ORDER BY duration DESC;

2. 会话锁信息

锁是数据库并发控制的重要机制，但不合理的锁使用会导致性能问题和死锁。以下命令可以帮助您查看和分析锁信息：

-- 查看所有表级锁信息，包括持有锁的会话和锁模式
SELECT pid, usename, relation::regclass, mode, granted FROM pg_locks WHERE relation IS NOT NULL;

-- 结合pg_stat_activity视图，查看锁对应的具体查询内容
SELECT l.pid, l.usename, l.relation::regclass, l.mode, l.granted, a.query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.relation IS NOT NULL;

性能诊断命令

1. 缓存命中率

缓存命中率是衡量数据库性能的重要指标，较高的命中率意味着更多查询可以从内存中获取数据，减少磁盘I/O：

-- 计算共享缓冲区的堆缓存命中率，理想值应在99%以上
SELECT
  sum(heap_blks_hit) AS heap_hits,
  sum(heap_blks_read) AS heap_reads,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric AS heap_hit_ratio
FROM pg_statio_user_tables;

-- 计算索引缓存命中率，理想值也应在99%以上
SELECT
  sum(idx_blks_hit) AS idx_hits,
  sum(idx_blks_read) AS idx_reads,
  sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::numeric AS idx_hit_ratio
FROM pg_statio_user_indexes;

2. 表与索引统计

了解表和索引的大小、访问情况对于性能优化至关重要：

-- 查看所有用户表的大小（包括索引），按大小排序
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- 查看所有用户索引的大小
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size FROM pg_indexes WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- 手动收集表的统计信息并输出详细日志
ANALYZE VERBOSE table_name;

3. WAL写入统计

WAL（Write-Ahead Log）是PostgreSQL实现ACID特性的关键组件，监控WAL写入情况有助于了解数据库的写入负载：

-- 计算当前WAL写入的总字节数
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS wal_bytes;

-- 查看WAL写入的详细统计信息，包括写入量、同步时间等
SELECT * FROM pg_stat_wal;

I/O性能诊断

I/O是数据库性能的常见瓶颈，以下命令可以帮助您分析I/O使用情况：

1. 表I/O统计

查看各个表和索引的I/O访问情况，识别I/O密集型的对象：

-- 查看表的I/O统计，按物理读次数降序排列
SELECT schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables ORDER BY heap_blks_read DESC;

-- 查看索引的I/O统计，按物理读次数降序排列
SELECT schemaname, relname, indexrelname, idx_blks_read, idx_blks_hit FROM pg_statio_user_indexes ORDER BY idx_blks_read DESC;

2. 系统级I/O统计

从系统层面查看I/O使用情况，了解整体的I/O负载：

-- 查看数据库级别的I/O统计，包括缓存命中率
SELECT datname, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_database;

内存使用情况

合理配置和监控内存使用是确保数据库性能的关键：

1. 内存参数配置

查看与内存相关的配置参数，了解当前的内存分配策略：

-- 查看关键内存参数的配置值和说明
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

2. 当前内存使用

监控数据库实例的内存使用情况，及时发现内存泄漏或过度使用问题：

-- 查看每个会话的详细信息，有助于识别内存使用异常的会话
SELECT pid, usename, datname, current_query, usesysid, ssl, backend_start, xact_start, state, query_start, waiting, client_addr FROM pg_stat_activity;

-- 查看后台写进程的内存使用和活动统计
SELECT * FROM pg_stat_bgwriter;

常用系统视图查询

PostgreSQL提供了丰富的系统视图，用于监控和诊断数据库性能：

1. 数据库统计信息

查看数据库级别的统计信息，包括事务数、块读写和元组操作等：

-- 数据库级别的统计信息
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database;

2. 表统计信息

查看表级别的统计信息，包括扫描次数、索引使用情况和自动维护活动：

-- 表级别的详细统计信息
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;

常见问题（FAQ）

Q1：如何快速定位长时间运行的查询？

A1：使用以下命令可以快速查找运行时间超过5分钟的查询，并按持续时间降序排列，便于优先处理：

SELECT pid, usename, datname, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes' ORDER BY duration DESC;

Q2：如何查看数据库的磁盘使用情况？

A2：使用以下命令可以查看数据库、表和索引的磁盘使用情况：

-- 查看当前数据库的总大小
SELECT pg_size_pretty(pg_database_size(current_database()));

-- 查看指定表的大小（包括索引）
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

-- 查看所有表的大小，按大小降序排列
SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size FROM pg_tables ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC;

Q3：如何检查数据库是否存在死锁？

A3：死锁会导致事务无法继续执行，以下命令可以帮助识别和分析死锁情况：

-- 查看锁等待关系，分析是否存在死锁
SELECT l1.pid AS pid1, l1.usename AS usename1, l1.relation::regclass AS relation1, l1.mode AS mode1, l2.pid AS pid2, l2.usename AS usename2, l2.relation::regclass AS relation2, l2.mode AS mode2 FROM pg_locks l1 JOIN pg_locks l2 ON l1.relation = l2.relation AND l1.pid != l2.pid AND l1.granted AND NOT l2.granted WHERE l1.relation IS NOT NULL;

-- 使用pg_blocking_pids函数直接查找被阻塞的会话及其阻塞者
SELECT pid, pg_blocking_pids(pid) AS blocking_pids, query FROM pg_stat_activity WHERE pg_blocking_pids(pid) != '{}';

Q4：如何查看PostgreSQL的配置参数？

A4：有多种方式可以查看PostgreSQL的配置参数：

-- 查看所有配置参数
SHOW ALL;

-- 查看特定参数的值
SHOW shared_buffers;

-- 使用pg_settings视图查询参数，支持更灵活的筛选和排序
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name LIKE '%buffer%';

Q5：如何查看数据库的连接数限制和当前连接数？

A5：监控连接数有助于防止连接耗尽问题：

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

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

-- 按连接状态分组查看连接数分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;