Thread: DB crash SOS
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
On Thu, Jun 17, 2010 at 4:39 AM, Felde Norbert <fenor77@gmail.com> 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
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