public inbox for [email protected]
help / color / mirror / Atom feedpsql: error: could not connect to server: Connection refused
4+ messages / 2 participants
[nested] [flat]
* psql: error: could not connect to server: Connection refused
@ 2022-07-04 01:06 BeeRich Lists <[email protected]>
2022-07-04 01:45 ` Re: psql: error: could not connect to server: Connection refused Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: BeeRich Lists @ 2022-07-04 01:06 UTC (permalink / raw)
To: pgsql-novice
Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand why this isn’t showing up.
psql: error: could not connect to server: Connection refused
Is the server running on host "server1.local" (192.168.1.23) and accepting
TCP/IP connections on port 5432?
I cannot find an open port on that box using nmap for ports 5400-6500:
Starting Nmap 7.92 ( https://nmap.org ) at 2022-07-03 19:48 EDT
Nmap scan report for 192.168.1.23
Host is up (0.00041s latency).
All 1101 scanned ports on 192.168.1.23 are in ignored states.
Not shown: 1090 filtered tcp ports (no-response), 11 filtered tcp ports (admin-prohibited)
MAC Address: D0:50:99:87:7B:4A (ASRock Incorporation)
Nmap done: 1 IP address (1 host up) scanned in 5.94 seconds
listen_addresses = '*'
Here is my effective pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 md5
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all user1 192.168.1.4 255.255.255.0 md5
host all user1 192.168.1.24 255.255.255.0 md5
host all user1 192.168.1.0/24 md5
host all all 0.0.0.0/0 md5
Upon restart/start of the server, it spits this out:
Jul 03 18:07:51 server1.project33.ca postmaster[2321]: 2022-07-03 18:07:51.206 EDT [2321] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 03 18:07:51 server1.project33.ca postmaster[2321]: 2022-07-03 18:07:51.213 EDT [2321] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Those directives are the same in the server settings:
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
tcp_user_timeout | 0 | TCP user timeout.
How I can get this to be visible on the LAN and allow me to connect? For some reason TCP sockets aren’t showing up, yet I’ve set listen_addresses to '*'. I can also ssh into the box, so TCP sockets are running and runctional.
Cheers, Bee
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: psql: error: could not connect to server: Connection refused
2022-07-04 01:06 psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
@ 2022-07-04 01:45 ` Tom Lane <[email protected]>
2022-07-04 03:21 ` Re: psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-07-04 01:45 UTC (permalink / raw)
To: BeeRich Lists <[email protected]>; +Cc: pgsql-novice
BeeRich Lists <[email protected]> writes:
> Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand why this isn’t showing up.
It sure looks like the postmaster doesn't think you told it to
listen on any TCP sockets, because if it did they'd have been
listed in the initial log output. Are you sure that this setting
is really the active one?
> listen_addresses = '*'
It could also be that there is something weird about your system
environment that is causing '*' to expand to nothing. Try
explicitly specifying '127.0.0.1, ::1' instead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: psql: error: could not connect to server: Connection refused
2022-07-04 01:06 psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
2022-07-04 01:45 ` Re: psql: error: could not connect to server: Connection refused Tom Lane <[email protected]>
@ 2022-07-04 03:21 ` BeeRich Lists <[email protected]>
2022-07-04 03:45 ` Re: psql: error: could not connect to server: Connection refused Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: BeeRich Lists @ 2022-07-04 03:21 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-novice
Oops, a re-reply:
Hi Tom. Thanks for the reply.
That’s what I’m thinking as it’s not showing up. However I have told it to listen on '*'. Here are relevant settings:
port | 5432 | Sets the TCP port the server listens on.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
tcp_user_timeout | 0 | TCP user timeout.
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
Other than that I’m not sure how to turn that on. BTW those items come from `show all;`. In my postgresql.conf I have this:
listen_addresses = '*' # what IP address(es) to listen on;
Oh look at this…I changed that out as you expected, as well as its static IP on the LAN, and this showed up on restart:
Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.720 EDT [2534] LOG: listening on IPv4 address "192.168.1.23", port 5432
Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.720 EDT [2534] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.728 EDT [2534] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
More testing…
OK so it seems on the machine, the port is open (nmap localhost, nmap box.local, nmap <LAN IP address>, but it is not showing up on my workstation for some reason. But I think I found out why:
Not shown: 5474 filtered tcp ports (no-response), 26 filtered tcp ports (admin-prohibited)
admin-prohibited? Port 5432 is showing up on that machine, but not on my workstation. So the firewall is active, and here’s the firewall list:
$ sudo firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: enp0s25
sources:
services: cockpit dhcpv6-client ssh
ports:
protocols:
forward: yes
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
It seems I need to add postgresql as a `service`. `firewall-cmd --get-services` shows postgresql as a service.
It worked! It was the firewall that needed a port hole poked in it. This is what I did:
$ sudo firewall-cmd --zone=public --permanent --add-service=postgresql
$ sudo firewall-cmd --zone=public --permanent --add-port 5432/tcp
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: enp0s25
sources:
services: cockpit dhcpv6-client postgresql ssh
ports: 5432/tcp
protocols:
forward: yes
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
And I can get in using psql as well.
Well done, Tom!
Cheers, Bee
> On Jul 3, 2022, at 9:45 PM, Tom Lane <[email protected]> wrote:
>
> BeeRich Lists <[email protected]> writes:
>> Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand why this isn’t showing up.
>
> It sure looks like the postmaster doesn't think you told it to
> listen on any TCP sockets, because if it did they'd have been
> listed in the initial log output. Are you sure that this setting
> is really the active one?
>
>> listen_addresses = '*'
>
> It could also be that there is something weird about your system
> environment that is causing '*' to expand to nothing. Try
> explicitly specifying '127.0.0.1, ::1' instead.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: psql: error: could not connect to server: Connection refused
2022-07-04 01:06 psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
2022-07-04 01:45 ` Re: psql: error: could not connect to server: Connection refused Tom Lane <[email protected]>
2022-07-04 03:21 ` Re: psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
@ 2022-07-04 03:45 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-07-04 03:45 UTC (permalink / raw)
To: BeeRich Lists <[email protected]>; +Cc: pgsql-novice
BeeRich Lists <[email protected]> writes:
> It worked! It was the firewall that needed a port hole poked in it.
Huh, interesting. What I'd have expected from a firewall problem
is that the postmaster thinks it's listening on the port (and says
so at startup), but then no traffic actually reaches it. The fact
that you didn't see any log entry about "listening on IPv4 address
so-and-so" suggests that your machine's version of a firewall is
interfering with even binding to the address. Not impossible
I guess, but it's a new one on me.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-07-04 03:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-04 01:06 psql: error: could not connect to server: Connection refused BeeRich Lists <[email protected]>
2022-07-04 01:45 ` Tom Lane <[email protected]>
2022-07-04 03:21 ` BeeRich Lists <[email protected]>
2022-07-04 03:45 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox