Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Yk0-0043ZS-MY for pgsql-general@arkaria.postgresql.org; Thu, 02 May 2024 15:53:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s2Yjx-0022LF-7e for pgsql-general@arkaria.postgresql.org; Thu, 02 May 2024 15:52:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Yjw-0022L7-KF for pgsql-general@lists.postgresql.org; Thu, 02 May 2024 15:52:57 +0000 Received: from mail.uk.thalesgroup.com ([192.93.164.134]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Yju-001C7u-9x for pgsql-general@lists.postgresql.org; Thu, 02 May 2024 15:52:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=uk.thalesgroup.com; q=dns/txt; s=A01; t=1714665173; x=1746201173; h=from:to:subject:date:message-id:mime-version; bh=vkuGKtefWpWokxI65mqMGVnlMeM86cv+TOwmJzMw8k4=; b=t461lEyCJjHxyRO4DrwuMmNS+0E5nSX3cxN6rVDGwVFZ87IzbPHcLyc6 IgWZFS96/btsqlHONtzmT44qTG+OzvITJLfvH0d+Inkelj0VwMBs8rtmY 3u+iC0OuQaU9aQpMr/usoo33uLmINiWY1yGidG4+feQU+ZwTuSkRfmitI B8p/s+0S3ds+FsQjkomgXezUWAPLqqw1Vo6aX0AAOe9zp0j0oPxeWvQFY SDLLhbdcZgHkOMiVWoYuj/24hwSIQnar18mhmZPsHtvDTcX/LkNxcK0V6 UQEMXTEHqpYvpZFKXCB6H9piSF9CQL0EGWTzKt/5BvpnXbvtLsPDQ3nc9 A==; X-IronPort-AV: E=Sophos;i="6.07,247,1708387200"; d="scan'208,217";a="125881117" X-Trellix: Whitelist From: HORDER Philip To: "pgsql-general@lists.postgresql.org" Subject: Restore of a reference database kills the auto analyze processing. Thread-Topic: Restore of a reference database kills the auto analyze processing. Thread-Index: Adqcp3JgvdjOETQQS7CKrmgOUek/vw== Date: Thu, 2 May 2024 15:52:50 +0000 Message-ID: <5910d4dcbe244548bbf482dad0ceca83@uk.thalesgroup.com> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: Content-Type: multipart/alternative; boundary="_000_5910d4dcbe244548bbf482dad0ceca83ukthalesgroupcom_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_5910d4dcbe244548bbf482dad0ceca83ukthalesgroupcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 updat= es for it in pg_backup binary files, about 2MB each. This is accessed by a different postgres user (LFU) supplied to the COTS to= ol. 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_activ= ity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname =3D 'lfm' and= usename =3D '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 runn= ing with no dropouts. This works, some of the time. If I repeat the update process, somewhere around run #4 the auto analyzer s= tops 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, ev= en though there is still data processing into it. With log_autovacuum_min_duration =3D 0, we are logging all vacuum & analyze= operations, so we can see when the entries shut off in the Postgres log fi= les, e.g. 2024-05-02 14:52:01.597 GMT [6896]: [23-1] db=3D,user=3D,app=3D,client=3D L= OG: 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 curre= ntly have autovacuum_max_workers =3D 10 log_autovacuum_min_duration =3D 0 thanks, Phil Horder Database Mechanic Thales Land & Air Systems The information contained in this e-mail is confidential. It is intended on= ly for the stated addressee(s) and access to it by any other person is unau= thorised. If you are not an addressee, you must not disclose, copy, circula= te or in any other way use or rely on the information contained in this e-m= ail. Such unauthorised use may be unlawful. If you have received this e-mai= l 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 Of= fice: 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. --_000_5910d4dcbe244548bbf482dad0ceca83ukthalesgroupcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Running Postgres 15.3 with PostGIS 3.3

On Windows 10 (yes, I know)

 

It’s a single node db with no replication, top= ping out at about 200GB.

 

We have a schema (A) in the default 'postgres' maint= enance database, which our software services connect to, with one set of us= ers (SU)

 

We have another database, let’s call it LFM, w= hich contains reference data for some COTS software.  I don't know wha= t's in it, we just get given updates for it in pg_backup binary files, abou= t 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_ter= minate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_ac= tivity.datname =3D 'lfm' and usename =3D 'lfu';

  drop the existing reference database using th= e 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 =3D 0, we are loggi= ng 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=3D,use= r=3D,app=3D,client=3D LOG:  automatic analyze of table "lfm.pg_ca= talog.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 =3D 10

log_autovacuum_min_duration =3D 0

 

thanks,

 

Phil Horder

Database Mechanic

 

Thales Land & Air Systems

 

 

The information contained in this e-mail is confidential. It is intended on= ly for the stated addressee(s) and access to it by any other person is unau= thorised. If you are not an addressee, you must not disclose, copy, circula= te 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 orig= inator immediately and delete it and all copies from your system.

Thales UK Limited. A company registered in England and Wales. Registered Of= fice: 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.=

--_000_5910d4dcbe244548bbf482dad0ceca83ukthalesgroupcom_--