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 below

base    pg_clog       pg_dynshmem  pg_logical    pg_notify    pg_serial     pg_stat  pg_subtrans  pg_twophase  pg_xlog       postgresql.conf  postmaster.pid
global  pg_commit_ts  pg_hba.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   postgresql.auto.conf  postmaster.opts

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

edited by MattIPv4

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
2 answers

Hi there @yasinahmed23,

Indeed it looks like PostgreSQL is binding on 127.0.0.1:5432 and not 0.0.0.0:5432 which would explain why you are not able to connect.

Have you restarted PostgreSQL after making the changes to the pg_hba.conf file?

  • systemctl restart postgresql

Regards,
Bobby

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
Submit an Answer