Несколько раз в жизни сталкивался с ситуацией, когда «Постгрес» при попытке выполнить запрос, возвращает что-то вроде invalid page in block 460248 of relation base/16391/23665. Это всегда означает какую-то аппаратную проблему на системе хранения, где СУБД хранит свои данные.

Уже два раза я попадал в ситуацию, когда бакапа у клиента по каким-то причинам нет и надо сдампить то, что имеется, причём всегда на дворе всегда глубокая ночь, поэтому надо бы уже описать этот опыт, пока снова не забылся и не пришлось его в который раз восстанавливать спросонья.

Первым делом, разумеется надо отключить пользователей от базы данных, чтобы предотвратить её дальнейшее разрушение.

Второе, что надо сделать — посмотреть какая сущность у нас развалилась. Это можно сделать следующим запросом:

SELECT 23665::regclass::text;
    text
-------------
 some_items

Если это индекс, просто удаляем его и пытаемся снять полный дамп базы данных, записав какой индекс мы удалили. Подобные ошибки могут быть в других частях базы, поэтому повторяем до успеха.

Всё несколько хуже, есть речь идёт о таблице. Для начала надо определить строки с какими ID теперь недоступны и сдампить таблицу пока без них. Для этого я написал достаточно прямолинейную хранимую процедуру, которая пытается получить каждую строку, которая должна существовать и, если не удаётся, сообщает строка с каким ID недоступна.

CREATE OR REPLACE FUNCTION check_table(table_name TEXT) RETURNS VOID AS $$
DECLARE
    min_id INT;
    max_id INT;
    current_id INT;
BEGIN
    EXECUTE 'SELECT MIN(id), MAX(id) FROM ' || table_name INTO min_id, max_id;
    
    current_id := min_id;
    
    WHILE current_id <= max_id LOOP
        BEGIN
            EXECUTE 'SELECT * FROM ' || table_name || ' WHERE id = $1' USING current_id;
        EXCEPTION
            WHEN SQLSTATE 'XX001' THEN
                RAISE NOTICE 'Ошибка при обработке id: %', current_id;
        END;
        
        current_id := current_id + 1;
    END LOOP;
END
$$ LANGUAGE plpgsql;

Дальше таблицу надо сдампить, составив SELECT, исключающий сбойные участки. Я, обычно, делаю ряд запросов, соединённых через UNION ALL и подставляю его в COPY, предварительно выключив последовательное сканирование таблицы (SET enable_seqscan = OFF).

Если этого не сделать, «Постгрес» обязательно займётся сканированием таблицы, так как нужна она почти вся, а там у нас сбойные участки. Отключив этот метод сканирования, мы заставляем обращаться к таблице через первичный индекс, если он, конечно цел. Хуже, когда разрушен и он.

Я с такой ситуацией ещё не сталкивался, но если столкнусь, буду думать над проверкой строк через ctid.

В общем, должен получиться примерно такой запрос:

SET enable_seqscan = OFF;

COPY (
SELECT * FROM some_items WHERE id < 100500 UNION ALL
SELECT * FROM some_items WHERE id > 100500 AND id < 100800 UNION ALL
SELECT * FROM some_items WHERE id > 100800
) TO '/tmp/some_items-table.dump'

После того как мы определили и сдампили все сбойные таблицы, можно сдампить остальную базу данных следующей командой:

pg_dump --exclude-table-data=some_items -j10 -Fd -Z1 -f /dump_directory dbname

Как видно, мы дампим структуру, но не данные сбойной таблицы (ключ —exclude-table-data можно указать несколько раз), при этом параметр -Fd нужен, чтобы мы могли снимать дамп параллельно сразу несколькими процессами, -j задаёт количество этих процессов, ключ -f указывает директорий, куда будет записан бакап, а -Z1 регулирует степень сжатия, —с уровнем 1 снятие дампа идёт с максимальной скоростью.

Наконец, успех: основная база лежит единым куском, сбойные таблицы сняты отдельными дампами. Что ещё можно сделать?

В одном из случаев у меня были несколько реплик, которые развалились по-разному. Из них удалось наскрести недостающие куски. Их можно снять через тот же COPY с запросом SELECT и просто присоединить в конец дампа соответствующей таблицы — там текстовый формат.

Если реплики нет, можно посмотреть какие индексы есть на сбойной таблице. Не исключено, что критичные столбцы покрыты индексами, которые остались целыми.

Данные из индексов можно вытащить, сделав запрос так, чтобы он затрагивал только столбцы, которые есть в индексе. Если в интересующем нас индексе нет ключевого столбца, не беда, там есть технический столбец ctid, по которому потом можно соединить данные построчно.

Если строки восстановить не удалось, а на таблицу ссылаются другие таблицы, надо заполнить недостающее любым мусором, чтобы внешним ключам было куда ссылаться.

Итак, теперь у нас есть большой дамп почти всей базы данных и один или несколько дампов таблиц. Как же всё соединить на другом сервере? Последовательность следующая.

Для начала восстанавливаем основную базу данных, но без индексов и внешних ключей:

pg_restore -d dbname -Fd /dump_directory --section=pre-data --section=data -j10

Потом заливаем дампы отдельных таблиц, после чего доливаем индексы и внешние ключи:

pg_restore -d dbname -Fd /dump_directory --section=post-data -j10

Если всё сделано правильно, должна получиться консистентная база данных. Осталось разобраться с двумя вещами: создать удалённые ранее индексы, если таковые были и, если вы создавали мусорные строки, то надо понять как с ними быть дальше. Например, удалить их или заполнить значениями «данные утеряны».