Re: [GENERAL] Unable to start postgresql

On Wednesday 08 March 2017 17:22:21 Adrian Klaver wrote:
> 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?
----------------------------
[root@prod04 John]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4
        localhost4.localdomain4
::1         localhost localhost.localdomain localhost6
        localhost6.localdomain6
192.168.1.8 prod03.coaxpublications.ca localhost
192.168.1.1 default
----------------------------

Fairly standard I should think.

There is no internal DNS server, I rely on the Internet DNS to resolve the
internal connections to the various named web servers but they all run on
the same instance of Apache anyway.

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

In case it is relevant, the postgresql.conf file is the default that is
generated by install.  I was unable to move to /var/run due to permission
problems.  I know how to resolve this but I don't want too many balls in
the air at once.

John