Thread: Database owner can't analyze/vacuum all of the database tables

Hi all,

When I try to analyze (or vacuum) the database with the owner of the database I receive the following warnings:

help2.com=> ANALYZE ;
WARNING:  skipping "pg_authid" --- only table or database owner can analyze it
WARNING:  skipping "pg_tablespace" --- only table or database owner can analyze it
WARNING:  skipping "pg_pltemplate" --- only table or database owner can analyze it
WARNING:  skipping "pg_shdepend" --- only table or database owner can analyze it
WARNING:  skipping "pg_shdescription" --- only table or database owner can analyze it
WARNING:  skipping "pg_database" --- only table or database owner can analyze it
WARNING:  skipping "pg_auth_members" --- only table or database owner can analyze it
ANALYZE


I checked at the documentation and found that those tables are shared across all of the databases of the cluster.

So my question is, is it important to analyze/vacuum them? or maybe because they are scarcely used (I don't even know if this assumption is right) it's not important to analyze/vacuum them?

I can always change their owner to the database owner but I guess that if the database owner didn't get ownership over those tables too then there is a reason for that...

Thanks a lot in advance,
Yonatan Ben-Nes

Yonatan Ben-Nes wrote:
> Hi all,
>
> When I try to analyze (or vacuum) the database with the owner of the
> database I receive the following warnings:

> WARNING:  skipping "pg_authid" --- only table or database owner can analyze
...
> WARNING:  skipping "pg_auth_members" --- only table or database owner can

> I checked at the documentation and found that those tables are shared across
> all of the databases of the cluster.
>
> So my question is, is it important to analyze/vacuum them? or maybe because
> they are scarcely used (I don't even know if this assumption is right) it's
> not important to analyze/vacuum them?

You'll need to vacuum often enough to prevent transaction wraparound.
You might need to vacuum reasonably often if you create & drop a lot of
system objects (databases/users etc) and end up with bloated tables.

> I can always change their owner to the database owner but I guess that if
> the database owner didn't get ownership over those tables too then there is
> a reason for that...

Don't know if you could have a user other than postgres owning them. Not
sure anyone's tried.

I think most people schedule their vacuum/autovacuum to run as
"postgres" anyway - particularly if they have more than one database in
an installation.

--
   Richard Huxton
   Archonet Ltd