Re: Vacuumdb on a table

Sorry :-) standby and replica are synonyms in the world of postgresql. So
there is no sense to write "standby replica".
I was thinking about hot-standby.
There is a possibility that on long lasting sql executed on hot-standby
cause locks on blocks of table disallowing vacuum freeze.
But info about such lock should be also in message (verbose).

Regards Tomek

czw., 19 paź 2023 o 10:02 Tomek <tomekphotos(at)gmail(dot)com> napisał(a):

> Hi,
>
> There can be many reasons why vacuum do not work but definitely the reason
> should be recorded in the output log.
> Please make this vacuum "verbose".
>
> Possible reasons VACUUM FREEZE do not work:
> - table locked by some SQL - even on the standby replica
> - deadlock with some other vacuum process
> - table is corrupted - the worst case scenario
> - someone killed this process because was using to much resources (may be
> there is some other admin or someone set statement_timeout or something
> similar ? )
> But these all should be reported in log.
>
> Regards Tomek
>
> śr., 18 paź 2023 o 22:12 Murthy Nunna <mnunna(at)fnal(dot)gov> napisał(a):
>
>> Hi,
>>
>>
>>
>> The first table in the following query resulted in age(c.relfrozenxid) =
>> 148795396. But when I manually run vacuumdb command (vacuumdb -d db1 -t
>> tab1) on that table it is not lowering the relfrozenxid. There is no
>> indication in the pglog that vacuumdb failed.
>>
>>
>>
>> SELECT c.oid::regclass
>>
>> , age(c.relfrozenxid)
>>
>> , pg_size_pretty(pg_total_relation_size(c.oid))
>>
>> FROM pg_class c
>>
>> JOIN pg_namespace n on c.relnamespace = n.oid
>>
>> WHERE relkind IN ('r', 't', 'm')
>>
>> AND n.nspname NOT IN ('pg_toast')
>>
>> ORDER BY 2 DESC ;
>>
>>
>>
>> I am wondering why vacuumdb is unable to lower relfrozenxid on this
>> table? It seems to work on other tables though.
>>
>>
>>
>> Thank you!
>>
>