Can't connect to Postgresql on port 5432
Solution 1
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.
Solution 2
Uncomment the listen_addresses = '*' in the postgresql.conf
This has bitten me a second time so I thought might be worth mentioning. The line listen_addresses = '*' in the postgresql.conf is by default commented. Be sure to uncomment (remove the pound sign, # at the beginning) it after updating otherwise, remote connections will continue to be blocked.
PS: psql -U postgres -c 'SHOW config_file'
- to locate the postgresql.conf file path
Solution 3
Had same problem with psql via command line connecting and pgAdmin not connecting on RDS with AWS. I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following:
sudo find . -name *.conf
then sudo nano ./data/pg_hba.conf
then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5
and pgAdmin automatically logged me in.
This also worked in pg_hba.conf file
host all all md5
without any IP address and this also worked with my IP address host all all <myip>/32 md5
As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.
Solution 4
Remember to check firewall settings as well. after checking and double-checking my pg_hba.conf
and postgres.conf
files I finally found out that my firewall was overriding everything and therefore blocking connections
Solution 5
just summary from the accepted answer
if maybe someone like me overlook the filename that must be edit
in my case the conf file located at
/etc/postgresql/14/main'
if you type cd /etc/postgresql/14/main
so just sudo nano
add these 2 filenames
- postgresql.conf
listen_addresses = '*'
uncomment this line and change localhost to *
- pg_hba.conf
host all all 0.0.0.0/0
change the 127.0.0.1/32 change to 0.0.0.0/0
last don't forget to restart using sudo service postgresql restart
I hope this one clears the notification error
Rodrigo
Programmer and biologist, I'd love to leave the world a little better than I've found it.
Updated on January 30, 2022Comments
-
Rodrigo over 2 years
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