[GENERAL] ERROR: XX001: could not read block 2354 of relation …
Hi, I have the error ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying to Insert data into a table. I would say that 99% of Insert works and 100% of read works. This is only happenning since few weeks. I have done Vaccum Analyze without any success (the vaccum take 65 minutes but nothing is fixed, still have the ERROR XXX001). I have this error in the backend application and when I do some Insert Query in the PgAdmin tool. What can I do to fix that problem? Thank you,
[GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…
I am on Windows Server 2003 and humm I will have to check tommorow morning but I do not think any Anti-Virus is scanning. On Tue, Mar 31, 2009 at 6:53 PM, Scott Marlowe wrote: > On Tue, Mar 31, 2009 at 2:10 PM, Patrick Desjardins > wrote: > > Hi, > > > > I have the error ERROR: XX001: could not read block 2354 of relation > > 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when > trying > > to Insert data into a table. I would say that 99% of Insert works and > 100% > > of read works. This is only happenning since few weeks. I have done > Vaccum > > Analyze without any success (the vaccum take 65 minutes but nothing is > > fixed, still have the ERROR XXX001). I have this error in the backend > > application and when I do some Insert Query in the PgAdmin tool. > > What OS, pg version etc are you running? > > If windows, are you running any anti-virus software? A lot of > anti-virus packages are dumb as a brick and lock files when checking > them and cause these problems. If so, either exclude the pg > directories from virus checking or turn it off altogether. > > Just a guess. Not enough info to really know. >
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
Believe it or not, this morning I found that the IT departement has installed "Trend Micro Office Scan" on the server. I will contact them to remove it. Do I still need to dump everything thing and load back or this will solve the problem? If I need to dump, what type of dump do you recommend? On Tue, Mar 31, 2009 at 8:52 PM, Craig Ringer wrote: > Patrick Desjardins wrote: > >> I am on Windows Server 2003 and humm I will have to check tommorow morning >> but I do not think any Anti-Virus is scanning. >> > > Sometimes even an antivirus package that has its "realtime protection" > features disabled will still cause problems. This comes back to what Scott > Marlowe said: "A lot of anti-virus packages are dumb as a brick." They often > fail to unload hook DLLs when resident protection is disabled, and sometimes > even keep on scanning, just ignoring the results! (I've seen this multiple > times). > > I remain of the opinion that antivirus software has no place on a database > server. There should be no way a virus can get near it, because you're NEVER > granting users access to it except via the database engine, and the only > hole in the Windows Firewall should be for the database. > > Since this issue keeps on cropping up, I wonder how the other DB vendors > that support Windows handle it? Do antivirus products have standard APIs for > exceptions - "don't scan me" ? If so, isn't that a gaping security hole? And > if not, how do other DBs manage to get anything done when some half-wit > dodgy AV software is installed? Or do the other folks (Oracle etc) just have > these sorts of issues too? > > Proposed FAQ entry: > > - > Q: I'm getting weird, intermittent errors when starting PostgreSQL or > executing SQL statements. My PostgreSQL server runs on Windows. > > A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. > Remember to dump your database (you can use PgAdmin for this) BEFORE > uninstalling the old version of PostgreSQL. > > A(2): If you have any antivirus software installed, COMPLETELY UNINSTALL it > (at least as a test to see if it is the problem). Many anti-virus packages > are written without considering the needs of databases, and do things that > will interfere with the way a database accesses its files. Some have > implementation problems that mean that even disabling their real-time > protection is insufficient, since they STILL interfere with the database > even when supposedly disabled. Lots of AV packages also cause severe > performance problems with a database even when they appear to work fine. To > see if your antivirus software is causing your problems, completely > uninstall it and reboot your computer before re-testing. > > Q: I'm getting inexplicable network connection errors or network > performance problems with PostgreSQL. My PostgreSQL server runs on Windows. > > A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. > Remember to dump your database (you can use PgAdmin for this) BEFORE > uninstalling the old version of PostgreSQL. > > A(2): If you have any 3rd party firewall software installed, COMPLETELY > UNINSTALL it. Disabling it is not good enough, as many firewall packages > continue to interfere with Windows' networking even when disabled. 3rd party > firewall packages should not be necessary on any version of Windows with a > built-in firewall, and tend to cause more problems than they solve. They are > unsuitable for use on a machine intended for server use. If, after > uninstalling your firewall, you lose your network connection or have other > networking problems, run the following command: >netsh ip interface reset %HOMEPATH%\Desktop\resetlog.txt > which should clean up any mess left by the poorly written firewall > package's failure to cleanly uninstall its self. > > > > [Needs link to section in server admin docs "PostgreSQL Server > Administration for Windows" that discusses AV scanning, isolated server, > firewall, datadir location, permissions, etc - I'm happy to write at least a > basic version of this if folks here agree it'd be useful.] > - > > > > -- > Craig Ringer >
[GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…
I have not reindexes. I will try to use the Reindex command ( http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I get error I will try to drop them. If it doesn't solve I will pg_dump. I still need to wait the IT to remove the Anti-virus. Will give you more news later. On Wed, Apr 1, 2009 at 11:57 AM, Emanuel Calvo Franco < postgres@gmail.com> wrote: > 2009/4/1 Emanuel Calvo Franco : > > 2009/4/1 Patrick Desjardins : > > > Did you try to make a reindex? > > > > If you get an error with that, try to drop indexes and create > again. > > > > > -- > > Emanuel Calvo Franco > >Sumate al ARPUG ! > > (www.postgres-arg.org - > > www.arpug.com.ar) > >ArPUG / AOSUG Member > > Postgresql Support & Admin > > > > > > -- > Emanuel Calvo Franco >Sumate al ARPUG ! > (www.postgres-arg.org - > www.arpug.com.ar) >ArPUG / AOSUG Member > Postgresql Support & Admin >
[GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…
We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs. I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest. On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco < postgres@gmail.com> wrote: > 2009/3/31 Patrick Desjardins : > > Hi, > > > > I have the error ERROR: XX001: could not read block 2354 of relation > > 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when > trying > > to Insert data into a table. I would say that 99% of Insert works and > 100% > > of read works. This is only happenning since few weeks. I have done > Vaccum > > Analyze without any success (the vaccum take 65 minutes but nothing is > > fixed, still have the ERROR XXX001). I have this error in the backend > > application and when I do some Insert Query in the PgAdmin tool. > > > > This kind of error occurs generally on 7.x versions or if you are using > fsync =off and you have a surprised system down. > > Try to do a pg_dump of the database or clean (no delete) del wal files > (this is not > recommendable for new users). > > > What can I do to fix that problem? > > > > Thank you, > > > > > > -- > Emanuel Calvo Franco >Sumate al ARPUG ! > (www.postgres-arg.org - > www.arpug.com.ar) >ArPUG / AOSUG Member > Postgresql Support & Admin >
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
As far as I know we do not have the paying support. Once I will try everything suggested here, I will try to go in that direction. For the moment, I think all your suggestions might solve the problem. I just need the "ok" from the IT to start applying some of your suggestions guys. On Wed, Apr 1, 2009 at 12:59 PM, Scott Mead wrote: > If you are using the EnterpriseDB release, and paying for support, you can > always take this to them. > > Sent from my mobile device > > ------ > *From*: Patrick Desjardins > *Date*: Wed, 1 Apr 2009 11:53:59 -0400 > *To*: Emanuel Calvo Franco > *Subject*: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 > 4 of relation… > We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup > because weeks have pass since the first error occurs. > > I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump > like you suggest. > > On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco < > postgres@gmail.com> wrote: > >> 2009/3/31 Patrick Desjardins : >> > Hi, >> > >> > I have the error ERROR: XX001: could not read block 2354 of relation >> > 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when >> trying >> > to Insert data into a table. I would say that 99% of Insert works and >> 100% >> > of read works. This is only happenning since few weeks. I have done >> Vaccum >> > Analyze without any success (the vaccum take 65 minutes but nothing is >> > fixed, still have the ERROR XXX001). I have this error in the backend >> > application and when I do some Insert Query in the PgAdmin tool. >> > >> >> This kind of error occurs generally on 7.x versions or if you are using >> fsync =off and you have a surprised system down. >> >> Try to do a pg_dump of the database or clean (no delete) del wal files >> (this is not >> recommendable for new users). >> >> > What can I do to fix that problem? >> > >> > Thank you, >> > >> >> >> >> -- >> Emanuel Calvo Franco >>Sumate al ARPUG ! >> (www.postgres-arg.org - >> www.arpug.com.ar) >>ArPUG / AOSUG Member >> Postgresql Support & Admin >> > >
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
Humm, they want to close the AntiVirus for 1 night not anymore. Do you think that if we take out the database directory of the scan that it will solve the problem or it really need to have no antivirus on the server? They really want to keep it... On Wed, Apr 1, 2009 at 1:06 PM, Scott Marlowe wrote: > On Wed, Apr 1, 2009 at 10:32 AM, Patrick Desjardins > wrote: > > I have not reindexes. I will try to use the Reindex command > > (http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I > get > > error I will try to drop them. If it doesn't solve I will pg_dump. I > still > > need to wait the IT to remove the Anti-virus. Will give you more news > later. > > You may be tilting at windmills until they do. I'd go stand behind > somebody in IT until they came and fixed it. Seriously. >
