Пример восстановления таблиц 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

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 будет чуть сложнее, ну или что вам придется прогнать его несколько раз с разными списками атрибутов.

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

Автор: Postgres Professional

Источник