From: Alan Stange <stange@rentec.com>
To: pgsql-general General <pgsql-general@postgresql.org>
Subject: auto vacuum question
Date: Mon, 28 Aug 2023 16:06:21 -0400
Message-ID: <f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: stange@rentec.com
Subject: Re: auto vacuum question
In-Reply-To: <f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com>
References: <f04e52fe-cc44-41f8-bc5b-818309bb482f@rentec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox