Есть такая штука как full page image (FPI) - при первом изменении страницы после чекпойнта записывается в WAL копия всей страницы, а не только изменение этой страницы. (это защита для crash recovery на тот случай, если IO записало страницу только частично, страница у нас 8кб типично, страница памяти ОС чаще 4кб, а у диска адресация может быть и 512 байт: поэтому нужно предполагать, что есть ли мы попросили записать 8кб и где-то тут всё умерло, то на дисках может быть уже записано, например, 4кб нового содержимого страницы, а других 4кб остались старые - в итоге фигня)
Из менее очевидного и не помню чтобы задокументированного (помимо исходников), часть работы автовакуума подхватит выполнение других запросов. Так называемые micro vacuum и index hint bits, когда даже select может чего-нибудь писать в WAL. Небольшие оптимизации вида "ну это не моя работа, я выполняю запрос, но блок данных вот он в руке, всю работу по проверке видимости транзакций всё равно уже сделал, подпишу для потомков примечания чтобы они не тратили время на то же самое"
Ситуация вокруг hot_standby_feedback или max_standby_streaming_delay (max_standby_archive_delay полностью аналогично, но применяется на restore_command) в postgresql такая:
Реплика получает поток WAL, сделанных на мастере и обнаруживает, что на мастере физически из датафайла удалены данные (обычно автовакуумом), которые ещё может хотеть прочитать запрос, запущенный на этой реплике. Пропустить это изменение и накатывать репликацию дальше нельзя. Дальнейшшие изменения запросто могут быть в этом же самом датафайле, уже полагающиеся что это изменение было применено. Остаётся не так уж много вариантов что может делать реплика:
• или приостанавливать репликацию и подождать пока доработает запрос
• или отменять запрос и продолжать применять репликацию
postgresql сначала делает паузу репликации, затем по прошествии времени max_standby_streaming_delay отменяет мешающие запросы с разными вариантами terminating connection due to conflict with recovery. При этом, max_standby_streaming_delay считается от начала конфликта репликации и по таймауту снимает совсем всех кто мешает, даже если этот конкретный запрос запустился полсекунды назад.
hot_standby_feedback же это третий вариант работы, он предписывает реплике отправлять на мастер сведения о своей самой старой транзакции. Мастер это учитывает в своей работе и не удаляет данные, которые потенциально та транзакция может ещё запросить. То есть активно мешает оперативной работе автовакуума, от чего копятся ненужные версии строк в таблицах и индексах, занимают место и замедляют обработку всех запросов, и на мастере и на всех физических репликах (т.к. запрос должен проверить, вдруг эта версия строки видна именно ему)
Для аналитической реплики выбирается комфортный max_standby_streaming_delay. Полчаса, час, 10 часов - индивидуально по потребностям проекта. Баланс между "дать запросам поработать" и "реплика может отставать на такое время и это не авария, а полностью штатное поведение, о котором мы с вами ранее договорились"
hot_standby_feedback бизнес потребовать не мог. Это не бизнесовая задача. Выстрел себе в ногу в попытке починить сломавшийся или нестабильно работающий бизнес-процесс - да. Но бизнесовая проблема 100% в другом и не формулируется как "включить hot_standby_feedback".
За счет того что автовакууму обрабатывать больше мертвых версий строк или не только?
не только, но первопричина вернётся к этому.
Повезёт если бонусом не ляжет вообще всё из-за замедления ответов СУБД. Некоторые проекты красиво складываются минут за 20.
не, тут гораздо интереснее чуть выше Operating system error number 11 in a file operation.
то есть ошибка вовсе не самого mysql, а ОС не даёт работать.
3. Да были ошибки в логах о недоступности для записи таблиц.
можно пару цитат?
То есть основная гипотеза, vacuum full писал что-то большое, но не смог удалить либо старые либо новые датафайлы.
База при этом не уходила ли вообще в crash recovery по какой-то причине? Там есть варианты при которых остаются осиротевшие датафайлы https://www.cybertec-postgresql.com/en/orphaned-fi...
- место занимает именно PGDATA/base/ ? PGDATA/pg_wal нормального размера?
- "Если сделать бэкап и развернуть на другом сервере" - какой именно бекап имеется в виду?
- ошибки в логах базы при снапшоте?
- как именно снапшот делался? (LXC на btrfs/zfs/etc тоже можно назвать виртуалкой, а вот грабли будут свои)
PS: vacuum full на 1,4тб базе это довольно неожиданная идея
посмотрите что dmesg про них писал. Сюда влезли ручки всякого systemd и сломали ранее исправно работавшее именование интерфейсов через udev при конфликтующих именах, когда надо поменять местами два имени между собой. Помогает использование имён не ethX, а, например, NAME="ethbond0" или NAME="ethlan"
NEW и OLD в триггерах - это предварительно объявленные переменные типа record.
select from не предполагает использование переменной в from и пытается искать таблицу/view/etc с таким именем.
Что означает "Перестаёт работать"? Если отказывается стартовать, то с какой формулировкой. Если стартует, то, опять же, что в логах и в чём выражается "Перестаёт работать"
Security through obscurity мало того что бессмысленный, так вы сами себя им и запутали.
Не понятно почему два разных списка.
Потому что это два разных postgres'а.
Создавал базы так:
Соответственно, не в том postgresql о котором думали, но не проверили, к какому именно подключаетесь.
Развели бардак и закономерно потеряли что у вас где. Соберите бардак воедино в нормальный 5432, а безопасность обеспечить нормальным закрытием порта через firewall.
Плюс revoke all on database .. from public;
Где именно и как именно создали базы? Внимание на себя обращает нестандартный порт. Значит чего-то нестандартного навертели уже. И стоит начать с вопроса, а сколько экземпляров postgresql вообще запущено, не запутались ли сами в том что уже навертели.
Вместе с накоплением bloat получается что мы регулярно трогаем бОльшее число разных блоков, что и вызывает больше записи FPI.
https://wiki.postgresql.org/wiki/Full_page_writes
Из менее очевидного и не помню чтобы задокументированного (помимо исходников), часть работы автовакуума подхватит выполнение других запросов. Так называемые micro vacuum и index hint bits, когда даже select может чего-нибудь писать в WAL. Небольшие оптимизации вида "ну это не моя работа, я выполняю запрос, но блок данных вот он в руке, всю работу по проверке видимости транзакций всё равно уже сделал, подпишу для потомков примечания чтобы они не тратили время на то же самое"
Ситуация вокруг hot_standby_feedback или max_standby_streaming_delay (max_standby_archive_delay полностью аналогично, но применяется на restore_command) в postgresql такая:
Реплика получает поток WAL, сделанных на мастере и обнаруживает, что на мастере физически из датафайла удалены данные (обычно автовакуумом), которые ещё может хотеть прочитать запрос, запущенный на этой реплике. Пропустить это изменение и накатывать репликацию дальше нельзя. Дальнейшшие изменения запросто могут быть в этом же самом датафайле, уже полагающиеся что это изменение было применено. Остаётся не так уж много вариантов что может делать реплика:
• или приостанавливать репликацию и подождать пока доработает запрос
• или отменять запрос и продолжать применять репликацию
postgresql сначала делает паузу репликации, затем по прошествии времени max_standby_streaming_delay отменяет мешающие запросы с разными вариантами terminating connection due to conflict with recovery. При этом, max_standby_streaming_delay считается от начала конфликта репликации и по таймауту снимает совсем всех кто мешает, даже если этот конкретный запрос запустился полсекунды назад.
hot_standby_feedback же это третий вариант работы, он предписывает реплике отправлять на мастер сведения о своей самой старой транзакции. Мастер это учитывает в своей работе и не удаляет данные, которые потенциально та транзакция может ещё запросить. То есть активно мешает оперативной работе автовакуума, от чего копятся ненужные версии строк в таблицах и индексах, занимают место и замедляют обработку всех запросов, и на мастере и на всех физических репликах (т.к. запрос должен проверить, вдруг эта версия строки видна именно ему)
Для аналитической реплики выбирается комфортный max_standby_streaming_delay. Полчаса, час, 10 часов - индивидуально по потребностям проекта. Баланс между "дать запросам поработать" и "реплика может отставать на такое время и это не авария, а полностью штатное поведение, о котором мы с вами ранее договорились"