Postgresql无法连接问题汇总
一. 数据库服务未启动
❌报错信息：
[postgres@k8s-node2 ~]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?✅ 解决方案
1）用服务启动postgresql数据库：
[postgres@k8s-node2 ~]$ systemctl start postgresql
2）用命令启动postgresql数据库（需配置环境变量）
[postgres@k8s-node2 ~]$ pg_ctl start -D /var/lib/pgsql/data二. pg_hba.conf 认证问题
❌报错信息：
[postgres@k8s-node2 data]$ psql -U test_user -d postgres
psql: FATAL: Peer authentication failed for user "test_user"
或者：
[postgres@k8s-node2 data]$ psql -U test_user -d test_db
psql: FATAL: no pg_hba.conf entry for host "192.168.66.11", user "test_user", database "test_db"✅ 解决方案:
编辑data目录下的pg_hba.conf文件，IPv4 local connections: 下增加如下一行
host all all 0.0.0.0/0 md5
[postgres@k8s-node2 data]$ vim /var/lib/pgsql/data/pg_hba.conf
翻到文件最下方：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 md5
修改完需要重启数据库使修改参数生效，重启完再次连接提示需要密码，此时可正常连接。
[postgres@k8s-node2 data]$ pg_ctl restart
[postgres@k8s-node2 data]$ psql -U test_user -d test_db
Password for user test_user:三. listen_addresses 配置问题
❌报错信息：
[postgres@k8s-node2 data]$ psql -U test_user -h 192.168.66.101
psql: could not connect to server: Connection refused
Is the server running on host "192.168.66.101" and accepting
TCP/IP connections on port 5432?✅ 解决方案:
登录查看当前listen_addresses为默认的localhost（只允许127.0.0.1，本机局域网不可以）
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)
修改listen_addresses为'0.0.0.0';
postgres=# alter system set listen_addresses ='0.0.0.0';
ALTER SYSTEM
该参数重启数据库生效，重启完后，可正常登录，
[postgres@k8s-node2 data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-03-19 22:49:45.779 PDT [104870] LOG: redirecting log output to logging collector process
2025-03-19 22:49:45.779 PDT [104870] HINT: Future log output will appear in directory "../pg_log".
done
server started
[postgres@k8s-node2 data]$ psql -U test_user -h 192.168.66.101
Password for user test_user: 四. 防火墙已开启但未放开数据库的端口 （以centos7防火墙为例）
❌报错信息：
此类报错常见于用另一台服务器访问在数据库服务器的数据库，192.168.66.101上有安装数据库连接时报错：
[root@master etc]# psql -U test_user -p 5432 -h 192.168.66.101
psql: error: could not connect to server: No route to host
Is the server running on host "192.168.66.101" and accepting
TCP/IP connections on port 5432?
此时检查数据库服务器上的防火墙，可以看到防火墙是开启的，检查放开的端口为空，此时外部无法访问该服务器的5432端口，进而无法访问数据库
[root@k8s-node2 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: active (running) since Wed 2025-03-19 22:57:44 PDT; 26s ago
Docs: man:firewalld(1)
Main PID: 111666 (firewalld)
Tasks: 2
Memory: 32.9M
CGroup: /system.slice/firewalld.service
└─111666 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid
Mar 19 22:57:44 k8s-node2 systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 19 22:57:44 k8s-node2 systemd[1]: Started firewalld - dynamic firewall daemon.
Mar 19 22:57:44 k8s-node2 firewalld[111666]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure confi...t now.
Hint: Some lines were ellipsized, use -l to show in full.
[root@k8s-node2 ~]# firewall-cmd --list-ports
[root@k8s-node2 ~]#✅ 解决方案:
1）开启5432端口
[root@k8s-node2 ~]# firewall-cmd --permanent --add-port=5432/tcp
success
[root@k8s-node2 ~]# firewall-cmd --add-port=5432/tcp
success
2）关闭并禁用防火墙
[root@k8s-node2 ~]# systemctl stop firewalld
[root@k8s-node2 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.五、其他问题
1）端口被占用
2）用户权限不足
3）pg_hba.conf做了限制用户连接
.....................................................如有其他问题可在评论留言