Re: Re: Need help debugging why autovacuum seems "stuck" -- until I use superuser to vacuum freeze pg_database

Shawn wrote:

> The problem is though, when I look at age(datfrozenxid) for
> "my_database_name" it is the oldest at 654,189,546 and should trigger the
> autovacuum_freeze_max_age of 200,000,000 but it doesn't. The relation with
> this age is "pg_database". If I issue a manual "vacuum freeze pg_database"
> in the database, autovacuum will run on the next oldest database but get
> stuck again with same symptoms (i.e. pg_database is the relation with the
> oldest age). If I don't issue the manual "vacuum freeze" for all databases
> and left things alone, it would get to wrap-around state.

I was thinking about this in connection with the shared catalog
vacuuming bug I fixed yesterday. One problem here is that the
relfrozenxid/relminmxid settings are local to each database, so a
for-wraparound vacuum would have to scan the table in all databases, and
advance the counter in all databases, before the overall "age" of the
system goes down again, which is inconvenient. This is a problem
inherent to having these columns in a per-database catalog, and the only
solution I can see is to have another shared catalog containing age
state for the shared catalogs. (However, this issue isn't terribly
serious -- it just means you have to scan the catalog N times instead of
one.)

However this doesn't explain why the vacuuming of pg_database in one
database doesn't actually advance the relfrozenxid in that database; it
certainly should. This is the serious problem you're facing, it seems.
I wonder if you've tweaked the freeze settings somehow.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services