Re: [INTERNET] Re: auto vacuum question

On 8/28/23 18:35, Jerry Sievers wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
>>>