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

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

From

Joao Miguel Ferreira

Date:

04 February 2021, 17:20:10

Hello all,

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.

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

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

From

hubert depesz lubaczewski

Date:

04 February 2021, 17:26:27

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
thatmight 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

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

From

Joao Miguel Ferreira

Date:

04 February 2021, 17:36:28

On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz@depesz.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@deb10tp:/$ exit
root@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@deb10tp:~#

Thank you very much

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

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

From

Tom Lane

Date:

04 February 2021, 17:50:32

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

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

From

Joao Miguel Ferreira

Date:

04 February 2021, 18:02:56

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

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

From

Joao Miguel Ferreira

Date:

04 February 2021, 18:04:25

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

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

From

Joao Miguel Ferreira

Date:

04 February 2021, 18:08:07

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

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)

here is the full list:

root@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@deb10tp:~#

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

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

From

"Jain, Ankit"

Date:

05 February 2021, 16:16:26

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.miguel.c.ferreira@gmail.com>
Sent: Thursday, February 4, 2021 10:08 AM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: depesz@depesz.com; pgsql-general <pgsql-general@lists.postgresql.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.miguel.c.ferreira@gmail.com> wrote:

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)

here is the full list:

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

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

From

Joao Miguel Ferreira

Date:

05 February 2021, 16:55:58

Hello Jain

On Fri, Feb 5, 2021 at 1:16 PM Jain, Ankit <Ankit.Jain@snapon.com> wrote:

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

It was not necessary to change firewall settings because I was able to connect to the database with the "-p 5433" option

and all is fine now

thanks