使用psql连接 postgresql失败

使用psql链接postgresql服务失败，telnet这个地址和端口有没有反映。

scutech@infokist:~$ psql -h 192.168.17.40 -U scutech -d test
psql: could not connect to server: Connection refused
	Is the server running on host "192.168.17.40" and accepting
	TCP/IP connections on port 5432?
scutech@infokist:~$ telnet 192.168.17.40 5432
Trying 192.168.17.40...
telnet: Unable to connect to remote host: Connection refused

从提示看，端口显然没有被侦听，修改postgresql.conf ，把要侦听的地址改成’*’ ，不然它只侦听127.0.0.1。

root@scutech:/etc/postgresql/12/main# vi postgresql.conf 
....
listen_addresses = '*'          # what IP address(es) to listen on;

重新启动服务，发现之前只侦听127.0.0.1，现在ip v4 和v6都听。

root@scutech:/etc/postgresql/12/main# netstat -natp|grep postgre
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      14123/postgres      
root@scutech:/etc/postgresql/12/main# service postgresql restart
root@scutech:/etc/postgresql/12/main# netstat -natp|grep postgre
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      14221/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      14221/postgres      
root@scutech:/etc/postgresql/12/main# 

再连接进来发现连接已经通了，是主机信任的问题

scutech@infokist:~$ psql -h 192.168.17.40 -U scutech -d test
psql: FATAL:  no pg_hba.conf entry for host "192.168.87.155", user "scutech", database "test", SSL on
FATAL:  no pg_hba.conf entry for host "192.168.87.155", user "scutech", database "test", SSL off
scutech@infokist:~$ 

修改pg_hba.conf ，把ip v4 对应的一行从127.0.0.1改重all，即所有主机。

#host    all             all             127.0.0.1/32            md5
host    all             all             all            md5

重新启动后连接成功

scutech@infokist:~$ psql -h 192.168.17.40 -U scutech -d test
Password for user scutech: 
psql (9.5.21, server 12.3 (Ubuntu 12.3-1.pgdg18.04+1))
WARNING: psql major version 9.5, server major version 12.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test=> 

文章下方是我的微信，欢迎加我。👇