Ubuntu下如何解决PostgreSQL连接问题

Ubuntu下PostgreSQL连接问题排查与修复

一、快速自检

• 确认服务是否在运行：sudo systemctl status postgresql（或 sudo service postgresql status）。若未运行，启动：sudo systemctl start postgresql；设置开机自启：sudo systemctl enable postgresql。
• 本地套接字直连测试：psql -U postgres -h 127.0.0.1 -p 5432；若仅本地“socket”方式失败，可尝试 psql -U postgres（使用Unix域套接字）。
• 远程连接测试（从另一台机器）：psql -h <服务器IP> -p 5432 -U <用户> -d <库名>。
• 监听与端口检查：ss -lntp | grep 5432 或 netstat -a | grep PGSQL，确认有监听 5432。
• 查看日志定位启动或认证失败原因：/var/log/postgresql/postgresql-<版本>-main.log。

二、常见错误与对应修复

• 错误1：could not connect to server: No such file or directory（Unix 域套接字不存在）
  原因：服务未启动或套接字目录不一致。
  处理：

  1. 确认服务已启动（见上节）；
  2. 检查套接字文件：sudo find /tmp -name .s.PGSQL.5432 与 sudo find /var/run/postgresql -name .s.PGSQL.5432；
  3. 若仅/tmp下存在而/var/run/postgresql下缺失，可建立符号链接：sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432；
  4. 仍失败时，查看日志 /var/log/postgresql/…-main.log 获取具体原因。

• 错误2：FATAL: private key file …/ssl-cert-snakeoil.key has group or world access
  原因：SSL私钥权限过宽。
  处理：

  1. 修正权限与属主：
     sudo chown postgres:postgres /etc/ssl/private/ssl-cert-snakeoil.key
     sudo chmod 640 /etc/ssl/private/ssl-cert-snakeoil.key
  2. 重启服务：sudo systemctl restart postgresql。

• 错误3：Connection refused / 超时（远程连不上）
  原因：未监听外部地址、未放行客户端网段、防火墙阻断。
  处理：

  1. 修改监听地址：编辑 /etc/postgresql/<版本>/main/postgresql.conf，设置 listen_addresses = ‘*’；
  2. 放行客户端网段：编辑 /etc/postgresql/<版本>/main/pg_hba.conf，添加例如：host all all 0.0.0.0/0 md5（生产环境请改为具体网段并采用更安全的认证方式）；
  3. 重启服务：sudo systemctl restart postgresql；
  4. 放行防火墙：sudo ufw allow 5432/tcp（或按实际防火墙放行策略配置）。

三、远程访问配置清单

• 修改监听：/etc/postgresql/<版本>/main/postgresql.conf
  • 将 #listen_addresses = ‘localhost’ 改为：listen_addresses = ‘*’
• 修改认证：/etc/postgresql/<版本>/main/pg_hba.conf（示例，按需收紧）
  • IPv4：host all all 0.0.0.0/0 md5
  • IPv6：host all all ::1/128 md5
• 重启：sudo systemctl restart postgresql
• 防火墙：sudo ufw allow 5432/tcp
• 客户端测试：psql -h <服务器IP> -p 5432 -U <用户> -d <库名>
• 注意：开放 0.0.0.0/0 存在安全风险，建议仅对受控网段开放，并使用强口令或更安全的认证方式。

四、实用命令速查

• 服务管理：
  • 启动/停止/重启：sudo systemctl start|stop|restart postgresql
  • 指定版本与实例：sudo systemctl start postgresql@<版本> 或 sudo pg_ctlcluster <版本> main start
• 登录数据库：
  • 本地：sudo -u postgres psql
  • 指定主机：psql -U <用户> -h <主机> -p <端口> -d <库名>
• 日志与监听：
  • 日志：/var/log/postgresql/postgresql-<版本>-main.log
  • 端口：ss -lntp | grep 5432