RE: Restore of a reference database kills the auto analyze processing.

Classified as: {OPEN}

> Assuming clean shutdowns the statistics will survive restarts. They would be wiped when you drop a database and start over, have an unclean shutdown or you use one of the reset functions...

Yes, stats are permanent, but are not being updated.
We don't use any of the pg_stat_reset functions.

-------------------------------------
I've left the system alone over the weekend.
Here's the timeline:

14th May:
Postgres working ok, 1767 log entries for "automatic analyze", mostly in database postgres.
03:30 Jenkins deployed an update, resulting in reload of lfm database.

15th May:
Postgres working ok, with 257 logged "automatic analyze" events, up until 03:30
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Log of the auto analyse around that update is attached.
No further auto analyse logged after 03.30

16th May:
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Only 3 logged "automatic analyze" in the whole file, timed at 03:30, for lfm.public and lfm.pg_catalog tables.

Test data feed restarts at 2024-05-16 14:54
Daily partitions are created for this data, and each partition from here is showing no vacuum or analyze timestamps

17th May:
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Only the reloaded database shows log entries for "automatic analyze", at 03:30, then nothing more.

18th - 21st may:
As per 17th

Stats output for a sample of tables is attached.
You can see that the partitions were auto analysed on the day they were created, and not since.
And that new partitions haven’t been analysed at all.
(accp does get a manual analyze occasionally, from an SQL function somewhere, but not enough to stop auto analyze from running as well)

Summary: since the reload of lfm database on 15th May, the only "automatic analyze" events logged have been for the lfm database, at the point of reload.
No other stats analyze seems to have taken place, on any database on the server since that point, even partitions with over a million rows.
Apart from that, Postgres appears to be working normally.

I'm sure that another restart of Postgres will restore the auto analyze, until the next pg_restore of lfm.
So what's going on? How is it that we're breaking this important function that we shouldn't be able to have any effect on?

Thanks for looking,
Phil Horder
Database Mechanic

Thales
Land & Air Systems

{OPEN}
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.