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

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

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

• Становимся контрибьютером в PostgreSQL;
• Потоковая репликация в PostgreSQL и пример фейловера;