Обсуждение: psql: error: could not connect to server: Connection refused
Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand
whythis 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
socketswill 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-domainsocket.
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’veset listen_addresses to '*'. I can also ssh into the box, so TCP sockets are running and runctional.
Cheers, Bee
BeeRich Lists <bee.lists@gmail.com> writes:
> Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand
whythis 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
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 showingup 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’sthe 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 <tgl@sss.pgh.pa.us> wrote: > > BeeRich Lists <bee.lists@gmail.com> writes: >> Hi folks. I cannot connect to my postgres server. I’ve run through all the directives and I still can’t understand whythis 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
BeeRich Lists <bee.lists@gmail.com> 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