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@xxxxxxxxxxx
