DevOps知识中心
DevOps知识中心

PostgreSQL 常见连接错误故障排除

遇到 PostgreSQL 连接问题了吗？本文提供了一份全面的指南，用于解决常见的错误，例如身份验证失败、网络拒绝和服务不可用。了解如何诊断 `pg_hba.conf` 冲突、错误的凭据以及服务器无响应等问题。通过实用的步骤、命令行示例和最佳实践，您将获得快速解决连接问题并确保您的 PostgreSQL 数据库始终可访问且安全的知识。

56 浏览量

故障排除常见的 PostgreSQL 连接错误

PostgreSQL 以其健壮性和可靠性而闻名，但与任何复杂的系统一样，连接问题也会出现，阻碍应用程序和用户访问其数据库。这些错误可能源于各种因素，包括不正确的身份验证凭据、配置错误的网络设置或数据库服务未激活。当您遇到 could not connect to server（无法连接到服务器）或 password authentication failed（密码身份验证失败）消息时，这可能会令人沮丧，尤其是在您需要快速访问数据时。

本综合指南旨在揭开常见的 PostgreSQL 连接错误的神秘面纱，并提供清晰、可操作的解决方案。我们将探讨导致这些问题的典型罪魁祸首——从身份验证失败和网络配置问题到服务不可用——并引导您完成系统的故障排除步骤。通过了解根本原因并应用此处概述的实用解决方案，您将有能力诊断和解决大多数连接问题，确保您的 PostgreSQL 数据库保持可访问和可操作。

理解 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 密码不匹配。

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

解决方案：
1. 检查应用程序配置： 查看应用程序的连接字符串或环境变量，确保密码正确。
2. 重置密码（如果您有超级用户权限）：

```bash
# 以 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 条目不存在，SSL 关闭/开启)

此错误是 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： 添加或修改一个条目以允许连接。一个允许来自任何地方使用密码身份验证连接的常见条目如下所示：

```ini
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
```
*   `TYPE`：`host` 表示 TCP/IP 连接。
*   `DATABASE`：`all`（或特定的数据库名称）。
*   `USER`：`all`（或特定的用户名）。
*   `ADDRESS`：客户端的 IP 地址范围（例如 `192.168.1.0/24`，本地为 `127.0.0.1/32`，所有 IPv4 地址为 `0.0.0.0/0`）。
*   `METHOD`：身份验证方法（例如 `md5`、`scram-sha-256`、`trust`、`ident`）。`md5` 常用于密码身份验证。

**警告：** 如果没有防火墙保护，将 `0.0.0.0/0` 与 `md5` 结合使用可能会带来安全风险。应尽可能将 `ADDRESS` 限制为已知的 IP 地址。

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

    ```bash

    在基于 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) (无法连接到服务器：连接被拒绝 (0x0000274D/10061))

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

解释： 这通常指向以下情况之一：
* PostgreSQL 服务未运行。
* PostgreSQL 未在预期的网络接口或端口上侦听。
* 防火墙正在阻止连接。

解决方案：

  1. PostgreSQL 是否在运行？

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

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

    • 检查 postgresql.conf： 确保 listen_addresses 配置正确。对于来自其他主机的连接，它应设置为 * 或服务器网络接口的特定 IP 地址，而不仅仅是 localhost (127.0.0.1)。
      ini # 在 postgresql.conf 中 listen_addresses = '*' # 监听所有可用的网络接口 port = 5432 # 默认端口
      更改 listen_addresses 后，您必须重启 PostgreSQL（重新加载不足以生效）。
      bash sudo systemctl restart postgresql
    • 验证侦听端口： 使用 netstat 或 ss 检查 PostgreSQL 是否确实在端口 5432（或您配置的端口）上侦听。
      bash sudo netstat -tulnp | 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 地址的传入连接是打开的。
      ```bash
      # 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），它可能忙于接受新连接，从而导致超时。检查系统资源利用率。

一般故障排除步骤

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

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

    ```bash

    检查最近日志的示例

    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）需要完全重启。

    bash sudo systemctl restart postgresql

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

    ```bash

    使用 Unix 域套接字连接（如果可用）

    psql -U your_username -d your_database

    或连接到 localhost 的 TCP/IP

    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 是否允许连接？凭据是否正确？

通过利用 PostgreSQL 详细的日志记录并了解其核心配置文件​​的作用，您可以获得强大的工具来维护数据库的可访问性和安全性。一致地应用这些故障排除技术，不仅可以解决眼前的问​​题，还可以提高您对 PostgreSQL 操作细微差别的总体理解，从而实现更具弹性、更可靠的数据库部署。