Restore of a reference database kills the auto analyze processing.

On 5/2/24 8:52 AM, 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)

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
adrian.klaver@aklaver.com

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@aklaver.com

Sorry, pg_dump.

Phil Horder
Database Mechanic

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: 02 May 2024 17:59
To: HORDER Philip <Phil.Horder@uk.thalesgroup.com>; pgsql-general@lists.postgresql.org
Subject: [EXTERNAL EMAIL] Re: Restore of a reference database kills the auto analyze processing.

On 5/2/24 8:52 AM, 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)

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
adrian.klaver@aklaver.com
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.

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

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.

On 5/7/24 02:38, HORDER Philip wrote:

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.

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.

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

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/7/24 08:24, Adrian Klaver wrote:

On 5/7/24 02:38, HORDER Philip wrote:

Thanks for your time Adrian

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.

4) The autovacuum settings you have in effect.

--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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.

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
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10

all other vacuum settings are defaults.

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

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.

On 5/15/24 01:08, HORDER Philip wrote:

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

From your original post:

"Running Postgres 15.3 ..."

--oids have not been supported with pg_dump since v11

When I try it:

pg_dump -V
pg_dump (PostgreSQL) 15.7 (Ubuntu 15.7-1.pgdg22.04+1)

pg_dump -d test -U postgres --oids -f test.sql
/usr/lib/postgresql/15/bin/pg_dump: unrecognized option '--oids'

So what version of pg_dump are you using?

Or you not working on a v15 instance of Postgres?

FYI, --blobs is only needed when dumping a specific table or schema,
otherwise it happens by default.

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.

Still working my way through that.

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

I can't parse this out well enough to really say anything. Though the
timestamps seem to indicate recent activity.

Using psql do

\x
select * from pg_stat_all_tables where relname = 'a.accp';

to get an extended output that is easier to read.

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
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10

all other vacuum settings are defaults.

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

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

Classified as: {OPEN}

--oids have not been supported with pg_dump since v11

You're absolutely correct, this command came from my notes, which are obviously out of date.

We're running Postgres 15 pg_dump, and I've updated my notes.
I'm double-checking the command options with the guy who creates these files, but he's on holiday. I expect we just dropped the OID option.

FYI, --blobs is only needed when dumping a specific table or schema, otherwise it happens by default.

Thanks, I didn’t know that.

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.

On 5/16/24 03:08, HORDER Philip wrote:

Classified as: {OPEN}

--oids have not been supported with pg_dump since v11

You're absolutely correct, this command came from my notes, which are obviously out of date.

We're running Postgres 15 pg_dump, and I've updated my notes.
I'm double-checking the command options with the guy who creates these files, but he's on holiday. I expect we just dropped the OID option.

FYI, --blobs is only needed when dumping a specific table or schema, otherwise it happens by default.

Thanks, I didn’t know that.

Did you have chance to do below?

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

I can't parse this out well enough to really say anything. Though the
timestamps seem to indicate recent activity.

Using psql do

\x
select * from pg_stat_all_tables where relname = 'a.accp';

to get an extended output that is easier to read.

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

Classified as: {OPEN}

Did you have chance to do below?
Using psql do

\x
select * from pg_stat_all_tables where relname = 'a.accp';

Sorry, missed that bit.
From this output you can see that no stats have been collected since the last two overnight updates.

postgres=# select * from pg_stat_all_tables where relname='accp';
-[ RECORD 1 ]-------+--------------------------------
relid | 811486381
schemaname | a
relname | accp
seq_scan | 1654767
seq_tup_read | 901811880
idx_scan | 146070383
idx_tup_fetch | 305949969
n_tup_ins | 2056948
n_tup_upd | 0
n_tup_del | 1662699
n_tup_hot_upd | 0
n_live_tup | 294
n_dead_tup | 75162
n_mod_since_analyze | 0
n_ins_since_vacuum | 75162
last_vacuum |
last_autovacuum | 2024-05-15 03:25:16.007386+00
last_analyze |
last_autoanalyze | 2024-05-15 03:25:16.008873+00
vacuum_count | 0
autovacuum_count | 1888
analyze_count | 19836
autoanalyze_count | 1715

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.

On 5/16/24 07:38, HORDER Philip wrote:

Classified as: {OPEN}

Did you have chance to do below?
Using psql do

\x
select * from pg_stat_all_tables where relname = 'a.accp';

Sorry, missed that bit.
From this output you can see that no stats have been collected since the last two overnight updates.

Still your contention was that autovacuum quit running after the initial
restore and that is not the case. Assuming this is the instance you
restored on May 2 2024 then approximate counts are:

autovacuum_count 1888/13 days = 145 autovacuums/day = 6/hour

autoanalyze_count 1715/13 = 132 autoanalyze/day = 5.5/hr

postgres=# select * from pg_stat_all_tables where relname='accp';
-[ RECORD 1 ]-------+--------------------------------
relid | 811486381
schemaname | a
relname | accp
seq_scan | 1654767
seq_tup_read | 901811880
idx_scan | 146070383
idx_tup_fetch | 305949969
n_tup_ins | 2056948
n_tup_upd | 0
n_tup_del | 1662699
n_tup_hot_upd | 0
n_live_tup | 294
n_dead_tup | 75162
n_mod_since_analyze | 0
n_ins_since_vacuum | 75162
last_vacuum |
last_autovacuum | 2024-05-15 03:25:16.007386+00
last_analyze |
last_autoanalyze | 2024-05-15 03:25:16.008873+00
vacuum_count | 0
autovacuum_count | 1888
analyze_count | 19836
autoanalyze_count | 1715

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

Classified as: {OPEN}

Adrian,

Still your contention was that autovacuum quit running after the initial restore and that is not the case....

This Postgres server has been restarted a few times since 2nd May most recently on Tuesday 14th, hence the more recent analyze status.

We've had some problems with our data feeds on this integration system, but these are now running again.
I'm planning to leave it all alone until I'm back in the office on Tuesday, and run this query again for a few tables and send you an update.
I'm expecting no further stats analysis, (and the performance to be appalling).

Thanks for your time.

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.

On 5/16/24 08:59, HORDER Philip wrote:

Classified as: {OPEN}

Adrian,

Still your contention was that autovacuum quit running after the initial restore and that is not the case....

This Postgres server has been restarted a few times since 2nd May most recently on Tuesday 14th, hence the more recent analyze status.

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 from here:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS

28.2.25. Statistics Functions

We've had some problems with our data feeds on this integration system, but these are now running again.
I'm planning to leave it all alone until I'm back in the office on Tuesday, and run this query again for a few tables and send you an update.
I'm expecting no further stats analysis, (and the performance to be appalling).

From here:

https://www.postgresql.org/docs/current/runtime-config-logging.html

log_autovacuum_min_duration

In addition, when this parameter is set to any value other than -1, a
message will be logged if an autovacuum action is skipped due to a
conflicting lock or a concurrently dropped relation.

Thanks for your time.

Phil Horder
Database Mechanic

Thales
Land & Air Systems

--
Adrian Klaver
adrian.klaver@aklaver.com

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.

On 5/21/24 06:00, HORDER Philip wrote:

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.

This is where I am getting confused. In your original post you had:

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

In other words DROP DATABASE then CREATE DATABASE and reload the schema
objects and associated data.

Yet your int stats output.txt file has things like the following:

-[ RECORD 3 ]-------+--------------------------------------------------
relid | 923130055
schemaname | a
relname | cr_pt_e_202405020000_202405030000
seq_scan | 1264
seq_tup_read | 8800722491
idx_scan | 4601405
idx_tup_fetch | 4415621
n_tup_ins | 3851400
n_tup_upd | 15790
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 7166325
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2024-05-03 09:03:44.810654+00
last_analyze | 2024-05-09 08:44:37.725674+00
last_autoanalyze | 2024-05-03 09:03:58.838664+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 1
autoanalyze_count | 1

I am having a hard time figuring out how both of the above can be true.
Dropping and recreating the database would wipe out the statistics.

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.

The above is confusing also. In your original post you only referred to
the postgres and lfm databases.

What other databases are you referring to?

As to partitions are you referring to partitions of tables in the lfm
database or something else?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

Classified as: {OPEN}

I am having a hard time figuring out how both of the above can be true.
Dropping and recreating the database would wipe out the statistics.

We have multiple databases on the one Postgres server.
The 'postgres' database contains our main application, with tables in schema 'a'.
We have two other databases, one of which is 'lfm'.

In PgAdmin, there is Databases (3), then those database names.
As the superuser, I can connect to any of these databases.

When we drop database lfm, and recreate it using pg_restore, the stats collection dies, for all databases on the server: 'postgres', 'lfm', 'lfm2'
I haven't dropped database 'postgres'

So the existing stats on tables in the 'postgres' database remain, but no further stats are collected.

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.

On 5/21/24 9:29 AM, HORDER Philip wrote:

Classified as: {OPEN}

I am having a hard time figuring out how both of the above can be true.
Dropping and recreating the database would wipe out the statistics.

We have multiple databases on the one Postgres server.
The 'postgres' database contains our main application, with tables in schema 'a'.
We have two other databases, one of which is 'lfm'.

My mistake, I was over concentrating on the log messages and did not pay
attention to the psql prompt which was showing postgres as the database.

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/21/24 9:29 AM, HORDER Philip wrote:

Classified as: {OPEN}

I am having a hard time figuring out how both of the above can be true.
Dropping and recreating the database would wipe out the statistics.

We have multiple databases on the one Postgres server.
The 'postgres' database contains our main application, with tables in schema 'a'.
We have two other databases, one of which is 'lfm'.

In PgAdmin, there is Databases (3), then those database names.
As the superuser, I can connect to any of these databases.

When we drop database lfm, and recreate it using pg_restore, the stats collection dies, for all databases on the server: 'postgres', 'lfm', 'lfm2'
I haven't dropped database 'postgres'

So the existing stats on tables in the 'postgres' database remain, but no further stats are collected.

From int-postgres-log-21-05-2024.txt:

2024-05-15 03:31:31.290 GMT [4556]: [3-1]
db=lfm,user=superuser,app=[unknown],client=::1 LOG: connection
authorized: user=superuser database=lfm application_name=pg_restore

That would be the lfm database being restored.

What does the log show after that as pertains to autovacuum?

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.

--
Adrian Klaver
adrian.klaver@aklaver.com