Re: cant connect to localhost:5432 (but unix socket ok)

On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

> On Thu, Feb 04, 2021 at 02:20:10PM +0000, Joao Miguel Ferreira wrote:
> > My database is not listening on TCP/localhost, desptite it is listening
> on the unix socket. How can I investigate this?
> > I could have done something that is out of my understanding because I
> have been loading some big pg_dumpall files that might contain
> > administrative changes that I am not fully aware of.
>
> There are couple of potential issues:
> 1. it might listen on different port than 5432. What does "show port;"
> show in psql?
> 2. it could be that there is a firewall (weird, but possible)
> 3. it could be that it's listening on another address(es) than
> 127.0.0.1 / ::1 - what is output of "show listen_addresses;"?
> 4. Verify that it really does listen on something. As root run:
> ss -ntlp | grep postgres
>

it's on 5433:

postgres=# show port;
port
------
5433
(1 row)

postgres=# show listen_addresses;
listen_addresses
------------------
localhost
(1 row)

postgres=#
postgres=# \q
postgres(at)deb10tp:/$ exit
root(at)deb10tp:~# ss -ntlp | grep postgres
LISTEN 0 128 127.0.0.1:5433 0.0.0.0:*
users:(("postgres",pid=825,fd=5))

LISTEN 0 128 [::1]:5433 [::]:*
users:(("postgres",pid=825,fd=3))

root(at)deb10tp:~#

Thank you very much

>
> depesz
>
> > Here are some details about the connection issue:
> > root(at)deb10tp:~# id
> > uid=0(root) gid=0(root) groups=0(root)
> > root(at)deb10tp:~# su postgres
> > postgres(at)deb10tp:/root$ id
> > uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert)
> > postgres(at)deb10tp:/root$ cd /
> > postgres(at)deb10tp:/$ psql -h localhost
> > psql: could not connect to server: Connection refused
> > Is the server running on host "localhost" (::1) and accepting
> > TCP/IP connections on port 5432?
> > could not connect to server: Connection refused
> > Is the server running on host "localhost" (127.0.0.1) and accepting
> > TCP/IP connections on port 5432?
> > postgres(at)deb10tp:/$ psql
> > psql (11.9 (Debian 11.9-0+deb10u1))
> > Type "help" for help.
> > postgres=#
> > Thank you
> > Joao
>