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 1qakqS-00AI82-8x for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 22:36:28 +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 1qakqR-00GnPU-2D for pgsql-general@arkaria.postgresql.org; Mon, 28 Aug 2023 22:36:26 +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 1qakqP-00GnO7-Sz for pgsql-general@lists.postgresql.org; Mon, 28 Aug 2023 22:36:26 +0000 Received: from resqmta-h1p-028596.sys.comcast.net ([2001:558:fd02:2446::4]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qakqK-001cKY-Tu for pgsql-general@postgresql.org; Mon, 28 Aug 2023 22:36:24 +0000 Received: from resomta-h1p-028517.sys.comcast.net ([96.102.179.206]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resqmta-h1p-028596.sys.comcast.net with ESMTP id abE9qCwLaNdOGakq7qpkUh; Mon, 28 Aug 2023 22:36:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1693262167; bh=5Gf/LA21UR2uWvXyjkBoTeBzycCz0Z5tgB9HJyc3i+I=; h=Received:Received:Received:From:To:Subject:Date:Message-ID: MIME-Version:Content-Type:Xfinity-Spam-Result; b=iEXzPPRLtETzjI1n8c2vvSP34ZBaWUB7J/Rpf2rV+6QM4hfokdoNOhBo3dYhR8UGR b6BD4U3AOXs9r+sYszs4IycilPGW3CBRzEULxJHkhq/SCb10cR96BgwsYPJjgKcmlW Eb0jrZSu+2uC1tOCaI59tha+MY5Sk4H8ufVg932sfDWvjX562A/jAGFdW/pmbxFlnD lKGfR+/YYE0zjYUy+TAaM/WFpmX46CQBuPBoAIa6RK72aeSaDCzW+BnvmAUOLm7Lpp 7jDX3f9Ny5hKMjPFay92MPd7sPC2q+OeXWrhIWfuMtZdzNOXQs2l69cBDfDxNI5Xw6 qptAwe4IMzl+Q== Received: from R9N0YC2TC3.localdomain ([134.238.215.18]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resomta-h1p-028517.sys.comcast.net with ESMTPA id akptqMjHmbXTjakpvqlBQK; Mon, 28 Aug 2023 22:36:05 +0000 Received: by R9N0YC2TC3.localdomain (Postfix, from userid 503) id 6136A21F45C4; Mon, 28 Aug 2023 17:35:53 -0500 (CDT) From: Jerry Sievers To: Adrian Klaver Cc: Alan Stange , pgsql-general General Subject: Re: auto vacuum question References: <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> Date: Mon, 28 Aug 2023 17:35:53 -0500 In-Reply-To: <41a740be-a8d7-b5c0-3054-c9bb7df4d362@aklaver.com> (Adrian Klaver's message of "Mon, 28 Aug 2023 13:11:56 -0700") Message-ID: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.1 (darwin) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 >> and >> then noticed some issues with the autovacuum processes.=C2=A0 We are run= ning >> 15.3, and had a login role, lets call it 'red', which had the superuser >> attribute assigned to it. =C2=A0 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.=C2=A0 After a couple of weeks and a lot of testin= g, we >> removed the superuser attribute from the original 'red' account.=C2=A0 A= ll >> was working as expected. >> After a bit we noticed some warnings in the postgresql server log >> file >> of the form 'WARNING:=C2=A0=C2=A0=C2=A0 skipping "tablexxx"=C2=A0=C2=A0 = --- 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.=C2=A0=C2=A0=C2=A0 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?=C2=A0=C2=A0 I googled ar= ound a >> bit, but didn't come up with anything useful for this. >> Thank you, >> Alan >>=20