ubuntu postgresql故障排查

Ubuntu 上 PostgreSQL 故障排查速查

一 快速定位流程

• 确认服务状态与实例：使用命令查看服务与集群状态，确认是否为期望的版本与端口在运行。
  示例：sudo systemctl status postgresql；Debian/Ubuntu 多版本可用 pg_lsclusters 查看所有实例（如 12/main、14/main）。
• 查看日志定位根因：PostgreSQL 日志通常位于 /var/log/postgresql/，按版本查看如 sudo tail -f /var/log/postgresql/postgresql-14-main.log，优先从日志中的 FATAL/ERROR 行入手。
• 核对端口占用：确认 5432 未被其他进程占用，必要时调整 postgresql.conf 的 port 或释放占用进程。
• 核对关键配置：检查 postgresql.conf 的 listen_addresses、port，以及 pg_hba.conf 的本地/远程认证规则是否匹配当前连接方式。
• 检查数据目录与权限：确保数据目录（如 /var/lib/postgresql/14/main）存在且属主为 postgres:postgres，权限正确。
• 配置生效：修改配置后执行 sudo systemctl reload postgresql（或针对实例 sudo systemctl reload postgresql@14-main）。

二 常见症状与修复要点

• 症状 A：psql 报 “could not connect to server: No such file or directory … /var/run/postgresql/.s.PGSQL.5432”
  可能原因：服务器未运行、Unix 域套接字目录不一致、套接字文件未生成或被清理。
  处理步骤：

  1. 确认服务已启动：sudo systemctl status postgresql；若未运行，启动服务并再次检查日志。
  2. 检查套接字文件：在常见目录查找如 find /tmp -name .s.PGSQL.5432 与 find /var/run/postgresql -name .s.PGSQL.5432；若仅存在于 /tmp 而客户端默认查找 /var/run/postgresql，可创建符号链接：sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432（注意目录权限与重启后可能重建）。
  3. 仍失败时，回到日志定位启动失败的根因。

• 症状 B：启动失败或反复重启，日志提示 “FATAL: database files are incompatible with server”
  可能原因：跨主版本直接读取数据目录（如 PG 12 数据目录被 PG 14 读取）。
  处理步骤：

  1. 保留旧版本并安装新版本（示例在 Ubuntu 22.04 保留 12、安装 14）。
  2. 安装对应扩展（如 PostGIS）到两个版本：postgresql-12-postgis-2.5、postgresql-14-postgis-3。
  3. 使用 pg_upgrade 迁移：先初始化新数据目录，再执行升级（可按需使用 --link 减少拷贝）。
  4. 启动新集群并做数据一致性校验与回归测试。

• 症状 C：本地连接报 “FATAL: Peer authentication failed for user ‘postgres’” 或 “Ident authentication failed”
  可能原因：pg_hba.conf 对本地连接使用了 peer/ident，而当前 OS 用户与数据库用户不匹配。
  处理步骤：

  1. 临时方案（测试环境）：在 pg_hba.conf 将对应行改为 trust 或 md5，然后 sudo systemctl reload postgresql。
  2. 推荐方案：使用与数据库同名的 OS 用户并通过 sudo -u postgres psql 连接，或按需创建同名数据库用户并配置合适的认证方式。
  3. 远程连接需同时配置 listen_addresses 与 pg_hba.conf 的 host 规则（如 192.168.1.0/24 md5）。

• 症状 D：端口 5432 被占用或配置冲突
  可能原因：其他进程占用 5432，或 postgresql.conf 的 port 设置异常。
  处理步骤：

  1. 检查占用：sudo netstat -tulpen | grep 5432（或 ss -lntp | grep 5432）。
  2. 释放占用进程或修改 postgresql.conf 的 port，重启服务。

• 症状 E：SSL 密钥权限错误导致启动失败
  可能原因：如 /etc/ssl/private/ssl-cert-snakeoil.key 权限过宽。
  处理步骤：

  1. 修正权限与属主：sudo chown postgres /etc/ssl/private/ssl-cert-snakeoil.key，sudo chgrp postgres ...，sudo chmod 740 ...。
  2. 重启服务。

三 常用命令清单

• 服务与实例：sudo systemctl status postgresql、sudo systemctl restart postgresql、pg_lsclusters
• 日志：sudo tail -f /var/log/postgresql/postgresql-14-main.log
• 端口检查：sudo ss -lntp | grep 5432 或 sudo netstat -tulpen | grep 5432
• 套接字文件：find /tmp -name .s.PGSQL.5432、find /var/run/postgresql -name .s.PGSQL.5432
• 配置与权限：sudo -u postgres psql -c "\du"（查看角色）、检查 /var/lib/postgresql/ 属主与权限
• 配置生效：sudo systemctl reload postgresql（或 sudo systemctl reload postgresql@14-main）

四 升级与兼容性注意

• 跨主版本（如 12 → 14）不可直接读取数据目录，需通过 pg_upgrade 或逻辑导出/导入迁移，并提前处理好扩展（如 PostGIS）的版本匹配与升级路径。
• 升级前务必全量备份，升级后执行数据一致性校验与性能回归测试，再切换生产流量。

五 风险提示

• 修改 pg_hba.conf 为 trust 或调整 SSL 密钥权限会降低安全性，仅应在受控环境、测试场景或变更窗口内短时采用，并尽快恢复为更安全的认证方式。
• 执行 pg_upgrade、initdb、pg_resetwal 等高风险操作前请先做好完整备份，并在非生产环境充分演练。