PostgreSQL 10.20 crashes / Antivirus

From: "Thomas, Richard" <Richard(dot)Thomas(at)atkinsglobal(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: PostgreSQL 10.20 crashes / Antivirus
Date: 2022-04-06 10:59:02
Message-ID: LO2P123MB509428EC0EFAA847AC95F060F1E79@LO2P123MB5094.GBRP123.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've been having problems with several PostgreSQL crashes over the last month (originally with 10.16, then more recently with 10.20, all on a Windows Server 2012R2 platform), always with a low level permissions error:
"server process (PID 5664) was terminated by exception 0xC0000022"
(This NT error translates as "Access Denied: A process has requested access to an object but has not been granted those access rights").

I was certainly having issues earlier with McAfee Antivirus causing some of these problems but this has been reduced after following the advice here to prevent scanning of PGDATA folders and postgres.exe (if someone can confirm that reference to "postgresql.exe" is a typo on that page I will correct it, but don't want to in case it refers to a different version of PostgreSQL):
https://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Antivirus_software
I have also got our IT to disable antivirus on our tablespace folder and on the folder running our backup PowerShell script (which calls pg_dump).

The last few crashes have occurred during the backup process, with the most recent one apparently after one database pg_dump completes (successfully) but before the next database pgdump can begin - although the new pg_dump log file was created (is empty) it does not get as far as creating the .backup file and the PostgreSQL log file shows no new operations (I have logging set quite verbose including log_min_duration_statement = 0 to log every statement). I have copied out the bit running up to the failure at the end of this email where you can see the final part (dumping of sde_xml_indexes) of the pg_dump on database a9sde, then it disconnecting from that database.

Any suggestions for how to fix/diagnose? Some things I have tried already:

* Ran "sfc" (System File Checker) to check for/fix any Windows OS corruptions (none found that were relevant)
* Ran "amcheck" on several of the databases
* Unfortunately checksums are not turned on with the database (not an option with the EDB PostgreSQL Windows Installer) and can't be turned on after initdb on version 10
* I hoped to run pg_catcheck but cannot find a binary version of this online and I think it would take me quite a lot of work to build it myself (I don't have Visual Studio, only VSC).

Richard

2022-04-02 02:14:53 BST: [7480] user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOG: 00000: duration: 0.816 ms statement: COPY sde.sde_xml_indexes (index_id, index_name, owner, index_type, description) TO stdout;
2022-04-02 02:14:53 BST: [7480] user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOCATION: exec_simple_query, postgres.c:1200
2022-04-02 02:14:54 BST: [7480] user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOG: 00000: disconnection: session time: 0:36:52.430 user=scde_suad database=a9sde host=a9dualling.atkinsgeospatial.com port=59467
2022-04-02 02:14:54 BST: [7480] user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOCATION: log_disconnections, postgres.c:4577
2022-04-02 02:14:55 BST: [5016] LOG: 00000: server process (PID 100) was terminated by exception 0xC0000022

At Atkins - member of the SNC-Lavalin Group, we work flexible hours around the world. Although I have sent this email at a time convenient for me, I don't expect you to respond until it works for you.

NOTICE - This email message and any attachments may contain information or material that is confidential, privileged, and/or subject to copyright or other rights. Any unauthorized viewing, disclosure, retransmission, dissemination, or other use of or reliance on this message or anything contained therein is strictly prohibited and may be unlawful. If you believe you may have received this message in error, kindly inform the sender by return email and delete this message from your system. Thank you.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ajin Cherian 2022-04-06 11:37:06 Re: Support logical replication of DDLs
Previous Message Laurenz Albe 2022-04-06 10:37:19 Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)