Обсуждение: Issue enabling track_counts to launch autovacuum in 9.4.5

Derek Elder wrote:

> From what I had read, this setting should be on by default. When I checked
> our other servers I see that track_counts is on and the autovacuum process
> is working correctly on them. Indeed we don't even have the setting
> explicitly listed in our postgresql.conf on these servers.
>
> We first tried a simple restart, but that didn't work.

Try
  ALTER SYSTEM RESET track_counts;

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Derek Elder <dereke@mirthcorp.com> writes:
> That was indeed the root cause. The /etc/hosts file on the server had
> incorrect permissions which caused localhost to not resolve.

It strikes me that this should not have been so hard to solve.  The
stats collector was trying to tell you what was wrong, but evidently
you could not interpret those messages correctly.  I am thinking that
we need to do some work on the message wording; or maybe there is one
more message that needs to be emitted so you can follow the causal
chain?

In particular, perhaps it wasn't immediately obvious that the first
of these messages was the cause of the second:

> 2016-03-02 14:58:09 EST [14366]: [8-1] LOG: could not resolve "localhost": Name or service not known
> 2016-03-02 14:58:09 EST [14366]: [9-1] LOG: disabling statistics collector for lack of working socket

in which case maybe we could rephrase the first message along the
lines of "could not resolve "localhost" to establish statistics
collector socket: <strerror detail here>".  (There are a few other
messages in the same area that would need to be changed similarly.)

Or maybe the problem was that when we forced track_counts off because of
no stats collector, we didn't emit any bleat noting that, which if we had
might have led you to realize that the above messages were the direct
cause of the next one:

> 2016-03-02 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started because of misconfiguration
> 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts" option.

Or both changes, or something else entirely?

I'd be interested to hear how you perceived these log messages and
what you think might help the next person.

            regards, tom lane

On 03/02/2016 02:49 PM, Tom Lane wrote:

> Or maybe the problem was that when we forced track_counts off because of
> no stats collector, we didn't emit any bleat noting that, which if we had
> might have led you to realize that the above messages were the direct
> cause of the next one:
>
>> 2016-03-02 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started because of misconfiguration
>> 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts" option.
>
> Or both changes, or something else entirely?
>
> I'd be interested to hear how you perceived these log messages and
> what you think might help the next person.

I can tell you that as I read them, they meant nothing and I know what
they mean. This is why:

2016-03-02 14:58:09 EST [14366]: [8-1] LOG: could not resolve
"localhost": Name or service not known
2016-03-02 14:58:09 EST [14366]: [9-1] LOG: disabling statistics
collector for lack of working socket

Why would I care about a "LOG" message. Generally speaking they don't
tell us anything useful. If, it was:

ERROR: could not resolve "localhost" ...
ERROR: Disabling statistics collector ....

It would mean a whole lot more. The second set makes sense to me but
what isn't clear is that the first one is really an error that can cause
the warnings of the second.

I do think that it should be an error if we are disabling statistics due
to something being wrong. We can't really operate properly without them.

Sincerely,

JD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ​The fact that the first two are only LOG level and not WARNING would seems
> like the easiest improvement to make.

Unfortunately, that would be a disimprovement, because in many common
configurations WARNING messages don't appear in the postmaster log at all.
In fact, I'd say it's a bug that the "autovacuum not started" message is
emitted as a WARNING not LOG.

Maybe we need some way of printing something at LOG priority but having
the printed text be WARNING.  I'm afraid that might cause about as much
confusion as it would solve, though.

> It probably would help to specify, if known, whether the suspected
> mis-configuration is external or internal to PostgreSQL - i.e., do I need
> to fix postgres.conf or is something external (like the hosts file) to
> blame.

In the case of a name resolution failure, the problem is certainly
external to Postgres, but we don't have enough information to say more
than that.  We could print a hint guessing at causes (like broken
/etc/host or /etc/resolv.conf files), but it would be guesses --- and
I'm afraid there's enough cross-system variation in the way this stuff is
configured that any hint would be likely to just be misleading.

> This is getting a bit deep for a rare problem like this - I think that
> making ​the root messages WARNING (or ERROR)

ERROR would mean that the postmaster fails to start at all.  That doesn't
seem like an improvement either.

            regards, tom lane