Обсуждение: auto vacuum question
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
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
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 >> >>
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
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 >>
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 >>>
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 >>>>