Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1lri5w-0002mS-HU
	for pgsql-admin@arkaria.postgresql.org; Fri, 11 Jun 2021 14:25:12 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1lri5u-0007Wd-37
	for pgsql-admin@arkaria.postgresql.org; Fri, 11 Jun 2021 14:25:10 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lri5t-0007RH-Pm
	for pgsql-admin@lists.postgresql.org; Fri, 11 Jun 2021 14:25:09 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lri5n-0007Or-6I
	for pgsql-admin@lists.postgresql.org; Fri, 11 Jun 2021 14:25:08 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 15BEP0VI1879645;
	Fri, 11 Jun 2021 10:25:00 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vineet Naik <naikvin@gmail.com>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: Autovacuum or manual vacuum to recover from XID wraparound?
In-reply-to: 
 <CADmbCiOycg7Pb7SE7WFpLMzRWeThSh2sYV9TjAs43iqAXgzLiw@mail.gmail.com>
References: 
 <CADmbCiOycg7Pb7SE7WFpLMzRWeThSh2sYV9TjAs43iqAXgzLiw@mail.gmail.com>
Comments: In-reply-to Vineet Naik <naikvin@gmail.com>
	message dated "Fri, 11 Jun 2021 17:46:29 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1879643.1623421500.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 11 Jun 2021 10:25:00 -0400
Message-ID: <1879644.1623421500@sss.pgh.pa.us>
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/1879644.1623421500%40sss.pgh.pa.us>
Precedence: bulk

Vineet Naik <naikvin@gmail.com> writes:
> 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 transact=
ions.

> 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 m=
ode?

If you can afford to wait, it'd be interesting to see what happens.
Looking at the code, it looks to me like what's probably happening is
that those errors are coming from auto-analyze attempting to update
pg_statistic.  That should not, however, prevent auto-vacuum from
cleaning things up and advancing the transaction threshold limit,
because the catalog updates that are needed for that are nontransactional
and so don't need to consume XIDs.

I wonder whether we should disable auto-analyze once we get into
vacuum-for-wraparound mode.  But in any case, it'd be useful to
see whether there's an actual bug in there.

It'd also be useful to know exactly which PG version you are running.

			regards, tom lane