Re: 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(dot)iliffe(at)iliffe(dot)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(at)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(at)prod04 John]# su postgres
> [postgres(at)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(at)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(at)prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
>>> could not change directory to "/home/John": Permission denied
>>> server starting
>>> [postgres(at)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(dot)klaver(at)aklaver(dot)com