Daten aus korrupten Tabellen in PostgreSQL^® retten

PostgreSQL^® ist eine äußerst robuste Datenbank, der auch die meisten unserer Kunden ihre Daten anvertrauen. Kommt es jedoch trotzdem einmal zu Fehlern, so liegen diese meistens am Storage-System, in dem einzelne Bits oder Bytes kippen, oder ganze Blöcke verfälscht werden. Wir zeigen, wie man die Daten aus korrupten Tabellen retten kann.

Der Benutzer ist im Fehlerfall mit Meldungen konfrontiert, die aus dem Storagelayer oder anderen PostgreSQL^®-Subsystemen kommen:

postgres=# select * from t;
ERROR:  missing chunk number 0 for toast value 192436 in pg_toast_192430

postgres=# select * from a;
ERROR:  invalid memory alloc request size 18446744073709551613

Wenn nur einzelne Tupel defekt sind, kann man sich teilweise behelfen, indem man diese einzeln ausliest, z.B. nach id, was in vielen Fällen jedoch auch nicht weiter hilft:

select * from t where id = 1;

Erfolgversprechender ist, die Tupel direkt mit ihrer internen Tupel-ID, in PostgreSQL^® ctid genannt, anzusprechen:

select * from t where ctid = '(0,1)';

Um nun alle Tupel auszulesen, nutzen wir eine Schleife in plpgsql:

for page in 0 .. pages-1 loop
  for item in 1 .. ??? loop
     select * from t where ctid = '('||page||','||item||')' into r;
     return next r;
  end loop;
end loop;

Wir brauchen noch die Zahl der Seiten in der Tabelle, die bekommen wir von pg_relation_size(), und die Zahl der Tupel auf der Seite, wofür wir die Extension pageinspect bemühen.

select pg_relation_size(relname) / current_setting('block_size')::int into pages;

for page in 0 .. pages-1 loop
  for item in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
    SELECT * FROM t WHERE ctid=item into r;
    if r is not null then
      return next r;
    end if;
  end loop;
end loop;

Jetzt kommt der wichtigste Teil: Der Zugriff auf die beschädigten Tupel oder Seiten verursacht Fehler, die wir mit einem begin..exception..end-Block abfangen müssen. Die Fehlermeldungen geben wir als NOTICE an den Benutzer weiter. Außerdem soll die Funktion nicht nur für eine Tabelle funktionieren, sondern einen Parameter relname erhalten. Die gesamte plpgsql-Funktion sieht dann so aus:

create extension pageinspect;

create or replace function read_table(relname regclass)
returns setof record
as $$
declare
  pages int;
  page int;
  ctid tid;
  r record;
  sql_state text;
  error text;
begin
  select pg_relation_size(relname) / current_setting('block_size')::int into pages;

  for page in 0 .. pages-1 loop

    begin

      for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
        begin
          execute format('SELECT * FROM %s WHERE ctid=%L', relname, ctid) into r;
          if r is not null then
            return next r;
          end if;
        exception -- bad tuple
          when others then
            get stacked diagnostics sql_state := RETURNED_SQLSTATE;
            get stacked diagnostics error := MESSAGE_TEXT;
            raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
        end;
      end loop;

    exception -- bad page
      when others then
        get stacked diagnostics sql_state := RETURNED_SQLSTATE;
        get stacked diagnostics error := MESSAGE_TEXT;
        raise notice 'Skipping page %: %: %', page, sql_state, error;
    end;

  end loop;
end;
$$ language plpgsql;

Da die Funktion „record“ zurück gibt, muss beim Aufruf die Tabellensignatur mitgegeben werden:

postgres =# select * from read_table('t') as t(t text);
NOTICE:  Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
       t
───────────────
 one
 two
 three
...

Eine alternative Variante schreibt die gelesenen Daten direkt in eine neue Tabelle:

postgres =# select rescue_table('t');
NOTICE:  t: page 0 of 1
NOTICE:  Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
                                    rescue_table
─────────────────────────────────────────────────────────────────────────────────────
 rescue_table t into t_rescue: 0 of 1 pages are bad, 1 bad tuples, 100 tuples copied
(1 row)

Die Tabelle t_rescue wurde automatisch angelegt.

create extension pageinspect;

create or replace function rescue_table(relname regclass, savename name default null, "create" boolean default true)
returns text
as $$
declare
  pages int;
  page int;
  ctid tid;
  row_count bigint;
  good_tuples bigint := 0;
  bad_pages bigint := 0;
  bad_tuples bigint := 0;
  sql_state text;
  error text;
begin
  if savename is null then
    savename := relname || '_rescue';
  end if;
  if rescue_table.create then
    execute format('CREATE TABLE %s (LIKE %s)', savename, relname);
  end if;

  select pg_relation_size(relname) / current_setting('block_size')::int into pages;

  for page in 0 .. pages-1 loop
    if page % 10000 = 0 then
      raise notice '%: page % of %', relname, page, pages;
    end if;

    begin

      for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
        begin
          execute format('INSERT INTO %s SELECT * FROM %s WHERE ctid=%L', savename, relname, ctid);
          get diagnostics row_count = ROW_COUNT;
          good_tuples := good_tuples + row_count;
        exception -- bad tuple
          when others then
            get stacked diagnostics sql_state := RETURNED_SQLSTATE;
            get stacked diagnostics error := MESSAGE_TEXT;
            raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
            bad_tuples := bad_tuples + 1;
        end;
      end loop;

    exception -- bad page
      when others then
        get stacked diagnostics sql_state := RETURNED_SQLSTATE;
        get stacked diagnostics error := MESSAGE_TEXT;
        raise notice 'Skipping page %: %: %', page, sql_state, error;
        bad_pages := bad_pages + 1;
    end;

  end loop;

  error := format('rescue_table %s into %s: %s of %s pages are bad, %s bad tuples, %s tuples copied',
    relname, savename, bad_pages, pages, bad_tuples, good_tuples);
  raise log '%', error;
  return error;
end;
$$ language plpgsql;

Die SQL-Skripte sind auch im git-Repository von pg_dirtyread verfügbar.

Unterstützung

Falls Sie Unterstützung bei der Rettung Ihrer Daten oder dem allgemeinen Einsatz von PostgreSQL^® benötigen, steht Ihnen unser PostgreSQL^® Competence Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns auf Ihre Kontaktaufnahme.