PostgreSQL 信息收集

信息收集是PostgreSQL数据库运维的重要组成部分，它有助于故障诊断、性能分析和日常维护。本指南将详细介绍如何收集PostgreSQL数据库的各种信息，包括系统信息、数据库信息、性能信息、日志信息和复制信息等。

系统信息收集

1. 操作系统信息

操作系统信息是数据库性能分析和故障诊断的基础，包括CPU、内存、磁盘和网络等资源的使用情况。

bash

# 查看操作系统版本
cat /etc/os-release  # 查看Linux发行版信息
uname -a             # 查看内核版本和系统架构

# 查看CPU信息
lscpu                              # 查看CPU详细信息
cat /proc/cpuinfo | grep -i "model name" | head -n 1  # 查看CPU型号

# 查看内存信息
free -h              # 查看内存使用情况，以人类可读格式显示
cat /proc/meminfo | grep -i "memtotal\|memfree\|cached"  # 查看详细内存统计

# 查看磁盘信息
df -h                # 查看磁盘分区使用情况
lsblk                # 查看块设备信息
fdisk -l             # 查看磁盘分区表

# 查看磁盘I/O性能
iostat -x 1          # 每1秒输出一次磁盘I/O统计，显示扩展信息

# 查看网络信息
ifconfig -a          # 查看所有网络接口信息（传统命令）
ip addr              # 查看所有网络接口信息（现代命令）
netstat -tuln        # 查看TCP/UDP监听端口（传统命令）
ss -tuln             # 查看TCP/UDP监听端口（现代命令）

2. PostgreSQL进程信息

PostgreSQL进程信息可以帮助我们了解数据库的运行状态和资源使用情况。

bash

# 查看PostgreSQL进程
ps aux | grep postgres  # 查看所有PostgreSQL相关进程
pgrep -fl postgres      # 更精确地查找PostgreSQL进程

# 查看PostgreSQL进程树
pstree -p $(pgrep -f postgres)  # 显示PostgreSQL进程间的父子关系

# 查看进程资源使用情况
top -p $(pgrep -f postgres)  # 实时查看PostgreSQL进程的资源使用
htop -p $(pgrep -f postgres)  # 更友好的交互式进程查看工具

# 查看进程打开的文件
lsof -p $(pgrep -f "postgres -D")  # 查看PostgreSQL主进程打开的所有文件

数据库信息收集

1. 数据库基本信息

数据库基本信息包括版本、数据库列表、用户列表等，是了解数据库环境的基础。

sql

-- 查看PostgreSQL版本
SELECT version();       -- 查看完整版本信息
SHOW server_version;    -- 仅查看服务器版本号

-- 查看数据库列表
\l                      -- psql命令：查看所有数据库
SELECT datname FROM pg_database;  -- SQL命令：查看所有数据库

-- 查看当前数据库
SELECT current_database();  -- 查看当前连接的数据库

-- 查看用户列表
\du                      -- psql命令：查看所有用户和角色
SELECT usename FROM pg_user;  -- SQL命令：查看所有用户

-- 查看角色权限
\dp                      -- psql命令：查看表权限

2. 数据库配置信息

数据库配置信息直接影响数据库的性能和行为，了解这些配置对于性能调优至关重要。

sql

-- 查看所有配置参数
SHOW ALL;               -- 查看所有配置参数及其当前值

-- 查看特定配置参数（性能相关）
SHOW shared_buffers;        -- 共享缓冲区大小
SHOW work_mem;              -- 每个查询工作内存
SHOW maintenance_work_mem;  -- 维护操作（如VACUUM）内存
SHOW effective_cache_size;  -- 有效缓存大小（估计值）

-- 从配置文件中查看参数
SELECT name, setting, source FROM pg_settings;  -- 查看参数值及其来源

-- 查看运行时参数
SELECT name, setting FROM pg_settings WHERE source = 'runtime';  -- 仅查看运行时可修改的参数

3. 数据库对象信息

数据库对象包括表、索引、视图、函数和扩展等，了解这些对象的状态对于数据库维护至关重要。

sql

-- 查看当前数据库的表列表
\dt                      -- psql命令：查看当前数据库的表
SELECT tablename FROM pg_tables WHERE schemaname = 'public';  -- SQL命令：查看public模式下的表

-- 查看表结构
\d tablename             -- psql命令：查看指定表的结构

-- 查看索引列表
\di                      -- psql命令：查看当前数据库的索引
SELECT indexname FROM pg_indexes WHERE schemaname = 'public';  -- SQL命令：查看public模式下的索引

-- 查看视图列表
\dv                      -- psql命令：查看当前数据库的视图
SELECT viewname FROM pg_views WHERE schemaname = 'public';  -- SQL命令：查看public模式下的视图

-- 查看函数列表
\df                      -- psql命令：查看当前数据库的函数
SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace;  -- SQL命令：查看public模式下的函数

-- 查看扩展列表
\dx                      -- psql命令：查看当前数据库的扩展
SELECT extname FROM pg_extension;  -- SQL命令：查看当前数据库的扩展

性能信息收集

性能信息是数据库性能分析和调优的关键，包括连接信息、锁信息、表和索引统计信息等。

1. 连接和会话信息

连接和会话信息可以帮助我们了解数据库的负载情况和用户活动。

sql

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;  -- 查看当前所有连接数

-- 查看活跃连接
SELECT * FROM pg_stat_activity WHERE state = 'active';  -- 查看正在执行查询的连接

-- 查看阻塞连接
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL;  -- 查看正在等待资源的连接

-- 查看长时间运行的查询
SELECT pid, usename, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';  -- 查看运行超过5分钟的查询

2. 锁信息

锁信息对于诊断死锁和性能问题至关重要，它可以帮助我们了解数据库中的并发问题。

sql

-- 查看锁信息
SELECT * FROM pg_locks;  -- 查看所有锁信息

-- 查看锁等待（死锁检测）
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_query,
       blocking_activity.query   AS blocking_query
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;  -- 查找被阻塞的锁请求

3. 表和索引统计信息

表和索引统计信息可以帮助我们了解数据分布和索引使用情况，是性能调优的重要依据。

sql

-- 查看表大小
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;  -- 按大小降序排列

-- 查看索引大小
SELECT schemaname, indexname, tablename, pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC;  -- 按大小降序排列

-- 查看表的统计信息
SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE schemaname = 'public';  -- 查看表的行数、死行数和最后VACUUM/ANALYZE时间

-- 查看索引的使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_all_indexes
WHERE schemaname = 'public';  -- 查看索引的扫描次数和读取/获取的行数

4. 缓冲区使用情况

缓冲区使用情况可以帮助我们了解数据库的内存使用效率，对于性能调优至关重要。

sql

-- 查看缓冲区命中率
SELECT
  round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS buffer_hit_ratio
FROM pg_stat_database;  -- 计算缓冲区命中率，理想值应高于99%

-- 查看表的缓冲区使用情况
SELECT
  c.relname,
  pg_size_pretty(count(*) * 8192) AS buffered,  -- PostgreSQL默认块大小为8192字节
  round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer / 128, 2) AS buffered_percent
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 3 DESC
LIMIT 10;  -- 查看前10个占用缓冲区最多的表

日志信息收集

日志信息是故障诊断和性能分析的重要依据，PostgreSQL提供了详细的日志记录功能。

1. 日志配置信息

了解日志配置可以帮助我们确定日志的存储位置、格式和内容，便于后续的日志分析。

sql

-- 查看日志配置
SHOW log_destination;        -- 日志输出目标（stderr, csvlog等）
SHOW logging_collector;      -- 是否启用日志收集器
SHOW log_directory;          -- 日志文件存储目录
SHOW log_filename;           -- 日志文件名格式
SHOW log_rotation_age;       -- 日志文件滚动的时间间隔
SHOW log_rotation_size;      -- 日志文件滚动的大小阈值
SHOW log_line_prefix;        -- 日志行前缀格式
SHOW log_statement;          -- 记录哪些类型的语句

2. 日志文件查看

直接查看日志文件是最常用的日志分析方法，可以帮助我们快速定位问题。

bash

# 查看日志目录
ls -la $(psql -t -c "SHOW log_directory;")  -- 列出日志目录中的所有文件

# 查看最新的日志文件
tail -f $(psql -t -c "SHOW log_directory;")/$(ls -lt $(psql -t -c "SHOW log_directory;") | grep -v \. | head -n 1 | awk '{print $9}')  -- 实时跟踪最新日志文件

# 搜索错误日志
grep -i error $(psql -t -c "SHOW log_directory;")/*.log  -- 搜索所有日志文件中的错误信息

# 搜索慢查询日志
grep -i "duration:.*ms" $(psql -t -c "SHOW log_directory;")/*.log | sort -nr -k 3  -- 搜索慢查询并按执行时间降序排列

3. 慢查询日志分析

慢查询是性能问题的主要来源，分析慢查询日志可以帮助我们找出性能瓶颈。

sql

-- 查看慢查询配置
SHOW log_min_duration_statement;  -- 记录的最小语句执行时间
SHOW log_temp_files;              -- 是否记录临时文件信息

-- 使用pg_stat_statements查看慢查询（需要安装扩展）
SELECT queryid, query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;  -- 查看平均执行时间最长的10个查询

-- 重置pg_stat_statements统计
SELECT pg_stat_statements_reset();  -- 重置所有统计信息，谨慎使用

复制信息收集

对于使用复制的PostgreSQL集群，复制状态信息至关重要，可以帮助我们了解复制的健康状况。

1. 复制状态信息

sql

-- 查看主库复制状态
SELECT * FROM pg_stat_replication;  -- 主库查看所有连接的备库信息

-- 查看备库复制状态
SELECT * FROM pg_stat_wal_receiver;  -- 备库查看WAL接收器状态
SELECT * FROM pg_stat_recovery_prefetch;  -- 备库查看恢复预取状态

-- 查看复制延迟（主库视角）
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_delay
FROM pg_stat_replication;  -- 计算主备库之间的WAL位置差异

-- 查看复制槽信息
SELECT * FROM pg_replication_slots;  -- 查看所有复制槽的状态

信息收集脚本

手动收集信息效率较低，使用脚本可以自动收集和归档信息，便于后续分析和比较。

1. 综合信息收集脚本

下面是一个综合的PostgreSQL信息收集脚本，可以收集系统信息、数据库信息、性能信息和日志信息等。

bash

#!/bin/bash

# PostgreSQL信息收集脚本
# 该脚本用于自动收集PostgreSQL数据库的各种信息，便于故障诊断和性能分析

# 设置变量
PG_HOST="localhost"         # 数据库主机地址
PG_PORT="5432"             # 数据库端口
PG_USER="postgres"         # 数据库用户名
PG_DB="postgres"           # 连接的数据库
OUTPUT_DIR="/tmp/pg_info_$(date +%Y%m%d_%H%M%S)"  # 输出目录，包含时间戳

# 创建输出目录
mkdir -p $OUTPUT_DIR

# 收集系统信息
echo "收集系统信息..."
cat /etc/os-release > $OUTPUT_DIR/os_info.txt       # 操作系统信息
uname -a >> $OUTPUT_DIR/os_info.txt                  # 内核信息
lscpu > $OUTPUT_DIR/cpu_info.txt                    # CPU信息
free -h > $OUTPUT_DIR/mem_info.txt                  # 内存信息
df -h > $OUTPUT_DIR/disk_info.txt                   # 磁盘使用情况
ps aux | grep postgres > $OUTPUT_DIR/postgres_processes.txt  # PostgreSQL进程信息

# 收集数据库信息
echo "收集数据库信息..."
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT version();" > $OUTPUT_DIR/db_version.txt  # 数据库版本
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "\l" > $OUTPUT_DIR/db_list.txt  # 数据库列表
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SHOW ALL;" > $OUTPUT_DIR/db_config.txt  # 数据库配置
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM pg_stat_activity;" > $OUTPUT_DIR/db_connections.txt  # 连接信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM pg_stat_database;" > $OUTPUT_DIR/db_stats.txt  # 数据库统计信息

# 收集性能信息
echo "收集性能信息..."
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM pg_stat_all_tables;" > $OUTPUT_DIR/table_stats.txt  # 表统计信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM pg_stat_all_indexes;" > $OUTPUT_DIR/index_stats.txt  # 索引统计信息
psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM pg_locks;" > $OUTPUT_DIR/locks.txt  # 锁信息

# 收集日志信息
echo "收集日志信息..."
LOG_DIR=$(psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -t -c "SHOW log_directory;")  # 获取日志目录
if [ -d "$LOG_DIR" ]; then
    cp $LOG_DIR/*.log $OUTPUT_DIR/ 2>/dev/null  # 复制日志文件
    cp $LOG_DIR/*.csv $OUTPUT_DIR/ 2>/dev/null  # 复制CSV格式日志（如果有）
fi

# 压缩输出
echo "压缩输出文件..."
tar -czf ${OUTPUT_DIR}.tar.gz $OUTPUT_DIR  # 压缩输出目录
rm -rf $OUTPUT_DIR  # 删除临时目录

echo "信息收集完成！输出文件：${OUTPUT_DIR}.tar.gz"

信息收集最佳实践

1. 定期收集信息

对于生产环境，建议每天自动收集一次完整信息
对于关键业务系统，可以每小时收集一次性能相关信息
在系统变更前后收集信息，便于比较和分析变更影响

2. 合理存储信息

建立合理的日志轮换机制，避免日志过大
定期归档历史信息，便于后续分析和审计
对于敏感信息，注意保护和加密

3. 结合监控工具

将信息收集与监控工具（如Prometheus、Grafana等）结合使用
建立告警机制，及时发现和处理异常情况
定期分析收集的信息，持续优化数据库性能

常见问题（FAQ）

Q1：如何收集PostgreSQL的慢查询信息？

A1：可以通过以下方式收集慢查询信息：

配置log_min_duration_statement参数，记录超过指定时间的查询
使用pg_stat_statements扩展，实时监控查询性能
分析日志文件中的慢查询记录
使用pgBadger等工具分析慢查询日志

Q2：如何查看PostgreSQL的连接数？

A2：可以使用以下命令查看连接数：

sql

SELECT count(*) FROM pg_stat_activity;

Q3：如何查看PostgreSQL的锁信息？

A3：可以使用以下命令查看锁信息：

sql

SELECT * FROM pg_locks;

Q4：如何查看PostgreSQL的缓冲区命中率？

A4：可以使用以下命令查看缓冲区命中率：

sql

SELECT round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS buffer_hit_ratio
FROM pg_stat_database;

Q5：如何收集PostgreSQL的复制状态信息？

A5：可以使用以下命令收集复制状态信息：

sql

-- 主库查看备库信息
SELECT * FROM pg_stat_replication;

-- 备库查看自身状态
SELECT * FROM pg_stat_wal_receiver;

Q6：如何使用脚本自动收集PostgreSQL信息？

A6：可以使用上面提供的综合信息收集脚本，或者根据实际需求编写自定义脚本。脚本可以定期执行，自动收集和归档信息，便于故障诊断和性能分析。建议将脚本添加到crontab中定期执行，例如：

bash

# 每天凌晨2点执行信息收集脚本
0 2 * * * /path/to/pg_info_collector.sh

Q7：收集的信息如何用于故障诊断？

A7：收集的信息可以用于以下故障诊断场景：

系统级问题：通过操作系统信息和进程信息诊断硬件或系统级故障
数据库连接问题：通过连接信息诊断连接泄漏或连接数过多问题
性能问题：通过慢查询信息和缓冲区使用情况诊断性能瓶颈
复制问题：通过复制状态信息诊断主备同步问题
锁问题：通过锁信息诊断死锁或长时间阻塞问题

Q8：如何保护收集的敏感信息？

A8：保护敏感信息的方法包括：

对收集的信息进行加密存储
限制访问收集信息的用户和权限
定期清理过期的敏感信息
在收集过程中过滤掉敏感数据（如密码、个人信息等）
遵守相关法规和合规性要求（如GDPR、PCI-DSS等）

PostgreSQL 信息收集 ​

系统信息收集 ​

1. 操作系统信息 ​

2. PostgreSQL进程信息 ​

数据库信息收集 ​

1. 数据库基本信息 ​

2. 数据库配置信息 ​

3. 数据库对象信息 ​

性能信息收集 ​

1. 连接和会话信息 ​

2. 锁信息 ​

3. 表和索引统计信息 ​

4. 缓冲区使用情况 ​

日志信息收集 ​

1. 日志配置信息 ​

2. 日志文件查看 ​

3. 慢查询日志分析 ​

复制信息收集 ​

1. 复制状态信息 ​

信息收集脚本 ​

1. 综合信息收集脚本 ​

信息收集最佳实践 ​

1. 定期收集信息 ​

2. 合理存储信息 ​

3. 结合监控工具 ​

常见问题（FAQ） ​

Q1：如何收集PostgreSQL的慢查询信息？ ​

Q2：如何查看PostgreSQL的连接数？ ​

Q3：如何查看PostgreSQL的锁信息？ ​

Q4：如何查看PostgreSQL的缓冲区命中率？ ​

Q5：如何收集PostgreSQL的复制状态信息？ ​

Q6：如何使用脚本自动收集PostgreSQL信息？ ​

Q7：收集的信息如何用于故障诊断？ ​

Q8：如何保护收集的敏感信息？ ​

PostgreSQL 信息收集

系统信息收集

1. 操作系统信息

2. PostgreSQL进程信息

数据库信息收集

1. 数据库基本信息

2. 数据库配置信息

3. 数据库对象信息

性能信息收集

1. 连接和会话信息

2. 锁信息

3. 表和索引统计信息

4. 缓冲区使用情况

日志信息收集

1. 日志配置信息

2. 日志文件查看

3. 慢查询日志分析

复制信息收集

1. 复制状态信息

信息收集脚本

1. 综合信息收集脚本

信息收集最佳实践

1. 定期收集信息

2. 合理存储信息

3. 结合监控工具

常见问题（FAQ）

Q1：如何收集PostgreSQL的慢查询信息？

Q2：如何查看PostgreSQL的连接数？

Q3：如何查看PostgreSQL的锁信息？

Q4：如何查看PostgreSQL的缓冲区命中率？

Q5：如何收集PostgreSQL的复制状态信息？

Q6：如何使用脚本自动收集PostgreSQL信息？

Q7：收集的信息如何用于故障诊断？

Q8：如何保护收集的敏感信息？