By default PostgreSQL is configured to be bound to "localhost".
2 $ netstat -nlt 3 Proto Recv-Q Send-Q Local Address Foreign Address State 4 tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 5 tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN 6 tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 7 tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 8 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 9 tcp 0 0 127.0.0.1:3737 0.0.0.0:* LISTEN 10 tcp6 0 0 :::22 :::* LISTENAs we can see above port 5432 is bound to 127.0.0.1 . It means any attempt to connect to the postgresql server from outside the machine will be refused. We can try hitting the port 5432 by using telnet.
2 $ telnet 107.170.11.79 5432 3 Trying 107.170.11.79... 4 telnet: connect to address 107.170.11.79: Connection refused 5 telnet: Unable to connect to remote hostConfiguring postgresql.conf
In order to fix this issue we need to find postgresql.conf . In different systems it is located at different place. I usually search for 2 $ find / -name "postgresql.conf" 3 /var/lib/pgsql/9.4/data/postgresql.conf
Open postgresql.conf file and replace line
2 listen_addresses = 'localhost' 2 listen_addresses = '*'Now restart postgresql server.
2 $ netstat -nlt 3 Proto Recv-Q Send-Q Local Address Foreign Address State 4 tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN 5 tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 6 tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 8 tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 9 tcp 0 0 127.0.0.1:2812 0.0.0.0:* LISTEN 10 tcp6 0 0 ::1:11211 :::* LISTEN 11 tcp6 0 0 :::22 :::* LISTEN 12 tcp6 0 0 :::5432 :::* LISTEN 13 tcp6 0 0 ::1:25 :::* LISTENHere we can see that "Local Address" for port 5432 has changed to 0.0.0.0 .
Configuring pg_hba.conf
Let's try to connect to remote postgresql server using "psql".
2 $ psql -h 107.170.158.89 -U postgres 3 psql: could not connect to server: Connection refused 4 Is the server running on host "107.170.158.89" and accepting 5 TCP/IP connections on port 5432?In order to fix it, open pg_hba.conf and add following entry at the very end.
2 host all all 0.0.0.0/0 md5 3 host all all ::/0 md5The second entry is for IPv6 network.
Do not get confused by "md5" option mentioned above. All it means is that a password needs to be provided. If you want client to allow collection without providing any password then change "md5" to "trust" and that will allow connection unconditionally.
Restart postgresql server.
2 $ psql -h 107.170.158.89 -U postgres 3 Password for user postgres: 4 psql (9.4.1, server 9.4.5) 5 Type "help" for help. 7 postgres=# \l