Re: BUG #16620: Autovacuum does not process certain databases after migration from postgresql 10

Hi,

The problem occurred again.

I see that only one database is processed by autovacuum daemon while there
are tables in other databases that should be processed and there are free
autovacuum workers.

Here is information about forzenxid you requested.

renamed_db=# select datname, datfrozenxid, datminmxid from pg_database
where datname = 'renamed_db';
datname | datfrozenxid | datminmxid
-----------+--------------+------------
renamed_db | 2665923884 | 504434938
(1 row)

renamed_db=# select relfrozenxid, relminmxid from pg_class where
relname = 'renamed_table_in_renamed_db';

relfrozenxid | relminmxid
--------------+------------
2742239890 | 504663548
(1 row)

I don't think this is because of pg_upradate because autovacuum worked
correctly since the last restart.
It looks like autovacuum hangs on the one database.

Im sure that the problem will be solved by restarting.
I would like to find reasons before.
Please help investigate the problem.

чт, 17 сент. 2020 г. в 04:19, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Max Vikharev <bm(dot)kinder(at)gmail(dot)com> writes:
> > Problem gone after restarting the cluster.
> > Autovacuum started to process relations in other databases.
>
> Hmm, interesting.
>
> > I dont know how to reproduce the issue, we will monitor it.
> > If there any way to debug it when it occurs again - let me know.
>
> Did you by any chance capture the contents of pg_database.datfrozenxid
> and datminmxid and compare them to the pg_class.relfrozenxid and
> relminmxid fields in the problematic databases?
>
> It's not hard to imagine that if the pg_database fields somehow
> didn't get updated correctly during pg_upgrade, that would prevent
> autovacuum from processing some databases to prevent wraparound.
> However, that doesn't explain failure to examine those databases
> at all, so I'm a bit at a loss.
>
> Another thing to check is whether the stats collector is working.
> Specifically look at whether counts in pg_stat_all_tables are
> incrementing in the problem databases.
>
> My guess is that somehow pg_upgrade left something in a slightly
> hosed state, and that restarting de-hosed it, so that you aren't
> going to see this again ... at least not till your next upgrade.
> But I don't know exactly what the something could be.
>
> regards, tom lane
>