public inbox for [email protected]
help / color / mirror / Atom feedHi Corruption in Postgres
2+ messages / 2 participants
[nested] [flat]
* Hi Corruption in Postgres
@ 2024-11-07 18:06 Pär Mattsson <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Pär Mattsson @ 2024-11-07 18:06 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
1500000 rows
Mvh Pär
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Hi Corruption in Postgres
@ 2024-11-08 15:04 Achilleas Mantzios - cloud <[email protected]>
parent: Pär Mattsson <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Achilleas Mantzios - cloud @ 2024-11-08 15:04 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-08 15:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-07 18:06 Hi Corruption in Postgres Pär Mattsson <[email protected]>
2024-11-08 15:04 ` Achilleas Mantzios - cloud <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox