Re: [GENERAL] Unable to start postgresql

Поиск
Список
Период
Сортировка
От John Iliffe
Тема Re: [GENERAL] Unable to start postgresql
Дата
Msg-id 201703081628.00928.john.iliffe@iliffe.ca
обсуждение исходный текст
Ответ на Re: [GENERAL] Unable to start postgresql  (Adrian Klaver <adrian.klaver@aklaver.com>)
Ответы Re: [GENERAL] Unable to start postgresql  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
On Wednesday 08 March 2017 11:18:59 Adrian Klaver wrote:
> On 03/08/2017 07:37 AM, John Iliffe wrote:
> > On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:
> >> John Iliffe <john.iliffe@iliffe.ca> writes:
> >>> Now, running as user postgres I try and start as stated in the
> >>> manual postgres -D /usr/pgsql_tablespaces
> >>>
> >>> The result is:
> >>> [postgres@prod04 postgresql-9.6.2]$ postgres -D
> >>> /usr/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-07 22:22:57 EST
> >>> LOG:  MultiXact member wraparound protections are now enabled
> >>> LOG:  database system is ready to accept connections
> >>> LOG:  autovacuum launcher started
> >>
> >> To clarify: the postmaster *is* starting here.  It failed to bind to
> >> the IPv4 port 5432, but it must have succeeded in binding to at
> >> least one other port (IPv6 and/or a Unix socket), else it would have
> >> stopped and you'd have not seen the last four log lines.
> >>
> >> It might be helpful to check with lsof to see what the postmaster
> >> process has open after you do this.
> >
> > I noticed that when I deleted the postmaster.pid file as suggested by
> > another answer and restarted that process issued a lot more messages
> > before crashing  :-(   Still couldn't connect to port 5432 though.
>
> Have you tried the firewall setup from here:
>
> https://fedoraproject.org/wiki/PostgreSQL
>   Firewall
>
> PostgreSQL operates on port 5432 (or whatever else you set in your
> postgresql.conf). In firewalld you can open it like this:
>
> $ # make it last after reboot
> $ firewall-cmd --permanent --add-port=5432/tcp
> $ # change runtime configuration
> $ firewall-cmd --add-port=5432/tcp
>

OK, I tried this, along with some suggestions from other responses.  I also
rebooted to get a completely clean environment again, and have the
following results:

1.  the firewall now has port 5432 added permanently.  This seems to me to
be a security exposure since the socket connection that I need is an
INTERNAL (ie on the same machine) connection, not an incoming connection
from another machine.  Does anyone have any comments on that?

2.  The start up messages (still on the screen for convenience) are:

-------------------------------
[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
[postgres@prod04 John]$ 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 10:40:27 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
------------------------------------

3.   I found the config file (in the tablespace????) and changed socket file
to /var/run/.  That caused a failure of the database since user postgres
does not have write authority on /var/run.  That isn't the problem at the
moment so I'll file it to think about later!

There is a socket and a lock file for PGSQL in the /tmp directory.

srwxrwxrwx.  1 postgres postgres   0 Mar  8 15:32 .s.PGSQL.5432
-rw-------.  1 postgres postgres  49 Mar  8 15:32 .s.PGSQL.5432.lock

4.  I did a shut down of postmaster to be sure these weren't abandoned files
and they disappeared.  So I conclude that socket #5432 was, in fact,
connected at start up (???) despite what the log says.  The pid file also
disappeared as expected.

5.  Restarted,  Same messages as before.  The message says fairly
specifically that it can't bind an IPv4 socket.  Is there a chance that
there is an IPv6 socket involved here somewhere that I'm not seeing?

6  Because:

----------------------------------------------------
psql -U postgres
psql (9.6.2)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres
+
           |          |          |             |             |
postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres
+
           |          |          |             |             |
postgres=CTc/postgres
(3 rows)

postgres=#
-------------------------------------------------------

So, to the extent I can test at the moment, it looks like I have a working
database with a lot of strange messages.  Odd that user postgres can log on
without a password but I guess that is an hba file issue to fix.

Whether an external programme, such as one of the web server programmes can
use it is an open question since the web server isn't installed yet.

More at end.

> More comments below.
>
> > -------------------------------
> > [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
> > could not change directory to "/home/John": Permission denied
> > server starting
> > [postgres@prod04 John]$ 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 interrupted; last known up at 2017-03-08
> > 09:42:16 EST
> > LOG:  database system was not properly shut down; automatic recovery
> > in progress
> > LOG:  invalid record length at 0/1561138: wanted 24, got 0
> > LOG:  redo is not required
> > LOG:  MultiXact member wraparound protections are now enabled
> > LOG:  database system is ready to accept connections
> > LOG:  autovacuum launcher started
> > -----------------------------
> >
> > so I corrected the initial error by changing to the bin directory and
> > starting again, after removing the postmaster.pid file.  Same result.
> >
> > lsof says that there is nothing assigned to postmaster at this time.
> >
> > I did manage to get a clean stop this time; no remaining pid file.
> >
> >>> I checked with lsof and there is no process bound to socket 5432.
> >>> There is no entry in /var/run for a socket related to postgresql.
> >>
> >> With the default configure options you used, the postmaster would
> >> have put its Unix socket file into /tmp, not /var/run.  I wonder
> >> whether your problem is that you're trying to connect to it with
> >> distro-supplied psql+libpq that expects to find the Unix socket in
> >> /var/run.
> >
> > Yes.  socket file and also lock file were there.  I'll fix that in
> > config, BUT in the original case they weren't there.
>
> So what is the below?
>
> > srwxrwxrwx.  1 postgres postgres        0 Mar  8 10:10 .s.PGSQL.5432
> > -rw-------.  1 postgres postgres       49 Mar  8 10:10
> > .s.PGSQL.5432.lock
> >
> > Still, the first lines of the log are the same; can't connect to
> > socket 5432.
> >
> > The following processes show up in ps
> >
> > root      1149  1136  0 10:18 pts/1    00:00:00 su postgres
> > postgres  1150  1149  0 10:18 pts/1    00:00:00 bash
> > postgres  1230     1  0 10:26 pts/1    00:00:00
> >
> >     /usr/postgres-9.6.2/bin/postgres -D /usr/pgsql_tablespaces
> >
> > postgres  1232  1230  0 10:26 ?        00:00:00 postgres: checkpointer
> >
> >      process
> >
> > postgres  1233  1230  0 10:26 ?        00:00:00 postgres: writer
> > process postgres  1234  1230  0 10:26 ?        00:00:00 postgres: wal
> > writer
> >
> >      process
> >
> > postgres  1235  1230  0 10:26 ?        00:00:00 postgres: autovacuum
> >
> >      launcher process
> >
> > postgres  1236  1230  0 10:26 ?        00:00:00 postgres: stats
> > collector
> >
> >      process
> >
> > ----------------------------
>
> So Postgres is running.
>
> >>> One thing that I haven't been able to find any the log files.  Where
> >>> are they normally stored?
>
> Where you configure them:
>
> https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html
>
A few comments:

1.  I know all you folks are volunteers and I would like to express my
sincere thanks for the rapid and detailed responses.

2.  I have installed many Linux, and other Unix, instances in the past and
this is the first time I have encountered a "default security on" situation.
Usually I get to configure the server first and then turn on the security!
Also, I didn't specify in the software selection screen that I needed a
firewall so I was caught by surprise on that.  Thank you for telling me to
check that.  I would have looked for iptables based on experience, not
firewalld.

3.  I'm not at all sure that this is a viable configuration of PostgreSQL.
If anyone reading this has any comments I humbly await them!

Regards,

John




В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Unable to start postgresql
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Unable to start postgresql