Debian中PostgreSQL如何进行故障排查

首先确认PostgreSQL服务是否正在运行，使用以下命令查看服务状态：

sudo systemctl status postgresql

若服务未启动，可通过sudo systemctl start postgresql手动启动；若启动失败，需查看启动日志获取具体原因：

sudo tail -f /var/lib/pgsql/data/pg_log/postgresql-$(date +%Y-%m-%d).log # 根据实际日期调整

PostgreSQL的日志文件默认存储在/var/log/postgresql/目录下（如postgresql-15-main.log），可通过以下命令实时查看最新日志：

sudo tail -f /var/log/postgresql/postgresql-$(ls /var/log/postgresql/ | grep -E 'postgresql-[0-9]+-main\.log' | sort -V | tail -1)

日志分析可借助pgBadger工具（需提前安装）：

sudo apt install pgbadger pgbadger /var/log/postgresql/postgresql-*.log -o /var/log/pgbadger/report.html # 生成HTML格式分析报告

日志中重点关注ERROR、FATAL级别的错误信息（如连接失败、磁盘空间不足、查询超时等）。

无法连接数据库：
- 检查防火墙是否放行PostgreSQL默认端口（5432）：
```
sudo ufw allow 5432/tcp # 使用ufw防火墙 sudo iptables -L -n | grep 5432 # 使用iptables防火墙 
```
- 确认pg_hba.conf文件（位于/etc/postgresql/<version>/main/）是否允许目标IP连接（如添加host all all 0.0.0.0/0 md5允许所有IP通过密码认证）。

慢查询分析：使用EXPLAIN命令查看查询执行计划，识别性能瓶颈（如缺少索引）：
```
EXPLAIN SELECT * FROM users WHERE age > 30; -- 查看查询计划 
```
若查询未使用索引，可创建索引加速：
```
CREATE INDEX idx_age ON users(age); -- 创建普通索引 
```
更新统计信息：使用ANALYZE命令让查询优化器获取最新数据分布，提升查询计划准确性：
```
ANALYZE; -- 更新所有表的统计信息 
```
启用pg_stat_statements：收集SQL语句执行统计信息，识别高频慢查询（需修改postgresql.conf并重启服务）：
```
shared_preload_libraries = 'pg_stat_statements' # 取消注释并添加 pg_stat_statements.track = all 
```
然后执行\dx命令在psql中启用扩展，并通过pg_stat_statements视图查看慢查询。

检查磁盘空间：使用df -h命令查看各分区使用情况，重点关注PostgreSQL数据目录所在分区（如/var/lib/postgresql/<version>/main）：
```
df -h /var/lib/postgresql/ 
```

清理WAL日志：若WAL日志堆积导致磁盘满，可清理过期WAL文件（需确认归档机制正常）：

sudo pg_archivecleanup /var/lib/postgresql/<version>/main/pg_wal 0000000100000001000000AB # 替换为实际文件名

检查复制槽：废弃的复制槽会导致WAL日志无法删除，可通过以下命令查看并删除：

SELECT * FROM pg_replication_slots; -- 查看复制槽 SELECT pg_drop_replication_slot('slot_name'); -- 删除废弃复制槽

优化检查点配置：调整postgresql.conf中的检查点参数，延长检查点间隔，减少WAL日志生成频率：

max_wal_size = 20GB # 最大WAL大小 min_wal_size = 5GB # 最小WAL大小 checkpoint_timeout = 30min # 检查点超时时间 wal_compression = on # 启用WAL压缩

查看内存使用：使用free -m命令查看内存占用，若内存不足，可调整work_mem（排序、哈希操作的内存限制）或优化查询减少内存消耗：
```
work_mem = '4MB' # 根据服务器内存调整（默认4MB） 
```

查看CPU使用：使用top或htop命令查看PostgreSQL进程的CPU占用，若某个查询占用过高，可使用pg_stat_activity视图定位并终止：

SELECT * FROM pg_stat_activity WHERE state = 'active'; -- 查看活跃查询 SELECT pg_terminate_backend(pid); -- 终止指定PID的查询（谨慎使用）

PostgreSQL的主配置文件位于/etc/postgresql/<version>/main/postgresql.conf，需检查以下关键参数：

pg_stat_activity：查看当前活动连接、查询状态及等待事件（如锁等待）：

SELECT datname, usename, application_name, client_addr, state, query FROM pg_stat_activity; -- 查看所有活动连接

pg_stat_statements：识别高频慢查询（需提前启用）：

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 查看耗时最长的10条查询

若使用SSL连接，需确保证书正确配置：

检查证书文件路径（postgresql.conf中的ssl_cert_file、ssl_key_file）是否存在且权限正确（chmod 600）；

测试SSL连接：

openssl s_client -connect localhost:5432 -showcerts # 测试SSL握手

客户端连接时需指定--ssl参数：

psql -h localhost -p 5432 -U username -d database --ssl

权限问题：确保用户对数据库、表有足够的权限（如使用GRANT语句授予权限）；
表空间溢出：使用pg_tablespaces视图查看表空间使用情况，若空间不足可扩展或清理数据：
```
SELECT * FROM pg_tablespaces; -- 查看表空间 
```
重启服务：若以上方法均无效，可尝试重启PostgreSQL服务（谨慎使用，可能导致短暂停机）：
```
sudo systemctl restart postgresql 
```

通过以上步骤，可系统排查Debian系统中PostgreSQL的常见故障。若问题仍未解决，建议参考PostgreSQL官方文档或寻求专业支持。

最新问答