Re: [GENERAL] Unable to start postgresql

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

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

>>
>> They seem to be showing up on postmaster's stderr (ie, your terminal)
>> which again is the vanilla-configuration default if you didn't do
>> anything to edit the postgresql.conf settings.
>>
>> I suspect that you're used to the behavior of a vendor-configured
>> postgres package and have not taken the steps needed to make a build
>> from source behave the same way.  Recommend looking into what patches
>> the vendor package applies and what configure options are used.
>>
>
> No, actually I have been using postgresql since about 2007, always from
> manual installs.  Just never needed to ask a question before!  I run a
> small publishing business specializing an Amateur Radio training and we
> rely on these databases to run just about everything from sales to user
> support to accounting.  At the moment the old server is on pgsql 9.2.1
> which is about 5 years old.
>
> I almost never use the distro supplied application software because I've
> had problems with automatic updates making everything fail and no idea what
> happened.  This way the applications are where I put them and I know when
> there is an update.
>
> FYI, my core skills are emphatically NOT as a sysadmin or system
> programmer!
>
>> Having said all that, it's very un-obvious why you're failing to bind
>> to the IPv4 socket.  If there's no active postmaster on the machine,
>> that should be free.  I could believe SELinux blocking it, except that
>> the targeted SELinux policy shouldn't constrain a manually-started
>> postmaster at all --- not to mention that you're in permissive mode.
>> Seems like the answer must be elsewhere.
>>
>> You didn't mention what platform you're on, but the reference to SELinux
>> makes me think it's probably Red Hat.  A bit of digging in RH's support
>> portal turns up a few mentions of kernel bugs causing unexpected
>> EADDRNOTAVAIL errors, which matches this symptom ... so how up-to-date
>> is this server?
>>
> Good guess but no prize!  I am on Fedora 25, downloaded and installed last
> Saturday (4 March) and then patched to the current levels with dnf before I
> started to do the application software installation.
>
>>             regards, tom lane


--
Adrian Klaver
adrian.klaver@aklaver.com