Обсуждение: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it
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
От
Scott Marlowe
Дата:
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 ...)
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
От
Alvaro Herrera
Дата:
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
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
От
Scott Marlowe
Дата:
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?
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.
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.
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