CODEWITHSUNDEEP
postgresql
Luc
Luc

Reputation: 17082

Cannot connect to postgres from remote host

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 ?

Upvotes: 44

Views: 98420

Answers (7)

Ferhat Özdogan
Ferhat Özdogan

Reputation: 59

I had the same problem with my Ubuntu 22.04, PostGreSQL 16 setup and got stuck for quite a while. Besides the other answers that concern the *.conf-files, I also had to configure the firewall with the commands below:

root# firewall-cmd --zone=public --add-port=5432/tcp --permanent
success
root# firewall-cmd --reload
success

I found this on this webpage: https://www.project-open.com/en/howto-postgresql-port-secure-remote-access

I hope it helps and wish you (and everybody else concerned) 'good luck'!

Upvotes: 0

raven_arkadon
raven_arkadon

Reputation: 400

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

Upvotes: 6

Donald J. Axel
Donald J. Axel

Reputation: 11

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.

Upvotes: 1

Erick Amoedo
Erick Amoedo

Reputation: 485

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

Upvotes: 0

ikraduya edian
ikraduya edian

Reputation: 80

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

Upvotes: 2

araqnid
araqnid

Reputation: 133712

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.

Upvotes: 65

Miki
Miki

Reputation: 7188

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

Upvotes: 4

Related Questions