印刻数据库

外观

PostgreSQL 连接失败 ​

连接失败是 PostgreSQL 数据库运维中常见的问题，可能由多种原因引起，包括网络问题、数据库服务问题、认证授权问题、资源限制问题等。本文档详细介绍了 PostgreSQL 连接失败的原因、诊断方法和解决方案。

连接失败原因分析 ​

1. 网络问题 ​

问题类型描述常见错误信息
网络不通客户端与数据库服务器之间网络不通"could not connect to server: Connection refused"
防火墙阻止防火墙阻止了数据库端口"could not connect to server: Connection timed out"
DNS解析失败无法解析数据库服务器主机名"could not translate host name "postgres" to address"
端口配置错误客户端使用了错误的数据库端口"could not connect to server: Connection refused"

2. 数据库服务问题 ​

问题类型描述常见错误信息
服务未启动PostgreSQL 服务未运行"could not connect to server: Connection refused"
监听地址配置错误数据库未监听客户端请求的地址"could not connect to server: Connection refused"
端口被占用数据库端口被其他进程占用服务启动失败
服务崩溃数据库服务异常崩溃服务不可用

3. 认证和授权问题 ​

问题类型描述常见错误信息
用户名/密码错误客户端使用了错误的用户名或密码"FATAL: password authentication failed for user "postgres""
认证方法不匹配pg_hba.conf中认证方法配置与客户端不符"FATAL: no pg_hba.conf entry for host "192.168.1.100""
数据库不存在客户端请求的数据库不存在"FATAL: database "nonexistentdb" does not exist"
用户无权限用户名无权访问请求的数据库"FATAL: permission denied for database "mydb""

4. 资源限制问题 ​

问题类型描述常见错误信息
连接数超过限制数据库连接数已达到max_connections限制"FATAL: sorry, too many clients already"
系统资源不足数据库服务器内存或CPU资源不足服务响应缓慢或拒绝连接
共享内存不足共享内存配置不足服务启动失败

5. 配置问题 ​

问题类型描述常见错误信息
配置文件错误postgresql.conf或pg_hba.conf配置错误启动失败或认证失败
参数配置不合理max_connections等参数配置不合理连接数限制或性能问题
SSL配置错误SSL配置错误导致连接失败"FATAL: no pg_hba.conf entry for host "192.168.1.100", user "postgres", database "postgres", SSL off"

连接失败的诊断方法 ​

1. 查看错误日志 ​

数据库日志是诊断连接失败问题的重要依据，包含了详细的错误信息和时间戳。

bash
# 查看最新的数据库日志
tail -n 100 /var/lib/pgsql/14/data/log/postgresql-$(date +%Y-%m-%d).log

# 查看所有包含"FATAL"或"ERROR"的日志
grep -i "fatal\|error" /var/lib/pgsql/14/data/log/postgresql-*.log

2. 使用工具诊断网络问题 ​

bash
# 检查网络连通性
ping postgres-server

# 检查端口是否开放
telnet postgres-server 5432

# 或使用nc命令
nc -zv postgres-server 5432

# 检查DNS解析
dig postgres-server
nslookup postgres-server

# 检查路由
traceroute postgres-server

3. 检查数据库服务状态 ​

bash
# 检查PostgreSQL服务状态
systemctl status postgresql-14

# 检查PostgreSQL进程是否运行
ps aux | grep postmaster

# 检查监听端口
netstat -tuln | grep 5432

# 或使用ss命令
ss -tuln | grep 5432

4. 检查认证配置 ​

bash
# 查看pg_hba.conf配置
cat /var/lib/pgsql/14/data/pg_hba.conf

# 查看postgresql.conf中监听配置
grep -E "listen_addresses|port" /var/lib/pgsql/14/data/postgresql.conf

5. 检查连接数和资源使用 ​

sql
-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看连接数限制
SHOW max_connections;

-- 查看详细连接信息
SELECT pid, usename, datname, state, client_addr, client_port, backend_start
FROM pg_stat_activity;

-- 查看系统资源使用
SELECT * FROM pg_stat_bgwriter;

连接失败的解决方案 ​

1. 网络问题解决方案 ​

1.1 检查网络连通性 ​

确保客户端与服务器之间网络通畅，检查防火墙规则，确保数据库端口开放。

bash
# 检查防火墙规则
sudo iptables -L -n | grep 5432

# 或使用firewalld
sudo firewall-cmd --list-ports | grep 5432

# 临时开放数据库端口
sudo firewall-cmd --add-port=5432/tcp --zone=public --permanent
sudo firewall-cmd --reload

1.2 检查DNS配置 ​

确保DNS能够正确解析数据库服务器主机名，或在客户端hosts文件中添加主机名映射。

bash
# 在客户端hosts文件中添加主机名映射
echo "192.168.1.100 postgres-server" >> /etc/hosts

1.3 检查端口配置 ​

确保客户端使用了正确的数据库端口，数据库服务正在监听该端口。

sql
-- 查看当前监听端口
SHOW port;

-- 在postgresql.conf中配置端口
# port = 5432

2. 数据库服务问题解决方案 ​

2.1 启动PostgreSQL服务 ​

如果数据库服务未启动，启动服务并设置开机自启。

bash
# 启动PostgreSQL服务
sudo systemctl start postgresql-14

# 设置服务开机自启
sudo systemctl enable postgresql-14

2.2 检查监听地址配置 ​

确保数据库监听了客户端请求的地址。

sql
-- 查看当前监听地址
SHOW listen_addresses;

-- 修改监听地址，允许所有IP访问
ALTER SYSTEM SET listen_addresses = '*';

-- 重启服务生效
sudo systemctl restart postgresql-14

2.3 解决端口占用问题 ​

如果数据库端口被其他进程占用，查找并杀死占用端口的进程，然后重启PostgreSQL服务。

bash
# 查找占用5432端口的进程
lsof -i :5432

# 杀死占用端口的进程
kill -9 <PID>

# 重启PostgreSQL服务
sudo systemctl restart postgresql-14

3. 认证和授权问题解决方案 ​

3.1 检查用户名和密码 ​

确保客户端使用了正确的用户名和密码，必要时创建用户或修改密码。

sql
-- 创建用户或修改密码
CREATE USER myuser WITH PASSWORD 'mypassword';
ALTER USER myuser WITH PASSWORD 'newpassword';

-- 授予用户数据库访问权限
GRANT CONNECT ON DATABASE mydb TO myuser;

3.2 配置pg_hba.conf ​

确保pg_hba.conf中配置了允许客户端访问的规则。

bash
# 编辑pg_hba.conf
vi /var/lib/pgsql/14/data/pg_hba.conf

# 添加允许客户端访问的规则
host    all             all             192.168.1.0/24         md5

# 重载配置
sudo systemctl reload postgresql-14

# 或使用pg_ctl
pg_ctl reload -D /var/lib/pgsql/14/data

3.3 创建缺失的数据库 ​

如果客户端请求的数据库不存在，创建该数据库并授予用户访问权限。

sql
-- 创建数据库
CREATE DATABASE mydb;

-- 授予用户数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

4. 资源限制问题解决方案 ​

4.1 增加连接数限制 ​

如果连接数已达到max_connections限制，增加连接数限制或清理空闲连接。

sql
-- 查看当前连接数限制
SHOW max_connections;

-- 修改连接数限制
ALTER SYSTEM SET max_connections = 200;

-- 重启服务生效
sudo systemctl restart postgresql-14

4.2 优化连接池配置 ​

使用连接池（如PgBouncer、pgpool-II）管理数据库连接，减少后端连接数。

ini
# PgBouncer配置示例
[pgbouncer]
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

4.3 清理空闲连接 ​

定期清理长时间空闲的连接，释放连接资源。

sql
-- 查看空闲连接
SELECT pid, usename, datname, state, backend_start, now() - backend_start AS idle_time
FROM pg_stat_activity
WHERE state = 'idle' AND now() - backend_start > interval '10 minutes';

-- 终止空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - backend_start > interval '10 minutes';

5. 配置问题解决方案 ​

5.1 修复配置文件错误 ​

检查并修复postgresql.conf或pg_hba.conf中的配置错误。

bash
# 检查配置文件语法
pg_ctl check -D /var/lib/pgsql/14/data

# 查看配置文件错误
postgres -D /var/lib/pgsql/14/data --single -c "select 1;"

5.2 优化SSL配置 ​

确保SSL证书和密钥文件存在且权限正确。

sql
-- 查看SSL配置
SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;

-- 确保SSL证书和密钥文件存在且权限正确
ls -l /var/lib/pgsql/14/data/server.crt /var/lib/pgsql/14/data/server.key
chmod 600 /var/lib/pgsql/14/data/server.key

连接失败的预防措施 ​

1. 监控和告警 ​

  • 监控连接数：创建连接数监控视图，实时监控连接使用情况
  • 配置告警：当连接使用率超过阈值时发送告警
  • 监控连接失败率：当连接失败率超过阈值时发送告警

2. 定期维护 ​

  • 定期检查网络：确保网络连接稳定
  • 定期检查服务状态：确保PostgreSQL服务正常运行
  • 定期审查配置：确保配置文件正确无误
  • 定期清理空闲连接：避免连接资源浪费

3. 配置优化 ​

  • 合理设置连接数：根据服务器资源设置合适的max_connections
  • 使用连接池：部署PgBouncer或pgpool-II等连接池
  • 优化应用程序连接管理：确保应用程序正确关闭连接
  • 配置连接超时：设置合理的连接超时时间

4. 测试和演练 ​

  • 定期测试连接：使用脚本定期测试数据库连接
  • 故障演练：模拟连接失败场景，测试应急预案
  • 容灾测试：测试主从切换时的连接情况

连接失败的案例分析 ​

案例一：网络防火墙阻止连接 ​

问题现象：应用程序无法连接到数据库，报错"could not connect to server: Connection timed out"

诊断过程：

  1. 使用ping命令测试网络连通性，发现网络通畅
  2. 使用telnet测试端口，发现连接超时
  3. 检查防火墙规则，发现数据库端口未开放

解决方案：

bash
# 开放数据库端口
sudo firewall-cmd --add-port=5432/tcp --zone=public --permanent
sudo firewall-cmd --reload

案例二：连接数超过限制 ​

问题现象：应用程序无法连接到数据库，报错"FATAL: sorry, too many clients already"

诊断过程：

  1. 查看当前连接数，发现已达到max_connections限制
  2. 查看连接详情，发现大量空闲连接

解决方案：

sql
-- 增加连接数限制
ALTER SYSTEM SET max_connections = 300;

-- 清理空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - backend_start > interval '5 minutes';

-- 优化应用程序连接管理，使用连接池

案例三：认证配置错误 ​

问题现象：客户端无法连接到数据库，报错"FATAL: no pg_hba.conf entry for host "192.168.1.100""

诊断过程：

  1. 查看pg_hba.conf配置，发现缺少客户端IP段的访问规则

解决方案：

bash
# 编辑pg_hba.conf，添加客户端IP段
vi /var/lib/pgsql/14/data/pg_hba.conf
# 添加规则：host    all             all             192.168.1.0/24         md5

# 重载配置
sudo systemctl reload postgresql-14

案例四：服务未启动 ​

问题现象：客户端无法连接到数据库，报错"could not connect to server: Connection refused"

诊断过程：

  1. 检查PostgreSQL服务状态，发现服务未启动
  2. 查看服务日志，发现启动失败，原因是配置文件错误

解决方案：

bash
# 修复配置文件错误
vi /var/lib/pgsql/14/data/postgresql.conf

# 启动服务
sudo systemctl start postgresql-14

版本差异注意事项 ​

版本差异说明
PostgreSQL 9.x连接管理功能相对简单，缺少一些高级特性
PostgreSQL 10+增强了连接管理功能，引入了连接池相关的优化
PostgreSQL 12+改进了pg_stat_activity视图，提供了更多连接信息
PostgreSQL 14+增强了SSL配置，支持更多的认证方式
PostgreSQL 15+改进了连接错误信息，更容易定位问题

连接管理最佳实践 ​

  1. 使用连接池：部署PgBouncer或pgpool-II等连接池，减少后端连接数
  2. 合理设置连接数：根据服务器资源设置合适的max_connections
  3. 配置连接超时：设置合理的连接超时时间，避免连接泄漏
  4. 监控连接使用：实时监控连接使用情况，及时发现异常
  5. 定期清理空闲连接：避免连接资源浪费
  6. 优化应用程序连接管理：确保应用程序正确关闭连接
  7. 测试连接可靠性：定期测试数据库连接，确保系统可靠
  8. 制定应急预案：制定连接失败的应急预案，快速恢复服务

总结 ​

连接失败是 PostgreSQL 数据库运维中常见的问题，需要快速定位和解决。连接失败的原因多种多样，包括网络问题、数据库服务问题、认证授权问题、资源限制问题等。通过使用合适的诊断工具和方法，可以快速定位问题，并采取相应的解决方案。

预防连接失败同样重要，包括监控和告警、定期维护、配置优化和测试演练等措施。通过建立完善的连接管理机制，可以减少连接失败的发生，提高数据库系统的可用性和可靠性。

在实际运维工作中，应根据具体情况选择合适的解决方案，并不断总结经验，优化连接管理策略，确保数据库系统的稳定运行。