Postgresql проверка базы на ошибки - Исправление ошибок и поиск оптимальных решений проблем

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Алексея Лесовского — «Поиск и устранение проблем в Postgres с помощью pgCenter»

Время от времени при эксплуатации Postgres’а возникают проблемы, и чем быстрее найдены и устранены источники проблемы, тем благодарнее пользователи. pgCenter это набор CLI утилит которые является мощным средством для выявления и устранения проблем в режиме «здесь и сейчас». В этом докладе я расскажу как эффективно использовать pgCenter для поиска и устранения проблем, в каких направлениях осуществлять поиск и как реагировать на те или иные проблемы, в частности, как:

проверить, все ли в порядке с Postgres’ом;
быстро найти плохих клиентов и устранить их;
выявлять тяжелые запросы;
и другие полезные приемы с pgCenter.

Всем привет, меня зовут Алексей Лесовский. Я работаю в компании Data Egret. Это консалтинговая компания. И я вам расскажу, как мы в нашей консалтинговой компании занимаемся поиском и устранением неисправностей в PostgreSQL.

Я расскажу о том, как с помощью консольной утилиты pgCenter можно хорошо, быстро и эффективно находить самые разные проблемы и переходить к их устранению.

Немного о себе. Я долгое время был системным администратором. Занимался Linux, виртуализацией, мониторингом. И в какой-то момент времени стал заниматься больше Postgres’ом. И работа с Postgres стала занимать большую часть времени. И так я стал PostgreSQL DBA. И сейчас уже работая в консалтинговой компании, я работаю с Postgres каждый день. И каждый день наши заказчики предоставляют нам самый разный материал для новых конференций.

Все общение с нашими заказчиками происходит в виде беседы в чатах. Это самые разные чаты: Slack, Telegram. Но наши заказчики часто обнаруживают какую-нибудь проблему у себя и пишут. Мы в свою очередь должны на это отреагировать.

На слайде всем широко известная диаграмма Брендана Грэгга, как находить различные проблемы, связанные с производительностью в Linix. Это довольно познавательная диаграмма. Она показывает, как устроен Linux и какие утилиты есть для нахождения проблем. По сути, можно обложиться всеми этими утилитами и смотреть, что происходит.

Но в любом случае мы увидим то, что у нас все замыкается на Postgres. Процессорное время потребляется Postgres. Дисковый ввод-вывод так же генерируется Postgres’ом. Всю память съел тоже Postgres. Мы будем видеть только один Postgres.

Для Postgres есть аналогичная картинка. Она также разбивает Postgres на несколько подсистем и показывает из чего состоит Postgres. Кроме того, в Postgres есть большое количество статистических представлений (views), с помощью которых можно анализировать работу этих подсистем.

И этих статистических представлений довольно много. Но во всех этих представлениях есть колонки. Эти колонки имеют собственные имена. И держать все это в голове бывает довольно-таки сложно.

И когда ты начинаешь искать какую-то проблему, нужно вспомнить имена нужных представлений, найти свои скрипты, которые ты, возможно, заранее приготовил. И это довольно-таки тяжело. И одновременно возникает масса вопросов: «Что тормозит?», «Где тормозит?» и «Что с этим делать?». На поиск всего этого нужно время, которого как обычно мало.

И разбираясь с проблемой одного из заказчиков, когда я тоже разбирал свои скрипты и пытался диагностировать проблему выполняя рутинные однообразные действия, мне пришла в голову идея, что нужна программа, которая будет это все дело облегчать. Более-менее хороших программ не нашлось и так пришла идея написать pgCenter.

Изначально она была написана на С. Это консольная утилита, которая показывала статистику в TOP-подобном виде.

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

Go это компилируемый язык и чтобы использовать pgCenter его нужно скомпилировать. Но в репозитории уже есть пакеты и вам как пользователю не нужно компилировать программу самостоятельно. К репозиторию подключена система сборки, которая после каждого коммита, компилирует бинарник, собирает targz, deb- и rpm-пакеты и выкладывает их в релизы. Т.е. не нужно устанавливать какие-то пакеты, ставить make, GCC или Golang. Достаточно просто зайти в релизы, скопировать нужный пакет по ссылке, распаковать руками или установить через дефолтный пакетный менеджер и можно уже пользоваться.

Изначально весь pgCenter представлял собой именно просмотрщик статистики, который в top-режиме показывает текущие изменения статистики за последнюю секунду (интервал изменений настраивается).

Однако потом я начал добавлять новые функции. И позже уже появились такие штуки, как сохранение статистики в файл и построение отчетов. И буквально совсем недавно я добавил сэмплер wait_events. Это штука позволяет смотреть, на каком месте запросы проводят время в ожидании чего-либо.

По ходу разработки я постарался сохранить синтаксис команды PSQL. Если вы работаете с Postgres, вы знаете что запустив PSQL без аргументов и параметров можно подключиться к Postgres. С pgCenter тоже самое, достаточно запустить «pgcenter top» из под postgres пользователя и она начнет показывать вам какую-то статистику (по-умолчанию, текущая активность в БД).

В более сложных случаях, если вы работаете от другого пользователя или нужно подключиться к какой-то другой базе, или к БД, которая находится на другом хосте, то можно указать те же самые ключи, которые вы используете в PSQL, которые определяют подключения к хосту, к конкретному порту, к конкретной базе и под конкретным именем пользователя.

Но можно даже не указывать хост, можно подключаться через UNIX-сокеты, т. к. go’шный драйвер, который используется под капотом pgCenter позволяет подключиться не только к сетевым сокетам, но и к UNIX.

Кроме того, тот же драйвер поддерживает переменные окружения libpq. Даже если у вас особенный случай, например, pg_stat_statements установлен в отдельной схеме и pgCenter не может его найти, то можно переопределить поведение через переменные окружения. Это тоже поддерживается.

И вот так выглядит внешний вид утилиты (pgcenter top). И в первый раз, когда ее запускаешь, это может немного напрячь. Это похоже, что это какой-то центр управления полетами. Много цифр, много букв, все это быстро меняется. Но это на самом деле не важно. Здесь важно помнить, что интерфейс pgCenter, а именно top-просмотрщика, состоит из трех частей.

Первая часть – системная информация. Эта информация находится в верхнем левом углу.

Вторая часть – верхний правый угол, здесь располагается сводная информация по Postgres. Здесь можно получить уже какую-то детальную информацию о том, как Postgres работает в данный момент.

И третья часть – статистика из самих статистических представлений (views). Здесь информация из stat-представлений, которые есть в Postgres, в этой части отображаются изменения этой статистики.

Кроме того, интерфейс предоставляет дополнительные функции. Нажимая стрелки влево-вправо, вы можете менять сортировку. Вы можете указать сортировать строки по интересующему вас полю. Например, сортировку по именам таблиц, сортировку по текстам запросов, по времени и жизни транзакций, и т. д.

Если информации слишком много, можно использовать фильтры. В качестве фильтров там применяются регулярные выражения (regexp). И вы можете отфильтровать выводимую инфоормацию по интересующим вас критериям. Например, показать запросы конкретного пользователя, базы или конкретно какие-то запросы: селекты или апдейты.

Далее в докладе я расскажу, с какими конкретно кейсами вы можете столкнуться у себя в работе и какие подходы можете применять используя pgCenter. И самый основной кейс – это проверка все ли в порядке с базой и нет ли там каких-то проблем.

И здесь мы все делаем, как по известному USE методу. Нам нужно определить использование ресурсов. Если ресурсы используются более-менее хорошо и прекрасно, то мы смотрим наличие ошибок.

Для этого мы начинаем смотреть системную информацию. У нас есть информация об использовании процессора. Всем, кто знаком с утилитой линуксовой Top, вот этот раздел статистики будет очень хорошо знаком. Он показывает утилизацию процессора: системную, пользовательскую и т.д.

Если нам интересно посмотреть память, то в этой же строке также мы можем посмотреть и память: сколько у нас есть памяти вообще, сколько свободно, сколько занято.

И, соответственно, swap. Если в системе есть swap, а для базы данных он важен, то можно посмотреть еще и статистику по swap. Таким образом в части по системной статистике можно быстро определить – есть ли у нас проблема с утилизацией ресурсов.

Кто-то может спросить: «А как с блочным вводом-выводом и сетью?». Эта статистика тоже есть. Ее я покажу чуть позже, по ней тоже есть цифры.

Когда мы посмотрели нет ли у нас проблем с утилизацией ресурсов, мы можем идти к проверке – нет ли у нас каких-либо ошибок на уровне работы Postgres. Мы можем посмотреть uptime. Вообще, uptime в Postgres – не совсем честный, но тем не менее это лучше, чем совсем ничего. С аптаймом можно быстро определить, как давно у нас работает Postgres и не было ли у него незапланированных рестартов.

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

И, конечно, автовакуум. Я думаю, многие из вас знают, что такое автовакуум. С ним связано много интересных историй, поэтому про вакуум тоже можно посмотреть. Например, сколько воркеров запущено и посмотреть их длительность. И после этого можно как-то реагировать на эту информацию.

И долгие транзакции, потому что Postgres MVCC база данных. В ней MVCC движок. И он очень сильно зависит от того, как долго там работают транзакции. Поэтому самые долгие транзакции тоже важно отслеживать и быстро о них узнавать.

На этом слайде было переключение на статистику баз данных с помощью горячей клавиши ‘d’.

Говоря про ошибки, важно отметить pg_stat_database которая предоставляет некоторое количество информации об ошибках, тут нас интересует поле «rollbacks». Это поле не только про команду «ROLLBACK», но еще и про различные ошибки которые привели к отмене запроса/транзакции. Это могут быть ошибки нарушения ограничений (constraints), это могут быть ошибки синтаксиса и т. п. По этой статистике можно уже отследить, что происходит в базе.

И плюс в pg_stat_database есть еще информация по конфликатм репликации (conflicts) и взаимоблокировкам (deadlocks). По сути, это тоже виды ошибок, которые говорят о том, что в базе что-то идет не так.

ОК, мы запустили pgCenter. И за относительно короткое время мы смогли посмотреть много вещей, ради которых нам бы пришлось запустить несколько утилит. Во-первых, это:

top
vmstat
iostat
nicstat
pg_stat_activity
pg_stat_statements

Плюс там еще использовали некоторые функции, которые тоже показывают информацию в более понятном виде.

Допустим, что в процессе проверки мы обнаружили, что у нас какая-то есть нагрузка на процессор.

Вот такой простой пример. Вам не обязательно все это рассматривать, важно отследить те места, которые используются в процессе оценки. Т. е. здесь CPU usage – 85 %. Это говорит о том, что у нас нагрузка на процессоры довольно-таки высокая. И нам нужно найти, кто же так активно использует процессора. Понятно, что это Postgres. Нам нужно заглянуть глубже в Postgres и посмотреть, какие типы запросов у нас больше всего потребляют процессорного времени.

Если мы посмотрим на вторую часть экрана, то мы увидим, что у нас 38 активных клиентов, которые что-то делают. При этом нужно посмотреть на соседние state: на waiting и на idle_xact. Waiting у нас 0, т. е. у нас клиенты не находятся в режиме ожидания и это хорошо. С другой стороны, у нас есть 20 idle транзакций. Соответственно, мы можем включить сортировку по длительности транзакций (xact_age) и посмотреть – сколько времени наши транзакции находятся в простое. И здесь видно, что простаивает всего одна транзакция. И ее время жизни – 15 секунд. Это не страшно, и в большинстве случаев это можно не считать криминалом.

(Примечание: На слайде переключились в pg_stat_statements. Чтобы переключится в pg_stat_statements необходимо нажать «x», там будут нужные столбцы. Второй вариант «shift + x» и в меню выбрать pg_stat_statements_timings. Если будет ошибка «pg_stat_statements not available on this database», то нужно установить расширение pg_stat_statements от юзера postgres в базу postgres: create extension pg_stat_statements;)

Но мы же ищем источник, кто у нас потребляет больше всего (процессорного) времени. Нам нужно оценить, какие запросы используют больше всего времени. Для этого мы используем pg_stat_statements. Это contrib. Он показывает нам статистику по запросам: сколько они выполнялись, сколько ресурсов потребляли. Этот contrib должен быть установлен в базе, чтобы брать с нее статистику. К сожалению, он выключен по-умолчанию. И одна из основных рекомендаций по настройке Postgres – включать pg_stat_statements.

Предположим, что он у нас стоит. Нам нужно посмотреть время, кто у нас тратить больше всего. Мы стрелочками переключаем сортировку. И видим те запросы, которые у нас тратили больше всего CPU с момента сброса статистики pg_stat_statements. Тут отражен примерно суточный разрез – за сутки конкретный тип запроса отработал 2 часа с лишним. Это запрос SELECT COUNT (*) FROM "game_competition_events". Т. е. уже имея на руках запрос, мы можем сходить в логи, взять параметры этого запроса и посмотреть, какой у него план, и попытаться с ним разобраться. Может быть, там нет какого-нибудь индекса, может быть, там запрос написан неоптимальный или еще что-то. Уже у нас есть конкретная информация о том, кто потребляет процессорное время.

Но здесь есть небольшая ловушка. Мы используем сортировку под total_time. А в total_time включается не только процессорное время, но еще и время, потраченное на операции блочного ввода/вывода: на чтение и на запись. Соответственно, нам желательно включить сортировку по полю «t_cpu_t». Оно нам более релевантно. Оно нам позволяет смотреть именно процессороемкие запросы.

Как я уже сказал, эта статистика показывает самые жадные до ресурсов запросы с момента сброса статистики. Если нам нужно смотреть запросы, которые отнимают процессорное время здесь и сейчас, то мы смотрим уже по полю «cpu_t», это, условно говоря, дельта. Мы берем snapshot статистики за прошлую секунду, за текущую секунду, считаем дельту и показываем. Здесь запрос уже совершенно другой. Это SELECT "courses_logs".* FROM course_logs. И здесь видно, что текущую секунду он съел уже 5 секунд процессорного времени. Это либо запрос, который использует параллельные воркеры, либо, возможно, он просто запускается слишком часто.

И если посмотреть на соседнюю колонку «calls», то там будет видно, что запрос выполняется один. Один запрос в секунду. Т. е. это запрос с параллельными воркерами.

Пока мы все это смотрели, мы могли использовать другие утилиты. Это Top и плюс нам нужно было заглянуть в pg_stat_activity и в pg_stat_statements. Но с помощью pgCenter это все в одном месте собрано и можно этим пользоваться.

Другой вариант – это нагрузка на ввод-вывод. Это другая противоположность, когда с процессорами у нас все в порядке, но диски слабые и нужно разобраться, кто утилизирует ввод-вывод.

Ситуация похожа на предыдущий случай. Мы смотрим на утилизацию процессоров и видим, что у нас утилизация процессоров на время ожидания блочного ввода-вывода довольно высокое – 27 %. Нам нужно найти те запросы, которые вызывают этот ввод-вывод.

Плюс мы можем еще обратить внимание на то, что многие клиенты с типом «background worker». Это явный показатель, что у нас параллелизм включен и запрос выполняется параллельно.

Посмотрим по соседним «wait_event». И тут видно, что эти клиенты находятся в ожидание ввода-вывода. Т. е. очень много времени тратится на чтение данных с диска.

И здесь нам уже понадобится статистика по блочному вводу-выводу. С помощью горячей клавиши ‘B’ мы включаем встроенный iostat. И он нам показывает утилизацию дисковых устройств. Здесь видно, что утилизация одного из устройств 99 %. Но здесь самое главное – это не попасть в ловушку, потому что устройство у нас NVME. И нужно уже смотреть не только на утилизацию, но и на latency.

Если посмотреть на latency, то latency для этого устройства будет составлять всего лишь 1 миллисекунду. И это вполне нормально.

Это значит, что у нас нет особых проблем в производительности. Это связано с тем, что современные SSD и NVME-устройства выполняют операции ввода-вывода в несколько потоков, поэтому мы можем видеть большую утилизацию, но при этом низкий latency. Если мы видим большие цифры по latency, то это значит, что у нас действительно уже есть проблемы и нужно что-то делать.

Но тем не менее давайте смотреть, какие запросы выполняют больше операций ввода-вывода. Мы переключаемся на pg_stat_statements и смотрим уже не процессорное время, а время ввода-вывода. Это колонка «t_read_t», т. е. время, потраченное на чтение данных с момента сброса статистики.

Аналогичная колонка есть и для просмотра статистики за последнюю секунду. Это колонка «read_t». Мы можем менять сортировки и смотреть, какие запросы за весь интервал времени сожрали больше всего ввода-вывода, либо за последнюю секунду.

Важно отметить что статистика по ввод/выводу собирается только при включенном track_io_timing.

И уже имея текст запроса мы можем переходить к его поиску в логах, найти его параметры и узнать, что там долго там работает. Но pgCenter еще предоставляет такую штуку как queryid. Это такой идентификатор запроса. Но это не такой идентификатор, который предоставляется в pg_stat_statements. Он немного другой. Его можно использовать для построения отчетов. Т. е. pgCenter предоставляет такую функцию как построение отчетов по конкретной группе запросов. Также через горячие клавиши мы смотрим по queryid. И pgCenter предоставляет отчет.

Отчет состоит из трех частей:

Первая часть – это summary, общая картина составленная на основе той статистики, которая накопилась в pg_stat_statements. Это количество запросов, затраченное время в процессорах, затраченное время ввода-вывода.
Вторая секция уже связана уже с нашим запросом, эта секция описывает, какой вклад запрос вносит в общую статистику в summary. И мы уже можем видеть статистику связанную с этим запросом относительно всех остальных запросов.
И, конечно, сам текст запроса.

Строя такие отчеты, мы можем быстро посмотреть, насколько наш запрос вносит нагрузку в суммарную картину.

И если рассматривать, что мы затронули под капотом, пока это все смотрели, то все это покрывается утилитами top, iostat и представлениями pg_stat_activity, pg_stat_statements. Плюс там есть еще несколько функций, которые приводят все это в понятный вид.

Но запросы клиентские – это не единственная вещь, которая позволяет генерировать ввод-вывод. И в Postgres есть еще всякие фоновые задачи, которые тоже могут создавать нагрузку на диск.

Это:

Checkpointer pocess.
WAL writer process.
Autovacuum workers.
Background workers.

На данный момент pgCenter показывает только прогресс по вакууму, по остальным пока информации нет, но тем не менее это уже хорошо.

Предположим, что у нас с ресурсами все в порядке: блочного ввода-вывода никто особо не потребляет, с процессорами тоже полный порядок. И мы переходим к вопросу, что нужно посмотреть, что у нас на уровне ошибок.

И чаще всего это описывается ситуациями, когда клиент пишет в чат, что у него ничего не работает. Всё лежит и нужно что-то делать.

Здесь мы мельком посмотрели утилизацию ресурсов.

И тут нужно уже смотреть на состояние подключенных клиентов. Если посмотреть на клиентов, то будет видно, что у нас 22 активных клиентов и при этом 21 из них находится в режиме ожидания. Это уже показатель того, что что-то работает не так.

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

Нужно посмотреть на соседнее поле, которое показывает транзакции в режиме простоя (idle in transaction). И здесь мы видим, что их 6 штук. И нужно включить сортировку по времени работы транзакции.

Если посмотреть на отсортированное поле, то мы увидим, что у нас есть 10-минутная транзакция, которая ничего не делает в данный момент. Если мы посмотрим ниже, то есть еще куча транзакций, которые 7 минут находятся в ожидании. И они явно выстроились как раз в хвост за 10-минутной транзакцией.

Если посмотреть на wait_etype, wait_event этой транзакции, которая ничего не делает, то мы увидим, что она ждет как раз ожидания ввода со стороны клинского приложения (Client:Client Read). Приложение открыло транзакцию, что-то поделало, а потом ушло делать какую-то другую работу. И, возможно, где-то произошла ошибка, приложаени упало в том участке кода, но транзакция осталась незакрытой. Пришли другие транзакции и попытались обновить другие строки и прочитать данные, которые изменила эта транзакция, но попали в заблокированное состояние и теперь они все ждут завершения транзакции.

Самое просто решение – это отменить эту транзакцию. Есть две функции: pg_cancel_backend и pg_terminate_backend. Они позволяют отменить запрос, либо просто завершить работу этого backend. В pgCenter тоже есть эти функции. Можно с помощью горячих клавиш убивать как отдельный backend и запросы на основе pid, либо убивать их группами на основе маски.

Тем не менее под капотом здесь у нас:

Pg_stat_activity.
Pg_stat_statements.
Pg_cancel_backend ().
Pg_terminate_backend ().

Опыт показывает, что ситуации бывают разные. Бывает, не только, что собрался хвост из длинных транзакций.

Бывает долгая транзакция на таблице с очередью. Тот случай когда очередь реализована не отдельным брокером сообщений, а реализованы непосредственно в базе данных. У нас есть какая-то таблица. В нее вставляется много записей, также много строк обновляется и много строк удаляется (добавилось событие, изменился его статус, удалилось событие). Пришла какая-то долгая транзакция, которая поработала с этой таблицей, но также она перешла в состояние idle transaction и ничего не делает. И у нас также собрался длинный хвост из блокировок и все повисло — очередь перестала работать.

Другой кейс – это когда приложение в несколько потоков пытается обновлять одни и те же данные. И эти потоки начинают конфликтовать друг с другом, в результате возникают ситуации блокировок и взаимоблокировок (deadlocks) и все начинает работать совсем плохо.

Миграции. Можно сделать ALTER TABLE, добавление колонки, например, с простановкой дефолтных значений. Это очень тяжелая операция. Ее, к счастью, исправили в 11-ой версии и начиная с нее проставление дефолтов при добавлении поля работает безболезненно. Но тем не менее у многих заказчиков стоят старые версии Postgres, которые работают по старому. И любой такой тяжелый ALTER может также собрать на себе хвост ждущих транзакций и остановить работу приложения.

И классика жанра – это CREATE INDEX без CONCURRENTLY, когда кто-то по незнанию, либо просто забыл, что запустил создание индекса. Создание индекса заблокировало таблицу и появился снова хвост из блокировок.

Сейчас немного про репликацию, т.к. сегодня сложно представить, чтобы в production был сервер Postgres без реплики, поэтому бывает необходимость проверить репликацию и все ли с ней в порядке.

Для этого есть представление pg_stat_replication. Она показывает клиентов, подключенных к Postgres, которые принимают журнал транзакций с этого узла по протоколу репликации.

И pgCenter тоже поддерживает pg_stat_replication. И можно переключиться с помощью горячих клавиш, и посмотреть, что там происходит.

В данном случае у нас здесь 5 клиентов. Они все подключены и принимают журнал транзакций.

Если посмотреть на их имена, то можно будет понять, кто это такие и что они делают. У нас здесь 2 walreceiver, т. е. это конкретно 2 реплики.

И дальше нас интересует, какой лаг репликации у этих клиентов, потому что лаг репликации непосредственно влияет на величину проблемы, которая у нас есть. Если маленький лаг, значит, более-менее все в порядке. Большой лаг, значит, проблемы есть – реплика сильно отстает по каким-то причинам и нам нужно выяснить по каким.

Соответственно, pg_stat_replication предоставляет разную информацию, которая позволяет нам посчитать лаг в байтах и лаг в секундах. И здесь лаг у одной из реплик на уровне 1,5 GB. И replay_lag в секундах – 2 часа. На самом деле это нормальная реплика. Она просто настроена с отложенным восстановлением журнала транзакций. У нее выставлено восстановление на уровне 2-х часов. Она скачивает все журналы к себе и воспроизводит их с задержкой в 2 часа, т. е. это вполне нормальная ситуация.

Если мы посмотрим на других клиентов, то будет видно, что у нас есть 2 pg_basebackup и 1 pg_receivewal. Pg_basebackup – это резервное копирование которое также работает по протоколу репликации. Он также виден в pg_stat_replication. И pg_receivewal – это процесс, который принимает журналы транзакций и сохраняет для задач архивирования. Т. е. здесь, в принципе, никакой проблемы нет. Здесь нет каких-то криминальных реплик, которые нужно было бы расследовать.

Но тем не менее pg_stat_replication позволяет показывать лаг в нескольких единицах измерениях. Это байты. И самое интересное, что этих метрик здесь аж 5 штук. Это: pending, write, flush, replay, total_lag. Т. е. лаг репликации может быть разным.

Pending – это когда журнал транзакций сгенерировался, лежит на Мастере. И Мастер его еще не успел передать реплике.

Write – это когда передача журналов уже идет, но до реплики еще не дошла, т. е. она еще не успела записаться.

Flush – это когда успели записать уже на реплику, но не успели сбросить на надежное хранилище.

Replay – это когда сбросили на надежное хранилище. И осталось только проиграть.

Total_lag – это максимальная величина от момента генерации до момента проигрывания.

Соответственно, наблюдая лаг в этих местах, в этих контрольных точках, мы можем более-менее понять, где у нас проблема. Например, проблема на дисковой подсистеме Мастера; либо ошибки сети, которые снижают скорость передачи; либо это загруженная дисковая подсистема реплики, которая не успевает все это писать, синхронизировать с диском и воспроизводить.

Кроме того, есть лаг репликации во времени. Он более человекопонятный. Когда людям говоришь про минуты, про секунды, они это лучше воспринимают.

И последний момент – это лаг репликации в транзакциях, т. е. можно отследить величину – сколько транзакций нужно проиграть реплике, чтобы она догнала Мастера. Эта штука по умолчанию выключена в Postgres, ее нужно включать отдельно. Но она редко бывает нужна, только в каких-то особых случаях.

Под капотом этой всей диагностики у нас:

pg_stat_replication.
pg_wal_lsn_diff().
pg_current_wal_lsn().
pg_last_commited_xact().

Я вам рассказал все эти кейсы, но за кадром есть еще много других вещей.

Например, в top можнос смотреть статистику по таблицам. Табличные статистики – это все Seq Scan, количество update, delete, insert, живые и мертвые строки.

Статистика по индексам. Можно посмотреть утилизацию индексов. Отыскивать неиспользуемые индексы и их заносить в черный список и потом удалять.

Статистика по функциям. Можно смотреть, какие пользовательские функции запускаются больше всего, сколько времени они потребляют. Можно также сортировать, смотреть и выбирать кандидата для оптимизации.

И, конечно, pg_stat_progress_vacuum появился в 9.6. Раньше, в плане статистики, вакуум был черным ящиком, было сложно понять, как долго работает, как скоро он закончится и сколько ему еще работы надо делать. И pg_stat_progress_vacuum – это способ заглянуть в этот черный ящик и понять что там происходит. Можно оценить, сколько ему там осталось доработать. Хотя, конечно, есть недостатки, есть претензии к нему. Но тем не менее лучше, чем ничего.

И есть вспомогательные, админские функции для самого администратора. Это просмотр логов, просмотр и изменение конфигурации, т. е. мы можем через горячие клавиши открыть postgresql.conf, что-то в нем поправить и потом горячей клавишей сделать reload. Это не самая правильная практика, конечно, но тем не менее возможность есть.

Плюс есть функции по просмотру логов. Вам не нужно помнить, где же лежит лог, как он там называется и набирать руками путь до него. Нажимает хоткей и лог открывается в просмотрщике. Можно найти там нужный запрос с нужными параметрами, скопировать и дальше изучать его.

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

top-просмотрщик – это основная утилита, которая развивалась изначально в pgCenter. Но помимо top есть еще другие утилиты, которые тоже являются частью pgCenter. Это record и report.

Суть в том, что мы делаем мгновенные снимки статистики и сохраняем их в файле. Мы можем запускать сбор с нужным интервалом и эти снимки будут сохраняться. А потом с помощью report строить отчеты, аналогичные тому, что показывает Top. В некоторых ситуациях это бывает полезно — например отсутствие полноценого мониторинга.

Я использую это для своих микро-тестов. Когда мне нужно что-то потестировать, я запускаю record. Раз в секунду он там все записывает и мне не нужно ставить никаких мониторинговых агентов. Я могу это все так быстро на коленке посмотреть.

Плюс недавно я добавил сэмплер wait_event’ов, можно брать свои долгие запросы и смотреть, на каких участках запрос тратит свое время на ожидание.

Вот простой пример: SELECT всех строк из таблицы с последующей сортировкой. Если посмотреть, куда тратится время, то видно, что 44 % времени запрос выполняется, делает какую-то полезную работу, а оставшееся время – это ожидание ввода-вывода при чтении файлов, взаимодействие между параллельными воркерами.

Второй пример: это VACUUM FULL. Здесь видно, что большую часть времени VACUUM FULL работает с дисковым IO и читает данные, а работает он всего 12 %. Вот эта штука довольно полезная, когда есть любопытство попрофилировать свои долгие запросы и посмотреть, чем они занимаются и в каких местах проводят время в ожидании.

Вопросы

Спасибо за утилиту! Лично я ею пользуюсь. Она мне нравится. Я часто использую ее с ноутбука. И когда я смотрю, например, pg_stat_statements, у меня колонка query с текстом не влезает. Есть ли какая-то возможность поменять порядок столбцов или какие-то столбцы отключить на время?

К сожалению, такой функции нет. Архитектурно программа так устроена, что эта фичу тяжело запилить, но можно, наверное, что-то придумать там, переписать и такая функция появится. Как минимум, не перестановку колонок, а отключение-включение по желанию. Но отсюда вылезает второй вопрос. Наверняка кто-то захочет сохранить отображение этих колонок и при последующем запуске показать. Т. е. появится необходимость конфига, который нужно поддерживать, хранить где-то. Это такая задачка, которая собирает еще другие задачки, чтобы ее реализовать. Я думаю, что это можно сделать, но пока этого нет.

И второй комментарий из той же оперы. Когда режим базы данных, то у вас количество rollbacks. Иногда хотелось бы отличать rollbacks, которые были по команде от тех rollbacks, которые не по команде.

Это, к сожалению, невозможно, потому что сам интерфейс pg_stat_database не позволяет такого. Он показывает только rollbacks и все.

Там можно из pg_stat_statements считать rollbacks, вычитать.

Можно, если заморочиться. Можно расширить запрос. Мы делаем `SELECT FROM pg_stat_database JNOIN pg_stat_statements` под запрос к pg_stat_statements, где мы считаем rollbacks и плюс арифметика, которая это все высчитывает. Теоретически можно. Но нужно посмотреть, как быстро будет работать этот запрос и не займет ли выполнение больше 10-20 миллисекунд.

Алексей, спасибо за доклад! С какой минимальной версией Postgres ваша утилита работает?

Я ее тестировал с версии 9.0-9.1, когда еще на C писал. Если какие-то запросы, особенно связанные с репликацией, не работают, то она пишет небольшую ошибку и есть возможность переключится на другой скрин, на другую статистику. Go версию тестировал, начиная с 9.4. Потому что в 9.4 появился функционал, когда мы берем SELECT… where filter. Это такой синтаксис интересный. В старых версиях (9.3) его нет. А у меня часть запросов как раз используют этот синтаксис и в старых версиях это работать не будет. Либо это будет работать, но будет показывать нули там, где эта статистика используется. Но я стараюсь на всех версия тестировать, проверять, чтобы, как минимум, ошибок не было.

Алексей, спасибо за утилиту! И спасибо за то, что интерфейс там от TOP. За это двойное спасибо.

Более того, я старался горячие клавиши делать, похожими на другие утилиты. Например, кнопка фильтра – это слеш. Кто знаком с less (пейджер такой), то слеш – это поле ввода, чтобы набрать шаблон для поиска.

У меня вопрос по поводу сортировки. Там так же как в TOP подсветка поля идет сортируемого?

Да. У меня на скриншотах это не видно, я, видимо, упустил этот момент. На некоторых скриншотах видно, на некоторых не видно. Да, поле подсвечивается. Когда вы стрелками перемещаетесь, вы видите, что у вас сортировка меняется и имя в колонке подсвечивается.

Спасибо за утилиту! Сегодня в первый раз о ней узнал. Оказывается, столько много возможностей. Вы размышляете, что вот у меня 85% CPU usage, давайте проанализирую, что сейчас происходит. Для этого пойду и обращусь к временным снимкам pg_stat_statements. Но там же архив находится. А нас интересует, что сейчас происходит.

Не архив. В нагрузке на CPU есть два поля. Первое – t_all_t. Это сколько времени намотал запрос со времени сброса статистики pg_stat_statements. Условно, у нас суточный срез. Если мы раз в сутки сбрасываем статистику, то мы получим статистику за текущий момент от начала суток. Плюс есть разбивка t с подчеркиванием и они уже здесь заканчиваются. Т. е. у нас есть аналогичные поля без префикса «t». Они как раз нам показывают статистику за текущую секунду. Т. е. мы можем смотреть, сколько процессорного времени потрачено запросом прямо сейчас.

Это понятно. Но если сейчас происходит то, что еще нет в pg_stat_statements, то как мы это проанализируем?

Мы каждую секунду выходим к pg_stat_statements и берем снапшот статистики прямо в real time.

Вопрос был в том, что в pg_stat_statements залетит уже после, а цифра 85 CPU usage – она сейчас.

Да, расхождение статистики будет. Но вы все равно можете смотреть текущую статистику, которую вам pg_stat_statements показывает. Если вы видите, что у вас использование процессора упало, то вы уже не увидите тот срез статистики, который был 10 секунд назад, когда использование процессоров было высокое. Тут нет такой интроспекции на 10 секунд назад – запомнить и отмотать, как это сделано, например, в atop. Вы на atop намекаете?

Примерно, да. Тут некое будущее и прошлое pg_stat_statements, а CPU, которая сейчас, нужно потом проанализировать, что залетит в pg_stat_statements.

Для этого придумана система мониторинга. Например, Grafana, там есть отрисовка графиков и вы уже в исторической перспективе все эти графики смотрите и анализируете. Т. е. pgCenter – это инструмент, который нам нужен здесь и сейчас, чтобы быстро продиагностировать, быстро понять, что происходит.

В связи с этим же вопрос. pgCenter умеет как atop сбрасывать статистику в текстовом виде, чтобы потом можно было запихнуть в Grafana?

Есть функции pgCenter report и pgCenter record, т. е. можно снапшоты статистики сбрасывать в текстовые файлы. Единственное, что там нет интерфейса, как по стрелочкам переключаться и смотреть. Т. е., условно говоря, с помощью pgCenter report запросить нужную статистику, например, по pg_stat_database и он прочитаем там все файлы накопленной статистики и как sar покажет. Я вдохновлялся больше им. Нет такого как у atop, когда можно стрелочками работать.

Алексей, большое спасибо! В отличие от всей известной базы Х, в Postgres, к сожалению, нет, кумулятивных статистик ожиданий. Вы сделали профайлер, который показывает разброску по ожиданиям. А с какой частой вы их опрашиваете? Какая там детализация?

Дефолтный интервал в 10 миллисекунд. А через флажок «-f», можно указывать частоту детализаций. Понятно, что высокая частота детализации, например, в 10 миллисекунд тоже создает нагрузку на систему. Но учитывая, что pg_stat_activity в памяти, то запросы там довольно легковесные. Они доли миллисекунд занимают. Но если такая частота напрягает, можно менять. Поставить, например, раз в 50 миллисекунд. Я замерял, как отражается это на конечной статистике, там погрешность есть на уровне 1-2 %. Т. е. если просуммировать все столбики, то мы увидим, что у нас куда-то 0,5-2 % потерялось.

Там в конце суммарная статистика есть. Можно видеть, что что-то потерялось.

Да, там видно будет. Да, даже на нашем примере мы видим, что 0,04 % не учли. Но, я думаю, это не критично. Это не какой-то инструмент для хардкор аналитики.

Но лучше все равно ничего нет.

Интересно просто посмотреть, что там у нас происходит.

Алексей, спасибо за доклад! Вдогонку вопрос по профайлеру. Вот этот wait_event Running – это просто отсутствующий?

Да.

Я его воспринимаю как ожидание на CPU.

Да, именно так. Т. е. когда мы заметили, что wait_events у этого PID нет, то мы считаем, что backend делает какую-то полезную работу, прямо крутится на процессоре, что-то высчитывает. И мы закидываем в Running, типа он работает, т. е. он не находится в ожидании.

Но все же это ближе к CPU?

Да, это ближе к CPU, т. е. запрос делает какую-то работу. Это не ожидание.

Привет! Спасибо за доклад! Насчет пакетирования есть какие-то планы, например, Ubuntu?

Когда она была написана на C, то все майнтейнеры были радостные. Говорили, что круто, сейчас тебе пакетов насобираем. И в официальном репозитории PDGD были пакеты собраны для Ubuntu. Я на Launchpad собирал пакеты, но у них какая-то странная система сборки. Бинарник иногда сегфолтится. И я не мог понять, почему так. А сейчас на Go у меня просто Мастер-ветка, dev-ветка. В Мастере она релизы отсчитывает. И когда я делаю коммит с релизом, то travis-ci не только делает build, он еще делает build бинарника и выкатывает его в раздел релизов. Т. е. если посмотреть в Realeses, то туда релизы будут сваливаться. Вам остается только взять wget, сходить по ссылке, забрать и распаковать tar’ом архив, и можно будет пользоваться.

Есть проект Goreleaser, который позволяет автоматизировать это. И можно собирать пакеты.

Круто, я с GO не очень знаком. Я еще раз повторяю, я не профессиональный программист. Я не знаю, что такое SOLID. И то, что вы говорите, что Goreleaser есть, это интересная штука, я посмотрю, что это такое. Раньше C’шные исходники у меня собирались, и я был счастлив. А сейчас мне приходится всем говорить, что есть ссылка на Realeses. Спасибо за совет!

Updated. Goreleaser успешно добавлен, большое спасибо за идею!

Алексей, вопрос по поводу queryid. Мы там видим queryid. У вас получается, что там поле немножко урезано и мы хвост не видим. Мы можем его полностью увидеть?

Да, конечно. Если мы не будем обрезать названия, то у нас в какой-то момент ширина колонок будет прыгать. И это для глаз не очень хорошо, плохо воспринимается. Поэтому ширина колонок рассчитывается под какую-то величину. Величина рассчитывается по сложному кейсу. Но в итоге ширина колонок ужимается и не прыгает. Если мы хотим ее увеличить, то мы стрелочкой переходим на это поле через сортировку. А потом стрелкой вверх увеличиваем ширину. А стрелкой вниз можем уменьшить ширину. И она сохраняется. Вы потом дальше можете сортировку менять, у вас ширина поля останется той, какой вы задали.

Ясно, это важный момент, потому что queryid – это четкий адрес.

Да, изначально ширина колонок прыгала и это раздражало. Я в dev-ветке это поправил, а в Мастер-ветке этого пока нет. В середине февраля я хочу выпустить Event Profiler. И как раз фиксированная ширина колонок будет.

Замечательно.

Да, через стрелки можно регулировать ширину.

Спасибо, Алексей!

Спасибо большое вам!

Видео:

Источник

pg_verifybackup — verify the integrity of a base backup of a PostgreSQL cluster

Synopsis

pg_verifybackup [option…]

Description

pg_verifybackup is used to check the integrity of a database cluster backup taken using pg_basebackup against a backup_manifest generated by the server at the time of the backup. The backup must be stored in the «plain» format; a «tar» format backup can be checked after extracting it.

It is important to note that the validation which is performed by pg_verifybackup does not and cannot include every check which will be performed by a running server when attempting to make use of the backup. Even if you use this tool, you should still perform test restores and verify that the resulting databases work as expected and that they appear to contain the correct data. However, pg_verifybackup can detect many problems that commonly occur due to storage problems or user error.

Backup verification proceeds in four stages. First, pg_verifybackup reads the backup_manifest file. If that file does not exist, cannot be read, is malformed, or fails verification against its own internal checksum, pg_verifybackup will terminate with a fatal error.

Second, pg_verifybackup will attempt to verify that the data files currently stored on disk are exactly the same as the data files which the server intended to send, with some exceptions that are described below. Extra and missing files will be detected, with a few exceptions. This step will ignore the presence or absence of, or any modifications to, postgresql.auto.conf, standby.signal, and recovery.signal, because it is expected that these files may have been created or modified as part of the process of taking the backup. It also won’t complain about a backup_manifest file in the target directory or about anything inside pg_wal, even though these files won’t be listed in the backup manifest. Only files are checked; the presence or absence of directories is not verified, except indirectly: if a directory is missing, any files it should have contained will necessarily also be missing.

Next, pg_verifybackup will checksum all the files, compare the checksums against the values in the manifest, and emit errors for any files for which the computed checksum does not match the checksum stored in the manifest. This step is not performed for any files which produced errors in the previous step, since they are already known to have problems. Files which were ignored in the previous step are also ignored in this step.

Finally, pg_verifybackup will use the manifest to verify that the write-ahead log records which will be needed to recover the backup are present and that they can be read and parsed. The backup_manifest contains information about which write-ahead log records will be needed, and pg_verifybackup will use that information to invoke pg_waldump to parse those write-ahead log records. The --quiet flag will be used, so that pg_waldump will only report errors, without producing any other output. While this level of verification is sufficient to detect obvious problems such as a missing file or one whose internal checksums do not match, they aren’t extensive enough to detect every possible problem that might occur when attempting to recover. For instance, a server bug that produces write-ahead log records that have the correct checksums but specify nonsensical actions can’t be detected by this method.

Note that if extra WAL files which are not required to recover the backup are present, they will not be checked by this tool, although a separate invocation of pg_waldump could be used for that purpose. Also note that WAL verification is version-specific: you must use the version of pg_verifybackup, and thus of pg_waldump, which pertains to the backup being checked. In contrast, the data file integrity checks should work with any version of the server that generates a backup_manifest file.

Options

pg_verifybackup accepts the following command-line arguments:

-e --exit-on-error: Exit as soon as a problem with the backup is detected. If this option is not specified, pg_verifybackup will continue checking the backup even after a problem has been detected, and will report all problems detected as errors.
-i path --ignore=path: Ignore the specified file or directory, which should be expressed as a relative path name, when comparing the list of data files actually present in the backup to those listed in the backup_manifest file. If a directory is specified, this option affects the entire subtree rooted at that location. Complaints about extra files, missing files, file size differences, or checksum mismatches will be suppressed if the relative path name matches the specified path name. This option can be specified multiple times.
-m path --manifest-path=path: Use the manifest file at the specified path, rather than one located in the root of the backup directory.
-n --no-parse-wal: Don’t attempt to parse write-ahead log data that will be needed to recover from this backup.
-q --quiet: Don’t print anything when a backup is successfully verified.
-s --skip-checksums: Do not verify data file checksums. The presence or absence of files and the sizes of those files will still be checked. This is much faster, because the files themselves do not need to be read.
-w path --wal-directory=path: Try to parse WAL files stored in the specified directory, rather than in pg_wal. This may be useful if the backup is stored in a separate location from the WAL archive.

Other options are also available:

-V --version: Print the pg_verifybackup version and exit.
-? --help: Show help about pg_verifybackup command line arguments, and exit.

Examples

To create a base backup of the server at mydbserver and verify the integrity of the backup:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
$ pg_verifybackup /usr/local/pgsql/data

To create a base backup of the server at mydbserver, move the manifest somewhere outside the backup directory, and verify the backup:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/backup1234
$ mv /usr/local/pgsql/backup1234/backup_manifest /my/secure/location/backup_manifest.1234
$ pg_verifybackup -m /my/secure/location/backup_manifest.1234 /usr/local/pgsql/backup1234

To verify a backup while ignoring a file that was added manually to the backup directory, and also skipping checksum verification:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
$ edit /usr/local/pgsql/data/note.to.self
$ pg_verifybackup --ignore=note.to.self --skip-checksums /usr/local/pgsql/data

Источник

Процедура от Ola Hallengren — DatabaseIntegrityCheck, позволяет проверить базу, файловую группу или таблицу. Если используется полноценный SQL Server, то можно использовать его в Maintains Paln. Если SQL Express, то можно использовать данную процедуру в планировщике, так как агент-SQL в этой редакции отсутствует. Проверка необходима перед архивацией базы. Нет смысла архивировать разрушенную базу!!! поэтому перед архивацией необходим проверить целостность данных. В качестве параметров можно указать список баз данных, время блокировки, объекты проверки и некоторые другие параметры. Данную процедуру удобно использовать при большом количестве баз данных или при обслуживании баз разработчиков, которые постоянно плодят новые ветки и заливают в новые базы. Полный список параметров и примеры использования ниже

Параметры

В качестве аргументов, отвечающих за выбор объектов проверки можно передать SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES и AVAILABILITY_GROUP_DATABASES, если поддерживается. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

Databases

Переменная	Описание
SYSTEM_DATABASES	Все системные базы (master, msdb, and model)
USER_DATABASES	Все пользовательские базы
ALL_DATABASES	Все базы
AVAILABILITY_GROUP_DATABASES	Все базы и группы доступности
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	Все пользовательские базы, исключая группы доступности
Db1	Ваза Db1
Db1, Db2	Базы Db1 и Db2
USER_DATABASES, -Db1	Все пользовательские базы, за исключением Db1
%Db%	Все базы, имеющие в названии Db
%Db%, -Db1	Все базы имеющие в названии Db, за исключением базы Db1
ALL_DATABASES, -%Db%	Все базы, за исключением тех, которые имеют в названии Db

CheckCommands

Использование данного параметра может ускорить скорость проверки путем исключения не нужных объектов или разделить задание на два этапа.

Переменная	Описание
CHECKDB	Проверяет логическую и физическую целостность всех объектов. По умолчанию
CHECKFILEGROUP	Проверяет распределение и структуру целостности всех таблиц и индексированных представлений в файловой группе
CHECKTABLE	Проверяет целостность всех страниц и структур, составляющих таблицу или индексированное представление
CHECKALLOC	Проверяет согласованность структур выделения места на диске
CHECKCATALOG	Проверяет согласованность каталогов в указанной базе данных
CHECKALLOC,CHECKCATALOG	Совмещает два параметра — проверку таблиц и представлений
CHECKFILEGROUP,CHECKCATALOG	Проверяет представления и таблицы для файловых групп
CHECKALLOC,CHECKTABLE,CHECKCATALOG	Проверяет параметры свободного места для таблиц и индексов, а также согласованность каталогов

При составлении команд использовалось описание для SQL Server: DBCC CHECKDB проверка баз данных, DBCC CHECKFILEGROUP проверка фаловых групп, DBCC CHECKTABLE проверка таблиц и индексных представлений, DBCC CHECKALLOC проверка свободного места в таблицах и DBCC CHECKCATALOG проверка согласованности каталогов.

PhysicalOnly

Ограничиться только физической проверкой структуры базы

Переменная	Описание
Y	Ограничиться только физической проверкой структуры базы
N	Не ограничиться только физической проверкой структуры базы. По умолчанию

Параметр PHYSICAL_ONLY использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

NoIndex

Не проверять не кластеризированные индексы

Переменная	Описание
Y	Не проверять
N	Проверять. По умолчанию

Параметр NoIndex использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

ExtendedLogicalChecks

Упрощенная проверка логической структуры

Переменная	Описание
Y	Провести простую проверку
N	Не проводить простую проверку. По умолчанию

Параметр ExtendedLogicalChecks использует опции следующих команд EXTENDED_LOGICAL_CHECKS и команды SQL Server DBCC CHECKDB

Нельзя комбинировать параметры PhysicalOnly и ExtendedLogicalChecks.

TabLock

Позволяет использовать внешний снапшот

Переменная	Описание
Y	Использовать блокировку, для проверки
N	Использовать снапшот. По умолчанию

Параметр TabLock использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

FileGroups

Выбор файловых групп. Данный параметр поддерживает выборку по словам. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_FILEGROUPS	Все файловые группы
Db1.FileGroup1	Файловая группа FileGroup1 в базе Db1
Db1.FileGroup1, Db2.FileGroup2	Файловая группа FileGroup1 в базе Db1 и фаловая группа FileGroup2 в базе Db2
ALL_FILEGROUPS, -Db1.FileGroup1	Все файловые группы, за исключением FileGroup1 в базе Db1
Db1.%FileGroup%	Все файловые группы в базе Db1 имеющие в своем имени “FileGroup”

Используются только специфические опции CHECKFILEGROUPS.

Objects

Выбор объектов. Параметр ALL_OBJECTS поддерживает выбор по маске. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_OBJECTS	Все объекты
Db1.Schema1.Tbl1	Объект Schema1.Tbl1 в базе Db1
Db1.Schema1.Object1, Db2.Schema2.Object2	Объект Schema1.Tbl1 в базе Db1 и объект Schema2.Tbl2 б базе данных Db2
ALL_OBJECTS, -Db1.Schema1.Object1	Все объекты, кроме Schema1.Object1 в базе Db1
Db1.Schema1.%	Все объекты в схеме Schema1 находящейся в базе Db1

Данный параметр можно использовать только совместно с параметром CHECKTABLE

MaxDOP

Количество ядер процессора выделенных для проверки базы, файловой группы или таблицы. Если данный параметр не задан, будут использоваться максимальное значение степени параллелизма.

Данный параметр может быть использован совместно с параметрами DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

AvailabilityGroups

выбор групп доступности. Параметр ALL_AVAILABILITY_GROUPS разрешает использование маски. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_AVAILABILITY_GROUPS	Все группы доступности
AG1	Группа доступности AG1
AG1, AG2	Группа доступности AG1 и AG1
ALL_AVAILABILITY_GROUPS, -AG1	Все группы доступности, за исключением группы AG1
%AG%	Все группы доступности содержащие в своем имени “AG”
%AG%, -AG1	Все группы доступности содержащие в своем имени “AG” за исключением группы AG1
ALL_AVAILABILITY_GROUPS, -%AG%	Все группы доступности не содержащие в своем имени “AG”

AvailabilityGroupReplicas

Выбор реплики для необходимости проверки.

Переменная	Описание
ALL	Проверяжтся все реплики. По умолчанию
PRIMARY	Проверяется только первичная реплика
SECONDARY	Проверяется только вторичная реплика

Updateability

Задает режим для базы данны READ_ONLY/READ_WRITE

Переменная	Описание
ALL	READ_ONLY и READ_WRITE — база данных. До умолчанию
READ_ONLY	READ_ONLY — только чтение
READ_WRITE	READ_WRITE — чтение запись

Переменная READ_ONLY в sys.databases используется если база в режиме READ_ONLY или READ_WRITE (Трудности перевода, пока не разбирался с данной переменной)

LockTimeout

Устанавливает время блокировки, после которого необходимо освободить захваченный объект. По умолчанию лимит отсутствует.

Параметр LockTimeout в хранимой процедуре IndexOptimize использует SET LOCK_TIMEOUT в терминологии SQL Server.

LogToTable

Логирование команд в таблицу dbo.CommandLog

Переменная	Описание
Y	Логировать команды в таблицу
N	Не логировать команды. По умолчанию

Execute

Выполнение команд. По умолчанию команды отправляются на выполнение. Если установлено значение N, то команды будут выведены без выполнения.

Переменная	Описание
Y	Выполнять команды. По умолчанию
N	Только вывеси команды

Примеры использования вложенной процедуры

A. Проверка целостности во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’

B. Проверка физической целостности данных во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@PhysicalOnly = ‘Y’

C. Проверка целостности данных во всех пользовательских базах, с использованием опции исключающей проверку не кластеризованных индексов

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@NoIndex = ‘Y’

D. Проверка целостности данных во всех пользовательских базах, с использованием опции упрощенной проверки логической структуры

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@ExtendedLogicalChecks = ‘Y’

E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘AdventureWorks.PRIMARY’

F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘ALL_FILEGROUPS, -AdventureWorks.PRIMARY’

G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘AdventureWorks.Production.Product’

H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘ALL_OBJECTS, -AdventureWorks.Production.Product’

I. Проверка дискового места во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKALLOC’

Выполнение

Для выполнения хранимой процедуры, необходимо использовать sqlcmd и опцию -b. Вызов процедуры происходит по имени

sqlcmd —E —S $(ESCAPE_SQUOTE(SRVR)) —d master —Q «EXECUTE dbo.DatabaseIntegrityCheck @Databases = ‘USER_DATABASES'» —b

Загрузка…

Источник

Media failure is one of the crucial things that the database administrator should be aware of. Media failure is nothing but a physical problem reading or writing to files on the storage medium.

A typical example of media failure is a disk head crash, which causes the loss of all files on a disk drive. All files associated with a database are vulnerable to a disk crash, including datafiles, wal files, and control files.

This is the comprehensive post which focuses on disk failure in PostgreSQL and the ways you can retrieve the data from PostgreSQL Database after failure(other than restoring the backup).

In this post, we are going to do archaeology on the below error and will understand how to solve the error.

WARNING: page verification failed, calculated checksum 21135 but expected 3252
ERROR: invalid page in block 0 of relation base/13455/16395

During the process, you are going to learn a whole new bunch of stuff in PostgreSQL.

PostgreSQL Checksum: The definitive guide
- But what is a PostgreSQL checksum?
- How does PostgreSQL checksum work?
- How PostgreSQL Checks Page Validity?
Let’s work on the practical understanding of whatever we learned so far.
- First, check whether the checksum is enabled or not?
  - Let me enable the page checksum in PostgreSQL v12.
  - To check the PostgreSQL page errors, we use the following command.
  - Let us dig deeper into the issue and confirm that the block is corrupted
How to resolve the PostgreSQL corrupted page issue?

PostgreSQL Checksum: The definitive guide

Chapter 1: What is a checksum?
Chapter 2: PostgreSQL checksum: Practical implementation
Chapter 3: How to resolve the PostgreSQL corrupted page issue?

With v9.3, PostgreSQL introduced a feature known as data checksums and it has undergone many changes since then. Now we have a well-sophisticated view in PostgreSQL v12 to find the checksums called pg_checksums.

But what is a PostgreSQL checksum?

When the checksum is enabled, a small integer checksum is written to each “page” of data that Postgres stores on your hard drive. Upon reading that block, the checksum value is recomputed and compared to the stored one.

This detects data corruption, which (without checksums) could be silently lurking in your database for a long time.

Good, checksum, when enabled, detects data corruption.

How does PostgreSQL checksum work?

PostgreSQL maintains page validity primarily on the way in and out of its buffer cache.

Also read: A comprehensive guide – PostgreSQL Caching

From here we understood that the PostgreSQL page has to pass through OS Cache before it leaves or enters into shared buffers. So page validity happens before leaving the shared buffers and before entering the shared buffers.

when PostgreSQL tries to copy the page into its buffer cache then it will (if possible) detect that something is wrong, and it will not allow page to enter into shared buffers with this invalid 8k page, and error out any queries that require this page for processing with the ERROR message

ERROR: invalid page in block 0 of relation base/13455/16395

If you already have a block with invalid data at disk-level and its page version at buffer level, during the next checkpoint, while page out, it will update invalid checksum details but which is rarely possible in real-time environments.

confused, bear with me.

And finally,

If the invalid byte is part of the PostgreSQL database buffer cache, then PostgreSQL will quite happily assume nothing is wrong and attempt to process the data on the page. Results are unpredictable; Some times you will get an error and sometimes you may end up with wrong data.

How PostgreSQL Checks Page Validity?

In a typical page, if data checksums are enabled, information is stored in a 2-byte field containing flag bits after the page header.

Also Read: A comprehensive guide on PostgreSQL: page header

This is followed by three 2-byte integer fields (pd_lower, pd_upper, and pd_special). These contain byte offsets from the page start to the start of unallocated space, to the end of unallocated space, and to the start of the special space.

The checksum value typically begins with zero and every time reading that block, the checksum value is recomputed and compared to the stored one. This detects data corruption.

Checksums are not maintained for blocks while they are in the shared buffers – so if you look at a buffer in the PostgreSQL page cache with pageinspect and you see a checksum value, note that when you do page inspect on a page which is already in the buffer, you may not get the actual checksum. The checksum is calculated and stamped onto the page when the page is written out of the buffer cache into the operating system page cache.

Also read: A comprehensive guide – PostgreSQL Caching

Let’s work on the practical understanding of whatever we learned so far.

I have a table check_corruption wherein I am going to do all the garbage work.

my table size is 8 kB.
has 5 records.
the version I am using is PostgreSQL v12.

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

postgres=# SELECT * FROM page_header(get_raw_page(‘check_corruption’,0));

——————+—————+————+————+————+—————+—————+—————+——————

0/17EFCA0 | 0 | 0 | 44 | 7552 | 8192 | 8192 | 4 | 0

(1 row)

postgres=# dt+ check_corruption

List of relations

————+—————————+————+—————+——————+———————

(1 row)

postgres=# select pg_relation_filepath(‘check_corruption’);

pg_relation_filepath

———————————

base/13455/16490

(1 row)

postgres=#

First, check whether the checksum is enabled or not?

[postgres@stagdb ~]$ pg_controldata -D /u01/pgsql/data | grep checksum
Data page checksum version: 0
[postgres@stagdb ~]$

It is disabled.

Let me enable the page checksum in PostgreSQL v12.

Syntax: pg_checksums -D /u01/pgsql/data –enable –progress –verbose

[postgres@stagdb ~]$ pg_checksums —D /u01/pgsql/data —enable —progress —verbose

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/2847»

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/1260_fsm»

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/4175»

23/23 MB (100%) computed

Checksum operation completed

Files scanned: 969

Blocks scanned: 3006

pg_checksums: syncing data directory

pg_checksums: updating control file

Data checksum version: 1

Checksums enabled in cluster

Again, check the status of checksums in PostgreSQL

[postgres@stagdb ~]$
[postgres@stagdb ~]$ pg_controldata -D /u01/pgsql/data | grep checksum
Data page checksum version: 1
[postgres@stagdb ~]$

we can disable the checksums with –disable option

[postgres@stagdb ~]$

[postgres@stagdb ~]$ pg_checksums —D /u01/pgsql/data —disable

pg_checksums: syncing data directory

pg_checksums: updating control file

Checksums disabled in cluster

[postgres@stagdb ~]$

Let’s first check the current data directory for errors, then play with data.

To check the PostgreSQL page errors, we use the following command.

pg_checksums -D /u01/pgsql/data –check

[postgres@stagdb ~]$ pg_checksums -D /u01/pgsql/data –check
Checksum operation completed
Files scanned: 969
Blocks scanned: 3006
Bad checksums: 0
Data checksum version: 1
[postgres@stagdb ~]$

Warning!! Do not perform the below case study in your production machine.

As the table check_corruption data file is 16490, I am going to corrupt the file with the Operating system’s dd command.

dd bs=8192 count=1 seek=1 of=16490 if=16490

[postgres@stagdb 13455]$ dd bs=8192 count=1 seek=1 of=16490 if=16490

Now, log in and get the result

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

I got the result, but why?

I got the result from shared buffers. Let me restart the cluster and fetch the same.

/usr/local/pgsql/bin/pg_ctl restart -D /u01/pgsql/data

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

But again why?

As we discussed earlier, during restart my PostgreSQL has replaced error checksum with the value of shared buffer.

How can we trigger a checksum warning?

We need to get that row out of shared buffers. The quickest way to do so in this test scenario is to restart the database, then make sure we do not even look at (e.g. SELECT) the table before we make our on-disk modification. Once that is done, the checksum will fail and we will, as expected, receive a checksum error:

i.e., stop the server, corrupt the disk and start it.

/usr/local/pgsql/bin/pg_ctl stop -D /u01/pgsql/data
dd bs=8192 count=1 seek=1 of=16490 if=16490
/usr/local/pgsql/bin/pg_ctl start -D /u01/pgsql/data

During the next fetch, I got below error

postgres=# select * from check_corruption;
2020-02-06 19:06:17.433 IST [25218] WARNING: page verification failed, calculated checksum 39428 but expected 39427
WARNING: page verification failed, calculated checksum 39428 but expected 39427
2020-02-06 19:06:17.434 IST [25218] ERROR: invalid page in block 1 of relation base/13455/16490
2020-02-06 19:06:17.434 IST [25218] STATEMENT: select * from check_corruption;
ERROR: invalid page in block 1 of relation base/13455/16490

Let us dig deeper into the issue and confirm that the block is corrupted

There are a couple of ways you can find the issue which includes Linux commands like

dd
od
hexdump

Usind dd command : dd if=16490 bs=8192 count=1 skip=1 | od -A d -t x1z -w16 | head -1

[postgres@stagdb 13455]$ dd if=16490 bs=8192 count=1 skip=1 | od -A d -t x1z -w16 | head -2
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.5e-05 seconds, 182 MB/s
0000000 00 00 00 00 a0 fc 7e 01 03 9a 00 00 2c 00 80 1d >……~…..,…<

here,

00 00 00 00 a0 fc 7e 01 the first 8 bytes indicate pd_lsn and the next two bytes

03 9a indicates checksums.

Using hexdump : hexdump -C 16490 | head -1

[postgres@stagdb 13455]$ hexdump -C 16490 | head -1
00000000 00 00 00 00 a0 fc 7e 01 03 9a 00 00 2c 00 80 1d |……~…..,…|
[postgres@stagdb 13455]$

Both hexdump and dd returned same result.

Let’s understand what our PostgreSQL very own pg_checksums has to say?

command: pg_checksums -D /u01/pgsql/data –check

[postgres@stagdb 13455]$ pg_checksums -D /u01/pgsql/data –check
pg_checksums: error: checksum verification failed in file “/u01/pgsql/data/base/13455/16490”, block 1: calculated checksum 9A04 but block contains 9A03
Checksum operation completed
Files scanned: 968
Blocks scanned: 3013
Bad checksums: 1
Data checksum version: 1
[postgres@stagdb 13455]$

here, according to pg_checksums checksum 9A03 is matching with that of hexdump’s checksum 9A03.

Converting Hex 9A03 to decimals, I got 39427

which is matching the error

2020-02-06 19:06:17.433 IST [25218] WARNING: page verification failed, calculated checksum 39428 but expected 39427

How to resolve the PostgreSQL corrupted page issue?

use the below function to find the exact location where the page is corrupted.

CREATE OR REPLACE FUNCTION

find_bad_row(tableName TEXT)

RETURNS tid

as $find_bad_row$

DECLARE

result tid;

curs REFCURSOR;

row1 RECORD;

row2 RECORD;

tabName TEXT;

count BIGINT := 0;

BEGIN

SELECT reverse(split_part(reverse($1), ‘.’, 1)) INTO tabName;

OPEN curs FOR EXECUTE ‘SELECT ctid FROM ‘ || tableName;

count := 1;

FETCH curs INTO row1;

WHILE row1.ctid IS NOT NULL LOOP

result = row1.ctid;

count := count + 1;

FETCH curs INTO row1;

EXECUTE ‘SELECT (each(hstore(‘ || tabName || ‘))).* FROM ‘

|| tableName || ‘ WHERE ctid = $1’ INTO row2

USING row1.ctid;

IF count % 100000 = 0 THEN

RAISE NOTICE ‘rows processed: %’, count;

END IF;

END LOOP;

CLOSE curs;

RETURN row1.ctid;

EXCEPTION

WHEN OTHERS THEN

RAISE NOTICE ‘LAST CTID: %’, result;

RAISE NOTICE ‘%: %’, SQLSTATE, SQLERRM;

RETURN result;

END

$find_bad_row$

LANGUAGE plpgsql;

Now, using the function find_bad_row(), you can find the ctid of the corrupted location.

you need hstore extension to use the function

postgres=# CREATE EXTENSION hstore;
CREATE EXTENSION
postgres=#

postgres=# select find_bad_row(‘check_corruption’);
2020-02-06 19:44:24.227 IST [25929] WARNING: page verification failed, calculated checksum 39428 but expected 39427
2020-02-06 19:44:24.227 IST [25929] CONTEXT: PL/pgSQL function find_bad_row(text) line 21 at FETCH
WARNING: page verification failed, calculated checksum 39428 but expected 39427
NOTICE: LAST CTID: (0,5)
NOTICE: XX001: invalid page in block 1 of relation base/13455/16490
find_bad_row
————–
(0,5)
(1 row)

Deleting that particular CTID will resolve the issue

postgres=# delete from check_corruption where ctid='(0,6)’;
DELETE 1
postgres=#

If deleting ctid has not worked for you, you have an alternative solution which is setting zero_damaged_pages parameter.

Example.,

postgres=# select * from master;
WARNING: page verification failed, calculated checksum 8770 but expected 8769
ERROR: invalid page in block 1 of relation base/13455/16770
postgres=#

I can’t access the data from table master as block is corrupted.

Solution:

postgres=# SET zero_damaged_pages = on;
SET

postgres=# vacuum full master;

postgres=# select * from master;

WARNING: page verification failed, calculated checksum 8770 but expected 8769

WARNING: invalid page in block 1 of relation base/13455/16770; zeroing out page

id | name | city

——+—————+——————

1 | Orson | hyderabad

2 | Colin | chennai

3 | Leonard | newyork

here, it cleared the damaged page and gave the rest of the result.

What your document has to say?

zero_damaged_pages (boolean): Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.

There are a couple of things to be aware when using this feature though. First, using checksums has a cost in performance as it introduces extra calculations for each data page (8kB by default), so be aware of the tradeoff between data security and performance when using it.

There are many factors that influence how much slower things are when checksums are enabled, including:

How likely things are to be read from shared_buffers, which depends on how large shared_buffers is set, and how much of your active database fits inside of it
How fast your server is in general, and how well it (and your compiler) are able to optimize the checksum calculation
How many data pages you have (which can be influenced by your data types)
How often you are writing new pages (via COPY, INSERT, or UPDATE)
How often you are reading values (via SELECT)

The more that shared buffers are used (and using them efficiently is a good general goal), the less checksumming is done, and the less the impact of checksums on database performance will be. On an average if you enable checksum the performance cost would be more than 2% and for inserts, the average difference was 6%. For selects, that jumps to 19%. Complete computation benchmark test can be found here

Bonus

You can dump the content of the file with pg_filedump before and after the test and can use diff command to analyze data corruption

pg_filedump -if 16770 > before_corrupt.txt
corrupt the disk block
pg_filedump -if 16770 > before_corrupt.txt
diff or beyond compare both the files.

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out to the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestions/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.

Источник