Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

Hi,

Le 11/02/2021 à 01:21, Guy Burgess a écrit :
>
> Hello,
>
> Running 13.1 on Windows Server 2019, I am getting the following log
> entries occasionally:
>
>     2021-02-11 12:34:10.149 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000D3": Permission denied
>     2021-02-11 12:40:31.377 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000D3": Permission denied
>     2021-02-11 12:46:06.294 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000D3": Permission denied
>     2021-02-11 12:46:16.502 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000DA": Permission denied
>     2021-02-11 12:50:20.917 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000D3": Permission denied
>     2021-02-11 12:50:31.098 NZDT [6072] LOG:  could not rename file
> "pg_wal/0000000100000099000000DA": Permission denied
>
> What appears to be happening is the affected WAL files (which is
> usually only 2 or 3 WAL files at a time) are somehow "losing" their
> NTFS permissions, so the PG process can't rename them - though of
> course the PG process created them. Even running icacls as admin gives
> "Access is denied" on those files. A further oddity is the affected
> files do end up disappearing after a while.
>
> The NTFS permissions on the pg_wal directory are correct, and most WAL
> files are unaffected. Chkdsk reports no problems, and the database is
> working fine otherwise. Have tried disabling antivirus software in
> case that was doing something but no difference.
>

I haven't dealt with a Windows environment for quite some time, but from
what I remember an antivirus installs a driver intercepting file
accesses and these drivers are still active even if you disable the
antivirus (I suppose they just call a noop instead of content analysis
code) and can still interfere with your system. For example some years
ago I've seen what looked like a race condition involving rename for
MySQL on Windows that could not be fixed by disabling the antivirus but
could by uninstalling it completely.

You might want to uninstall the antivirus temporarily to check this.

Best regards,

--
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/