Re: [GENERAL] Unable to start postgresql

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

I don't use Fedora so all I can do is point you at:
https://fedoraproject.org/wiki/Firewalld?rd=FirewallD

It does have a the concept of an internal zone:

https://fedoraproject.org/wiki/Firewalld?rd=FirewallD#Which_zones_are_available.3F

Not sure if that applies here though. I am beginning to suspect the
firewall is not the issue here though, so once we iron what is you could
probably undo the open port.

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

This is somewhat suspicious.

What if you shutdown the Postgres server and then su - postgres to and run?:

pg_ctl start -D /usr/pgsql_tablespaces

Along that line what user 'owns' /usr/pgsql_tablespaces?

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

Not so sure that this not actually indicating what Tom suggested that
there is an IPv4 config issue.

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

Well this indicates the socket is working.

What if you do?:

psql -U postgres -h ::1

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

That is set in pg_hba.conf. The default is:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

where trust is:

https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html

trust

     Allow the connection unconditionally. This method allows anyone
that can connect to the PostgreSQL database server to login as any
PostgreSQL user they wish, without the need for a password or any other
authentication. See Section 20.3.1 for details.


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


--
Adrian Klaver
adrian.klaver@aklaver.com