FAQ
Hi all,

I use 8.2 on a windows server 2008.
Suddenly postgres crashed and I can not do anything.
Strange things happend to postgres in the last few weeks. Once, there
was so many postgres process, that I could not connect to it with
pgAdmin3. It said that too many connections and I had to restart
postgres. Than, after a while the postgres service stopped but some
process ran and it was still accsessable so the application which used
it ran without any problem and I could simply start postgres again.

Now something is wrong, I can not make backup and the vacuum stops too.
The message is
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

The only one thing I found to correct this is to create a file filled
with binary 0 and replace clog/0003. Both files size was the same but
I get that errormessage again.

I tried even with a bigger empty clog/0003 file but 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 tried to drop the last few transaction with pg_resetxlog and hoped
to save some data but there is again the original error message.

I checked the permissions of the whole data dir. The owner of it is
postgres and has full permission.


Can anyone suggest something?
Many data would be lost if I can not repaire that so please!

Thanks,
fenor

Search Discussions

  • Robert Haas at Jun 17, 2010 at 12:51 pm

    On Thu, Jun 17, 2010 at 4:39 AM, Felde Norbert wrote:
    I use 8.2 on a windows server 2008.
    Suddenly postgres crashed and I can not do anything.
    Strange things happend to postgres in the last few weeks. Once, there
    was so many postgres process, that I could not connect to it with
    pgAdmin3. It said that too many connections and I had to restart
    postgres. Than, after a while the postgres service stopped but some
    process ran and it was still accsessable so the application which used
    it ran without any problem and I could simply start postgres again.

    Now something is wrong, I can not make backup and the vacuum stops too.
    The message is
    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

    The only one thing I found to correct this is to create a file filled
    with binary 0 and replace clog/0003. Both files size was the same but
    I get that errormessage again.

    I tried even with a bigger empty clog/0003 file but 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 tried to drop the last few transaction with pg_resetxlog and hoped
    to save some data but there is again the original error message.

    I checked the permissions of the whole data dir. The owner of it is
    postgres and has full permission.


    Can anyone suggest something?
    Many data would be lost if I can not repaire that so please!
    First, I'd suggest you make a copy of the database before you do anything else.

    Second, is it possible your disk filled up at some point during all of this?

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Florian Pflug at Jun 17, 2010 at 1:12 pm

    On Jun 17, 2010, at 10:39 , Felde Norbert wrote:
    I tried even with a bigger empty clog/0003 file but 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
    Hm, you could try to make the clog/0003 file 256kB. Thats the maximum size of clog segments.

    best regards,
    Florian Pflug

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 17, '10 at 8:39a
activeJun 17, '10 at 1:12p
posts3
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase