2

My logs are constantly filling up with these messages for hundreds of tables:

WARNING:  skipping "some_table_name" --- only table or database owner can analyze it

How do I perform any the following and/or resolve this?

  1. Determine in which database(s) these tables reside? Would be nice if the warning included that information.
  2. Grant the PostgreSQL superuser access to vacuum these tables
  3. Disable these warnings in the log file

Side note: none of the log file rotation, truncation, size/date limits settings are working either but I'll save that for a separate post.

3 Answers 3

2

It sounds like you revoked superuser privileges from the cluster's founding user. Don't do that. Log in as your remaining superuser, and grant superuser privileges back to the founding user. If you have no remaining superusers, then you need to shut down the server, start it up in single-user mode, and regrant superuser privs.

The founding user is the username specified to initdb's -U argument when the database was created, or if that argument was not provided, then it is the name of the OS user who ran the initdb. This is most often postgres.

1
  • Thank you for responding. > from the cluster's = It's a standalone server. > This is most often postgres. = It is. > grant superuser privileges back to the founding user ted=# ALTER USER postgres WITH SUPERUSER; ALTER ROLE -- This seems to have resolved it.
    – Teflon Ted
    Commented Feb 20, 2017 at 14:44
0

You need to restore postgres' rights to superuser:

ALTER USER postgres WITH SUPERUSER;
0

The answers given so far are not the end of the story.

Checklist:

  • [ ] you must know the "founding user" of the DB (initdb -U arg), it's often postgres but not always;
  • [ ] that user must have SUPERUSER privilege (in psql, check with \du postgres).

Determine in which database(s) these tables reside?

That's 1 query away:

select table_catalog from information_schema.tables where table_name='some_table_name';

Grant the PostgreSQL superuser access to vacuum these tables

As mentioned everywhere else, ALTER USER postgres WITH SUPERUSER; should suffice — but this changed nothing in my setup, as \du postgres was already showing superuser. Autovacuum warnings continued logging.

BTW, "the PostgreSQL superuser" is not a thing. WITH SUPERUSER is a capability/attribute on a role; you can have multiple "super users", and you can have 0 of them.

Disable these warnings in the log file

The only thing that stopped the stream of warnings for me, was hand-vacuuming every warned table:

VACUUM VERBOSE ANALYZE "some_table_name";

I guess that resets a timer of some sorts, and the postgres-builtin autovacuum thread stops attempting it.

The warning itself seems buggy to me; complaints about it keep coming from 2009, one example is this email thread from Ray Stell in pgsql-admin. It did not conclude satisfactorily for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.