This article describes the impact of antivirus software in the database server and possible recommendations.
Antivirus software installed on the database servers can cause issues such as operations like autovacuum and pg_dump exit with errors Operation not permitted, although the files have the required permissions.
Sometimes, the logs may indicate errors similar to the following:
pg_dump: archiver (db) connection to database "postgres" failed: FATAL: could not open file "global/XXXX": Operation not permitted
DETAIL: Cold not open file "pg_multixact/offsets/XXXX": Operation not permitted.
While these errors are accurate, in certain situations, they may be mislead DBAs and sysadmins into thinking that either the concerned file is corrupted or doesn't have required OS permissions.
If these error messages appear in the database server, and the permissions appear to be set correctly, an additional recommendation would be to check if any anti-virus software is running on the problematic machine, and whether it is scanning the concerned file when an operations like pg_dump and autovacuum is accessing it.
Antivirus software constantly scans for files, and Postgres can easily get into a problem because Postgres regularly creates heap, index, and WAL files, and binary content is written into these files. Depending on what data is written, these files can contain patterns that are mistaken for viruses, causing the files to be removed from Postgres-controlled directories. Obviously, this hampers the PostgreSQL database operations.
Also, while PostgreSQL is continuously reading from and writing to the same set of files from multiple processes, antivirus software has the potential to intercept relevant system calls, thereby subject data to race conditions and erratic behavior. Such phenomena can cause unexpected errors to appear or even corrupt data.
To prevent this issue, always make sure that the EPAS or PostgreSQL binaries (install directory) and data directory are added to the anti-virus software exclusion list.