Thread: LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat"
LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat"
From
Erik Wienhold
Date:
I manage two Postgres instances (12.14 and 14.7) on a Windows 10 server at work. Both instances are regular EnterpriseDB installations. Lately, I see the following log messages on both instances: 2023-03-07 02:37:08.171 CET [4624] LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":Permission denied 2023-03-07 02:37:11.874 CET [3656] LOG: using stale statistics instead of current ones because stats collector is notresponding Both messages appear at irregular intervals. The first message every few minutes. The second message every few hours. The "permission denied" message apparently is caused by another process having an open file handle at the same time, likely some antivirus software scanning the system. What's the impact of this on Postgres, besides using stale statistics, if this does not get resolved? I assume the file will be renamed eventually once no other process has an open file handle and the statistics will eventually be refreshed. The log messages appeared after a server admin switched from Symantec Endpoint Protection to Cortex XDR. I had the admin exclude Postgres from the scan and they disabled Cortex XDR entirely. But the messages still appear, although less frequently. I doubt that Cortex XDR is fully disabled because it's still writing its event log. But its UI says disabled. The switch to Cortex XDR was triggered because something deleted pg_ctl.exe from my Postgres 14 instance. I still don't know what caused that. But the admin had the brilliant idea of replacing one snake oil with another. Maybe it's unrelated to the snake oil but I haven't received any further info on possible causes from the admin. -- Erik
Re: LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat"
From
Holger Jakobs
Date:
Am 10.03.23 um 18:39 schrieb Erik Wienhold: > I manage two Postgres instances (12.14 and 14.7) on a Windows 10 > server at work. Both instances are regular EnterpriseDB installations. > > Lately, I see the following log messages on both instances: > > 2023-03-07 02:37:08.171 CET [4624] LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat":Permission denied > 2023-03-07 02:37:11.874 CET [3656] LOG: using stale statistics instead of current ones because stats collector is notresponding > > Both messages appear at irregular intervals. The first message every few > minutes. The second message every few hours. > > The "permission denied" message apparently is caused by another process having > an open file handle at the same time, likely some antivirus software scanning > the system. > > What's the impact of this on Postgres, besides using stale statistics, if this > does not get resolved? I assume the file will be renamed eventually once no > other process has an open file handle and the statistics will eventually be > refreshed. > > The log messages appeared after a server admin switched from Symantec Endpoint > Protection to Cortex XDR. I had the admin exclude Postgres from the scan and > they disabled Cortex XDR entirely. But the messages still appear, although > less frequently. I doubt that Cortex XDR is fully disabled because it's still > writing its event log. But its UI says disabled. > > The switch to Cortex XDR was triggered because something deleted pg_ctl.exe > from my Postgres 14 instance. I still don't know what caused that. But the > admin had the brilliant idea of replacing one snake oil with another. Maybe > it's unrelated to the snake oil but I haven't received any further info on > possible causes from the admin. > What is a bit problematic running PostgreSQL on Windows is that the installer by EDB doesn't create a new system user 'postgres' like ususally is done by the package systems on Linux systems making this system user the owner all directories and files of the database clusters with exclusive permissions. Instead, the postgres.exe processes are run as an 'NT service', which is also used by a lot of non-postgres processes. These processes have the same permissions on Postgres' files as the Postgres processes. When I set up a lot of PostgreSQL servers on Windows machines a couple of years ago, I had a script create a special user for the database system and also made this special user the owner of all files and directories of the clusters. I am afraid that some non-postgres process might have meddled with the Postgres cluster causing trouble. Maybe you can check ownership and permissions of the respective files. Unfortunately, the permissions system on Windows is far more complex than the one on Linux with just user owner, group owner and the permissions r, w and x. Kind Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
Re: LOG: could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat"
From
Erik Wienhold
Date:
> On 10/03/2023 19:06 CET Holger Jakobs <holger@jakobs.com> wrote: > > What is a bit problematic running PostgreSQL on Windows is that the > installer by EDB doesn't create a new system user 'postgres' like > ususally is done by the package systems on Linux systems making this > system user the owner all directories and files of the database clusters > with exclusive permissions. Yeah, that also bugs me because I have to manage the password for the initial superuser. > Instead, the postgres.exe processes are run as an 'NT service', which is > also used by a lot of non-postgres processes. These processes have the > same permissions on Postgres' files as the Postgres processes. > > When I set up a lot of PostgreSQL servers on Windows machines a couple > of years ago, I had a script create a special user for the database > system and also made this special user the owner of all files and > directories of the clusters. Thanks for the tip. > I am afraid that some non-postgres process might have meddled with the > Postgres cluster causing trouble. My guess is Symantec Endpoint Protection because in the past it detected the EnterpriseDB installer and PostGIS installer as false-positive around 50% of the time. Seems plausible that it detected pg_ctl.exe after I updated to 14.7. > Maybe you can check ownership and permissions of the respective files. > Unfortunately, the permissions system on Windows is far more complex > than the one on Linux with just user owner, group owner and the > permissions r, w and x. * Owner of c:/program files/postgresql/{12,14} is group "Administrators" which has a couple of users. Also owns everything under bin/. * User "NETWORK SERVICE" is owner of most of the files under data/ with some files owned by the user who installed Postgres (myself in case of pg14, some other user in case of pg12). -- Erik