Autovacuum or manual vacuum to recover from XID wraparound?

From: Vineet Naik <naikvin(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Autovacuum or manual vacuum to recover from XID wraparound?
Date: 2021-06-11 12:16:29
Message-ID: CADmbCiOycg7Pb7SE7WFpLMzRWeThSh2sYV9TjAs43iqAXgzLiw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2021-06-11 14:25:00 Re: Autovacuum or manual vacuum to recover from XID wraparound?
Previous Message Jwiencek3 2021-06-11 09:07:51 Re: RHEL-8 Kernel Settings