Re: [GENERAL] Unable to start postgresql

On Wednesday 08 March 2017 17:11:16 Adrian Klaver wrote:
> On 03/08/2017 02:01 PM, John Iliffe wrote:
>
> Please use Reply All so others get your posts.
> Ccing list
>
> > On Wednesday 08 March 2017 16:40:03 you wrote:
> >> On 03/08/2017 01:28 PM, John Iliffe wrote:
> >>> -------------------------------
> >>> [root@prod04 John]# su postgres
> >>> [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
> >>> could not change directory to "/home/John": Permission denied
> >>> server starting
> >>
> >> This is somewhat suspicious.
>
> For the list the below is from a previous message which I forgot to Cc.
>
> >> What if you shutdown the Postgres server and then su - postgres to
> >> and
> >
> >> run?:
> > I was starting postgres as user postgres; but it was still in my home
> > directory. Everything did start but I changed to /usr/pgsql_dataspaces
> > and the message went away.  Since there is no requirement to actually
> > cd to anything the message is a bit of a red herring.
> >
> > That said, I'm not sure how it will be handled by the system start up
> > task. I'll have to check when I add it.  These are unusual manual
> > (re)starts.
> >
> >> pg_ctl start -D /usr/pgsql_tablespaces
> >>
> >> Along that line what user 'owns' /usr/pgsql_tablespaces?
> >
> > /usr/pgsql_tablespaces is owned by postgres, group postgres, with
> > permissions drwe...... on the directory and rw....... on all the
> > files. This would seem correct to me.
> >
> >> Well this indicates the socket is working.
> >>
> >> What if you do?:
> >>
> >> psql -U postgres -h ::1
> >
> > [root@prod04 John]# psql -U postgres -h ::1
> > psql (9.6.2)
> > Type "help" for help.
>
> So Postgres is binding to IPv6.
>
> >> That is set in pg_hba.conf. The default is:
> >>
> >> # "local" is for Unix domain socket connections only
> >> local   all             all                                     trust
> >
> > Yes, I recall major changes to that file that I did on the current
> > server. But that was at least 5 years ago :-(
> >
> > I have that one fairly tightly locked down since this is a web server.
> >
> >>>>> lsof says that there is nothing assigned to postmaster at this
> >>>>> time.
>
> Given that you can connect I have to believe lsof would show something,
> so what options are you using with lsof and what user are you running it
> as?

[root@prod04 John]# lsof | grep postmaster
[root@prod04 John]#

No parameters so I get the whole list.  As noted postmaster doesn't find
anything whereas I think that I said that the hits on postgres are for the
table space, the libraries and such.  I did find the following just now for
postgres:

postgres  1991      postgres    5u     unix 0xffff9e53a16edc00       0t0
       63085 /tmp/.s.PGSQL.5432 type=STREAM

That said, the start up error still shows up, including on the run that I
extracted that from.

------------------------------
[postgres@prod04 John]$ cd /usr/pgsql_tablespaces
[postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces
server starting
[postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
        Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a
        few seconds and retry.
LOG:  database system was shut down at 2017-03-08 23:05:14 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
--------------------------------
[postgres@prod04 pgsql_tablespaces]$ exit
exit
[root@prod04 John]# lsof -n | grep postmaster
[root@prod04 John]# lsof -n | grep postgres | grep 5432
postgres  2760      postgres    5u     unix 0xffff9e5390b5b800       0t0
         69422 /tmp/.s.PGSQL.5432 type=STREAM
[root@prod04 John]#
----------------------------------

This is a domain socket I think, in which case it still isn't getting the
IP socket for some reason.

Regards,

John