"psql: could not connect to server: Connection refused" Error when connecting to remote database

Question

I am trying to connect to a Postgres database installed in a remote server using the following command:

psql -h host_ip -U db_username -d db_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?

1. Postgres installed version is 9.4.
2. Host operating system: Ubuntu 15.04
3. Client operating system: Centos 7

I already tried the following but the issue remains unresolved:

1. Edited pg_hba.conf file to include

host all all 0.0.0.0/0 md5

2. Edited 'postgresql.conf' and changed the listen parameter to

listen_addresses='*'

3. Restarted Postgres service.
4. Disabled firewall and iptables on host and client.
5. I checked by running the psql command locally and it worked.
6. 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.

Faker

firewall is still blocking port 5432. check your configs for iptables. – Isostasy

@Dmitry I disabled iptables by running sudo ufw disable, but I'm still getting the same error. – Faker

Can you try a 'netstat -na|grep 5432' and see if postgresql binds to all interfaces? – Teodora

Only this statement saved me spending hell lot of time host all all 0.0.0.0/0 md5 – Psychognosis

Answer 1

cd /etc/postgresql/9.x/main/

open file named postgresql.conf

sudo vi postgresql.conf 

{Note:-(You can use sudo "nano") If facing issues with editing through vi(Vim)]

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

Concertina

Hi, is not working at UBUNTU 16 LTS with pg9.5... Do you have a complete pg_hba.conf and postgresql.conf scripts adapted to this env and permissions? I need to use PGPASSWORD=postgres psql -U postgres. – Chil

@PeterKrauss I am facing the issue. It is not working at UBUNTU 16 LTS with pg9.5. Did it work for you? – Tumulus

Sorry @Arpit, was 2017... The best solution today (near 2020) is to upgrade UBUNTU to 18 LTS and PostgreSQL to v11+. – Chil

After upgrading from Ubuntu 14 to Ubuntu 16 I received this error. Simply restarting postgresql worked for me. Thanks! – Tokay

For the poor soul who also happens upon this, try also replacing your pg_hba.conf with a default version. fml. – Unwritten

Just doing the first step worked for me - Ubuntu, Django – Parrish

Answer 2

Check the port defined in postgresql.conf. My installation of postgres 9.4 uses port 5433 instead of 5432

Foetid

Just wanted to add that this also happened to my postgresql.conf in Postgres 11... never minded to check postgresql.conf but fixing it to 5432 was all it took. – Affront

It happened with me too, Postgres 12. – Bidle

Bizarre, I picked 5432 in the installer menu and there it is, 5433. So it affects new versions rather than old – Silvanasilvano

Answer 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]

Amaya

Answer 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

Connotative

Thanks @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). – Serin

thank 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 – Brunson

Answer 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

Lastminute

[at aws ec2] Also I have to edit the inbound port, to accept traffic on 5432. – Nike

Answer 6

Step 1: edit file potgresql.conf

• file location should be : etc/postgresql/10/main/

• Look for:

  #Connection Settings -

  #listen_addresses = '' # what IP address(es) to listen on;

• remove # before listening addresses

• add '*' :

  listen_addresses = '*'

---

Step 2: edit file pg_hba.conf

• file location should be : etc/postgresql/10/main/

• add below given line at the end

  host all all 0.0.0.0/0 md5

---

Step 3: restart postgres server

sudo /etc/init.d/postgresql restart

---

Step 4: check postgres server status

sudo netstat -plunt |grep postgres

• Make sure you are using same port to access the DB

Heman

Answer 7

Mine was quite straightforward if you are on a Mac try:

brew install postgres

This will tell you if you have it already install and what version or install the latest version for you if not then run

brew upgrade postgresql

This will make sure you have the latest version installed then finally

brew services start postgresql

This will start the service again. I hope this helps someone.

Peru

Answer 8

In my case I had removed a locale and generated another locale. Database failed to open because of fatal errors in the postgresql.conf file, on 'lc_messages', 'lc_monetary', 'lc_numberic', and 'lc_time'.

Restoring the locale sorted it out for me.

Dyedinthewool

Thank you, my friend. This was also the case for me. – Anesthetize

Answer 9

I think you are using the machine-name instead of the ip of the host.

I got the same error when i tried with machine's name. Because, It is allowed only when both the client and host are under same network and they have the same Operating system installed.

Cryoscope

Answer 10

In my case, I did not change azure default security policy in management portal. The original is port 22 allowed and the rest are all denied. As long as I add 5432 port, everything becomes good.

Afternoons

Answer 11

The following helped me on macos Mojave:

$sudo mv /usr/local/var/postgres /usr/local/var/postgres.save
$brew uninstall postgres
$brew install postgres

Grunenwald

Answer 12

See the port and make a port change in postgresql.conf. My installation of postgres 9.4 uses port 5431 or 5434 instead of 5432. If it say the port is in use so change the port. And check if you give password in psql installation so give the password in file and save it.

Crashaw

Answer 13

Another situation,postgresql.confandpg_hba.conffile not locate at /etc/postgresql/9.1/main/.Because postgres can start at any location you set.

For example when you use command pg_ctl -D /tmp/pgsql/ start ,the postgresql.conf and pg_hba.conf will located at /tmp/pgsql/.

Myatt

Answer 14

I had the exact same problem, with my configuration files correct. In my case the issue comes from the Eduroam wifi I used : when I connect via another wifi everything works. It seems that Eduroam blocks port 5432, at least in my university.

Precious

Answer 15

Try to migrate your database. For instance, if you are using Heroku to host your project and with Django, then try heroku run python manage.py migrate command; the error should go away.

Deduce

Answer 16

I had a problem like this where I had to ssh into a server and than run a query in psql console so the query was in a script but everytime I got this error psql not found so what I did was just added the psql full path from the bin which we get from cat .bash_profile and its done

PATH=$PATH:/usr/local/pgsql/bin:/usr/local/mysql/bin

So I added the whole /usr/local/mysql/bin/psql intead of just psql for remote execution.

Cysteine

Answer 17

and another one here:
both host and remote are on real servers
you need '*' exactly.
'localhost , xxx.xxx.xxx.xxx' will not work. all these answers(i've seen two) should be wiped out.
what you don't need : host all all 0.0.0.0/0 md5 and this stuff

Charmain

Answer 18

For me, I just removed the existing PostgreSQL 14 Server which was on the left-hand side of the pgAdmin4 GUI interface under the servers and then I manually added a new server from the option which is inside Quick Links of pgAdmin4.

I followed the documentation of bitnami.com.

Overweening

Answer 19

I had a problem with access to external server via 5432. I noticed that any network but mine saw the service

nmap server -p 5432

Fortunatelly, I recalled that I was playing with exposing my internal postgres server to outside world using my mikrotik router. Somehow it effectively closed external 5432 for internal network. As soon as I removed all nat rules with 5432 port - it worked like a charm.

Nudity

Answer 20

In case you are using Windows, I open the task manager, and started the postgres service, then I could connect to the database

task manager windows

Chomp

Answer 21

Try changing the host-ip/hostname if you have connection URL, your host name would be the string between "@" and the db name.

Ex URL: postgres://USERNAME:[e-mail]/data_db_1zqi

for the above URL host name would be --> dpg-clpc4m146foc73a8jr90-a.singapore-postgres.render.com

Mozambique

Answer 22

1. Check PostgreSQL Service: Ensure that the PostgreSQL service is running. You can check the status of the service using the following command:

   systemctl status postgresql

2. If the service is not running, start it using:

   systemctl start postgresql

Apology