Thread: 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. http://www.postgresql.org/ > 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, =20 Thank you for your quick reply. =20 I am not allowed to uninstall anti virus software on this system but can co= nfigure the required exclusions. Anti Virus software being used is Symantec. =20 Do any of the problems I am facing are fixed in a recent release so that I = can upgrade to that release? =20 Yesterday night while I was working on this system, I discovered that a Win= dows Junction Point was configured. Are there any known issues when a windo= ws junction point is configured to the Postgres folder? =20 Thanks, Murali > To: nmmreddy11@hotmail.com > CC: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5278: Postgres hangs / crashes every day=20 > Date: Thu, 14 Jan 2010 18:11:24 -0500 > From: tgl@sss.pgh.pa.us >=20 > "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= =92t > > connect to the data base through PG Admin also. Below given is the log > > snippet at the time of failure. >=20 > > Log Snippet1: > > 2009-12-16 10:59:11 FATAL: could not open relation 17253/33902/16729: No > > such file or directory >=20 > > Log Snippet2: > > 2009-12-16 09:44:50 ERROR: could not open relation 17253/33902/16691: > > Permission denied >=20 > 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. >=20 > There's some information about AV compatibility here: > http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Nati= ve_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. >=20 > 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. >=20 > 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. >=20 > regards, tom lane >=20 > --=20 > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs =20=09=09=20=09=20=20=20=09=09=20=20 _________________________________________________________________ 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