Re: [GENERAL] Unable to start postgresql - Mailing list pgsql-general

On 03/08/2017 01:48 PM, John Iliffe wrote:
> 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
>
> --------------------------------------------

So what does /etc/hosts contain?

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


--
Adrian Klaver
adrian.klaver@aklaver.com