auto vacuum question

From: Alan Stange <stange(at)rentec(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: auto vacuum question
Date: 2023-08-28 20:06:21
Message-ID: f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-08-28 20:11:56 Re: auto vacuum question
Previous Message Mike Roest 2023-08-28 18:34:43 pg_visible_in_snapshot clarification