Re: ESET NOD32 Antivirus interference with PostgreSQL

Just wanted to give a heads up to anyone who might be having a similar
problem. We had an installation on a customer machine that had the AV
product "ESET NOD32" installed. We quickly started having problems when
there were two or more concurrent queries against the same tables in
PostgreSQL v8.2.21 (over multiple connections via JDBC). Specifically
we were getting errors like:

ERROR: could not open relation 1663/27267/27472: Permission denied

We checked the file permissions (which checked out), we used
sysinternals tools to monitor the file system to make sure the file
permsisions werent being changed by the backup software running on the
system (they weren't), and finally uninstalled the "ESET NOD32" AV
software. We have been running without issue now that the AV software
has been uninstalled. The filters they use are apparently not very
happy with the multi-process, highly-concurrent nature of PostgeSQL.

Thought this might be of help to anyone else out there who comes across
this AV software during deployment and starts encountering strange behavior.

bruce

On 21 January 2012 00:45, Bruce Duncan <bduncan(at)visualmining(dot)com> wrote:
> Thought this might be of help to anyone else out there who comes across this
> AV software during deployment and starts encountering strange behavior.

Thanks for the report, but shouldn't you really be complaining to the
anti-virus vendor?

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On Sat, Jan 21, 2012 at 1:45 AM, Bruce Duncan <bduncan(at)visualmining(dot)com>wrote:

> Just wanted to give a heads up to anyone who might be having a similar
> problem. We had an installation on a customer machine that had the AV
> product "ESET NOD32" installed. We quickly started having problems when
> there were two or more concurrent queries against the same tables in
> PostgreSQL v8.2.21 (over multiple connections via JDBC). Specifically we
> were getting errors like:
>
> ERROR: could not open relation 1663/27267/27472: Permission denied
>
> We checked the file permissions (which checked out), we used sysinternals
> tools to monitor the file system to make sure the file permsisions werent
> being changed by the backup software running on the system (they weren't),
> and finally uninstalled the "ESET NOD32" AV software. We have been running
> without issue now that the AV software has been uninstalled. The filters
> they use are apparently not very happy with the multi-process,
> highly-concurrent nature of PostgeSQL.
>
> Thought this might be of help to anyone else out there who comes across
> this AV software during deployment and starts encountering strange behavior.
>
>
>

Short: see top-rated answer here:
http://serverfault.com/questions/329990/should-we-run-anti-virus-software-on-our-dedicated-sql-server

Long and serious:

Resident AV monitors register special hook somewhere near the windows
kernel, which intercepts all read/write requests and pipes the data through
AV scanner, delaying I/O until they think it's clean. During that delay,
you may get such error. I've seen it several times (with and without
postgres). It happens only with concurrent access (the process that
initiated the I/O does not get error - only delay)

For most office/home applications it's OK - the delay is small enough and
most I/O is via single process/thread.
But not for DBMS.

So, when running PostgreSQL on a Windows box together with resident
on-access AV software, it's rather a must to take following actions (any
combination of following):

* uninstall the AV software completely
* disable the realtime monitor completely (leave on-demand scanning, it
does not hurt)
* use an exclusion mechanism (most AV have this) to ignore I/O actions on
postgres data directory
* use an exclusion mechanism (most AV have this) to ignore I/O actions
taken postgres binaries

cheers,
Filip