Re: [GENERAL] Unable to start postgresql

On Thursday 09 March 2017 09:17:51 Adrian Klaver wrote:
> On 03/08/2017 09:28 PM, Tom Lane wrote:
> > John Iliffe <john.iliffe@iliffe.ca> writes:
> >> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> >>> That isn't proving a lot: as I showed in my example lsof output,
> >>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> >>> port number. (I'm sure there's a way to turn that off, but -n ain't
> >>> it.)
> >>
> >> Yes, but your lsof output also showed a line for postmaster and mine
> >> doesn't.
> >
> > That's because I started mine by saying "postmaster" not "postgres".
> > It's not real relevant, just ancient habit of mine.
> >
> >> In your case postmaster has an IPv6 TCP socket (but no IPv4 I
> >> notice)
> >
> > Uh, what?  I showed an IPv6, an IPv4, and a Unix socket.
> >
> >> The following is from ss, the new version of netstat:
> >> ------------------------------------
> >> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> >> tcp    LISTEN     0      128       ::1:postgres             :::*
> >> ------------------------------------
> >
> > Well, that's pretty interesting, because it proves that *something*
> > has got IPv4 port 5432 open.  If not your manually-started
> > postmaster, then what?  You need to inquire into that a bit harder.
> > Running lsof as root and examining all processes might help.
>
> Or using ss, something like:
>
> sudo ss -l -p| grep post
----------------------------------
> (1) u_str  LISTEN     0      128    /tmp/.s.PGSQL.5442 15355
> * 0                     users:(("postmaster",pid=848,fd=5))
-----------------------------------
> (2)  u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 15913
* 0       users:(("postmaster",pid=849,fd=5))
----------------------------------
> (3)  tcp    LISTEN    > 0      128     *:postgresql      *:*
> users:(("postmaster",pid=849,fd=3))
-----------------------------------
> (4)  tcp    LISTEN     0      128    127.0.0.1:5442                  *:*
> users:(("postmaster",pid=848,fd=4))
-----------------------------------
> (5)  tcp    LISTEN     0      128   :::postgresql           :::*
> users:(("postmaster",pid=849,fd=4))
-----------------------------------
> (6)  tcp    LISTEN     0      128      ::1:5442                 :::*
> users:(("postmaster",pid=848,fd=3))
>
> >             regards, tom lane

Thanks folks.  I'm getting way out of my depth here.  My ss output is
below.

***I have reformatted Adrian's output above because word wrap made it
unreadable in this reply.  I also numbered the separate lines so I could
reference what I am saying**

Comparing my results with Adrian's example, I notice that we both have the
Unix domain socket 5432 running (1) and we both have an IPv4 and an IPv6
socket open for postgres (3) and (5) BUT he has a couple of TCP sockets
that I don't have.(4) and (6) that are assigned specifically to 5442 in his
case.  Shouldn't I have these same lines but assigned to 5432?

I'm assuming that there are two instances of postgresql running on his
machine (?) on sockets 5432 and 5442 (?).

Here is my matching ss output

------------------------------
[root@prod04 John]# ss -l -p | grep post
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422                 *
0                     users:(("postgres",pid=2760,fd=5))
tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
users:(("postgres",pid=2760,fd=4))
tcp    LISTEN     0      128       ::1:postgres             :::*
users:(("postgres",pid=2760,fd=3))
[root@prod04 John]#
---------------------------------

I also stopped and restarted postgresql to see if SELinux picked up
anything on any tcp activity and it did not, so it doesn't seem to be the
culprit.

I ran netstat and grepped 5432 and no hits so nothing else has this socket.
Just as a test I changed the socket in postgresql.conf to 9876 and tried
again.  Same results as above.

[postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
Cannot assign requested address
HINT:  Is another postmaster already running on port 9876? If not, wait a
few seconds and retry.

Referring back to an earlier post, I changed the location for the domain
socket and lock file from /tmp to /var/run/postgres in the postgresql.conf
file and set the permissions so postgres could run.  Now psql complains that
the domain socket is not present.  There doesn't seem to be a config entry
or file for psql.  What did I miss?

John