Thread: pgsql error

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