Обсуждение: Error on Windows server could not open relation base/xxx/xxx Permission denied

One of my clients is getting this problem occasionally. Actually, we can cause it to happen quite reliably by pasting
certaintext into a couple of fields, but the vast majority of text entered into the vast majority of fields causes no
problem.

I've read enough to suggest that AV software might be the culprit. It has been said that it is not sufficient to
excludethe database directory nor even to disable to AV protection, it has to be removed. 

The problem is, their database server is also a file server. As a file server it must have AV protection. The server is
runningWindows Server 2003 I believe. It has RAID etc.  My client's antivirus software is AVG (paid, not free). 

Question: Is AV software still regarded as the likely culprit?

Question: If so, is any particular brand less likely to cause problems, more likely?

Question: Any other suggestions?

I'd had to tell my client to purchase more hardware because the database software I've recommended has a problem. I
havea number of other clients using Postgres and nobody else has had any problem. Switching AV software wouldn't be
suchan issue. 

Thanks.

John

On 07/06/10 10:29, John T. Dow wrote:
> One of my clients is getting this problem occasionally. Actually, we
> can cause it to happen quite reliably by pasting certain text into a
> couple of fields, but the vast majority of text entered into the vast
> majority of fields causes no problem.

> I've read enough to suggest that AV software might be the culprit.
> It has been said that it is not sufficient to exclude the database
> directory nor even to disable to AV protection, it has to be removed.

Depends on the AV software. That advice is general, and is given because
_some_ antivirus software is badly written and fails to properly exclude Pg.

Some AV software probably behaves fine.

> The problem is, their database server is also a file server. As a file server it must have AV protection. The server
isrunning Windows Server 2003 I believe. It has RAID etc.  My client's antivirus software is AVG (paid, not free). 

I would not trust AVG. I've been most unimpressed with it lately - their
resident scanning module is immature, incredibly slow, and seems prone
to false positives.

> Question: Is AV software still regarded as the likely culprit?

Likely enough - especially for intermittent issues - that the best thing
to do is uninstall it, reboot, and re-test to see if the issue remains.

If you can reproduce it without the AV software then it's worth
investigating further.

> Question: If so, is any particular brand less likely to cause problems, more likely?

While I can't speak for Pg here (I don't use Pg on any machine with
antivirus, and in fact don't use it in production on Windows at all) my
limited experience with AV on Windows has been "the bigger the better".
The antivirus modules from Symantec and McAfee seem to be the least
evil. Not good, just less evil.

In my experience supporting end users, at least, it's VERY, VERY
important never to install any "Internet Security" suite if you want the
computer to actually work, though. AV vendor firewall packages etc are
frickin' poison. Just the basic AV package seems to be OK, though again
I can't speak re Pg because I don't use Pg with AV.

I'm almost tempted to try tracking down some of these AV-related issues.
Unfortunately, though, AV software is not only lacking source code or
even .pdb debug symbol files, but tends to be designed to make it hard
to debug as part of its tamper resistance. So it's a nightmare to trace.
Add the fact that many of the issues reported seem to be races or bugs
in the AV file access hooks, and it quickly ceases being worth the pain.

It'd be interesting if someone with a paid contract for AV support would
go to their AV vendor and get them involved. With the active
co-operation of an AV vendor or two and a reproducible fault, some
progress might be possible.

> I'd had to tell my client to purchase more hardware because the
> database software I've recommended has a problem. I have a number of
> other clients using Postgres and nobody else has had any problem.
> Switching AV software wouldn't be such an issue.

First, I'd uninstall it, reboot, re-test to see if you can reproduce the
fault. If you still can, it's probably not AVG.

If you find the fault goes away when AVG does, try something else like
McAfee or Symantec's AV component and see how you go.

--
Craig Ringer

On Mon, Jun 7, 2010 at 08:44, Craig Ringer <craig@postnewspapers.com.au> wrote:
> On 07/06/10 10:29, John T. Dow wrote:
>> One of my clients is getting this problem occasionally. Actually, we
>> can cause it to happen quite reliably by pasting certain text into a
>> couple of fields, but the vast majority of text entered into the vast
>> majority of fields causes no problem.
>
>> I've read enough to suggest that AV software might be the culprit.
>> It has been said that it is not sufficient to exclude the database
>> directory nor even to disable to AV protection, it has to be removed.
>
> Depends on the AV software. That advice is general, and is given because
> _some_ antivirus software is badly written and fails to properly exclude Pg.

Actually, the issue isn't that they "fail to properly exclude postgres".

The issue is that they insert filtering functions in the Windows API,
that *breaks the Windows API*. The reason this is often not noticed on
other products before PostgreSQL is that PostgreSQL uses a
multiprocess architecture, and this is specifically the case where
they fail. This is uncommon in the extreme on Windows. In fact,
Microsoft has (or at elast used to have) examples up on their MSDN
site that break in this case.


> Some AV software probably behaves fine.

Probably.


>> The problem is, their database server is also a file server. As a file server it must have AV protection. The server
isrunning Windows Server 2003 I believe. It has RAID etc.  My client's antivirus software is AVG (paid, not free). 

It's generally a mistake to put a fileserver on the same machien as a
database server, if you want any kind of performance for either one of
them. But if you don't care about performance at all, it's a fairly
sensible move, yes.

>> Question: Is AV software still regarded as the likely culprit?
>
> Likely enough - especially for intermittent issues - that the best thing
> to do is uninstall it, reboot, and re-test to see if the issue remains.
>
> If you can reproduce it without the AV software then it's worth
> investigating further.

Yeah. And if it does go away without the AV you at least know what the
problem was. You can then decide if you want to try a different AV, or
if you want to look at perhaps running pg in a virtual machine on the
box.

> It'd be interesting if someone with a paid contract for AV support would
> go to their AV vendor and get them involved. With the active
> co-operation of an AV vendor or two and a reproducible fault, some
> progress might be possible.

Yes, that would be very useful.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander, 07.06.2010 15:52:
>> Some AV software probably behaves fine.
>
> Probably.

In case anyone is interested:

I have two development computers that run Postgres on Windows XP.
One with Avira the other with Sophos.

Neither has or had any problems installing or running Postgres

Regards
Thomas

On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Magnus Hagander, 07.06.2010 15:52:
>>>
>>> Some AV software probably behaves fine.
>>
>> Probably.
>
> In case anyone is interested:
>
> I have two development computers that run Postgres on Windows XP.
> One with Avira the other with Sophos.
>
> Neither has or had any problems installing or running Postgres

What kind of load do the systems have? Particularly, how many
parallell connections? That seems to push things over the edge more
often than high transaction single-user ones.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander, 07.06.2010 16:15:
> On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>> Magnus Hagander, 07.06.2010 15:52:
>>>>
>>>> Some AV software probably behaves fine.
>>>
>>> Probably.
>>
>> In case anyone is interested:
>>
>> I have two development computers that run Postgres on Windows XP.
>> One with Avira the other with Sophos.
>>
>> Neither has or had any problems installing or running Postgres
>
> What kind of load do the systems have? Particularly, how many
> parallell connections? That seems to push things over the edge more
> often than high transaction single-user ones.
>

Ah, that might make the difference:
I have no real load on those computers (as I said, developer machine)

So it's more a single-user type of load

Regards
Thomas

OP here....

We removed AVG from the computer and rebooted.

Same problem.

We are quite certain that AVG is no longer installed. It doesn't show up where it used to, and a search of the registry
for"AVG" finds a couple leftovers but doesn't seem to indicate that it's still installed. 

The computer is running Windows 2000 Server, SP4. Build 5.00.2195.  It's a Pentium (R) 4 CPU 2.80 GHz, AT/AT
Compatible,1 GB memory. 

That's not a particularly powerful computer, but so far we're only testing the application. Someone asked about load,
someonealso mentioned that mixing file serving and database serving is not good in general. Since we're only testing
rightnow and in fact don't expect much load when in production, those probably aren't issues. 

Anyway, it now looks like the problem is not caused by AV software.

Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows.

Thanks.

John


On Mon, 07 Jun 2010 16:35:33 +0200, Thomas Kellerer wrote:

>Magnus Hagander, 07.06.2010 16:15:
>> On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>>> Magnus Hagander, 07.06.2010 15:52:
>>>>>
>>>>> Some AV software probably behaves fine.
>>>>
>>>> Probably.
>>>
>>> In case anyone is interested:
>>>
>>> I have two development computers that run Postgres on Windows XP.
>>> One with Avira the other with Sophos.
>>>
>>> Neither has or had any problems installing or running Postgres
>>
>> What kind of load do the systems have? Particularly, how many
>> parallell connections? That seems to push things over the edge more
>> often than high transaction single-user ones.
>>
>
>Ah, that might make the difference:
>I have no real load on those computers (as I said, developer machine)
>
>So it's more a single-user type of load
>
>Regards
>Thomas
>
>
>
>
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

On Sun, Jun 6, 2010 at 8:29 PM, John T. Dow <john@johntdow.com> wrote:
> I'd had to tell my client to purchase more hardware because the database software I've recommended has a problem. I
havea number of other clients using Postgres and nobody else has had any problem. Switching AV software wouldn't be
suchan issue. 

In fairness to pg, the problem here, if it is the antivirus getting in
the way of file reads, is not pgsql's.  It expects to be able to
access it's files in a mode that the antivirus interferes with.  The
anti-virus is broken if it gets in the way of legitmate apps,
especially if turning off the avg doesn't fix it but removing it does
fix pg's problems.

A database expecting its files to be there, unmolested is not some
insane requirement.  It's pretty basic.  Putting a db on a file server
in Windows is a bad move.  I fed and watered NT Back in the 3.51 and
4.0 days, and it was never a good move to put disparate heavy services
like domain controller, file server, dhcp, database or web server on
the same box back then.  It's still not today.

Is it possible for you to run your file server OR pgsql inside a vm
that the anti-virus can't see or affect?  Or is there a spare server,
underutiliized you can clear off to put pg on?

On 8/06/2010 9:11 AM, John T. Dow wrote:
> OP here....
>
> We removed AVG from the computer and rebooted.
>
> Same problem.

OK, good to know. Thanks very much for testing that, and my apologies
for recommending something that didn't work out. Of course, it would
have been hard to progress without eliminating that possible factor.

> Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows.

Well, certainly I'd expect that Pg on Windows 2000 server gets about
zero regular testing. Why would you deploy a server OS that's already 10
years out of date, went EOL five years ago, and lost even the option of
paid extended support this year?

http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274

It'd be interesting to investigate this issue ... but win2k server isn't
exactly easy to come by. Anyone on the list got a win2k server (or
license) around they can do some experimenting on? All I have here is
NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop
suspects.

--
Craig Ringer

On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote:

>On 8/06/2010 9:11 AM, John T. Dow wrote:
>> OP here....
>>
>> We removed AVG from the computer and rebooted.
>>
>> Same problem.
>
>OK, good to know. Thanks very much for testing that, and my apologies
>for recommending something that didn't work out. Of course, it would
>have been hard to progress without eliminating that possible factor.
>
>> Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of
Windows.
>
>Well, certainly I'd expect that Pg on Windows 2000 server gets about
>zero regular testing. Why would you deploy a server OS that's already 10
>years out of date, went EOL five years ago, and lost even the option of
>paid extended support this year?
>
>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274
>





Good point.

It's not my server, it's my client's server, and I don't know the history of it.

They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the
databaseserver, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer
andsee if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard
driveand a RAID controller). The application, daily backups, and WAL files could all live on the original server. 

If they go that route, we'd never know for certain what the original problem was.

I'll post back after anything is done.

JOhn









>It'd be interesting to investigate this issue ... but win2k server isn't
>exactly easy to come by. Anyone on the list got a win2k server (or
>license) around they can do some experimenting on? All I have here is
>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop
>suspects.
>
>--
>Craig Ringer
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Here's the current status.

We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.)

We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed
withthe same problem mentioned in the subject line. 

So we zipped up the data directory and unzipped it on the XP computer.

We then attempted to paste in the "large" block of text (200 lines of plain ascii, 49000 bytes) and got the same
problemas before. 

Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty
whenwe paste certain text into certain fields. 

This computer is running "Symantec Endpoint Protection", with the proactive threat feature turned off.

Question: Is it possible that there's corruption in the database which is being incorrectly reported as "Permission
denied"?

Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source
andon both computers creates the incorrect message? 

We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from
thelegacy system extracted a few months ago and since then there has been additional data entered and changed as people
haveplayed with and tested the application. 

Is this a random event? A bug? Advice please on what to do next.

John





On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote:

>On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote:
>
>>On 8/06/2010 9:11 AM, John T. Dow wrote:
>>> OP here....
>>>
>>> We removed AVG from the computer and rebooted.
>>>
>>> Same problem.
>>
>>OK, good to know. Thanks very much for testing that, and my apologies
>>for recommending something that didn't work out. Of course, it would
>>have been hard to progress without eliminating that possible factor.
>>
>>> Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of
Windows.
>>
>>Well, certainly I'd expect that Pg on Windows 2000 server gets about
>>zero regular testing. Why would you deploy a server OS that's already 10
>>years out of date, went EOL five years ago, and lost even the option of
>>paid extended support this year?
>>
>>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274
>>
>
>
>
>
>
>Good point.
>
>It's not my server, it's my client's server, and I don't know the history of it.
>
>They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the
databaseserver, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer
andsee if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard
driveand a RAID controller). The application, daily backups, and WAL files could all live on the original server. 
>
>If they go that route, we'd never know for certain what the original problem was.
>
>I'll post back after anything is done.
>
>JOhn
>
>
>
>
>
>
>
>
>
>>It'd be interesting to investigate this issue ... but win2k server isn't
>>exactly easy to come by. Anyone on the list got a win2k server (or
>>license) around they can do some experimenting on? All I have here is
>>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop
>>suspects.
>>
>>--
>>Craig Ringer
>>
>>
>>--
>>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general