Classified as: {OPEN}
Installing 15.7 has indeed fixed the problem.
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. I
Classified as: {OPEN}
I actually hadn't thought to look for Postgres bugs, the system is so reliable
for us, it just doesn't go wrong!
I can trigger the fault be running a reload of the lfm database, and we've been
running Postgres 10 & 13 for several years now without seeing this problem.
Our
Classified as: {OPEN}
> https://www.postgresql.org/docs/15/release-15-5.html#id-1.11.6.7.4
> Fix race condition in database dropping that could lead to the autovacuum
> launcher getting stuck
Wow, that sounds like our problem!
I will investigate. Maybe try and find the orphaned stats entry to
Classified as: {OPEN}
> Just for confirmation your settings are still?:
> autovacuum_max_workers = 10
> log_autovacuum_min_duration = 0
Yes.
> You said previously:
> "The only way I can find of getting the analyzer back is to restart Postgres."
>
> To be clear this means:
> 1) The lfm database
Classified as: {OPEN}
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 a
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
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
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
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_st
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 creat
Classified as: {OPEN}
Backups of this db are created with:
pg_dump --file=fsm.dmp -Fc --blobs --oids --dbname=lfm --host=localhost
--port= --username=superuser
Restore is run with:
dropdb --port= --maintenance-db=postgres --username=superuser --if-exists
lfm
pg_restore -Fc --create -
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
Sorry, pg_dump.
Phil Horder
Database Mechanic
-Original Message-
From: Adrian Klaver
Sent: 02 May 2024 17:59
To: HORDER Philip ;
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
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 datab
14 matches
Mail list logo
