1.1 远程连接失败
无法通过远程TCP/IP方式连接PostgreSQL,而本地可连接。
远程连接失败:
$ psql -h 10.0.4.13 -U postgres -d postgres -p5432
psql: error: could not connect to server: Connection refused
Is the server running on host "10.0.4.13" and accepting
TCP/IP connections on port 5432?
本地可连:
$ psql -p 5432
psql (13.4)
Type "help" for help.
postgres=#
1.1.1 listen_addresses配置
通过本地连接PostgreSQL,查看当前listen_addresses参数设置
$ psql -p 5432
psql (13.4)
Type "help" for help.
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)
在PostgreSQL数据库目录下的postgresql.conf文件更改此项参数,并且重启数据库生效
vi $PGDATA/postgresql.conf
listen_addresses = '*'
1.1.2 pg_hba.conf白名单配置
查看PostgreSQL数据目录下pg_hba.conf的内容,是否允许指定IP或网段远程访问数据库
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
以上只允许所有用户拥有本地连接的访问权限,需要修改并添加如下规则,根据业务需要配置,修改或添加完成后重新加载配置即可生效
$ pg_ctl reload -D $PGDATA
server signaled
其它设置规则参考:
允许所有网段的所有用户免密的方式访问数据库:
host all all 0.0.0.0/0 trust
允许所有网段的所有用户通过md5验证的方式访问数据库:
host all all 0.0.0.0/0 md5
允许所有网段的user1用户通过md5验证的方式访问数据库:
host all user1 0.0.0.0/0 md5
允许192.168.0.0/16网段的所有用户通过md5验证的方式访问数据库:
host all all 192.168.0.0/16 md5
允许192.168.0.0/16网段的所有用户通过md5验证的方式访问db1数据库:
host db1 all 192.168.0.0/16 md5
1.2 连接信息有误
远程、本地都无法连接
远程:
$ psql -h 10.0.4.13 -U postgres -d postgres -p5432
psql: error: could not connect to server: Connection refused
Is the server running on host "10.0.4.13" and accepting
TCP/IP connections on port 5432?
本地:
$ psql -p 5432
psql: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
检查主机IP以及端口号等连接信息
查看端口号,以下为正确的情况,如果没有,则使用正确的端口号连接
$ netstat -tunlp | grep 5432
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 11186/postgres
tcp6 0 0 :::5432 :::* LISTEN 11186/postgres
或者查看服务有没有启动
未启动:
$ pg_ctl status -D $PGDATA
pg_ctl: no server running
启动:
$ pg_ctl status -D $PGDATA
pg_ctl: server is running (PID: 11186)
/software/pgsql13/bin/postgre