Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qaiiQ-00AD4J-Bs for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 20:20:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qaiiP-00Ffi8-6Z for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 20:20:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qaiiO-00Ffhj-R1 for pgsql-general@lists.postgresql.org; Mon, 28 Aug 2023 20:20:00 +0000 Received: from mx1-es.rentec.com ([216.223.242.6]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qaiiL-001TSn-MX for pgsql-general@postgresql.org; Mon, 28 Aug 2023 20:19:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=rentec.com; i=@rentec.com; q=dns/txt; s=rtc081020; t=1693253997; x=1724789997; h=message-id:date:mime-version:subject:to:references:from: in-reply-to:content-transfer-encoding; bh=XDl6wb24EXTJREqQYAJgCeVUTqHtcbT+teb7Plfzt4U=; b=KgIkMUHNNWU+vPQacr4sg2S0R9bKwEKd00sP4H1hw/goU/H1Ek3VJTyU LYhifg2kG1AUwAZ848538o2Rz3vmfoNuGzjFnFXzRnMwCpF+hOfUa47Ps zUO6/aFzmA7JsKBG6fP5n3LyI4/c3TW3nWKLnw8XwpktCvjDGOWWZBKWB p4LCed1ZPU+nmsaid8eOlIVn4dBoJB5gyL5CrGyLuwdHhow/oztDyRTUc Dsmqj2rq4So0Z96GbtoNsvZveAtXf256EHWOgrJOGoQAFxB3OOYmzqmRz pYwcpBdGX3yPNUv/2HxOgGrU9uZeaMaVGJKWNsZ6rjIfK4BvreTyB1Hq6 g==; Message-ID: Date: Mon, 28 Aug 2023 16:19:55 -0400 MIME-Version: 1.0 Subject: Re: [INTERNET] Re: auto vacuum question Content-Language: en-US To: pgsql-general General References: <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> From: Alan Stange In-Reply-To: <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 >> >>