BUG #5278: Postgres hangs / crashes every day
The following bug has been logged online:
Bug reference: 5278
Logged by: Murali
Email address: nmmreddy11@hotmail.com
PostgreSQL version: 8.0.6
Operating system: Windows Server 2003 Standard 32 Bit
Description: Postgres hangs / crashes every day
Details:
Postgres hangs / crashes every day (gets corrupted?). It neither serves any
requests from connected applications nor accepts new connections. I can’t
connect to the data base through PG Admin also. Below given is the log
snippet at the time of failure.
Log Snippet1:
2009-12-16 10:59:11 FATAL: could not open relation 17253/33902/16729: No
such file or directory
Log Snippet2:
2009-12-16 09:44:50 ERROR: could not open relation 17253/33902/16691:
Permission denied
2009-12-16 09:44:50 CONTEXT: writing block 100 of relation
17253/33902/16691
2009-12-16 09:44:50 WARNING: could not write block 100 of
17253/33902/16691
2009-12-16 09:44:50 DETAIL: Multiple failures --- write error may be
permanent.
What does Application do?
Multiple applications communicate with Postgres.
One application inserts the data into Postgres and all other applications
get the data from Postgres
Data will not be deleted from Postgres as soon as other applications get
data. It will be deleted at a alter point of time.
RAID configuration for the hard disk that Postgres is using:
RAID 1 + 0
Is anti-virus software monitoring the Postgres drives?
Anti-virus is running on the system but Postgres folder is excluded
Is any other application using the same hard disk?
All our applications log traces into this drive so heavy logging is
happening.
Is the hard disk health ok?
No event log found indicating hard drive errors. There were some power
module issues over a year back.
What is the database size?
Around 200k rows.
"Murali" <nmmreddy11@hotmail.com> writes:
Postgres hangs / crashes every day (gets corrupted?). It neither serves any
requests from connected applications nor accepts new connections. I can’t
connect to the data base through PG Admin also. Below given is the log
snippet at the time of failure.
Log Snippet1:
2009-12-16 10:59:11 FATAL: could not open relation 17253/33902/16729: No
such file or directory
Log Snippet2:
2009-12-16 09:44:50 ERROR: could not open relation 17253/33902/16691:
Permission denied
Given that last, I'd bet that most of your problems come from broken
antivirus software. Remove it. Do not trust that excluding PG's data
folder is sufficient. In fact, don't trust that turning the AV code off
is sufficient. Uninstall it.
There's some information about AV compatibility here:
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#What_Anti-Virus_software_is_compatible.3F
but unexpected permissions errors are a smoking gun that should trump
anything at all anyone may claim about particular AV code being okay.
It's fairly likely that the AV code's interference has created
corruption in your database, if Postgres was able to write some files
but was prevented from making other parts of the same logical update.
Once you're rid of the unexpected permissions errors, a dump and reload
would be a good idea to try to get back to a clean state.
The other thing you ought to consider is moving onto a supported version
of Postgres. 8.0.6 is very old and has got numerous known bugs,
particularly when running on Windows. You need at least
8.2.something-recent.
regards, tom lane
"Murali" <nmmreddy11@hotmail.com> wrote:
PostgreSQL version: 8.0.6
Operating system: Windows Server 2003 Standard 32 Bit
Description: Postgres hangs / crashes every day
You do realize that Windows is not a supported platform for any
release less than 8.2?
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
For anyone running on 8.0.x, they should be running the latest bug
fix release, which is 8.0.23. You're missing a lot of bug fixes.
Anti-virus is running on the system but Postgres folder is
excluded
Many people have found it necessary to totally *uninstall*
anti-virus software to keep it from interfering with database
operations.
You should consider an upgrade to at least 8.2.15 as soon as
practicable. I would also "harden" the machine by disabling
unnecessary services, and then uninstall the AV software.
I hope this helps.
-Kevin
Tom and Kevin,
Thank you for your quick reply.
I am not allowed to uninstall anti virus software on this system but can configure the required exclusions. Anti Virus software being used is Symantec.
Do any of the problems I am facing are fixed in a recent release so that I can upgrade to that release?
Yesterday night while I was working on this system, I discovered that a Windows Junction Point was configured. Are there any known issues when a windows junction point is configured to the Postgres folder?
Thanks,
Murali
To: nmmreddy11@hotmail.com
CC: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5278: Postgres hangs / crashes every day
Date: Thu, 14 Jan 2010 18:11:24 -0500
From: tgl@sss.pgh.pa.us"Murali" <nmmreddy11@hotmail.com> writes:
Postgres hangs / crashes every day (gets corrupted?). It neither serves any
requests from connected applications nor accepts new connections. I can’t
connect to the data base through PG Admin also. Below given is the log
snippet at the time of failure.Log Snippet1:
2009-12-16 10:59:11 FATAL: could not open relation 17253/33902/16729: No
such file or directoryLog Snippet2:
2009-12-16 09:44:50 ERROR: could not open relation 17253/33902/16691:
Permission deniedGiven that last, I'd bet that most of your problems come from broken
antivirus software. Remove it. Do not trust that excluding PG's data
folder is sufficient. In fact, don't trust that turning the AV code off
is sufficient. Uninstall it.There's some information about AV compatibility here:
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#What_Anti-Virus_software_is_compatible.3F
but unexpected permissions errors are a smoking gun that should trump
anything at all anyone may claim about particular AV code being okay.It's fairly likely that the AV code's interference has created
corruption in your database, if Postgres was able to write some files
but was prevented from making other parts of the same logical update.
Once you're rid of the unexpected permissions errors, a dump and reload
would be a good idea to try to get back to a clean state.The other thing you ought to consider is moving onto a supported version
of Postgres. 8.0.6 is very old and has got numerous known bugs,
particularly when running on Windows. You need at least
8.2.something-recent.regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
_________________________________________________________________
New Windows 7: Find the right PC for you. Learn more.
http://windows.microsoft.com/shop
Murali Mohan Nareddy <nmmreddy11@hotmail.com> wrote:
Do any of the problems I am facing are fixed in a recent release
so that I can upgrade to that release?
You're taking a big risk if you don't update, even though your
immediate problems seem to be caused by the AV software. To view
the bugs which have been fixed, along with new features, start at
the top of this page and follow all the links until you get to your
release number:
http://www.postgresql.org/docs/8.4/static/release.html
When you hit a description of any bug which scares you, it's time to
stop reading and schedule the upgrade.
Yesterday night while I was working on this system, I discovered
that a Windows Junction Point was configured. Are there any known
issues when a windows junction point is configured to the Postgres
folder?
I don't use Windows, but I've heard that they work fine. Have you
configured the AV software to ignore both sides of the junction
points?
-Kevin