Report this

What is the reason for this report?

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

Posted on July 11, 2020

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

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Start building today

From GPU-powered inference and Kubernetes to managed databases and storage, get everything you need to build, scale, and deploy intelligent applications.