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)
We have another database, let’s call it LFM, which contains reference data for some COTS software. I don't know what's in it, we just get given updates for it in pg_backup binary files, about 2MB each.
This is accessed by a different postgres user (LFU) supplied to the COTS tool.
To apply an update, we:
stop the applications that use LFM,
set the user (LFU) to NOLOGIN
kill any left-over connections: select pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'lfm' and usename = 'lfu';
drop the existing reference database using the dropDb utility.
reload the new file using pg_restore and the postgres super user.
set the user (LFU) to LOGIN
Other services connecting to the default db, with SU users should keep running with no dropouts.
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.
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"
The only way I can find of getting the analyzer back is to restart Postgres.
We've narrowed the cause down to the pg_restore, but have no idea where to go from here.
Can anyone help stand the anaylzer back up please?
Most configs are left at default, (apart from memory settings) but we currently have
autovacuum_max_workers = 10
log_autovacuum_min_duration = 0
thanks,
Phil Horder
Database Mechanic
Thales Land & Air Systems
The information contained in this e-mail is confidential. It is intended only for the stated addressee(s) and access to it by any other person is unauthorised. If you are not an addressee, you must not disclose, copy, circulate or in any other way use or rely on the information contained in this e-mail. Such unauthorised use may be unlawful. If you have received this e-mail in error, please inform the originator immediately and delete it and all copies from your system.
Thales UK Limited. A company registered in England and Wales. Registered Office: 350 Longwater Avenue, Green Park, Reading, Berks RG2 6GF. Registered Number: 868273
Please consider the environment before printing a hard copy of this e-mail.