Пример восстановления таблиц PostgreSQL с помощью новой мега фичи pg_filedump



    Позвольте я расскажу вам об одной классной фиче, которую мы с коллегами из Postgres Pro недавно запилили в утилите pg_filedump. Фича эта позволяет частично восстанавливать данные из базы, даже в случае, если база была сильно повреждена и инстанс PostgreSQL с такой базой уже не запустишь. Конечно, хочется верить, что потребность в таком функционале возникает крайне редко. Но на всякий случай нечто подобное хотелось бы иметь под рукой. Читайте дальше, и вы узнаете, как данная фича выглядит в действии.

    Частичное восстановление данных было представлено в коммите 52fa0201:

    commit 52fa0201f97808d518c64bcb9696f2a350678aa5
    Author: Teodor Sigaev <teodor@sigaev.ru>
    Date: Tue Jan 17 16:01:12 2017 +0300

    Partial data recovery (-D flag).

    This feature allows to partially recover data from a given segment file
    in format suitable for using in COPY FROM statement. List of supported
    data types is currently not full and TOAST is not yet supported, but
    it's better than nothing. Hopefully data recovery will be improved in
    the future.

    Implemented by Aleksander Alekseev, reviewed by Dmitry Ivanov, tested
    by Dmitry Ivanov and Grigoriy Smolkin.


    Допустим, есть какая-то таблица:

    create table tt (x int, y bool, z text, w timestamp);

    … заполненная какими-то данными:

    insert into tt values(123, true, 'Text test test', now());
    insert into tt values(456, null, 'Ололо трооло', null);
    checkpoint;

    Здесь я говорю checkpoint, чтобы данные обязательно попали на диск. Иначе они попадут в WAL, но buffer manager будет держать их в памяти, пока таплы (tuple, кортеж, строка в таблице) не будут вытеснены более новыми и/или часто используемыми таплами. Или чекпоинтом по таймауту/накоплению max_wal. Думаю, это самый частый сценарий для синка страницы на диск. — прим. Стаса Кельвича.

    Также узнаем имя сегмента, соответствующего таблице:

    select relfilenode from pg_class where relname = 'tt';

    В моем случае relfilenode у таблицы был 16393. Найдем этот сегмент (или сегменты, если таблица больше 1 Гб) на диске:

    find /path/to/db/ -type f | grep 16393

    Скопируем его куда-нибудь и представим, что нам хочется восстановить данные, имея на руках только файл сегмента.

    Для этого соберем последнюю версию pg_filedump:

    git clone git://git.postgresql.org/git/pg_filedump.git
    cd pg_filedump
    make
    

    Обычно схема базы известна, так как на руках есть приложение, которое с ней работает. Значит, мы знаем типы столбцов в таблице и можем декодировать их таким образом:

    ./pg_filedump -D int,bool,text,timestamp /path/to/db/base/16384/16393

    Пример вывода:

    *******************************************************************
    * PostgreSQL File/Block Formatted Dump Utility - Version 9.6.0
    *
    * File: /home/eax/work/postgrespro/postgresql-install/data-master/base/16384/16393
    * Options used: -D int,bool,text,timestamp
    *
    * Dump created on: Tue Jan 17 16:28:07 2017
    *******************************************************************
    
    Block    0 ********************************************************
    <Header> -----
     Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
     Block: Size 8192  Version    4            Upper    8080 (0x1f90)
     LSN:  logid      0 recoff 0x0301e4c0      Special  8192 (0x2000)
     Items:    2                      Free Space: 8048
     Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
     Length (including item array): 32
    
    <Data> ------
     Item   1 -- Length:   56  Offset: 8136 (0x1fc8)  Flags: NORMAL
    COPY: 123   t   Text test test  2017-01-17 16:25:03.448488
     Item   2 -- Length:   52  Offset: 8080 (0x1f90)  Flags: NORMAL
    COPY: 456   \N  Ололо трооло    \N
    
    *** End of File Encountered. Last Block Read: 0 ***
    

    Тут довольно много данных, так как pg_filedump выводит информацию о каждой странице в сегменте и декодирует заголовок каждого тапла. К счастью, можно довольно просто отделить мух от котлет, например, так:

    pg_fiedump -D ...как..раньше... | grep COPY | perl -lne 's/^COPY: //g; print;' > /tmp/copy.txt
    cat /tmp/copy.txt

    Содержимое файла copy.txt:

    123 t   Text test test  2017-01-17 16:25:03.448488
    456 \N  Ололо трооло    \N

    Это данные нашей таблицы в формате, пригодном для использования в запросе COPY FROM. Проверяем:

    create table tt2 (x int, y bool, z text, w timestamp);
    copy tt2 from '/tmp/copy.txt';
    select * from tt2;

    Результат:

      x  | y |       z        |             w
    -----+---+----------------+----------------------------
     123 | t | Text test test | 2017-01-17 16:25:03.448488
     456 |   | Ололо трооло   |
    (2 rows)

    Как видите, все данные успешно восстановлены.

    Естественно, это был несколько упрощенный пример и на практике все сложнее. Во-первых, список поддерживаемых типов на данный момент несколько ограничен:

    static ParseCallbackTableItem callback_table[] = {
        { "smallserial", &decode_smallint },
        { "smallint", &decode_smallint },
        { "int", &decode_int },
        { "serial", &decode_int },
        { "bigint", &decode_bigint },
        { "bigserial", &decode_bigint },
        { "time", &decode_time },
        { "timetz", &decode_timetz },
        { "date", &decode_date },
        { "timestamp", &decode_timestamp },
        { "float4", &decode_float4 },
        { "float8", &decode_float8 },
        { "float", &decode_float8 },
        { "bool", &decode_bool },
        { "uuid", &decode_uuid },
        { "macaddr", &decode_macaddr },
    
        /* internally all string types are stored the same way */
        { "char", &decode_string },
        { "varchar", &decode_string },
        { "text", &decode_string },
        { "json", &decode_string },
        { "xml", &decode_string },
        { NULL, NULL},
    };

    Во-вторых, TOAST сейчас не поддерживается. Если строка хранится в несжатом виде или сжата на странице in-place, pg_filedump ее успешно восстановит (если сжатые данные не были испорчены). Однако если строка была перенесена во внешнюю TOAST-таблицу, вместо строки вы получите просто "(TOASTED)". В принципе, поддержка TOAST — не нерешаемая задача. Нужно только научить pg_filedump парсить каталог и находить соответствующую TOAST-таблицу. Просто пока что этого никто не сделал. Возможно, поддержка TOAST будет добавлена в будущих версиях pg_filedump.

    Наконец, на практике схема базы данных иногда меняется, столбцы в таблице появляются и исчезают. Удаление столбцов — не такая уж большая проблема, так как физически в тапле этот столбец остается, просто он всегда равен null. Вот с добавлением чуть сложнее, так как из-за него таплы в рамках одной таблицы могут иметь переменное число атрибутов. Если число атрибутов в тапле не соответствует количеству атрибутов, указанных пользователем, pg_filedump просто показывает предупреждение с частично декодированными данными, и переходит к следующему таплу. Это значит, что на практике парсинг вывода pg_filedump будет чуть сложнее, ну или что вам придется прогнать его несколько раз с разными списками атрибутов.

    На мой взгляд, да и не только мой, как крайнее средство восстановление данных, лучше иметь хотя бы такое, чем не иметь никакого :) Если у вас есть идеи по дальнейшему улучшению представленного функционала, да и вообще любые замечания и дополнения, мне будет крайне интересно ознакомиться с ними в комментариях!

    Также вас могут заинтересовать статьи:


     

    Продолжение — Еще одна новая фича pg_filedump: восстанавливаем каталог PostgreSQL.
    • +45
    • 9,3k
    • 4
    Postgres Professional 112,40
    Российский вендор PostgreSQL
    Поделиться публикацией
    Комментарии 4
    • +3
      Спасибо, добавлю в заметки, надеюсь никогда не пригодится.
      • +1

        Однажды попал именно на toast( Хорошо, резервные копии были. Надеюсь, его поддержка появится в будущих версиях) PS: А если схема совсем неизвестна? Я про базы 1С

        • 0
          Ну как же она неизвестна? Вы же можете сделать тестовую инсталляцию и сказать \d, правда?
          • 0

            Делать это до сбоя смысла ещё нет, а после сбоя — уже нет) Ведь так?

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое