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).
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@fnal.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.