Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t9QX3-00A5Qn-Uy for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2024 15:04:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t9QX0-00AEGd-SZ for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2024 15:04:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t9QX0-00AEGV-8r for pgsql-admin@lists.postgresql.org; Fri, 08 Nov 2024 15:04:15 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t9QWx-000p05-TW for pgsql-admin@lists.postgresql.org; Fri, 08 Nov 2024 15:04:13 +0000 Content-Type: multipart/alternative; boundary="------------aUxGdTN0c0W6aChDjd0e7B8q" Message-ID: <0bad110b-30b5-4782-b3a4-0042c8002985@cloud.gatewaynet.com> Date: Fri, 8 Nov 2024 16:04:07 +0100 MIME-Version: 1.0 Subject: Re: Hi Corruption in Postgres To: pgsql-admin@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------aUxGdTN0c0W6aChDjd0e7B8q Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------aUxGdTN0c0W6aChDjd0e7B8q Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


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
--------------aUxGdTN0c0W6aChDjd0e7B8q--