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

Did you try modifying the firewall settings ?

Add postgres service to the firewall. If that doesn’t work, explicitly open the port 5433 in the firewall.

From: Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>
Sent: Thursday, February 4, 2021 10:08 AM
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: depesz(at)depesz(dot)com; pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: cant connect to localhost:5432 (but unix socket ok)

CAUTION: This email originated from outside of Snap-on. Do not click on links or open attachments unless you have validated the sender, even if it is a known contact. Contact the sender by phone to validate the contents.

On Thu, Feb 4, 2021 at 3:04 PM Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com<mailto:joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>> wrote:

On Thu, Feb 4, 2021 at 3:02 PM Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com<mailto:joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>> wrote:
Hi Tom

On Thu, Feb 4, 2021 at 2:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com<mailto:joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>> writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com<mailto:depesz(at)depesz(dot)com>>
> wrote:
>>> My database is not listening on TCP/localhost, desptite it is listening
>>>> on the unix socket. How can I investigate this?

> it's on 5433:

Hmm, something odd there, because a port number mismatch should have
resulted in psql failing to connect via unix socket either. Maybe
you have more than one active postmaster?

"ps xauwww | grep postgres" shows only one postgres process (and a few vaccum related)

here is the full list:

root(at)deb10tp:~# ps xauww | grep postgres
postgres 825 0.0 0.1 213472 14980 ? S 09:59 0:01 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres 847 0.0 0.0 213572 5660 ? Ss 09:59 0:00 postgres: 11/main: checkpointer
postgres 848 0.0 0.0 213472 3808 ? Ss 09:59 0:00 postgres: 11/main: background writer
postgres 849 0.0 0.0 213472 3688 ? Ss 09:59 0:00 postgres: 11/main: walwriter
postgres 850 0.0 0.0 214012 5628 ? Ss 09:59 0:01 postgres: 11/main: autovacuum launcher
postgres 852 0.0 0.4 102172 34612 ? Ss 09:59 0:10 postgres: 11/main: stats collector
postgres 853 0.0 0.0 213880 4260 ? Ss 09:59 0:00 postgres: 11/main: logical replication launcher
root 9652 0.0 0.0 6208 884 pts/1 S+ 15:06 0:00 grep postgres
root(at)deb10tp:~#

yes, I see your point. makes sense. the unix socket is actually also on 5433

root(at)deb10tp:~# grep -nr 543 /etc/postgresql
/etc/postgresql/11/main/postgresql.conf:63:port = 5433
root(at)deb10tp:~# find /var/run/postgresql/ | grep 543
/var/run/postgresql/.s.PGSQL.5433
/var/run/postgresql/.s.PGSQL.5433.lock
root(at)deb10tp:~#

Anyway, given these settings, "psql -p 5433 -h localhost" should
connect. If you still get "connection refused" then you need to
look at the kernel firewall (packet filter) settings.

yes, with "-p 5433" I can connect

regards, tom lane

thanks