Subject: Re: Can long-running VACUUM be interrupted?
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>,
	<pgsql-admin@postgresql.org>
References: 
 <CAJw4d1UKbCZjP6vUTkDJZAouWazN3nX6zgn_Meg+EWZisaL2-w@mail.gmail.com>
From: Adrien NAYRAT <adrien.nayrat@anayrat.info>
Message-ID: <039bd8a3-bae5-cdee-e310-b53112f2086f@anayrat.info>
Date: Tue, 20 Nov 2018 09:05:17 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.1
MIME-Version: 1.0
In-Reply-To: 
 <CAJw4d1UKbCZjP6vUTkDJZAouWazN3nX6zgn_Meg+EWZisaL2-w@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Language: en-US-large
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 11/20/18 4:58 AM, Andrey Zhidenkov wrote:
> I have long-running prevent wraparound VACUUM (it has been running for
> 6 days at the moment). The question is: if I interrupt this process in
> some way (restart database, for instance), will it start from the
> beginning? On the one hand, it opens a new transaction at the
> beginning (according to the source code), on the other hand it updates
> some data on the disk (WAL?). It confuses me. Could someone please
> explain me this?
> -
> With best regards, Andrey Zhidenkov
> 

Hi Andrez,

You could interrupt with pg_cancel_backend function, but autovacuum will 
restart after autovacuum_naptime delay (1min by default).

If you are 9.6+, Postgres will be able to skip already frozen row on the 
table, but it will scan all indexes from the beginning.

Why do you want to interrupt? This kind of vacuum is necessary to avoid 
wraparound: 
https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND


Regards,