0

when I connect postgres on the server to postico on my local I get this error

could not connect to server: Operation timed out
Is the server running on host "http://159.65.65.203/" (92.242.140.2) and accepting
TCP/IP connections on port 5432?

I could not find anywhere postgresql.conf and pg_hba.conf files, so i created the files, as you see in the below image

enter image description here

I found this explanation but still does not solve my issue

Ubuntu does not use the standard pg_ctl for postgreSQL. Instead, it 
uses pg_ctlcluster.
That in turn controls the different PostgreSQL clusters. When you do 
an install of a new cluster, pg_ctlcluster is smart enough to put
postgresql.conf & pg_hba.conf into separate dirs.
So to be specific, /usr/lib/tmpfiles.d/postgresql.conf is just a tmp 
file that you can ingnore, /etc/postgresql/9.4/main/postgresql.conf is 
the original version for the 9.4 cluster and
/var/lib/postgresql/9.4/main/postgresql.auto.conf is the actual, live
version of the 9.4 cluster that you need to change to affect the 9.4
cluster. Likewise for the pg_hba.conf.
reference https://www.postgresql.org/message- 
id/CANu8FiygR5CdeqRTiE0YM+BQk6aK+qZyw2frH_FWV27J1exong@mail.gmail.com

here how i configured postgresql.conf

port=5432
listen_addresses='*'

here how i configured pg_hba.conf

 host    all             all              0.0.0.0/0                       md5
 host    all             all              ::/0                            md5

when i run

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program 
name
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
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      
29761/postgres
tcp6       0      0 :::22                   :::*                    LISTEN      -

Clearly 5432 is not open for all connections. when i make an api call using postman seems to work. I use mac currently firewall not on, i have my server on digitalocean use Ubuntu 16.04.6 (LTS) x64.

This is the path of postgres files

postgres@borroupapi:~/9.5/main$

when i run find / -name postgresql.conf i get this

/usr/lib/tmpfiles.d/postgresql.conf

but i cant find the path as logged in postgres user

2
  • Starting with the error message, it's quite puzzling that you seem to provide http://159.65.65.203/ as the host to connect to (where does that come from?) and that it seems to resolve to 92.242.140.2. – Daniel Vérité Jul 11 '20 at 13:07
  • Anyway it seems like a firewall at digitalocean is blocking 5432 (which makes sense) and you'd better use an SSH tunnel to use postgresql remotely rather than opening that port for the internet to try to hack into your instance. – Daniel Vérité Jul 11 '20 at 13:12
0

fixed the issue in postgres 9.5 postgresql.conf and pg_hba.conf files are located in

/etc/postgresql/9.5/main/

not in

/var/lib/postgresql/9.5/main/

you have to login as server root user and run each one of the command and that will give where are the files

find / -name postgresql.conf

find / -name pg_hba.conf

in my instance when i ran the command i got this

/var/lib/postgresql/9.5/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.