Комментарии 9
Как вышли из ситуации? Создали новую пустую таблицу и по блокам перетаскивали данные со старой таблицы в новую. Потом били блоки на меньшего размера и так до тех пор пока не выявили семнадцать битых строк, для которых были ссылки в других таблицах, но в целевой отсутствовали.
Можете подробнее про процесс рассказать или ткнуть в документацию, где это описано? Какими инструментами это выполняется?
Или это просто берем по N записей и копируем в другую таблицу, пока записи не кончатся (insert into ... select from ...)?
При вставке данных в таблицу history из-за внешнего ключа проверялось наличие записи в таблице items. Починили.
Если нет ключа в items, то на что history ссылалась? Какая схема данных была?
мы понимаем, что перестало работать секционирование таблицы history_text
Нативное секционирование имеет какие-то минусы? Не приходилось работать с pg_partman, но с нативным пока не замечал проблем.
Спасибо за хороший вопрос, делюсь ответом Ивана:
Можете подробнее про процесс рассказать или ткнуть в документацию, где это описано? Какими инструментами это выполняется?
Или это просто берем по N записей и копируем в другую таблицу, пока записи не кончатся (insert into ... select from ...)? «
Если у вас есть таблица с инкрементальным номером, то проблем вытащить данные не будет. Да выполняем команду insert into … select from where id between A and B Но у нас id был uuid. Поэтому мы потратили больше времени на вытаскивания данных – из-за этого было и время остановки сервера больше. Сначала я пробовал сделать так insert into … select from where ctid in ();
Об ctid тут https://postgrespro.ru/docs/postgresql/13/ddl-system-columns
Вытащил так примерно 70 процентов записей. Но потом пришлось пойти по-другому. Я нашел связанную таблицу по внешнему ключу (тут главное условие, что все данные из целевой таблицы точно должны быть в связанной таблице). И начал перебирать uuid из второй таблицы и подставлять в условие where. Так я вытащит все остальные записи.
По блокам.
Да вначале формировал блоки из 10 тыс. значений в запросе where id in (). Находит запросы, которые повисли. Вызывал повторно эти запросы, но блок уменьшал до 100 записей. Потом выискивал опять эти блоки и перебирал по 1 записи. Так и вышел на 17 битых записей.
Если нет ключа в items, то на что history ссылалась? Какая схема данных была?
Внешнего ключа не было. Так как и items и history были секционированы. Так как секционирование было сделано через наследование, то внешние ключи нельзя создать на родительской таблице. А когда делали секционирование таблицы items, то забыли сделать первичный ключ. Поэтому через какое-то время появилась описанная ситуация.
Нативное секционирование имеет какие-то минусы? Не приходилось работать с pg_partman, но с нативным пока не замечал проблем.
Ой, тут очень большая тема. Я могу дать ссылку на документацию https://postgrespro.ru/docs/postgresql/13/ddl-partitioning. Ограничений много. Но с каждой новой версии PostgreSQL что-то да улучшается в секционировании. В данном кейсе использовалось расширение partman об этом тут: https://pgxn.org/dist/pg_partman/doc/pg_partman.html
По первому кейсу : а Вы не настраиваете idle_in_transaction_session_timeout ? Все таки ситуация, когда кто-то открыл подключение, открыл в ней транзакцию, и потом что-то очень долго делает у себя на клиенте - не стандартная. Скорее всего, уже что-то пошло не так и сессию надо закрывать. То есть таймаут можно поставить достаточно небольшой.
Спасибо за хороший вопрос, делюсь ответом Ивана:
Да вы правы, можно было выставить данный параметр. В данном кластере этот параметр имел значение по умолчанию, те отсутствия таймаута. После данного инцидента он был выставлен в 1 час.
Но я в своей практики видел, когда данным параметром стреляли себе в ногу. Из-за того, что приложение написано не оптимально (что и выше кстати тоже), при выполнении длительного бизнес процесса (длительность доходила до суток) оно работало в одной транзакции. Спасало и БД и приложение, то что пользователей было немного и бизнес процессы работали с разными данными. Но как только мы выставили данный параметр в 1 час, у нас перестало работать приложение. Но так как приложение было большой монолит и переписать его не имело возможности. Поэтому после несколько часов не_работы приложения, данный параметр вернули в значение по умолчанию.
По-моему, транзакции, висящие длиннее часа в idle in transaction - это не "не оптимально", а "ужасно", и это надо в любом случае переделывать.
Просто стоит добавить, что длинные транзакции - это не просто ресурсы. Это, как минимум, еще блокировки записей, и update conflict'ы. И одна из самых критичных у нас проблем, с которой мы сталкивались - невозможность сделать vacuum по всей базе. В итоге самые часто изменяемые таблицы (в том числе и системные pg_) разрастались до огромных размеров, что приводил к сильному замедлению даже простых SELECT.
В итоге мы даже добавили уведомления прямо в систему (хотя можно и zabbix настроить), чтобы видеть длинные транзакции, и оптимизировать их.
И касательно всей статьи, я не знаю, что вы умудряетесь сделать с PostgreSQL, чтобы порушить там таблицы. У нас несколько десятков баз, многие из которых в несколько террабайт и тысячами одновременно работающих пользователей, делающих сложные изменения данных в одной транзакции (ERP-система). Чего у нас только не делали с СУБД. По oom killer'у они падали десятки раз (и это в момент большой нагрузки с десятками выполняемых транзакций). Снаружи очень много раз внезапно перегружали виртуальные машины (без уведомления гостевой). Падали по segmentation fault (есть там такой баг в PostgreSQL 13). Иногда даже отключили диск с одним tablespace'ом. И за 5 лет только один (!) раз "портилась" одна таблица - ее просто из бэкапа восстановили.
Падали по segmentation fault (есть там такой баг в PostgreSQL 13)
Неисправленный до сих пор? Тоже используем 13-ю версию и падений не замечали.
Ошибка возникает в очень редких случаях. У нас это проявлялось в том, что у клиента раз в месяц просто (по логам) завершался процесс по segmentation fault, и дальше postmaster перезапускал весь сервер из-за этого.
В лог при этом записывался запрос и место, где было падение. После первых 3х случаев выяснилось, что все они падали в одном случае :
Была открыта транзакция
Происходило выполнение PL/SQL функции внутри блока TRY (возможно внутри CATCH - не помню сейчас).
Segmentation fault происходил в момент отмены выполнения через pg_cancel_backend.
Мы просто переписали этот PL/SQL без TRY и проблема ушла. Как видите - это очень редкий случай, что мы решили не воспроизводить, и не писать bug report (хотя раньше мы уже писали bug report'ы и их фиксили, когда не могли обойти).
Нюансы работы с PostgreSQL в 3 кейсах от DBA