Комментарии 35
ws_smev_log — СМЭВ? НИИ Восход?
Эм… А если в этих данных были важные данные? То логичный ход — взять бекап этой таблицы, и сделать следующее: исторические данные взять из бекапа, а хвост из живой базы, склеить — это и будет продакшен версия на текущий момент.
Если же в базе тупо был лог действий, который был не особо нужен — почему нельзя было тупо таблицу "обнулить" и начать с начала? Или FK мешали бы?
Ну, что — опыт правда крутой, но статья какая-то недосказанная.
И, да, первоисточник проблемы нашли? А где гарантия, что база опять не покорраптится?
+1 поставил, но ждём… Продолжения.
А если в этих данных были важные данные?
Я не видел другого варианта. Да и данные эти были уже повреждены, их нельзя было никак восстановить. На мой взгляд, удаление проблемных записей было единственным правильным решением
То логичный ход — взять бекап этой таблицы
К сожалению, как это обычно бывает, бекап, который содержал таблицу ws_smev_log, запускался раз в неделю (командой pgdumpall). Но за успешностью его выполнения никто не следил, он просто аварийно завершался.
В остальные дни бекап запускался с помощью pg_dump, но таблица ws_smev_log исключалась, чтобы сэкономить дисковое пространство. Поэтому бекапов таблицы ws_smev_log не было.
Если же в базе тупо был лог действий, который был не особо нужен — почему нельзя было тупо таблицу «обнулить» и начать с начала?
Данные в таблице не были очень критичными, но и просто так удалять их было нельзя. Обнулить не разрешили.
То-есть у Вас бэкапом называется pumpall?
да, раз в неделю делаем pg_dumapall, каждый день делается просто pg_dump с исключением таблицы ws_smev_log.
грустно.
или это из серии «база особенно не нужна»?
или это из серии «база особенно не нужна»?
А как нужно правильно? С помощью barman и подобных утилит?
Например можно вот так: www.postgresql.org/docs/9.5/continuous-archiving.html
С логами и восстановлением на нужное время. Это быстрее дампирования.
С логами и восстановлением на нужное время. Это быстрее дампирования.
Ну, это очевидный трейдоф. Либо мы размазываем бекап по времени и получается стриминг. Либо мы забиваем на это и приходится выделять точные моменты времени для полного снапшота, но это "ударная" нагрузка на сервер, да и бекап может долго делаться.
tradeoff там по объёму данных на бекап-машине (особенно для задачи типа «надо восстановить таблицу за 2017 год»). И по времени разворачивания бекапа тоже. При том в обе стороны в зависимости от условий. Pitr вообще-то штука не очень-то быстрая, т.к. имеет склонность упираться в одно ядро CPU и крайне критичная к латентности io. Тогда как pg_restore можно и параллелить.
Ну и ещё такой фокус: по-моему мне за несколько лет работы нужно было восстанавливать именно целиком инстанс только один раз, а обычно задача «вытащить несколько строк из одной таблицы на любую дату до Х». Что куда быстрее делается из custom/directory форматов pg_dump.
Поэтому pg_dump или basebackup с архивом wal — выбирать надо по ситуации. Можно выбирать оба и комбинировать подходящим для именно ваших требований способом.
Ну и ещё такой фокус: по-моему мне за несколько лет работы нужно было восстанавливать именно целиком инстанс только один раз, а обычно задача «вытащить несколько строк из одной таблицы на любую дату до Х». Что куда быстрее делается из custom/directory форматов pg_dump.
Поэтому pg_dump или basebackup с архивом wal — выбирать надо по ситуации. Можно выбирать оба и комбинировать подходящим для именно ваших требований способом.
Теперь вы наверняка поняли, зачем нужна реплика. Что, если бы проблемных строк было не три, а тридцать тысяч и в каждой — какие-нибудь финансовые данные клиента, не отражённые до сих пор в бэкапе?
Наглядное пособие, для чего нужна была задача от гугла про небоскреб и два стеклянных шара.
В моём случае таблица содержала 1 628 991 строк! По-хорошему необходимо было позаботиться о партициирвоании данных
Нет, не надо было. Всего лишь полтора миллиона строк — это где от партицирования проблем больше чем пользы.
При тихом повреждении проверяйте диски и RAM. Даже если она ECC. Тихое повреждение страницы по вине сбоя ECC плашки памяти наблюдал сам.
Затем я подключился к базе, сделал VACUUM FULL (думаю делать было необязательно),
Обязательно. Но не всей базы, а только этой таблицы.
Ну а в остальном подход верный:
— сделать физическую копию и экспериментировать на копии
— проверить железо
— сверить данные с репликой если есть
— определить повреждённые данные. Если повреждён toast как у вас — попытаться прочитать строки не select *, а добавляя поля переменной длины по одному. Возможно повреждено только одно из полей. reindex — если сломан индекс. По системному каталогу путь base/16490/21396989 можно сопоставить с логическим объектом
— удалить повреждённые либо insert… select'ом создать копию таблицы рядом и удалить старую повреждённую таблицу.
zero_damaged_pages лучше не трогать.
PS: ну и имхо некорректно писать про postgresql и ссылаться на документацию стороннего форка.
PostgresPro переводят самую обычную официальную документацию. Плюс расширенные версии для двух своих форков (Pro Standard и Enterprise). Итого доступны три перевода. Там даже можно открывать параллельно рус и англ версии.
Нет, не надо было. Всего лишь полтора миллиона строк — это где от партицирования проблем больше чем пользы.
Я с вами согласен, но, когда надо? У меня ощущение, что где то за миллиардом, более точных просто найти не могу.
И проблемами поделитесь пожалуйста.
Так и не может быть точных данных.
Проблемы — смотрите хотя бы на planning time запроса. Партицированием не сложно сделать так, что запрос планируется дольше чем собственно выполняется. partition pruning сам по себе не бесплатный.
Или вот например какой-нибудь простой where user_id =? order by created_at limit 30 — это один составной индекс для плоской таблицы и весьма развесистый план для партицированной по дате (потому что 30 записей можно не набрать со всех разделов вовсе и надо предполагать идти во все разделы).
in real world о партицировании чаще думают в контексте «нам не нужны эти сотни гигабайт данных старше 3 лет в основной базе, надо их заархивировать и удалить». В плоской таблице это интересное мероприятие с разными подводными камнями (если кратко: не надо делать это одним куском delete from, только разбивать на части вменяемого размера и удалять частями в разных транзакциях с паузами между ними). А для партиции — drop table
Вариации задачи «данные нужны в этой же базе, но сильно реже и можно на более медленные и дешёвые диски перенести» — сюда же.
Проблемы — смотрите хотя бы на planning time запроса. Партицированием не сложно сделать так, что запрос планируется дольше чем собственно выполняется. partition pruning сам по себе не бесплатный.
Или вот например какой-нибудь простой where user_id =? order by created_at limit 30 — это один составной индекс для плоской таблицы и весьма развесистый план для партицированной по дате (потому что 30 записей можно не набрать со всех разделов вовсе и надо предполагать идти во все разделы).
in real world о партицировании чаще думают в контексте «нам не нужны эти сотни гигабайт данных старше 3 лет в основной базе, надо их заархивировать и удалить». В плоской таблице это интересное мероприятие с разными подводными камнями (если кратко: не надо делать это одним куском delete from, только разбивать на части вменяемого размера и удалять частями в разных транзакциях с паузами между ними). А для партиции — drop table
Вариации задачи «данные нужны в этой же базе, но сильно реже и можно на более медленные и дешёвые диски перенести» — сюда же.
Дак наоборот же, сейчас все начали напирать на партиционирование для «быстродействия». Пробовать, честно говоря, лень на значимых объемах.
В принципе доля истины в том есть, большой индекс будет работать медленней, но с другой стороны есть частичные индексы, которыми оперативный период можно умаслать в усмерть и реиндексировать хоть каждый день.
Касательно pruning: он не нивелируется prepared запросами? Я давненько так использую persistent connection в связке с prepared, посему на время планирования вообще перестал смотреть.
В принципе доля истины в том есть, большой индекс будет работать медленней, но с другой стороны есть частичные индексы, которыми оперативный период можно умаслать в усмерть и реиндексировать хоть каждый день.
Касательно pruning: он не нивелируется prepared запросами? Я давненько так использую persistent connection в связке с prepared, посему на время планирования вообще перестал смотреть.
Касательно pruning: он не нивелируется prepared запросами?
Вы СУБД, получаете parse message с запросом where user_id = $1. Видите что таблица партицирована по user_id. Есть идеи как сделать partition pruning? Ммм? Вы не знаете какие bind с execute будут дальше, а значит вы обязаны проигнорировать возможные оптимизации и предполагать посетить каждую партицию.
Только относительно недавно (в postgresql11) завезли runtime partition pruning — который срабатывает в executor'е в дополнении к планировщику. executor может не обращаться к таблице есть понимает что нужных данных там всё равно не будет опираясь на реальные переданные параметры.
большой индекс будет работать медленней
работать будет нормально, формально поиск по btree конечно от глубины дерева зависит, но фактически большой индекс печальнее из-за того что он большой и надо больше буфера/памяти под него.
Вот только… Если вы нарежете таблицу по условному hash(user_id) — меньше данных вам в буфере держать не получится, запрашивать-то будут данные разных пользователей.
Если же партицировать по дате — то да, горячие данные обычно недавние. Но это опять же вариация задачи «нам не нужны эти старые данные так часто»
Партицированием улучшить производительность тоже можно, но с пониманием как именно используются данные этой большой таблицы. И самое важное потом — не забывать указывать условие на отсечение партиций.
Чтоб не ждать так долго, можно было бы использовать метод половинного деления, каждый раз делая INSERT FROM SELECT во временную таблицу. Табличка небольшая, всего 1.6 миллиона записей.
Делим диапазон ID пополам (1..800K; 800K..1.6M) и копируем данные во временную таблицу. Запрос для одной из половинок завершится с ошибкой. Берём эту половину диапазона и выполняем два запроса для четвертинок (напр, 800K..1.2M;1.2M..1.6M) и так далее. Несколько минут — и вы получили бы ответ.
Запрос для одной из половинок завершится с ошибкой.
Нет. Запрос для обоих половинок имеет шанс завершится с ошибкой.
И это будет означать что у нас не одна проблемная строка, а несколько. В этом случае мы проходим по обеим ветвям, которые, в свою очередь, могут также породить новые ветви. Написать простенький скрипт с рекурсией на Bash, Python, PL/pgSQL и т.п. займет минут 15. Запустить, подождать несколько минут — и вуаля.
Их тоже разбить на половинки) бинарный поиск же
А что, разве в Postgres нет штатной утилиты для восстановления целостности БД с потерей информации, такой как gfix в Inetrbase и его потомках (или DBCC CHECKDB в MS SQL, или eseutil /p для БД на Extensible Storage Engine от MS — Exchange и системные БД компонентов Windows Server)? Упущение, однако, IMHO.
Ибо в Interbase в некие древние времена (ещё когда Delphi было модным средством разработки приложений) мне как-то пришлось воспользоваться gfix на боевой БД — и это было сильно проще, чем описанные в статье мучения.
Ибо в Interbase в некие древние времена (ещё когда Delphi было модным средством разработки приложений) мне как-то пришлось воспользоваться gfix на боевой БД — и это было сильно проще, чем описанные в статье мучения.
Использовал функцию отсюда которая работала несколько минут для миллиона строк и позволяла удалить нужную строку даже если не было своего уникального поля-идентификатора. (восстанавливал таблицы 1C)
Замечу, что всех описанных в статье страданий можно было избежать, просто посмотрев битую страницу через расширение pageinspect (https://www.postgresql.org/docs/current/pageinspect.html)
Угу, и дополню:
- Можно было-бы увидеть и понять что было в испорченных строках. В результате (вероятно) восстановить данные полностью (либо ввести вручную), а также пронять причину ошибки (аппаратный сбой или баг в коде postgres).
- Первый шаг автора не верный. Всё-таки стоило делать копию всех данных (ибо они не разрушены физически, т.е. нет проблем на HDD).
Вопрос к автору — когда снимали текстовый дамп, он случайно не обрывается на проблемной строчке?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Мой первый опыт восстановления базы данных Postgres после сбоя (invalid page in block 4123007 of relatton base/16490)