From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | HORDER Philip <Phil(dot)Horder(at)uk(dot)thalesgroup(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Restore of a reference database kills the auto analyze processing. |
Date: | 2024-05-02 18:41:53 |
Message-ID: | 4b64d2cc-1df6-4982-b842-39fb0d886731@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Sharma | 2024-05-03 00:28:04 | Ora2pg Delta Migration: Oracle to PostgreSQL |
Previous Message | Adrian Klaver | 2024-05-02 16:59:08 | Re: Restore of a reference database kills the auto analyze processing. |