Hello,
We have run into the situation on one of our clusters where PG forces
autovacuum for XID wraparound protection. Fortunately this is not our
main database, so we have managed to have our systems up for now by
sending new writes to a completely new pg instance and having the
applications read from 2 different pg clusters.
But at some point we want to be able to start writes (deletions) on
the old cluster as well.
autovacuum is currently running and looks like it will take a few
days. But I can see logs that say,
2021-06-11 11:53:37 UTC ERROR: database is not accepting commands to
avoid wraparound data loss in database "<dbname>"
2021-06-11 11:53:37 UTC HINT: Stop the postmaster and vacuum that
database in single-user mode.
You might also need to commit or roll back old prepared transactions.
My question is - Should we
1. wait and let the autovacuum run completely or
2. interrupt autovacuum, stop postmaster and run vacuum in single-user mode?
Basically, would the instance recover once the ongoing autovacuum completes?
The reason for the doubt is a reply on stackoverflow that I came
across - https://stackoverflow.com/a/23411365. It suggests autovacuum
would fail in the end at the time of updating the system catalog, but
I'm not sure if that's correct info.
Thanks,
Vineet