Thread: auto vacuum question

auto vacuum question

From
Alan Stange
Date:
All,

We recently changed the name of the superuser role in our database, and
then noticed some issues with the autovacuum processes.  We are running
15.3, and had a login role, lets call it 'red', which had the superuser
attribute assigned to it.   This was the original owner/creator of all
the database objects, and the login role ended up getting used in some
ways for which the superuser attribute was no longer appropriate. 

So we elected to make a new role, lets call it 'reddba', which had
tightly controlled entitlements, and which also had the super user
attribute added to it.  After a couple of weeks and a lot of testing, we
removed the superuser attribute from the original 'red' account.  All
was working as expected.

After a bit we noticed some warnings in the postgresql server log file
of the form 'WARNING:    skipping "tablexxx"   --- only table of
database owner can vacuum it" where tablexxx is many of the table names
in our system.

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



Re: auto vacuum question

From
Adrian Klaver
Date:
On 8/28/23 13:06, Alan Stange wrote:
> All,
> 
> We recently changed the name of the superuser role in our database, and
> then noticed some issues with the autovacuum processes.  We are running
> 15.3, and had a login role, lets call it 'red', which had the superuser
> attribute assigned to it.   This was the original owner/creator of all
> the database objects, and the login role ended up getting used in some
> ways for which the superuser attribute was no longer appropriate.
> 
> So we elected to make a new role, lets call it 'reddba', which had
> tightly controlled entitlements, and which also had the super user
> attribute added to it.  After a couple of weeks and a lot of testing, we
> removed the superuser attribute from the original 'red' account.  All
> was working as expected.
> 
> After a bit we noticed some warnings in the postgresql server log file
> of the form 'WARNING:    skipping "tablexxx"   --- only table of
> database owner can vacuum it" where tablexxx is many of the table names
> in our system.

Are you sure that is coming from autovacuum?

What are the log lines preceding the WARNING?

What is the complete warning line?

> 
> 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




Re: [INTERNET] Re: auto vacuum question

From
Alan Stange
Date:
On 8/28/23 16:11, Adrian Klaver wrote:
> On 8/28/23 13:06, Alan Stange wrote:
>> All,
>>
>> We recently changed the name of the superuser role in our database, and
>> then noticed some issues with the autovacuum processes.  We are running
>> 15.3, and had a login role, lets call it 'red', which had the superuser
>> attribute assigned to it.   This was the original owner/creator of all
>> the database objects, and the login role ended up getting used in some
>> ways for which the superuser attribute was no longer appropriate.
>>
>> So we elected to make a new role, lets call it 'reddba', which had
>> tightly controlled entitlements, and which also had the super user
>> attribute added to it.  After a couple of weeks and a lot of testing, we
>> removed the superuser attribute from the original 'red' account.  All
>> was working as expected.
>>
>> After a bit we noticed some warnings in the postgresql server log file
>> of the form 'WARNING:    skipping "tablexxx"   --- only table of
>> database owner can vacuum it" where tablexxx is many of the table names
>> in our system.
> 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.

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
>>
>>




Re: [INTERNET] Re: auto vacuum question

From
Adrian Klaver
Date:
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




Re: auto vacuum question

From
Jerry Sievers
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:

> On 8/28/23 13:06, Alan Stange wrote:
>
>> All,
>> We recently changed the name of the superuser role in our database,

My take on this, is that the *postmaster* user is perhaps the one that
the OP cut privileges on, and thus the launcher is (now) spawning
workers with less than full SU perms.

Just a guess.

FWIW

>> and
>> then noticed some issues with the autovacuum processes.  We are running
>> 15.3, and had a login role, lets call it 'red', which had the superuser
>> attribute assigned to it.   This was the original owner/creator of all
>> the database objects, and the login role ended up getting used in some
>> ways for which the superuser attribute was no longer appropriate.
>> So we elected to make a new role, lets call it 'reddba', which had
>> tightly controlled entitlements, and which also had the super user
>> attribute added to it.  After a couple of weeks and a lot of testing, we
>> removed the superuser attribute from the original 'red' account.  All
>> was working as expected.
>> After a bit we noticed some warnings in the postgresql server log
>> file
>> of the form 'WARNING:    skipping "tablexxx"   --- only table of
>> database owner can vacuum it" where tablexxx is many of the table names
>> in our system.
>
> Are you sure that is coming from autovacuum?
>
> What are the log lines preceding the WARNING?
>
> What is the complete warning line?
>
>> 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
>>



Re: [INTERNET] Re: auto vacuum question

From
Alan Stange
Date:
On 8/28/23 18:35, Jerry Sievers wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>
>> On 8/28/23 13:06, Alan Stange wrote:
>>
>>> All,
>>> We recently changed the name of the superuser role in our database,
> My take on this, is that the *postmaster* user is perhaps the one that
> the OP cut privileges on, and thus the launcher is (now) spawning
> workers with less than full SU perms.
>
> Just a guess.
>
> FWIW

Hypothetically speaking, for a friend, how would one change the
superuser that was assigned in the initdb command?      Your guess is
good, and it's clear now that the running database isn't accommodating
our removal of the superuser attribute from the original default role
created in the initdb command.

Thank you,

Alan


>
>>> and
>>> then noticed some issues with the autovacuum processes.  We are running
>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>> attribute assigned to it.   This was the original owner/creator of all
>>> the database objects, and the login role ended up getting used in some
>>> ways for which the superuser attribute was no longer appropriate.
>>> So we elected to make a new role, lets call it 'reddba', which had
>>> tightly controlled entitlements, and which also had the super user
>>> attribute added to it.  After a couple of weeks and a lot of testing, we
>>> removed the superuser attribute from the original 'red' account.  All
>>> was working as expected.
>>> After a bit we noticed some warnings in the postgresql server log
>>> file
>>> of the form 'WARNING:    skipping "tablexxx"   --- only table of
>>> database owner can vacuum it" where tablexxx is many of the table names
>>> in our system.
>> Are you sure that is coming from autovacuum?
>>
>> What are the log lines preceding the WARNING?
>>
>> What is the complete warning line?
>>
>>> 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
>>>




Re: [INTERNET] Re: auto vacuum question

From
Jerry Sievers
Date:
Alan Stange <stange@rentec.com> writes:

> On 8/28/23 18:35, Jerry Sievers wrote:
>
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>
>>> On 8/28/23 13:06, Alan Stange wrote:
>>>
>>>> All,
>>>> We recently changed the name of the superuser role in our database,
>> My take on this, is that the *postmaster* user is perhaps the one that
>> the OP cut privileges on, and thus the launcher is (now) spawning
>> workers with less than full SU perms.
>>
>> Just a guess.
>>
>> FWIW
>
> Hypothetically speaking, for a friend, how would one change the
> superuser that was assigned in the initdb command?      Your guess is
> good, and it's clear now that the running database isn't accommodating
> our removal of the superuser attribute from the original default role
> created in the initdb command.

Most sites I"ve worked at do *not* use the initdb user for anything but
administrator usage and create one or more regular users/roles with
appropriate object ownership to individual DBs therein.

NOt uncommonly you might wish to have an *owner* role for deployment
(DDL) activities and a lesser role for application use

Doing so help minimize blast radius in case the application is
compromised.

There are a number of finer details in all of this but essentially,
leave alone the initdb/postmaster user except for DBA like tasks.

HTH

>
> Thank you,
>
> Alan
>
>
>>
>>>> and
>>>> then noticed some issues with the autovacuum processes.  We are running
>>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>>> attribute assigned to it.   This was the original owner/creator of all
>>>> the database objects, and the login role ended up getting used in some
>>>> ways for which the superuser attribute was no longer appropriate.
>>>> So we elected to make a new role, lets call it 'reddba', which had
>>>> tightly controlled entitlements, and which also had the super user
>>>> attribute added to it.  After a couple of weeks and a lot of testing, we
>>>> removed the superuser attribute from the original 'red' account.  All
>>>> was working as expected.
>>>> After a bit we noticed some warnings in the postgresql server log
>>>> file
>>>> of the form 'WARNING:    skipping "tablexxx"   --- only table of
>>>> database owner can vacuum it" where tablexxx is many of the table names
>>>> in our system.
>>> Are you sure that is coming from autovacuum?
>>>
>>> What are the log lines preceding the WARNING?
>>>
>>> What is the complete warning line?
>>>
>>>> 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
>>>>