Re: postgres crash SOS

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Thursday, June 17, 2010 11:54 AM
> To: Felde Norbert
> Cc: pgsql-general(at)postgresql(dot)org; Scott Marlowe
> Subject: Re: [GENERAL] postgres crash SOS
>
> Felde Norbert <fenor77(at)gmail(dot)com> writes:
> > The message is the same for the original pg_clog/0003, the 0003
> > containing binary 0 and after pg_resetxlog:
> > pg_dump: Error message from server: ERROR: could not access status
> of
> > transaction 3974799
> > DETAIL: Could not read from file "pg_clog/0003" at offset 204800: No
> error.
> > pg_dump: The command was: COPY public.active_sessions_split (ct_sid,
> > ct_name, ct_pos, ct_val, ct_changed) TO stdout;
> > pg_dump: *** aborted because of error
>
> > If create the bigger 0003 containing 0 than I get that:
> > pg_dump: Error message from server: ERROR: xlog flush request
> > 0/A19F5BF8 is not satisfied --- flushed only to 0/A02A1AC8
> > CONTEXT: writing block 1149 of relation 1663/4192208/4192508
> > pg_dump: The command was: COPY public.history (historyid, adatkod,
> > elemid, userid, ido, actionid, targyid, szuloid, opvalue, longfield,
> > longtext) TO stdout;
> > pg_dump: *** aborted because of error
>
> I'm afraid this means you're screwed :-(. Both of those symptoms imply
> that one part of the database storage is out of sync with another part:
> the first error says there are transaction IDs in the
> active_sessions_split table that don't exist in pg_clog, and the second
> error says that there are pages in the history table that were last
> updated by WAL records that don't exist in pg_xlog. If there are two
> such errors, there are probably more.
>
> You weren't too specific about how you got into this state, but I
> suppose that it must have been a system crash or power failure. Even
> then, you would not have gotten burnt if the filesystem and hardware
> did what they're supposed to do. I suspect you have a setup wherein
> fsync() calls aren't being honored properly. You may need to disable
> write caching on your disks, and/or switch to another filesystem or OS.
> (Personally I'd never run a database I cared about on Windows.)

Somehow, I doubt that Windows is to blame. For instance, Oracle and SQL*Server seem to run fine on Windows without this sort of problem.