Несколько раз в жизни сталкивался с ситуацией, когда «Постгрес» при попытке выполнить запрос, возвращает что-то вроде invalid page in block 460248 of relation base\/16391\/23665<\/tt>. Это всегда означает какую-то аппаратную проблему на системе хранения, где СУБД хранит свои данные.<\/p>\n
Уже два раза я попадал в ситуацию, когда бакапа у клиента по каким-то причинам нет и надо сдампить то, что имеется, причём всегда на дворе всегда глубокая ночь, поэтому надо бы уже описать этот опыт, пока снова не забылся и не пришлось его в который раз восстанавливать спросонья.<\/p>\n
Первым делом, разумеется надо отключить пользователей от базы данных, чтобы предотвратить её дальнейшее разрушение.<\/p>\n
Второе, что надо сделать — посмотреть какая сущность у нас развалилась. Это можно сделать следующим запросом:<\/p>\n
SELECT 23665::regclass::text;\r\n text\r\n-------------\r\n some_items<\/code><\/pre>Если это индекс, просто удаляем его и пытаемся снять полный дамп базы данных, записав какой индекс мы удалили. Подобные ошибки могут быть в других частях базы, поэтому повторяем до успеха.<\/p>\n
Всё несколько хуже, есть речь идёт о таблице. Для начала надо определить строки с какими ID<\/tt> теперь недоступны и сдампить таблицу пока без них. Для этого я написал достаточно прямолинейную хранимую процедуру, которая пытается получить каждую строку, которая должна существовать и, если не удаётся, сообщает строка с каким ID<\/tt> недоступна.<\/p>\nCREATE OR REPLACE FUNCTION check_table(table_name TEXT) RETURNS VOID AS $$\r\nDECLARE\r\n min_id INT;\r\n max_id INT;\r\n current_id INT;\r\nBEGIN\r\n EXECUTE 'SELECT MIN(id), MAX(id) FROM ' || table_name INTO min_id, max_id;\r\n \r\n current_id := min_id;\r\n \r\n WHILE current_id <= max_id LOOP\r\n BEGIN\r\n EXECUTE 'SELECT * FROM ' || table_name || ' WHERE id = $1' USING current_id;\r\n EXCEPTION\r\n WHEN SQLSTATE 'XX001' THEN\r\n RAISE NOTICE 'Ошибка при обработке id: %', current_id;\r\n END;\r\n \r\n current_id := current_id + 1;\r\n END LOOP;\r\nEND\r\n$$ LANGUAGE plpgsql;<\/code><\/pre>Дальше таблицу надо сдампить, составив SELECT<\/tt>, исключающий сбойные участки. Я, обычно, делаю ряд запросов, соединённых через UNION ALL<\/tt> и подставляю его в COPY<\/tt>, предварительно выключив последовательное сканирование таблицы (SET enable_seqscan = OFF<\/tt>).<\/p>\n
Если этого не сделать, «Постгрес» обязательно займётся сканированием таблицы, так как нужна она почти вся, а там у нас сбойные участки. Отключив этот метод сканирования, мы заставляем обращаться к таблице через первичный индекс, если он, конечно цел. Хуже, когда разрушен и он.<\/p>\n
Я с такой ситуацией ещё не сталкивался, но если столкнусь, буду думать над проверкой строк через ctid<\/tt>.<\/p>\n
В общем, должен получиться примерно такой запрос:<\/p>\n
SET enable_seqscan = OFF;\r\n\r\nCOPY (\r\nSELECT * FROM some_items WHERE id < 100500 UNION ALL\r\nSELECT * FROM some_items WHERE id > 100500 AND id < 100800 UNION ALL\r\nSELECT * FROM some_items WHERE id > 100800\r\n) TO '\/tmp\/some_items-table.dump'<\/code><\/pre>После того как мы определили и сдампили все сбойные таблицы, можно сдампить остальную базу данных следующей командой:<\/p>\n
pg_dump --exclude-table-data=some_items -j10 -Fd -Z1 -f \/dump_directory dbname<\/code><\/pre>Как видно, мы дампим структуру, но не данные сбойной таблицы (ключ —exclude-table-data<\/tt> можно указать несколько раз), при этом параметр -Fd<\/tt> нужен, чтобы мы могли снимать дамп параллельно сразу несколькими процессами, -j<\/tt> задаёт количество этих процессов, ключ -f<\/tt> указывает директорий, куда будет записан бакап, а -Z1<\/tt> регулирует степень сжатия, —с уровнем 1<\/tt> снятие дампа идёт с максимальной скоростью.<\/p>\n
Наконец, успех: основная база лежит единым куском, сбойные таблицы сняты отдельными дампами. Что ещё можно сделать?<\/p>\n
В одном из случаев у меня были несколько реплик, которые развалились по-разному. Из них удалось наскрести недостающие куски. Их можно снять через тот же COPY<\/tt> с запросом SELECT<\/tt> и просто присоединить в конец дампа соответствующей таблицы — там текстовый формат.<\/p>\n
Если реплики нет, можно посмотреть какие индексы есть на сбойной таблице. Не исключено, что критичные столбцы покрыты индексами, которые остались целыми.<\/p>\n
Данные из индексов можно вытащить, сделав запрос так, чтобы он затрагивал только столбцы, которые есть в индексе. Если в интересующем нас индексе нет ключевого столбца, не беда, там есть технический столбец ctid<\/tt>, по которому потом можно соединить данные построчно.<\/p>\n
Если строки восстановить не удалось, а на таблицу ссылаются другие таблицы, надо заполнить недостающее любым мусором, чтобы внешним ключам было куда ссылаться.<\/p>\n
Итак, теперь у нас есть большой дамп почти всей базы данных и один или несколько дампов таблиц. Как же всё соединить на другом сервере? Последовательность следующая.<\/p>\n
Для начала восстанавливаем основную базу данных, но без индексов и внешних ключей:<\/p>\n
pg_restore -d dbname -Fd \/dump_directory --section=pre-data --section=data -j10<\/code><\/pre>Потом заливаем дампы отдельных таблиц, после чего доливаем индексы и внешние ключи:<\/p>\n
pg_restore -d dbname -Fd \/dump_directory --section=post-data -j10<\/code><\/pre>Если всё сделано правильно, должна получиться консистентная база данных. Осталось разобраться с двумя вещами: создать удалённые ранее индексы, если таковые были и, если вы создавали мусорные строки, то надо понять как с ними быть дальше. Например, удалить их или заполнить значениями «данные утеряны».<\/p>\n",
"date_published": "2023-09-20T23:08:53+05:00",
"date_modified": "2023-10-03T18:53:44+05:00",
"tags": [
"postgres",
"программирование"
],
"author": {
"name": "Евгений Степанищев",
"url": "https:\/\/bolknote.ru\/",
"avatar": "https:\/\/bolknote.ru\/pictures\/userpic\/userpic@2x.jpg?1760600028"
},
"_date_published_rfc2822": "Wed, 20 Sep 2023 23:08:53 +0500",
"_rss_guid_is_permalink": "false",
"_rss_guid": "123115",
"_rss_enclosures": [],
"_e2_data": {
"is_favourite": false,
"links_required": null,
"og_images": []
}
}
],
"_e2_version": 4079,
"_e2_ua_string": "Aegea 11.0 (v4079e)"
}