PostgreSQL在Ubuntu上的故障排查方法

Ubuntu 上 PostgreSQL 故障排查速查

一 快速定位流程

• 确认服务状态与实例
  • 查看服务：sudo systemctl status postgresql（或 systemctl status postgresql@15-main 按实例名）
  • 查看端口：ss -lntp | grep 5432；若被占用，停占用进程或更换端口
• 查看日志定位根因
  • 实例日志：sudo tail -f /var/log/postgresql/postgresql-9.x-main.log
  • 系统日志：sudo journalctl -u postgresql -b 或 -xe
• 本地连接测试
  • 本机 socket：psql -U postgres -h /var/run/postgresql -c “select 1”
  • TCP 连接：psql -U postgres -h 127.0.0.1 -p 5432 -c “select 1”
• 远程连通性
  • 网络：nc -vz 服务器IP 5432 或 telnet IP 5432
  • 防火墙：sudo ufw allow 5432/tcp（云主机还需安全组放行）
• 认证与配置
  • 核对 pg_hba.conf 的 METHOD（peer/md5/scram-sha-256）与客户端来源网段
  • 核对 postgresql.conf 的 listen_addresses、port、unix_socket_directories

二 常见故障与修复要点

• 无法启动或端口冲突
  • 检查端口占用并释放或改端口；确认数据目录存在且权限正确（属主为 postgres:postgres）；查看实例日志报错细节后对症处理（如修复配置、目录权限、磁盘空间等）
• Unix 域套接字找不到
  • 典型报错：could not connect to server: No such file or directory … /var/run/postgresql/.s.PGSQL.5432
  • 处理：确认服务已启动；检查 postgresql.conf 的 unix_socket_directories 与实际套接字目录是否一致；必要时重启服务或创建符号链接（ln -s）使客户端库能找到套接字文件
• SSL 密钥权限错误
  • 典型报错：private key file … ssl-cert-snakeoil.key 权限过宽
  • 处理：chown postgres:postgres 密钥文件；chmod 640/600；重启服务
• 数据目录在外部磁盘或 USB 上启动失败
  • 典型现象：systemd 报 Result: protocol，日志提示数据目录不可访问；原因是开机早于外置盘挂载
  • 处理：为实例添加启动延迟，例如创建 /etc/systemd/system/postgresql@15-main.service.d/override.conf，内容 [Service] ExecStartPre=/bin/sleep 5，然后 systemctl daemon-reload 并重启验证
• 共享内存或信号量不足
  • 典型报错：FATAL: could not create semaphores: No space left on device（并非磁盘满，而是 SEMMNS/SEMMNI 等内核限制）
  • 处理：临时降低 max_connections；或调大内核参数（如 kernel.sem）；必要时清理残留的 System V IPC 资源（ipcs/ipcrm）

三 性能与高可用排查

• 活跃会话与慢查询
  • 活跃与长时查询：SELECT pid, now() - query_start AS dur, query FROM pg_stat_activity WHERE state = ‘active’ AND now() - query_start > interval ‘1 min’;
  • 阻塞关系：SELECT blocked.pid AS blocked_pid, blocked.query, blocking.pid AS blocking_pid, blocking.query FROM pg_locks blocked JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid JOIN pg_locks blocking ON … JOIN pg_stat_activity blocking_activity ON …;
• 等待事件与锁
  • 等待事件：SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event IS NOT NULL;
• 复制与延迟（主备）
  • 主库复制状态：SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;
  • 备库回放进度：SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
  • 延迟秒数：SELECT EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) AS replication_lag;
• 资源与 I/O
  • 热点表/索引：SELECT relname, heap_blks_read, heap_blks_hit FROM pg_statio_user_tables ORDER BY heap_blks_read DESC LIMIT 10;
  • 刷写统计：SELECT * FROM pg_stat_bgwriter;

四 常用命令与配置路径速查

• 服务与日志
  • 状态/启动/重启：sudo systemctl status|start|restart postgresql（或 postgresql@15-main）
  • 日志：/var/log/postgresql/postgresql-9.x-main.log；journalctl -u postgresql -b
• 连接测试
  • 本机 socket：psql -U postgres -h /var/run/postgresql -c “select 1”
  • 本机 TCP：psql -U postgres -h 127.0.0.1 -p 5432 -c “select 1”
  • 远程：psql -U 用户名 -h IP -p 5432 -d 数据库名
• 配置与目录
  • 主配置：/etc/postgresql/9.x/main/postgresql.conf（常见项：listen_addresses、port、unix_socket_directories）
  • 认证：/etc/postgresql/9.x/main/pg_hba.conf
  • 数据目录：/var/lib/postgresql/9.x/main（权限应为 postgres:postgres）
  • 套接字目录：常见为 /var/run/postgresql 或 /tmp（以 postgresql.conf 为准）
• 网络与防火墙
  • 放行端口：sudo ufw allow 5432/tcp；云主机安全组同样需要放行

五 安全与变更建议

• 修改认证方式（如从 peer 改为 md5/scram-sha-256）后，先在 pg_hba.conf 中对受控来源放宽，验证无误再收紧；变更后用 sudo systemctl reload postgresql 使配置生效
• 调整 max_connections 等参数需权衡资源，必要时先降连接数或优化连接池；涉及内核参数（如 kernel.sem）变更前请在测试环境验证并评估系统影响
• 对数据目录、外部挂载盘、SSL 证书等涉及安全与可用性的变更，务必先做好备份与回滚方案，并在灰度/维护窗口内执行