Re: Hi Corruption in Postgres

Har någon typ av korruptionsproblematik sen 1/10 på lfv produktion : 

pg_dump: error: Dumping the contents of table "aim_notam_originalpart" failed: PQgetResult() failed.
pg_dump: error: Error message from server: ERROR:  unexpected chunk size 1408 (expected 1661) in chunk 0 of 1 for toast value 18918695 in pg_toast_18194
pg_dump: error: The command was: COPY aim.aim_notam_originalpart (notam_id, part_num, created_at, e_details, canonical_filing_dtg, canonical_orig_addr, canonical_text) TO stdout;
aimdb_dump: pg_dump failed

Your DB is corrupted. Either you have valid backup , or you try to rectify as is.

In this case, search the net / write a pgplsql func to pinpoint the corrupted toast row. Then zero it out.

This could be handy :

CREATE OR REPLACE FUNCTION find_bad_row(text,text,text) RETURNS VOID AS $f$

declare

tblname ALIAS FOR $1;

pkname ALIAS FOR $2;

culprit ALIAS FOR $3;

curid INT := 0;

vcontent TEXT;

badid INT;

begin

FOR badid IN EXECUTE 'SELECT '||pkname||' FROM '||tblname||' ORDER BY '||pkname LOOP

curid = curid + 1;

-- raise notice 'inspecting row %', curid;

if curid % 100000 = 0 then

     raise notice '% rows inspected', curid;

end if;

begin

     EXECUTE 'SELECT '||culprit||' FROM '||tblname||' where '||pkname||' = '|| badid INTO vcontent;

     vcontent := substr(vcontent,1,1000);

exception

     when others then

         raise notice 'data for table %,  pk=% is corrupt', tblname,badid;

         continue;

end;

end loop;

end;

$f$ LANGUAGE plpgsql;

1500000 rows

Mvh Pär