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

From: HORDER Philip <[email-masked]>
Date: Thu, 2 May 2024 15:52:50 +0000

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.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 2 May 2024 09:59:08 -0700

On 5/2/24 8:52 AM, HORDER Philip wrote:
> 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.

Do you mean pg_basebackup, pg_dump or something else?

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 2 May 2024 11:41:53 -0700

> 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.

> 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?

---

From: HORDER Philip <[email-masked]>
Date: Tue, 7 May 2024 09:30:43 +0000

Sorry, pg_dump.

Phil Horder
Database Mechanic

---

From: HORDER Philip <[email-masked]>
Date: Tue, 7 May 2024 09:38:51 +0000

Thanks for your time Adrian

> Is there enough data processing?

Yes, one table is receiving upwards of 20 million rows daily.
We noticed the problem when fetch performance on this table degraded after updates.

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

Yes we're sure.  Our biggest table is set for a fixed number of rows rather than a percentage, this gets an auto analyse about every 15 minutes.

After an update this just stops, and there are no analyse entries in the log file.  None at all, for any table.

When we restart Postgres the auto analyse restarts and catches up with the backlog.

Phil Horder
Database Mechanic

Thales
Land & Air Systems

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 7 May 2024 08:24:29 -0700

> After an update this just stops, and there are no analyse entries in the log file.  None at all, for any table.

1) What is the exact pg_restore command you are using?

2) From earlier post: '...  only analyzes tables in the new db at the 
point of reload, then shuts off again.' Provide that sequence of events 
from the Postgres log.

3) Also statistics from

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

for that table after the reload.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 7 May 2024 08:28:59 -0700

> for that table after the reload.

4) The autovacuum settings you have in effect.

---

From: HORDER Philip <[email-masked]>
Date: Wed, 15 May 2024 08:08:48 +0000

Classified as: {OPEN}

Backups of this db are created with:

pg_dump --file=fsm.dmp  -Fc --blobs --oids --dbname=lfm --host=localhost --port=nnnn --username=superuser

Restore is run with:

dropdb --port=nnnn --maintenance-db=postgres --username=superuser --if-exists lfm
pg_restore -Fc --create --dbname=postgres --port=nnnn  --username=superuser
fsm.dmp

-------------

> 2) From earlier post: '...  only analyzes tables in the new db at the point of reload, then shuts off again.' Provide that sequence of events from the Postgres log.

Log file extract is attached, with object names obfuscated.

Well, 'that' table is everything, I'll add an entry for a table that obviously needs stats collection.

From yesterday, current stats for table a.accp, from pg_STAT_all_tables:
"811486381""airscape""accp"16458538988177871456553503047055581967135016364880294000"2024-05-14 14:51:37.158892+00""2024-05-09 08:27:45.328468+00""2024-05-14 13:15:31.999198+00"01815170101653

This table has a low row count, but high content turnover.
It usually gets auto-analyzed every minute.

For today, this hasn't been auto analysed since the update at 3am.

> 4) The autovacuum settings you have in effect:

vacuum_cost_limit = 2000

all other vacuum settings are defaults.

------------

Phil Horder
Database Mechanic

Thales
Land & Air Systems

{OPEN}