Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can't connect to Postgresql on port 5432

I have PostgreSQL 9.3 installed on a server running Ubuntu Server 14.04.

If I ssh into the server via terminal, I'm able to connect with psql. But when I try to configure pgAdmin III to do the remote connection, I get:

Server doesn't listen The server doesn't accept connections: the connection library reports could not connect to server: Connection refused Is the server running on host "172.24.3.147" and accepting TCP/IP connections on port 5432?

When I run on the server service postgresql status it gives me:

9.3/main (port 5432): online 

So of course I'm missing something important here.

EDIT

When running netstat -na on the server, I get (relevant portion, I guess):

Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address           Foreign Address         State       tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      tcp        0      0 172.24.3.147:22         172.24.3.240:61950      ESTABLISHED tcp        0      0 172.24.3.147:22         172.24.3.240:60214      ESTABLISHED 
like image 399
Rodrigo Avatar asked Jul 19 '16 18:07

Rodrigo


People also ask

How do I check if port 5432 is open?

Check for Port IP 0.1:5432") shows that PostgreSQL is listening only for connects originating from the local computer, so we will have to edit the "postgresql. conf" configuration file. A result including "0.0. 0.0:5432" indicates that PostgreSQL is already listening for remote connections.

Can't connect to PostgreSQL database?

To be sure that PostgreSQL is running, you can also restart it with systemctl restart postgresql. If this does not fix the problem, the most likely cause of this error is that PostgreSQL is not configured to allow TCP/IP connections.


1 Answers

You have to edit postgresql.conf file and change line with 'listen_addresses'.

This file you can find in the /etc/postgresql/9.3/main directory.

Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:

listen_addresses = '*' 

Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:

host    all         all         192.168.1.0/24        md5 

Please, read comments in this file...

EDIT:

After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server.

EDIT2: Highlited configuration files.

like image 135
Jan Marek Avatar answered Oct 18 '22 13:10

Jan Marek