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 1qaiVO-00ACVj-Q6 for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 20:06:34 +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 1qaiVN-00FQtw-8p for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 20:06:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qaiVM-00FQtR-KQ for pgsql-general@lists.postgresql.org; Mon, 28 Aug 2023 20:06:32 +0000 Received: from mx1-es.rentec.com ([216.223.242.6]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qaiVF-001b2a-Rx for pgsql-general@postgresql.org; Mon, 28 Aug 2023 20:06:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=rentec.com; i=@rentec.com; q=dns/txt; s=rtc081020; t=1693253185; x=1724789185; h=message-id:date:mime-version:to:from:subject: content-transfer-encoding; bh=mis+CZuO4bR/V+yg/af2fMlvI9I+i1xnmeO4LlrVmtw=; b=I/TQ9HjU9jUHee2Kd3+WE4MzF9hrMo5r0EKqodw59ZNRZp0kj5F/XBe+ BFqPBHO89DrooNfgb+I+sjYlrtbRuEDPlsLtJf67oI650MQOHJeByzaM0 nv5ET8HJXQPUan7f58gJByWB1ml3TcEbhsv1XRWmtb0uQzLT/yRRMDP/D Yr4UgZlPTTZHlNd6FaRNY9i8cKZ4te7pq2pY2hzRMTbncBon+goGbmMFR aCNq2KmJKloPBGxif6WsSlfuvdsKef/mnJNd2b755E49omnEEt1CbglPN c1xuioVlme9e1efKqKqCeS3jlP0j7k7tZyzeUQ1u1v/XmnCjyN68NTwQC Q==; Message-ID: Date: Mon, 28 Aug 2023 16:06:21 -0400 MIME-Version: 1.0 To: pgsql-general General Content-Language: en-US From: Alan Stange Subject: auto vacuum question 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 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