public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios - cloud <[email protected]>
To: [email protected]
Subject: Re: Hi Corruption in Postgres
Date: Fri, 8 Nov 2024 16:04:07 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM6PR03MB55589D8CDAA42888E971C1D7A25C2@AM6PR03MB5558.eurprd03.prod.outlook.com>
References: <AM6PR03MB55589D8CDAA42888E971C1D7A25C2@AM6PR03MB5558.eurprd03.prod.outlook.com>


On 11/7/24 19:06, Pär Mattsson wrote:
>
> 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Hi Corruption in Postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox