Re: [INTERNET] Re: auto vacuum question

On 8/28/23 13:19, Alan Stange wrote:
> On 8/28/23 16:11, Adrian Klaver wrote:
>> On 8/28/23 13:06, Alan Stange wrote:
>>> All,
>>>

>> Are you sure that is coming from autovacuum?
>>
>> What are the log lines preceding the WARNING?
>>
>> What is the complete warning line?
> Thank you for your quick response.
>
> The prefix to the logfile lines are a datetime stamp and then a number,
> 2062375 in the example that I happen to be looking at now.
>
> I am 99.9% that these log lines came from the autovacuum, as it went on
> for days, whereas a regular vacuum on our multi-TB data base takes a
> couple of hours.   We don't have any explicit vacuum or analyze jobs
> running or scheduled.

I went back to the source for a refresher:

https://doxygen.postgresql.org/autovacuum_8c_source.html

The autovacuum system is structured in two different kinds of processes:
the autovacuum launcher and the autovacuum worker. The launcher is an
always-running process, started by the postmaster when the autovacuum
GUC parameter is set. The launcher schedules autovacuum workers to be
started when appropriate. The workers are the processes which execute
the actual vacuuming; they connect to a database as determined in the
launcher, and once connected they examine the catalogs to select the
tables to vacuum.

...

Connect to the selected database, specifying no particular user

I read that as the autovacuumn process runs directly from the backend
and not as any user. Therefore it should not be running into a user
permissions problem.

1) Is this the community version of Postgres?

2) Did you do any other changes when you altered the superuser?

3) Did the log lines before the Warning have anything useful?

4) Are you sure there is not some admin app running that is doing VACUUMs?

>
> Alan
>
>
>>
>>> We restored the superuser role to the original 'red' login role and
>>> these messages went away.
>>>
>>> We are not running any explicit vacuum's.    As far as I can tell, these
>>> warnings were comming from the autovacuum processes, and we did also
>>> notice that some query stats became stale which I assume is related to
>>> these same warnings.
>>>
>>>
>>> So, I'm wondering how we can move the superuser role from role A to B,
>>> so that the autovacuum process will still work?   I googled around a
>>> bit, but didn't come up with anything useful for this.
>>>
>>> Thank you,
>>>
>>> Alan
>>>
>>>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com