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

Hi there,

Last time the problem was solved again by restarting the cluster.
Last saturday (after two weeks) the problem occurred again. Currently 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.
We noticed that just before the last other database was processed were few
deadlocks in the log on the hanged database. Also there was wal-e
backup started in one hour after it.

Any suggestions on how to find the sources of the problem?

пт, 25 сент. 2020 г. в 16:53, Max Vikharev <bm(dot)kinder(at)gmail(dot)com>:

> 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
>>
>