Обсуждение: cant connect to localhost:5432 (but unix socket ok)

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

depesz

> Here are some details about the connection issue:
> root@deb10tp:~# id
> uid=0(root) gid=0(root) groups=0(root)
> root@deb10tp:~# su postgres
> postgres@deb10tp:/root$ id
> uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert)
> postgres@deb10tp:/root$ cd /
> postgres@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@deb10tp:/$ psql
> psql (11.9 (Debian 11.9-0+deb10u1))
> Type "help" for help.
> postgres=#
> Thank you
> Joao

Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com> writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz@depesz.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?

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.

                        regards, tom lane

Hi Tom

On Thu, Feb 4, 2021 at 2:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com> writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz@depesz.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?

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

root@deb10tp:~# grep -nr 543 /etc/postgresql
/etc/postgresql/11/main/postgresql.conf:63:port = 5433
root@deb10tp:~# find /var/run/postgresql/ | grep 543
/var/run/postgresql/.s.PGSQL.5433
/var/run/postgresql/.s.PGSQL.5433.lock
root@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

On Thu, Feb 4, 2021 at 3:02 PM Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com> wrote:

Hi Tom

On Thu, Feb 4, 2021 at 2:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com> writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz@depesz.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)

 

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

root@deb10tp:~# grep -nr 543 /etc/postgresql
/etc/postgresql/11/main/postgresql.conf:63:port = 5433
root@deb10tp:~# find /var/run/postgresql/ | grep 543
/var/run/postgresql/.s.PGSQL.5433
/var/run/postgresql/.s.PGSQL.5433.lock
root@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

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.

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