PostgreSQL在Ubuntu上的故障排查技巧有哪些

Ubuntu 上 PostgreSQL 故障排查技巧

一 快速定位连接失败

• 确认服务是否运行：使用 systemctl status postgresql 或 service postgresql status；若提示 Unit postgresql.service could not be found，说明未安装或未正确启用服务。
• 区分本地套接字与 TCP 连接：
  • 本地报错如 “could not connect to server: No such file or directory … /var/run/postgresql/.s.PGSQL.5432”，多为套接字未创建或目录不匹配。检查 postgresql.conf 中的 unix_socket_directories（常见为 /var/run/postgresql 或 /tmp），确认对应目录下是否存在 .s.PGSQL.5432；必要时重启服务以重建套接字。
  • 远程报错 “Connection refused”，优先检查 postgresql.conf 的 listen_addresses（如设为 ‘*’ 或具体网卡）与 port=5432，以及 pg_hba.conf 是否允许客户端来源网段与认证方式。
• 端口与进程冲突：用 sudo netstat -tulpen | grep 5432 检查是否被其他进程占用；若冲突，停止占用进程或调整端口。
• 日志优先：查看 /var/log/postgresql/ 下对应实例日志，例如 tail -f /var/log/postgresql/postgresql-*-main.log，从启动失败或认证拒绝的具体行入手。

二 启动失败与权限类问题

• 数据目录与权限：确认数据目录（如 /var/lib/postgresql/ 或自定义目录）存在且属主为 postgres:postgres，权限为 700/750；异常权限会直接导致启动失败。
• SSL 密钥权限：若日志出现 “private key file … has group or world access”，将密钥权限收紧，例如：
  • sudo chown postgres:postgres /etc/ssl/private/ssl-cert-snakeoil.key
  • sudo chmod 640 /etc/ssl/private/ssl-cert-snakeoil.key
• 外部存储延迟挂载：当数据目录位于 USB/HDD 等外置盘时，系统启动早期可能尚未挂载就绪，导致启动失败或 PID 文件无法创建。可通过 systemctl edit postgresql@15-main 增加 ExecStartPre=/bin/sleep 5 延迟启动，然后 systemctl daemon-reload 并重启验证。
• 套接字目录与运行时目录：若 /var/run/postgresql 为 tmpfs，重启后可能丢失，需确保服务以正确的 RuntimeDirectory=postgresql 配置启动，或调整 unix_socket_directories 到持久/正确的运行时目录。

三 运行期性能与异常查询定位

• 活跃会话与阻塞：在 psql 中查询 pg_stat_activity，定位长时间运行、等待锁或异常会话，必要时使用 pg_terminate_backend(pid) 结束问题会话。
• 执行计划与慢查询：用 EXPLAIN (ANALYZE, BUFFERS) 分析具体 SQL 的执行路径与代价；配合 auto_explain 模块自动记录慢查询计划。
• 语句级统计：启用 pg_stat_statements（在 shared_preload_libraries 中加载并重启），统计最耗时/最频繁语句，用于索引与 SQL 改写优化。
• 日志分析与可视化：使用 pgBadger 将 /var/log/postgresql/ 日志转为 HTML 报告，快速发现错误趋势与慢查询分布。

四 常用命令与配置速查

• 服务与实例管理：
  • 查看状态：systemctl status postgresql 或 systemctl status postgresql@15-main
  • 重启：systemctl restart postgresql
• 日志与目录：
  • 实时看日志：tail -f /var/log/postgresql/postgresql-*-main.log
  • 数据目录：常见为 /var/lib/postgresql/（权限与属主需正确）
• 端口与进程：
  • 检查占用：sudo netstat -tulpen | grep 5432
• 配置要点：
  • postgresql.conf：listen_addresses、port、unix_socket_directories
  • pg_hba.conf：客户端网段、METHOD（如 md5/peer/scram-sha-256）、认证顺序
• 延迟启动（外置盘场景）：
  • 编辑覆盖：sudo systemctl edit postgresql@15-main，加入 [Service] ExecStartPre=/bin/sleep 5，然后 systemctl daemon-reload 并重启验证。