Re: [GENERAL] Unable to start postgresql

On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
> John Iliffe <john.iliffe@iliffe.ca> writes:
> > I tried psql but it won't work, as expected, because socket 5432 is
> > not available.
>
> Actually, that's not all that expected.  psql by default would try to
> connect via a Unix socket, so it wouldn't matter whether or not the
> postmaster had been able to open an IPv4 port.  The most likely reason
> for failing to connect via Unix socket is looking in the wrong directory
> for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian
> is pressing you about other Postgres installations on the machine.  If
> your psql session is using a Red Hat-supplied libpq.so then it will
> likely look in /var/run/postgresql, whereas this stock-sources
> postmaster is going to have put it in /tmp by default.  (You could
> adjust the
> unix_socket_directories parameter to fix that.)  It would also help to
> pay close attention to the error message psql gives when it fails to
> connect.
>
> Even if you are doing "psql -h localhost", I'm pretty sure "localhost"
> will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it
> does on mine.  So if the postmaster successfully opened an IPv6 port,
> which I think it would do by default, then it still wouldn't matter that
> the IPv4 port wasn't there; the issue should still be masked.
>
> FWIW, this is what I see for network sockets when lsof'ing a stock
> postmaster on current Fedora 25:
>
> ...
> postmaste 20082  tgl    3u  IPv6              37256      0t0     TCP
> localhost:postgres (LISTEN) postmaste 20082  tgl    4u  IPv4
>   37257      0t0     TCP localhost:postgres (LISTEN) postmaste 20082
> tgl    5u  unix 0xffff9eb3435cfc00      0t0   37259 /tmp/.s.PGSQL.5432
> type=STREAM ...
>
> or with -n it looks like
>
> postmaste 20082  tgl    3u  IPv6              37256      0t0     TCP
> [::1]:postgres (LISTEN) postmaste 20082  tgl    4u  IPv4
> 37257      0t0     TCP 127.0.0.1:postgres (LISTEN) postmaste 20082  tgl
>    5u  unix 0xffff9eb3435cfc00      0t0   37259 /tmp/.s.PGSQL.5432
> type=STREAM
>
> What I am suspicious of at this point is that the root of the problem is
> networking misconfiguration on your machine, such that IPv4 doesn't work
> at all; given the platform's bias towards IPv6 for loopback, you might
> not have noticed otherwise.  You might check what results you get from
> "ping ::1" vs "ping 127.0.0.1" vs "ping localhost".
>
>             regards, tom lane

Hi Tom:

By now you have probably seen that I did get PSQL to run.  I had assumed
that meant that everything was at least running if not as expected, at
least running.  But I did an lsof and I don't get anything at all for
postmaster.  postgres has a lot of open files but they are libraries and so
forth so not applicable to this situation.

I did the pings you suggest with the following results:

-------------------------------------------
[root@prod04 John]# ping localhost
PING localhost(localhost (::1)) 56 data bytes
64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
^C
--- localhost ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4084ms
rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
[root@prod04 John]#
[root@prod04 John]# ping ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
^C
--- ::1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4108ms
rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
[root@prod04 John]#
[root@prod04 John]#
[root@prod04 John]# ping 127.0.0.1
PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
^C
--- 127.0.0.1 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3066ms
rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
[root@prod04 John]# ping 192.168.1.8   <--- current server as a network
test

PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
^C
--- 192.168.1.8 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 0.274/0.890/1.771/0.639 ms

--------------------------------------------

Also, I am connected to the server by ssh so at least that IPv4 function is
working.  (the router here only handles IPv4).

I did add 5432 to the firewalld configuration and rebooted to pick it up.

So, I think the network configuration is OK.  I set that up as the first task
after installing the OS.

John