Feedback-ID: i76984098:Fastmail
Message-ID: <4b64d2cc-1df6-4982-b842-39fb0d886731@aklaver.com>
Date: Thu, 2 May 2024 11:41:53 -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: <5910d4dcbe244548bbf482dad0ceca83@uk.thalesgroup.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <5910d4dcbe244548bbf482dad0ceca83@uk.thalesgroup.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/4b64d2cc-1df6-4982-b842-39fb0d886731%40aklaver.com>
Precedence: bulk

On 5/2/24 08:52, HORDER Philip wrote:
> Running Postgres 15.3 with PostGIS 3.3
> 
> On Windows 10 (yes, I know)
> 
> It’s a single node db with no replication, topping out at about 200GB.
> 
> We have a schema (A) in the default 'postgres' maintenance database, 
> which our software services connect to, with one set of users (SU)

This above is probably not a good idea, The 'postgres' database is 
generally taken to be a throw away database for establishing an initial 
connection. Many utilities/tools use it for that purpose, having your 
data in it exposes that data.


> This works, some of the time.
> 
> If I repeat the update process, somewhere around run #4 the auto 
> analyzer stops working, and only analyzes tables in the new db at the 
> point of reload, then shuts off again.
> 
> All vacuum and analyze operations on the 'postgres' database just stops, 
> even though there is still data processing into it.

Is there enough data processing?

Autovacuum has thresholds for turning on, are you sure those thresholds 
are just not being met?

> 
> With log_autovacuum_min_duration = 0, we are logging all vacuum & 
> analyze operations, so we can see when the entries shut off in the 
> Postgres log files, e.g.
> 
> 2024-05-02 14:52:01.597 GMT [6896]: [23-1] db=,user=,app=,client= LOG:  
> automatic analyze of table "lfm.pg_catalog.pg_trigger"

Except the above shows it working.

What is the evidence it is not?

> 
> The only way I can find of getting the analyzer back is to restart Postgres.

Did you wait to see if activity after the pg_restore crossed the 
autovacuum thresholds?


-- 
Adrian Klaver
adrian.klaver@aklaver.com