Question

can't connect postgres on the server to postico on my local

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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

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

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?

  1. systemctl restart postgresql

Regards, Bobby

This comment has been deleted