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

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

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)

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:~#

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<mailto: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<mailto: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<mailto:tgl@sss.pgh.pa.us>> wrote:
Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com<mailto:joao.miguel.c.ferreira@gmail.com>> writes:

On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <depesz@depesz.com<mailto: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

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