Re: [INTERNET] Re: auto vacuum question - Mailing list pgsql-general

From Adrian Klaver
Subject Re: [INTERNET] Re: auto vacuum question
Date
Msg-id a37aa146-5b8a-5784-07f1-a185c3c7ec0b@aklaver.com
Whole thread Raw
In response to Re: [INTERNET] Re: auto vacuum question  (Alan Stange <stange@rentec.com>)
List pgsql-general
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.klaver@aklaver.com




pgsql-general by date:

Previous
From: Alan Stange
Date:
Subject: Re: [INTERNET] Re: auto vacuum question
Next
From: Jerry Sievers
Date:
Subject: Re: auto vacuum question