Обсуждение: pgsql error
Hello all,
I'm new to pgsql and I'm taking over for a project manager that left the company.
I'm receiving the following error…
CONTEXT: writing block 614 of relation 394198/412175
WARNING: could not write block 614 of 394198/412175
DETAIL: Multiple failures --- write error may be permanent.
ERROR: xlog flush request 0/34D53680 is not satisfied --- flushed only to 0/34CD1EB0
Is there anyone who has seen this and can help me.
Thank you.
John W. McLeod
2011/7/25 Mcleod, John <johnm@spicergroup.com>: > Hello all, > > I'm new to pgsql and I'm taking over for a project manager that left the > company. > > I'm receiving the following error… > > > > CONTEXT: writing block 614 of relation 394198/412175 > > WARNING: could not write block 614 of 394198/412175 > > DETAIL: Multiple failures --- write error may be permanent. Maybe you have disk full ? > > ERROR: xlog flush request 0/34D53680 is not satisfied --- flushed only to > 0/34CD1EB0 > > > > Is there anyone who has seen this and can help me. > > > > Thank you. > > > > John W. McLeod > > > > -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On Mon, Jul 25, 2011 at 3:05 PM, Mcleod, John <johnm@spicergroup.com> wrote: > Hello all, > > I'm new to pgsql and I'm taking over for a project manager that left the > company. > > I'm receiving the following error… > > > > CONTEXT: writing block 614 of relation 394198/412175 > > WARNING: could not write block 614 of 394198/412175 > > DETAIL: Multiple failures --- write error may be permanent. > > ERROR: xlog flush request 0/34D53680 is not satisfied --- flushed only to > 0/34CD1EB0 This is a fairly low level error that is telling you the WAL could not be written out. Out of drive space? Data corruption? merlin
Merlin Moncure <mmoncure@gmail.com> writes: > On Mon, Jul 25, 2011 at 3:05 PM, Mcleod, John <johnm@spicergroup.com> wrote: >> I'm receiving the following error >> CONTEXT: writing block 614 of relation 394198/412175 >> WARNING: could not write block 614 of 394198/412175 >> DETAIL: Multiple failures --- write error may be permanent. >> ERROR: xlog flush request 0/34D53680 is not satisfied --- flushed only to >> 0/34CD1EB0 > This is a fairly low level error that is telling you the WAL could not > be written out. Out of drive space? Data corruption? Yeah, this looks like the detritus of some previous failure. There are basically two possibilities: 1. The problem page's LSN field has gotten trashed so that it appears to be past the end of WAL. 2. The page actually did get updated by a WAL entry with that LSN, and then there was a crash for some reason, and the database tried to recover by replaying WAL, and it hit some problem that caused it to stop recovering before what had really been the end of WAL. So now it thinks the end of WAL is 0/34CD1EB0, but there are page(s) out there with LSNs past that, and when it finds one you start getting complaints like this. I doubt theory #1, though, because there are nearby fields in a page header that evidently weren't trashed or else the page would have been recognized as being corrupt. Also the reported LSN is not very far past end of WAL, which would be unlikely in the event of random corruption. So I'm betting on #2. Unfortunately this tells us little about either the cause of the original crash, or the reason why recovery didn't work properly. We'd need a lot more information before speculating about that, for starters the exact Postgres version and the platform it's running on. regards, tom lane
Thank you for the reply. At command line, I ran... "psql --version" and received.. "psql (PostgreSQL) 7.5devel" The database is sitting on a Windows 2003 Server box. A mapping application, wrote in PHP, runs with Apache 2.05 I know in the past, the project manager would restart the database by just closing the .bat window, then restart by double-clickingthe postgis.bat file on the desktop. I'm not sure if this was the beginning of the problem. I've learned to shutdown the database by "Ctrl C". This batch file has the following... cd c:\ cd ms4w/apps/pgsql75win/data/ del postmaster.pid @ECHO OFF set PATH=%PATH%; \ms4w\apps\pgsql75win\lib;\ms4w\apps\pgsql75win\bin;\ms4w\apps\pgsql75win\share\contrib cd c:\ cd ms4w/apps/pgsql75win/ cmd /c "postmaster -D \ms4w\apps\pgsql75win\\data" I hope this will give you some clues. John -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, July 25, 2011 11:20 PM To: Merlin Moncure Cc: Mcleod, John; pgsql-general@postgresql.org Subject: Re: [GENERAL] pgsql error Merlin Moncure <mmoncure@gmail.com> writes: > On Mon, Jul 25, 2011 at 3:05 PM, Mcleod, John <johnm@spicergroup.com> wrote: >> I'm receiving the following error >> CONTEXT: writing block 614 of relation 394198/412175 >> WARNING: could not write block 614 of 394198/412175 >> DETAIL: Multiple failures --- write error may be permanent. >> ERROR: xlog flush request 0/34D53680 is not satisfied --- flushed >> only to >> 0/34CD1EB0 > This is a fairly low level error that is telling you the WAL could not > be written out. Out of drive space? Data corruption? Yeah, this looks like the detritus of some previous failure. There are basically two possibilities: 1. The problem page's LSN field has gotten trashed so that it appears to be past the end of WAL. 2. The page actually did get updated by a WAL entry with that LSN, and then there was a crash for some reason, and the databasetried to recover by replaying WAL, and it hit some problem that caused it to stop recovering before what had reallybeen the end of WAL. So now it thinks the end of WAL is 0/34CD1EB0, but there are page(s) out there with LSNs pastthat, and when it finds one you start getting complaints like this. I doubt theory #1, though, because there are nearby fields in a page header that evidently weren't trashed or else the pagewould have been recognized as being corrupt. Also the reported LSN is not very far past end of WAL, which would be unlikelyin the event of random corruption. So I'm betting on #2. Unfortunately this tells us little about either the cause of the original crash, or the reason why recovery didn't work properly. We'd need a lot more information before speculating about that, for starters the exact Postgres version and theplatform it's running on. regards, tom lane
On Tue, Jul 26, 2011 at 7:47 AM, Mcleod, John <johnm@spicergroup.com> wrote: > Thank you for the reply. > > At command line, I ran... > "psql --version" > and received.. > "psql (PostgreSQL) 7.5devel" > > The database is sitting on a Windows 2003 Server box. > A mapping application, wrote in PHP, runs with Apache 2.05 ok -- first you reported the version # of the client, not the back end. let's hope they are different. you are running an early development build of postgres 8.0 -- I know this because about 3/4 the way through the development cycle the version # changed from 7.5 to 8.0. This is essentially a completely unpactched, riddled with known bugs, alpha build. Prevailing wisdom is that the earliest good build to use on windows is 8.2 or maybe 8.3. Right now 100% of your energy should be devoted to double checking the version# against the backend (postgres -- version) and, if it is also 7.5 devel, capturing a good backup if you don't have one already and upgrading ASAP. merlin
"Mcleod, John" <johnm@spicergroup.com> writes: > Thank you for the reply. > At command line, I ran... > "psql --version" > and received.. > "psql (PostgreSQL) 7.5devel" Egad. That is an early development snapshot of what eventually got called the 8.0 release. You should try "select version();" in psql to verify that the server itself is the same version, but I'm betting it is. What you've got there is a development snapshot from perhaps mid-2004, and even if it were a supported release, we dropped support for it a couple years ago. > The database is sitting on a Windows 2003 Server box. Even worse. 8.0 was the first release that had native Windows support (so that probably explains why your predecessor tried to use it pre-release). The number and extent of the bugs in that is, well, remarkable. Given this information, what's remarkable is not that your DB got corrupted but that it survived this long without that. I think your best bet is (1) pg_dump as much data as you can, (2) reinstall a reasonably recent, supported PG version, (3) reload the dump. > I know in the past, the project manager would restart the database by just closing the .bat window, then restart by double-clickingthe postgis.bat file on the desktop. > I'm not sure if this was the beginning of the problem. Sure didn't help any ... in principle the DB ought to withstand that, but it's not a clean shutdown; and in the case of early Windows versions in particular I'm not sure we understood how to do fsyncs correctly on that platform. I'm not a Windows person myself, but I believe the recent EDB packagings of Postgres offer a much cleaner user interface than that. regards, tom lane
When I go to "C:\ms4w\apps\pgsql75win\bin" in the command line, and run "postgres --version", I get the following... "postgres (PostgreSQL) 7.5devel" John -----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: Tuesday, July 26, 2011 9:40 AM To: Mcleod, John Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pgsql error On Tue, Jul 26, 2011 at 7:47 AM, Mcleod, John <johnm@spicergroup.com> wrote: > Thank you for the reply. > > At command line, I ran... > "psql --version" > and received.. > "psql (PostgreSQL) 7.5devel" > > The database is sitting on a Windows 2003 Server box. > A mapping application, wrote in PHP, runs with Apache 2.05 ok -- first you reported the version # of the client, not the back end. let's hope they are different. you are runningan early development build of postgres 8.0 -- I know this because about 3/4 the way through the development cyclethe version # changed from 7.5 to 8.0. This is essentially a completely unpactched, riddled with known bugs, alphabuild. Prevailing wisdom is that the earliest good build to use on windows is 8.2 or maybe 8.3. Right now 100% of your energy should be devoted to double checking the version# against the backend (postgres -- version)and, if it is also 7.5 devel, capturing a good backup if you don't have one already and upgrading ASAP. merlin