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
Re: [INTERNET] Re: auto vacuum question
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
On 8/28/23 18:35, Jerry Sievers wrote: > Adrian Klaver 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 >>>
question about sql comments in postgresql server logs
Hello all, In order to track down some bugs, we thought it would be useful to append some comments to the sql being sent to postgresql like this: select foo from bar -- a comment with some metadata however, the postgresql server was not including the appended comment in the log file. If we instead sent select foo from /* a comment with some metadata */ bar then the comment was included in the server log file as we had hoped. Is there some way can get the statement as sent to the server emitted into the log file in a case like this? I looked through the server documentation and don't see an option that would clearly alter this behavior. Thank you, Alan
Re: question about sql comments in postgresql server logs
On 8/6/21 3:14 PM, Tom Lane wrote: > Alan Stange writes: >> In order to track down some bugs, we thought it would be useful to >> append some comments to the sql being sent to postgresql like this: >> select foo from bar -- a comment with some metadata >> however, the postgresql server was not including the appended comment in >> the log file. >> If we instead sent >> select foo from /* a comment with some metadata */ bar >> then the comment was included in the server log file as we had hoped. > If memory serves, this is a psql behavior not the server's. > I counsel using /* ... */ comments for the purpose. Thank you Tom, I tried both psql and connecting through jdbc and both had the same behavior, so assumed this was something in the server. We will go with /* */ to resolve the immediate problem, and will ask on the jdbc mailing list about this. Being able to freely append some comments for debugging/tracking purposes in the server logs seems like a useful feature. Thank you, Alan
pg_upgrade question
Hello all, We're running a 13.x installation and looking to upgrade to 14.1. This is all on Linux servers. We have a main instance running with a number of hot standby replicas configured. In the past, we have done a dump/restore on a slow evening and then rsynced all the bits around so that the main server and all replicas were identical at the start. The database has gotten larger now (~1TB) so that is a less desirable option. So I am thinking to do a pg_upgrade --link on each of the main and the hot standby replicas, and then restarting all the processes. I'm sure this will work fine on the main server, but I have not seen any mention of this working in the expected way on the hot standby replicas. Can someone confirm that the steps I described here will work in the way I am optimistically expecting it will? I should mention that we are running on ZFS, and will take a snapshot prior to the upgrade, so that if something goes sideways with our use of --link we can revert back instantly. Thank you, Alan
Re: pg_upgrade question
Of course that would be in the manual ;-) Thank you for pointing this out. We've been doing upgrades the reliable old school way for so long that I wasn't aware that something better was already well documented. Thank you, Alan On 12/2/21 11:10, Adrian Klaver wrote: > On 12/2/21 08:05, Alan Stange wrote: >> Hello all, >> >> We're running a 13.x installation and looking to upgrade to 14.1. This >> is all on Linux servers. We have a main instance running with a number >> of hot standby replicas configured. In the past, we have done a >> dump/restore on a slow evening and then rsynced all the bits around so >> that the main server and all replicas were identical at the start. The >> database has gotten larger now (~1TB) so that is a less desirable option. >> >> So I am thinking to do a pg_upgrade --link on each of the main and the >> hot standby replicas, and then restarting all the processes. I'm sure >> this will work fine on the main server, but I have not seen any mention >> of this working in the expected way on the hot standby replicas. Can >> someone confirm that the steps I described here will work in the way I >> am optimistically expecting it will? > Have you looked at steps 9 & 11 here?: > > https://www.postgresql.org/docs/current/pgupgrade.html > >> I should mention that we are running on ZFS, and will take a snapshot >> prior to the upgrade, so that if something goes sideways with our use of >> --link we can revert back instantly. >> >> Thank you, >> >> Alan >> >> >
