43

I have a database server (192.168.1.50) running postgres. I have created a database named "testdb" and a user "testuser" with password "testuserpw".
Locally, I can connect to the db using:

psql -d testdb -U testuser

When I issue the command from another host (192.168.1.60):

psql -h 192.168.1.50 -d testdb -U testuser

I have the error:

psql: could not connect to server: Connection refused
Is the server running on host "192.168.1.50" and accepting
TCP/IP connections on port 5432?

Any idea ?

Luc
  • 16,604
  • 34
  • 121
  • 183

6 Answers6

63

Check the setting of listen_addresses in your postgresql.conf file. Many distributions make it default to 127.0.0.1, i.e. listen only to connections coming in from localhost. It should be set to '*' to listen for connections on all interfaces.

If you are still having trouble, use lsof to see what network sockets the postgres process is listening on.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • 5
    As a note, there is also a pg_hba.conf config file in which one can configure trust properties for specific machines. – Travis Stevens May 27 '11 at 16:06
  • For Postgres 9 the setting file will be in `/var/lib/postgres/*`. But for Postgres 11, this file can be found in `/etc/postgresql/11/main/` – Shah Muzaffar Oct 14 '19 at 05:47
6

On Ubuntu, I noticed that remote access at some point stopped working (currently using 9.1.9). The reason is, that postgres is no longer started with the -i switch [1] so no matter what you configure for listen_addresses, it will be ignored.

Fortunately, adding the following line to /etc/environment solves the problem after logging out and in again (or reboot):

PGOPTIONS="-i"

See [2] for more options. Note, that adding this to /etc/postgresql/9.1/main/environment did NOT work for me.

Now, when doing nmap ip-of-my-remote-server I finally get this again:

5432/tcp open  postgresql

Yay!

[1] http://www.postgresql.org/docs/9.1/static/runtime-config-short.html

[2] http://www.postgresql.org/docs/9.1/static/libpq-envars.html

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
raven_arkadon
  • 390
  • 1
  • 5
  • 11
  • I'm also running 9.1.9 on Ubuntu 13.04 and this step doesn't seem to be necessary. – Rich Apodaca Aug 10 '13 at 18:21
  • I found I had to replace the double quotes with single quotes and it worked in the postgresql specific environment file just fine. Also, you have to actually use 'stop' and 'start' (as opposed to a simple reload) when you execute /etc/init.d/postgresql after making this change. – mdpatrick Dec 01 '13 at 01:08
4

Is the firewall letting the connections through? Or, check if pg_hba.conf allows connecting from addresses other than localhost.

Miki
  • 7,052
  • 2
  • 29
  • 39
1

The listen_address configvar in postgresql.conf is not the only way to get postgres to listen on the non-local IP-address (or addresses).

Use option "-o -h *" if you start postgres from pg_ctl, otherwise do add "-h" "*" to the postgres command line, like e.g.

/usr/local/pgsql/bin/postgres -D /pg/data "-h" "*"

Of course /pg/data must be changed to your current datapath.

This is especially useful when experimenting.

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Donald J. Axel
  • 11
  • 2
1

I came across the same problem as yours, and my source of problem is the firewall settings. If you're using Ubuntu, print your firewall status: sudo ufw status verbose

It may looks like this:

Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
...

The default rule of incoming connection is "deny", so you need to specify the "allow"ed port.

type: sudo ufw allow 5432/tcp

reference: https://www.vultr.com/docs/how-to-configure-ufw-firewall-on-ubuntu-14-04

ikraduya edian
  • 70
  • 1
  • 7
0

Connection refused (0x0000274D/10061) i fixed here with:

Open the terminal and type:

VIM /var/lib/pgsql/data/postgresql.conf

Edit the "listen_adresses", it should be set to '*'

enter image description here

After this, rest it on terminal: 

/scripts/restartsrv_postgres
Erick Amoedo
  • 475
  • 1
  • 4
  • 9