Hi,
On 2020-03-15 19:23:49 +0100, Karsten Hilbert wrote:
> > /home/marc# pg_dump -p 5432 --username=gm-dbo --dbname=gnumed_v22 --compress=0 --no-sync --format=custom
--file=/dev/null
> > pg_dump: Ausgabe des Inhalts der Tabelle »doc_obj« fehlgeschlagen: PQgetResult() fehlgeschlagen.
> > pg_dump: Fehlermeldung vom Server: ERROR: unexpected chunk number 2 (expected 0) for toast value 99027 in
pg_toast_18536
> > pg_dump: Die Anweisung war: COPY blobs.doc_obj (pk, fk_doc, seq_idx, comment, fk_intended_reviewer, data, filename)
TOstdout;
>
> (to note: column "data" is of type BYTEA)
>
> We have been able to identify the row (there may be more)
> in blobs.doc_obj which leads to the above error.
>
> blobs.doc_obj.pk -> 82224
>
> We have ruled out (?) below-PG hardware problems by a
> successful run of:
>
> cp -rv —preserve=all /var/lib/postgresql/9.6 /tmp/
FWIW, I don't think that rules out hardware problems at all. In plenty
cases of corruption you can just end up with corrupted on-disk data
(swapped blocks, zeroed blocks, randomly different values ...).
But obviously it is not at all guaranteed that is the case. Could you
describe the "history" of the database? Replication set up, failovers,
etc?
> Now, what else can we try to address the problem short of
> doing the
>
> pg_dump --exclude-table-data=blobs.doc_obj
>
> judicious use of COPY-FROM-with-subselect from blobs.doc_obj
>
> restore
>
> dance ?
A plpgsql function that returns the rows one-by-one and catches the
exception is probably your best bet.
It could roughly look something like:
CREATE OR REPLACE FUNCTION salvage(p_tblname regclass)
RETURNS SETOF text
LANGUAGE plpgsql AS
$$
DECLARE
v_row record;
BEGIN
FOR v_row IN EXECUTE 'SELECT * FROM '||p_tblname::text LOOP
BEGIN
-- this forces detoasting
RETURN NEXT v_row::text;
EXCEPTION WHEN internal_error OR data_corrupted OR index_corrupted THEN
-- add pkey or something else
RAISE NOTICE 'failed to return data';
END;
END LOOP;
END
$$
should work. You can call it like
SELECT (salvaged_rec.rec).* FROM (SELECT salvaged_text::salvage_me FROM salvage('salvage_me') AS salvaged_text) AS
salvaged_rec(rec)
Greetings,
Andres Freund