精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

常见 PostgreSQL 连接错误排查指南

通过检查服务状态、监听地址、pg_hba.conf、凭据、DNS、SSL 和日志来排查 PostgreSQL 连接错误。

常见 PostgreSQL 连接错误排查指南

PostgreSQL 连接错误看起来相似，直到你放慢速度并仔细阅读确切的错误信息。Connection refused 通常意味着客户端到达了主机，但该地址和端口上没有服务接受 TCP 连接。Connection timed out 通常意味着数据包从未收到有用的回复。password authentication failed 意味着服务器已到达但拒绝了凭据。no pg_hba.conf entry 意味着 PostgreSQL 未找到匹配的访问规则。

这个顺序很重要。从外到内逐步排查：主机、端口、服务、监听器、防火墙、pg_hba.conf、用户、数据库、密码、SSL，然后是应用程序连接池。在服务未监听时猜测密码会浪费时间。当 DNS 指向错误的主机时编辑 pg_hba.conf 也会浪费时间。

理解 PostgreSQL 连接基础

在深入探讨具体错误之前，理解 PostgreSQL 如何处理连接至关重要。PostgreSQL 采用客户端-服务器模型。客户端（例如 psql 命令行工具、Web 应用程序或桌面客户端）尝试连接到 PostgreSQL 服务器进程。该进程通常监听特定网络接口和端口（默认为 5432）上的传入连接。

有两个主要配置文件控制连接的接受和认证方式：

  • postgresql.conf：控制常规服务器行为，包括要监听的网络接口（listen_addresses）和端口（port）。
  • pg_hba.conf：（基于主机的认证）规定谁可以从哪里连接到哪个数据库，使用什么认证方法。此文件对于安全性和访问控制至关重要。

理解这些文件的作用以及客户端-服务器交互是有效排查问题的基础。

常见连接错误及解决方案

让我们分解你可能遇到的最常见的连接错误及其相应的解决方案。

错误 1：FATAL: database "..." does not exist

此错误意味着客户端指定的数据库名称在 PostgreSQL 服务器上不存在。

解释： 客户端应用程序或 psql 命令尝试连接到一个尚未创建或名称拼写错误的数据库。

解决方案：

  1. 验证数据库名称： 确保连接字符串或 psql 命令中的数据库名称正确。
  2. 列出现有数据库： 连接到默认数据库（如 postgres 或 template1）并使用 \l（或 \list）列出所有可用数据库。
# 尝试连接到默认的 'postgres' 数据库
psql -U your_username -h your_host -d postgres

# 连接后，列出所有数据库
\l

# 创建缺失数据库的示例
CREATE DATABASE my_app_db;

错误 2：FATAL: role "..." does not exist

这表明连接指定的用户名（角色）不存在。

解释： 类似于数据库错误，尝试连接的用户账户要么不存在，要么拼写错误。

解决方案：

  1. 验证用户名： 检查连接字符串中的用户名。
  2. 列出现有角色： 使用超级用户账户（例如 postgres 用户）连接，并使用 \du 列出所有角色。
# 以默认的 'postgres' 超级用户连接
psql -U postgres -h your_host -d postgres

# 列出所有角色（用户）
\du

# 创建缺失角色的示例
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'my_strong_password';

错误 3：FATAL: password authentication failed for user "..."

这是一个常见错误，表示提供的密码与指定用户的密码不匹配。

解释： 客户端提供的密码与 PostgreSQL 用户（角色）存储的密码不匹配。

解决方案：

  1. 检查应用程序配置： 查看应用程序的连接字符串或环境变量，确保密码正确。

  2. 重置密码（如果你有超级用户访问权限）：

    # 以 postgres 超级用户连接
psql -U postgres -h your_host -d postgres

# 更改有问题的用户的密码
ALTER USER my_app_user WITH PASSWORD 'new_strong_password';

    提示： 确保 pg_hba.conf 中该用户的条目指定了基于密码的认证方法（例如 md5、scram-sha-256），而不是 trust 或 ident（如果你打算使用密码）。

错误 4：FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on

此错误是 pg_hba.conf 配置问题，意味着服务器根据其访问规则明确拒绝了连接。

解释： pg_hba.conf 文件不包含与传入连接参数（客户端 IP、用户、数据库和认证方法）匹配的规则。

解决方案：

  1. 找到 pg_hba.conf： 位置因操作系统和安装方法而异（例如，Debian/Ubuntu 上为 /etc/postgresql/14/main/pg_hba.conf，或在 psql 中通过 SHOW hba_file; 指定）。

  2. 编辑 pg_hba.conf： 添加或修改条目以允许连接。一个允许从任何地方使用密码认证进行连接的常见条目如下所示：

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    my_app_db       my_app_user     203.0.113.25/32         scram-sha-256
    • TYPE：host 用于 TCP/IP 连接。
    • DATABASE：all（或特定数据库名称）。
    • USER：all（或特定用户名）。
    • ADDRESS：客户端的 IP 地址范围（例如 192.168.1.0/24、本地连接的 127.0.0.1/32，或单个公共客户端 IP）。
    • METHOD：认证方法。在客户端支持的情况下，现代 PostgreSQL 部署中密码认证首选 scram-sha-256。

    警告： 除非前面有非常谨慎的网络控制，否则避免使用 host all all 0.0.0.0/0 ...。狭窄的数据库、角色和 CIDR 更容易发现错误。

  3. 重新加载 PostgreSQL： 编辑 pg_hba.conf 后，你必须重新加载 PostgreSQL 配置才能使更改生效。

    # 在基于 systemd 的系统上

sudo systemctl reload postgresql

# 或使用 pg_ctl（需要指定数据目录）
# pg_ctl reload -D /var/lib/postgresql/14/main
```

错误 5：could not connect to server: Connection refused (0x0000274D/10061)

这是一个通用错误，表示客户端无法建立与 PostgreSQL 服务器的连接。服务器主动拒绝了连接尝试，通常是因为目标 IP/端口上没有服务在监听。

解释： 这通常指向以下原因之一：

  • PostgreSQL 服务未运行。
  • PostgreSQL 未在预期的网络接口或端口上监听。
  • 防火墙阻止了连接。

解决方案：

  1. PostgreSQL 是否在运行？

    • 检查服务状态：
      sudo systemctl status postgresql
# 或者，对于较旧的系统/其他设置：
# sudo service postgresql status
      如果未运行，启动它：
      sudo systemctl start postgresql
    • 检查日志： 查看 PostgreSQL 日志（例如 /var/log/postgresql/）以了解启动错误。

  2. 它是否在正确的地址/端口上监听？

    • 检查 postgresql.conf： 确保 listen_addresses 配置正确。对于来自其他主机的连接，它应为 * 或服务器网络接口的特定 IP 地址，而不仅仅是 localhost（127.0.0.1）。
      # 在 postgresql.conf 中
listen_addresses = '*'    # 在所有可用的网络接口上监听
port = 5432               # 默认端口
      更改 listen_addresses 后，你必须重启 PostgreSQL（仅重新加载是不够的）。
      sudo systemctl restart postgresql
    • 验证监听端口： 使用 netstat 或 ss 检查 PostgreSQL 是否实际在端口 5432（或你配置的端口）上监听。
      sudo ss -ltnp | grep 5432
# 预期输出示例：
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
      如果你没有看到 0.0.0.0:5432 或 your_server_ip:5432，则 PostgreSQL 可能仅在 127.0.0.1:5432 上监听或根本没有监听。

  3. 防火墙是否阻止了连接？

    • 服务器端防火墙： 检查 ufw（Ubuntu/Debian）、firewalld（CentOS/RHEL）或 iptables，确保端口 5432 已开放以接受来自客户端 IP 地址的传入连接。
      # UFW 示例
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# firewalld 示例
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
    • 客户端防火墙： 不太常见，但确保客户端的防火墙没有阻止到服务器端口 5432 的出站连接。

错误 6：timeout expired 或 connection timed out

此错误表明客户端尝试连接，但在指定时间内未收到服务器的响应。

解释： 与主动拒绝的 Connection refused 不同，超时意味着连接尝试从未到达服务器或服务器未响应。这通常指向网络连接问题或服务器严重过载。

解决方案：

  1. 网络连接：
    • Ping 服务器： ping server_ip_address。如果 ping 失败，则存在基本的网络问题（电缆、路由器、服务器离线）。
    • Traceroute/MTR： traceroute server_ip_address（Linux/macOS）或 tracert server_ip_address（Windows）可以帮助确定连接在网络路径上的哪个位置失败。
  2. 服务器 listen_addresses 和防火墙： 重新审视错误 5 的解决方案，因为配置错误的 listen_addresses 或防火墙也可能导致超时（如果服务器不可达）。
  3. 服务器负载： 如果服务器处于极端负载下（高 CPU、低内存、过多的磁盘 I/O），它可能太忙而无法及时接受新连接，从而导致超时。检查系统资源利用率。

错误 7：SSL Required, SSL Disabled, or Certificate Verification Failed

根据服务器设置和 pg_hba.conf 规则，PostgreSQL 可以接受加密和未加密的连接。客户端可能会因 SSL 关闭、SSL 要求或证书验证失败而失败。

检查三个地方：

# 查看服务器是否启用了 SSL
psql -U postgres -d postgres -c "SHOW ssl;"

# 尝试从客户端进行加密连接
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# 如果需要证书验证，请使用 verify-full 和受信任的根证书
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

sslmode=require 加密连接，但不像 verify-full 那样验证服务器身份。对于内部开发，这可能就足够了。对于跨不受信任网络的生产流量，请使用证书验证，并确保连接字符串中的主机名与证书匹配。

另外，检查 pg_hba.conf 是否使用了 hostssl 或 hostnossl。hostssl 规则不会匹配非 SSL 连接，而 hostnossl 规则不会匹配 SSL 连接。

错误 8：Too Many Clients Already

如果 PostgreSQL 返回 FATAL: sorry, too many clients already，则连接路径是正常的。服务器拒绝新会话是因为已达到 max_connections，或者只剩下保留的超级用户插槽。

首先，查看已连接的内容：

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

然后寻找模式。数百个 idle 会话通常指向配置过大的应用程序连接池、工作进程泄漏或每个都打开自己连接池的多个应用程序副本。增加 max_connections 可以争取时间，但也会增加内存压力，因为每个后端都有开销并可能使用 work_mem。在大多数 Web 应用程序中，使用事务池模式的 PgBouncer 是比让每个应用程序进程持有许多直接 PostgreSQL 会话更好的长期解决方案。

快速分类流程

当有人说“数据库宕机”时，我在接触配置之前使用一个简短路径：

# 1. 名称是否解析到预期的主机？
getent hosts db.example.com

# 2. 从此客户端是否可以访问 TCP 端口？
nc -vz db.example.com 5432

# 3. psql 能否使用相同的主机、端口、用户和数据库连接？
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. PostgreSQL 日志记录了失败尝试的什么内容？
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

如果 nc 失败，则停留在网络和监听器领域。如果 nc 成功但 psql 失败并显示 FATAL，则 PostgreSQL 可达，答案通常在认证、数据库名称、角色名称、SSL 模式或 pg_hba.conf 中。

一般排查步骤

当遇到持续连接问题时，请遵循以下一般步骤进行系统诊断：

  1. 检查 PostgreSQL 日志： 日志文件是你最好的朋友。它们包含有关启动问题、错误和拒绝连接尝试的详细信息。位置通常在 postgresql.conf 的 log_directory 中指定（例如，Debian/Ubuntu 上为 /var/log/postgresql/，或数据目录中的 pg_log）。

    # 检查最近日志的示例
sudo tail -f /var/log/postgresql/postgresql-14-main.log

  2. 验证配置文件： 仔细检查 postgresql.conf 和 pg_hba.conf 是否存在语法错误、拼写错误或不正确的值。即使一个字符放错位置也可能阻止服务器启动或接受连接。

  3. 重启 PostgreSQL（作为配置更改的最后手段）： 虽然 reload 通常足以处理 pg_hba.conf 和某些 postgresql.conf 参数，但某些关键更改（如 listen_addresses）需要完全重启。

    sudo systemctl restart postgresql

  4. 在服务器上本地测试： 如果从远程机器连接失败，请尝试直接在服务器上连接。这有助于确定问题是服务器端还是网络相关。

    # 使用 Unix 域套接字连接（如果可用）
psql -U your_username -d your_database

# 或通过 TCP/IP 连接到 localhost
psql -U your_username -h 127.0.0.1 -p 5432 -d your_database

    如果本地连接有效但远程连接无效，则问题很可能出在 listen_addresses、pg_hba.conf 或防火墙上。

  5. 检查客户端配置： 确保应用程序的连接字符串（例如 PGHOST、PGPORT、PGUSER、PGPASSWORD、PGDATABASE 环境变量或 libpq 连接字符串）配置正确，与服务器设置匹配。

提示和最佳实践

  • 最小权限原则： 避免将 postgres 超级用户用于常规应用程序连接。创建仅具有必要权限的特定角色。
  • 强密码： 始终为数据库角色使用强且唯一的密码。
  • 限制 pg_hba.conf： 不要使用 0.0.0.0/0，而是在 pg_hba.conf 中指定确切的客户端 IP 地址或窄 CIDR 范围，以增强安全性。
  • 定期监控日志： 建立查看 PostgreSQL 日志的例行程序。通过观察日志条目可以及早发现许多问题。
  • 记录你的配置： 清晰记录 postgresql.conf 和 pg_hba.conf 的设置，尤其是生产环境。

最快的修复通常来自将错误与产生错误的层匹配。网络工具告诉你主机和端口是否可达。PostgreSQL 日志告诉你服务器为何拒绝会话。pg_hba.conf 告诉你连接是否被允许。应用程序配置告诉你这些值是否实际在生产中使用。