"psql: could not connect to server: Connection refused" Error when connecting to remote database
Solution 1
cd /etc/postgresql/9.x/main/
open file named postgresql.conf
sudo vi postgresql.conf
add this line to that file
listen_addresses = '*'
then open file named pg_hba.conf
sudo vi pg_hba.conf
and add this line to that file
host all all 0.0.0.0/0 md5
It allows access to all databases for all users with an encrypted password
restart your server
sudo /etc/init.d/postgresql restart
Solution 2
Check the port defined in postgresql.conf
. My installation of postgres 9.4 uses port 5433
instead of 5432
Solution 3
I have struggled with this when trying to remotely connect to a new PostgreSQL installation on my Raspberry Pi. Here's the full breakdown of what I did to resolve this issue:
First, open the PostgreSQL configuration file and make sure that the service is going to listen outside of localhost.
sudo [editor] /etc/postgresql/[version]/main/postgresql.conf
I used nano
, but you can use the editor of your choice, and while I have version 9.1
installed, that directory will be for whichever version you have installed.
Search down to the section titled 'Connections and Authentication'. The first setting should be 'listen_addresses'
, and might look like this:
#listen_addresses = 'localhost' # what IP address(es) to listen on;
The comments to the right give good instructions on how to change this field, and using the suggested '*'
for all will work well.
Please note that this field is commented out with #. Per the comments, it will default to 'localhost', so just changing the value to '*'
isn't enough, you also need to uncomment the setting by removing the leading #
.
It should now look like this:
listen_addresses = '*' # what IP address(es) to listen on;
You can also check the next setting, 'port', to make sure that you're connecting correctly. 5432 is the default, and is the port that psql will try to connect to if you don't specify one.
Save and close the file, then open the Client Authentication config file, which is in the same directory:
sudo [editor] /etc/postgresql/[version]/main/pg_hba.conf
I recommend reading the file if you want to restrict access, but for basic open connections you'll jump to the bottom of the file and add a line like this:
host all all all md5
You can press tab instead of space to line the fields up with the existing columns if you like.
Personally, I instead added a row that looked like this:
host [database_name] pi 192.168.1.0/24 md5
This restricts the connection to just the one user and just the one database on the local area network subnet.
Once you've saved changes to the file you will need to restart the service to implement the changes.
sudo service postgresql restart
Now you can check to make sure that the service is openly listening on the correct port by using the following command:
sudo netstat -ltpn
If you don't run it as elevated (using sudo
) it doesn't tell you the names of the processes listening on those ports.
One of the processes should be Postgres, and the Local Address should be open (0.0.0.0) and not restricted to local traffic only (127.0.0.1). If it isn't open, then you'll need to double check your config files and restart the service. You can again confirm that the service is listening on the correct port (default is 5432
, but your configuration could be different).
Finally you'll be able to successfully connect from a remote computer using the command:
psql -h [server ip address] -p [port number, optional if 5432] -U [postgres user name] [database name]
Solution 4
Make sure the settings are applied correctly in the config file.
vim /etc/postgresql/x.x/main/postgresql.conf
Try the following to see the logs and find your problem.
tail /var/log/postgresql/postgresql-x.x-main.log
Solution 5
Following configuration, you need to set:
To open the port 5432 edit your /etc/postgresql/9.1/main/postgresql.conf
and change
# Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
In /etc/postgresql/10/main/pg_hba.conf
# IPv4 local connections:
host all all 0.0.0.0/0 md5
Now restart your DBMS
sudo service postgresql restart
Now you can connect with
psql -h hostname(IP) -p port -U username -d database
Related videos on Youtube
Poonam Anthony
Updated on March 03, 2022Comments
-
Poonam Anthony about 2 years
I am trying to connect to a Postgres database installed in a remote server using the following command:
psql -h
host_ip
-Udb_username
-ddb_name
This is the error that occurs:
psql: could not connect to server: Connection refused Is the server running on host "<host_ip>" and accepting TCP/IP connections on port 5432?
- Postgres installed version is 9.4.
- Host operating system: Ubuntu 15.04
- Client operating system: Centos 7
I already tried the following but the issue remains unresolved:
- Edited
pg_hba.conf
file to include
host all all 0.0.0.0/0 md5
- Edited 'postgresql.conf' and changed the listen parameter to
listen_addresses='*'
- Restarted Postgres service.
- Disabled firewall and iptables on host and client.
- I checked by running the psql command locally and it worked.
- I tried the second solution given in this question. Running
nmap
gave me the following output:
Starting Nmap 6.47 ( http://nmap.org ) at 2015-09-07 18:08 IST Nmap scan report for 10.17.250.250 Host is up (0.0000040s latency). Not shown: 997 closed ports PORT STATE SERVICE 22/tcp open ssh 25/tcp open smtp 80/tcp open http
Am I missing something? Hope someone can help.
-
Dmitry S over 8 yearsfirewall is still blocking port 5432. check your configs for iptables.
-
Poonam Anthony over 8 years@Dmitry I disabled iptables by running
sudo ufw disable
, but I'm still getting the same error. -
kometen over 8 yearsCan you try a 'netstat -na|grep 5432' and see if postgresql binds to all interfaces?
-
ShivarajRH about 4 yearsOnly this statement saved me spending hell lot of time host all all 0.0.0.0/0 md5
-
Peter Krauss almost 7 yearsHi, is not working at UBUNTU 16 LTS with pg9.5... Do you have a complete
pg_hba.conf
andpostgresql.conf
scripts adapted to this env and permissions? I need to usePGPASSWORD=postgres psql -U postgres
. -
Aniket Singh about 5 yearsThanks @amirio , this is right way to find exact problem. In my case, there was problem related to file permissions 2019-05-20 19:53:45.406 IST [4843] FATAL: data directory
"/var/lib/postgresql/9.5/main" has group or world access 2019-05-20 19:53:45.406 IST [4843] DETAIL: Permissions should be u=rwx (0700).
-
Arpit over 4 years@PeterKrauss I am facing the issue. It is not working at UBUNTU 16 LTS with pg9.5. Did it work for you?
-
Peter Krauss over 4 yearsSorry @Arpit, was 2017... The best solution today (near 2020) is to upgrade UBUNTU to 18 LTS and PostgreSQL to v11+.
-
Arpit over 4 years@PeterKrauss Thanks for replying. Highly Appreciate!
-
Jacob Morris about 4 yearsAfter upgrading from Ubuntu 14 to Ubuntu 16 I received this error. Simply restarting postgresql worked for me. Thanks!
-
Franco Gil over 3 years[at aws ec2] Also I have to edit the inbound port, to accept traffic on 5432.
-
jason m over 3 yearsTHANK BE THE LORD YOU BEAUTIFUL HUMAN.
-
jason m over 3 yearsFor the poor soul who also happens upon this, try also replacing your pg_hba.conf with a
default
version. fml. -
paule mandengue over 3 yearsthank you so much for this after checking mu log i saw i had to give (0700) to my /var/lib/10/main directory it worked for me
-
Carl H about 3 yearsJust wanted to add that this also happened to my
postgresql.conf
in Postgres 11... never minded to checkpostgresql.conf
but fixing it to 5432 was all it took. -
Andre almost 3 yearsIt happened with me too, Postgres 12.
-
Josh over 2 yearsJust doing the first step worked for me - Ubuntu, Django
-
Alkanshel over 2 yearsBizarre, I picked 5432 in the installer menu and there it is, 5433. So it affects new versions rather than old