Thread: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
Running 8.2.15 on RHEL4 the log reports this from autovacuum along with other pg_ tables: VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it Any ideas how I can clean this up or how I got into this hole? Vacuum works as superuser. Thanks.
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Scott Marlowe
Date:
On Sat, Jan 30, 2010 at 7:59 AM, Ray Stell <stellr@cns.vt.edu> wrote: > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > other pg_ tables: > > VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it > > Any ideas how I can clean this up or how I got into this hole? > Vacuum works as superuser. Make yourself the owner of the database? (i.e. alter database ...)
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
On Sat, Jan 30, 2010 at 10:28:37AM -0700, Scott Marlowe wrote: > On Sat, Jan 30, 2010 at 7:59 AM, Ray Stell <stellr@cns.vt.edu> wrote: > > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > > other pg_ tables: > Make yourself the owner of the database? (i.e. alter database ...) autovacuum is throwing these. If I vacuum as the current owner it seems to work: template1=# VACUUM VERBOSE ANALYZE pg_statistic; INFO: vacuuming "pg_catalog.pg_statistic" INFO: index "pg_statistic_relid_att_index" now contains 298 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_statistic": found 0 removable, 298 nonremovable row versions in 9 pages DETAIL: 0 dead row versions cannot be removed yet. There were 2 unused item pointers. 2 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_2619" INFO: index "pg_toast_2619_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_2619": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Alvaro Herrera
Date:
Ray Stell wrote: > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > other pg_ tables: > > VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it Huh, that's pretty weird ... autovacuum is supposed to connect as superuser internally. Did you do something funny to the system role (typically called "postgres")? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote: > Ray Stell wrote: > > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > > other pg_ tables: > > > > VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it > > Huh, that's pretty weird ... autovacuum is supposed to connect as > superuser internally. Did you do something funny to the system role > (typically called "postgres")? agreed. If I did, I don't know what it was. I suppose I'm reduced to creating a new cluster and restoring the apps into it, unless somebody can suggest an audit method that would uncover the injury.
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Scott Marlowe
Date:
On Sat, Jan 30, 2010 at 7:00 PM, Ray Stell <stellr@cns.vt.edu> wrote: > On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote: >> Ray Stell wrote: >> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with >> > other pg_ tables: >> > >> > VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it >> >> Huh, that's pretty weird ... autovacuum is supposed to connect as >> superuser internally. Did you do something funny to the system role >> (typically called "postgres")? > > > agreed. If I did, I don't know what it was. I suppose I'm reduced > to creating a new cluster and restoring the apps into it, unless > somebody can suggest an audit method that would uncover the injury. Whoa, don't burn down the village just yet. What does \du postgres say?
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
On Sat, Jan 30, 2010 at 07:18:52PM -0700, Scott Marlowe wrote: > On Sat, Jan 30, 2010 at 7:00 PM, Ray Stell <stellr@cns.vt.edu> wrote: > > On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote: > >> Ray Stell wrote: > >> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > >> > other pg_ tables: > >> > > >> > VACUUM WARNING: ?skipping "pg_database" --- only table or database owner can vacuum it > >> > >> Huh, that's pretty weird ... autovacuum is supposed to connect as > >> superuser internally. ?Did you do something funny to the system role > >> (typically called "postgres")? > > > > > > agreed. ?If I did, I don't know what it was. I suppose I'm reduced > > to creating a new cluster and restoring the apps into it, unless > > somebody can suggest an audit method that would uncover the injury. > > Whoa, don't burn down the village just yet. no worries, fairly small cluster, but if somebody knows how to weed this out that would be a great help. > What does \du postgres say? I used "-U pgadmin" on my initdb, so I don't have postgres user: template1=# \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of -----------+-----------+-------------+-----------+-------------+----------- admin | no | no | no | no limit | pgadmin | yes | yes | yes | no limit | (2 rows) but you see she does own the farm: template1=# \l List of databases Name | Owner | Encoding -----------+---------+---------- fms | pgadmin | UTF8 postgres | pgadmin | UTF8 template0 | pgadmin | UTF8 template1 | pgadmin | UTF8 (4 rows) Thanks for your time.
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
On Sat, Jan 30, 2010 at 09:59:36AM -0500, Ray Stell wrote: > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with > other pg_ tables: > > VACUUM WARNING: skipping "pg_database" --- only table or database owner can vacuum it More logging shows the user generating the msg is not superuser. The user is named "admin," as it is an application administrator and it chokes on all the rels in information_schema and pg_catalog. fms=# select * from pg_user where usename = 'admin'; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ---------+----------+-------------+----------+-----------+----------+----------+----------- admin | 16385 | f | f | f | ******** | | (1 row) Superuser owns these rels: fms=# select * from pg_tables where tablename = 'sql_sizing'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers --------------------+------------+------------+------------+------------+----------+------------- information_schema | sql_sizing | postgres | | f | f | f (1 row) This vacuum.c if clause that gets to the warning msg: if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) || (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared))) The following printf in the vacuum.c clause shows all is well, but raises some questions: 1 pg_class_ownercheck(RelationGetRelid(onerel), GetUserId())=0 2 RelationGetRelid(onerel)=sql_sizing 3 GetUserId()=16385 4 pg_database_ownercheck(MyDatabaseId, GetUserId())=0 5 MyDatabaseId=16384 6 onerel->rd_rel->relisshared=0 Is autovacuum handing all users off to vacuum all rels? Even those it does not own? Perhaps "admin" an unfortunate usename choice? Thanks.
Re: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
From
Ray Stell
Date:
On Tue, Feb 09, 2010 at 05:39:52PM -0500, Ray Stell wrote: > On Sat, Jan 30, 2010 at 09:59:36AM -0500, Ray Stell wrote: > > Is autovacuum handing all users off to vacuum all rels? Even those > it does not own? Perhaps "admin" an unfortunate usename choice? doh...the light comes on. reading through postinit.c and others makes it clear autovacuum is not the source of the vacuum. No doubt the app, Cisco Fabric Manager Server, is doing something stupid. Yep: /var/local/cisco_mds9000 # strings ./jboss/server/default/deploy/dcm.ear/dcm.jar/com/cisco/dcbu/sm/server/db/PostgresWrapper.class | grep -i vacuum vacuum_analyze vacuum analyze vacuum_analyze failed: 2. Irrelephant Anything that is unrelated to an elephant. http://www.urbandictionary.com/define.php?term=irrelephant