postgres

Недавно у одного из клиентов развалилась часть таблицы в «Постгресе» — в логах видно, что при чтении не совпадают контрольные суммы. Ничего особо нового — за последние несколько лет встречал эту проблему несколько раз у разных клиентов и даже описал алгоритм восстановления базы из разных источников у себя на сайте.

Там у меня в заметке есть хранимая функция, которая сканирует таблицу и сообщает какие именно строки требуется восстановить. Но в этот раз она мне, как ни странно, не помогла — завершилась, не выдав ничего. Раньше она осечек не давала и я, не поверив себе, даже запустил её дважды.

Долго разбирался в чём дело, разобрался. Принцип работы моей функции такой — она вынимает каждую строку, если получает ошибку, то сообщает об этом. Но оказалось, что если какие-либо разрушенные данные лежат в «тостах» (это механизм автоматического сжатия и выноса больших значений полей в «Постгресе»), то этого недостаточно. Видимо «Постгрес» в реальности не достаёт эти данные, пока они не понадобятся для чего-либо и поэтому ошибки не возникает.

В итоге я переделал хранимку — передаю всё строку целиком в функцию ROW_TO_JSON, чтобы прочитались и значения из «тостов». Это помогло, функция стала работать как и задумывалось.

CREATE OR REPLACE FUNCTION check_table(table_name TEXT)
RETURNS void AS $$
DECLARE
    rec RECORD;
    row_data RECORD;
BEGIN
    FOR rec IN EXECUTE format('SELECT ctid::text AS ctid_str, id FROM %I', table_name)
    LOOP
        BEGIN
            EXECUTE format('SELECT * FROM %I WHERE ctid = %L::tid', table_name, rec.ctid_str)
            INTO STRICT row_data;

        row_data := ROW(ROW_TO_JSON(row_data)); /* to read the toasts */

        EXCEPTION WHEN others THEN
            RAISE WARNING 'CTID: %, ID: %, Error: %', rec.ctid_str, rec.id, SQLERRM;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Недавно моему сайту исполнилось 25 лет и я вспоминал свою первую заметку, с которой всё началось. Она положила начало традиции, которая длится столько, сколько существует этот сайт — писать «песню о пиве», это такая американская считалочка, на различных языках программирования.

Когда я пересматривал ту заметку, обратил внимание насколько сложно написана была «песня о пиве» на языке СУБД «Постгрес». Это был 2000-й год, седьмая версия, не исключено что тогда проще было и нельзя. Но прошедшие десятилетия «Постгрес» сильно изменился, хочу показать насколько проще можно сейчас написать то же самое:

(
    WITH phrase(ph) AS (
        SELECT COALESCE(NULLIF(beer, 0)::text, 'no') || ' bottle' ||
            CASE beer WHEN 1 THEN '' ELSE 's' END || ' of beer'
        FROM generate_series(99, 0, -1) AS c(beer)
    )
    SELECT ph || ' on the wall, ' || ph || '.' || e'\n' ||
        'Take one down and pass it around, ' ||
        LEAD(ph) OVER() || e' on the wall.\n' AS " "
    FROM phrase LIMIT 99
)
UNION ALL SELECT
'No more bottles of beer on the wall, no more bottles of beer.
Go to the store and buy some more, 99 bottles of beer on the wall.';

Несколько раз в жизни сталкивался с ситуацией, когда «Постгрес» при попытке выполнить запрос, возвращает что-то вроде 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

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

Я уже публиковал сегодня одну попытку написать «песню про пиво» на языке утилиты psql, но, к сожалению, результат получился не совсем честный — для математики и сравнения мне пришлось использовать вставку на эскуэеле.

Чисто интуитивно чувствовалось, что какое-то подобие арифметики можно сделать безо всякого эскуэля и эта мысль не давала мне покоя. Сегодня ездил в больницу — кашель никак не проходит и, пока ждал врача, в голове появилась конкретная идея.

Реализовать задуманное получилось через таблицу замен (об этом ниже) и кодогенерацию — psql позволяет писать в файлы произвольное содержимое, а потом эти же файлы подключать. К сожалению, после этого остаётся немного мусора во временной папке, но тут уж ничего не попишешь.

Таблицу замен я реализовал через девять переменных с числовыми именами, значением которых является число на единицу меньше имени. Например: \set 9 8 — тут переменная с именем 9 получает значение 8.

Чтобы реализовать вычитание единицы, пришлось разделить счётчик цикла на две переменные — beer_h (старший разряд) и beer_l (младший разряд), вычитание производится для каждой цифры отдельно. На диск записывается код, в который помещается имя переменной и её новое значение, используя текущее значение, как имя переменной, из которой надо взять следующее. Понимаю, что запутанно, надеюсь на примере понять будет проще.

Вот этот кусок: \qecho '\\set beer_h :':beer_h. Если текущее значение переменной beer_h равно 9, то на диск будет помещен скрипт с содержимым \set beer_h :9, после его подключения код выполнится, а так как значение переменной 9 — 8, то beer_h получит значение 8.

Одновременно я таким же способом создаю логические переменные beer_разряд_value_значение со значением 1, которые я использую потом как флаги, чтобы остановить цикл или отключить использование множественного числа, когда число «бутылок» доходит до единицы.

С кодогенерацией, кстати, работать веселее — сгенерированный код потом можно использовать как некий аналог подпрограмм, экономя размер кода.

В общем, в результате получилось реализовать «песню про пиво» на чистом языке утилиты psql, не привлекая другие языки программирования.

\if 0
    99.psql
    Psql beer song v.2
    Written by Evgeny Stepanischev https://bolknote.ru
\endif

\if :{?beer_l} \else
    \set 9 8 \set 8 7 \set 7 6 \set 6 5 \set 5 4 \set 4 3 \set 3 2 \set 2 1 \set 1 0
    \set print /tmp/beer_print.sql
    \set minus /tmp/beer_minus.psql

    \out :print
    \qecho '\\if :{?beer_h_value_0} \\echo -n :beer_l'
    \qecho '\\else \\echo -n :beer_h:beer_l \\endif'

    \set beer_h 9
    \set beer_l 9
    \set s s
\endif

\include :print
\echo -n ' bottle':s' of beer on the wall, '
\include :print
\echo ' bottle':s' of beer.'

\if :{?beer_l_value_0}
    \unset beer_l_value_0
    \set beer_l 9
    \out :minus
    \qecho '\\set beer_h :':beer_h '\\set beer_h_value_:':beer_h 1
    \include :minus
\else
    \out :minus
    \qecho '\\set beer_l :':beer_l '\\set beer_l_value_:':beer_l 1
    \include :minus
\endif

\if :{?beer_h_value_0}
    \if :{?beer_l_value_1}
        \set s
    \endif
    \if :{?beer_l_value_0}
        \echo Take one down and pass it around, no bottles of beer.'\n'
        \echo No more bottles of beer on the wall, no more bottles of beer.
        \echo Go to the store and buy some more, 99 bottles of beer on the wall.
        \q
    \endif
\else
    \unset beer_l_value_1
\endif

\echo -n 'Take one down and pass it around, '
\include :print
\echo ' bottle':s' of beer.\n'

\include_relative 99.psql

Давно чесались руки написать очередную «песню о пиве» на каком-нибудь языке программирования, но что-то никак не мог определиться с выбором. В конечном счёте выбрал psql.

75. Psql — не совсем язык программирования, это название клиентской программы, распространяемая вместе с СУБД «Постгрес». У неё есть довольно скромный встроенный язык, в который относительно недавно добавили условный блок.

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

Добавлено позднее: мне всё же удалось реализовать «песню о пиве» без использования эскуэля.

\if 0
    99.psql
    Psql beer song
    Written by Evgeny Stepanischev https://bolknote.ru
\endif 

\if :{?beer} \else \set beer 99 \endif
\echo :beer bottles of beer on the wall, :beer bottles of beer.

select :beer-1 beer, :beer > 2 notend \g /dev/null
\gset

\if :notend \set s s \else \set s \endif

\echo Take one down and pass it around, :beer bottle:s of beer.'\n'

\if :notend
    \include_relative 99.psql
\else
    \echo 1 bottle of beer on the wall, 1 bottle of beer.
    \echo Take one down and pass it around, no bottle:s of beer.'\n'
    \echo No more bottles of beer on the wall, no more bottles of beer.
    \echo Go to the store and buy some more, 99 bottles of beer on the wall.
\endif

Вроде код понятен почти без объяснений, но несколько моментов без знакомства с утилитой вряд ли возможно расшифровать самостоятельно, поэтому я их вкратце хочу объяснить.

:{?beer} — синтаксис проверки существования переменной, это нужно чтобы понять запускаем ли мы программу в первый раз или включили её в себя же, чтобы имитировать цикл.

select :beer-1 beer, :beer > 2 notend — эскуэль-запрос, которым делается математика и сравнение — вычитается единица из переменной и проверяется не пора ли выходить из цикла.

\g /dev/null — запуск запроса в выводом результата в /dev/null (то есть в никуда), поскольку его результат на экране нам не нужен.

\gset — записываем результат запроса в переменные, имена которых равны именам столбцов запроса.

У меня есть одна идея, как сделать вычитание, не привлекая эскуэль, надо будет попробовать. А вот с остановом хуже — там никаких идей.