Feedback-ID: i76984098:Fastmail
Message-ID: <e600fc07-0efb-4249-9b30-494dd68d8e36@aklaver.com>
Date: Wed, 22 May 2024 08:38:47 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Restore of a reference database kills the auto analyze
 processing.
To: HORDER Philip <Phil.Horder@uk.thalesgroup.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <d5bc195d2f1742639074bfa23fa9dfe9@uk.thalesgroup.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <d5bc195d2f1742639074bfa23fa9dfe9@uk.thalesgroup.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e600fc07-0efb-4249-9b30-494dd68d8e36%40aklaver.com>
Precedence: bulk

On 5/22/24 01:33, HORDER Philip wrote:
> Classified as: {OPEN}

>> 2) There is a round of autovacuum immediately after the lfm is restored.
> Yes, some tables in the lfm database, but not all, an apparently random selection, anywhere between 2 and 21 tables, across the lfm schemas, public & pg_catalog.
> 
>> 3) autovacuum then goes silent.
> Yes. Dead in a ditch. But with no errors.
> 
>> 4) Before the next drop/create lfm you restart the Postgres server and autovacuum starts again.
> I haven't restarted in a week, and the pattern remains, with a bit of analyze at each reload of lfm, and then nothing.
> 
>> What is in the logs when you do the restart?
> Nothing notable:
> 1) denied connections, while restarting
> 2) authorized connections
> 3) auto analyze going into overdrive:
> See below
> 
>> Is there some process that runs shortly after the drop/create lfm cycle?
> Not that I can see.

I was hoping more coffee would lead to enlightenment, it did not. It did 
lead me to do what I should have done at the start which is look at the 
release notes for 15.x. You are on Postgres 15.3 and current is 15.7. On 
the path from .5 --> .7 is:

https://www.postgresql.org/docs/15/release-15-5.html#id-1.11.6.7.4

Fix race condition in database dropping that could lead to the 
autovacuum launcher getting stuck (Andres Freund, Will Mortensen, Jacob 
Speidel)

The race could lead to a statistics entry for the removed database 
remaining present, confusing the launcher's selection of which database 
to process.


> Phil Horder
> Database Mechanic

-- 
Adrian Klaver
adrian.klaver@aklaver.com