Предыстория

Аварийная ситуация — информационная система жутко тормозит.
Как обычно- все менеджеры в панике.
Как обычно — «что там с СУБД»?
Как обычно — «с СУБД, всё хорошо — критичных ошибок нет, отклик не увеличивается, аномалий по метрикам мониторинга СУБД — нет.»

Новая вводная- «У нас все запросы стали очень медленно выполняться».
К сожалению на период развития событий, способа получить объективную картину времени выполнения запросов — не было.

Метрика, позволяющее получать время отклика СУБД показывает «среднюю температуру по больнице». А учитывая, что система высоконагруженная, среднее время отклика не может являться надежной метрикой оценки производительности СУБД. Так, что от разнообразных вариаций на тему SUM(total_time) / SUM(calls) — пользы не так и много.

Убедить разработчиков и менеджеров, что на стороне СУБД проблем нет — не получается.
И поэтому и возникла идея — сделать простой механизм, позволяющий получить оценку производительности СУБД — гистограммы максимального и среднего времени выполнения запросов, на основе уже используемого расширения pg_stat_statements.

Очень неприятная ситуация для DBA: «что-то происходит с СУБД, но что именно — нет информации». Первый и иногда достаточный способ избежать неопределенности — всегда иметь полную информацию о нештатных ситуациях в работе СУБД на текущий период и в истории. Для решения этой в общем то стандартной задачи и нужно мониторить и анализировать лог СУБД. PostgreSQL в данном случае не исключение.

К рассмотрению предлагается один из способов мониторинга ошибок СУБД PostgreSQL и получением итоговой информации, используя Zabbix. Данная статья не tutorial и не roadmap, скорее как эскиз для обмена мнениями с коллегами.

Постановка задачи

От бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных, расположенных в разных кластерах PostgreSQL.

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



Первое и самое очевидное решение — pg_dump

Достоинства — простота решения. Штатные методы. Все отработано, документации и материалов великое множество.

Но, достоинства есть продолжения недостатков.
Во-первых: объемы дампов.
Во-вторых: и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.

Возможно этот случай заслужит отдельной заметки. Как оказалось, не все так однозначно, с pg_dump

В-третьих: время, сначала на создание дампа, потом на восстановление БД из дампа.

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

Не факт, что решение получилось максимально эффективным и не будет изменено/улучшено. Но как этюд на тему использования возможностей PostgreSQL, идея показалась как минимум интересной.

В короткой заметке описан cценарий Point In Time Recovery с использованием средств из стандартной поставки PostgreSQL версии 11.

Для создания базовой резервной копии используется штатная утилита — pg_basebackeup.
Для потоковой архивации файлов WAL используется штатная утилита — pg_receivewal

Общий сценарий довольно подробно описан в документации Непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery, PITR), однако довольно общими фразами. Поэтому при попытке реализовать на практике возникли некоторые, хотя и вполне преодолимые шероховатости.

Посмотрел по поиску на Хабре, вроде не нашел статей о PITR штатными средствами. Так, что может быть кому то пригодится, в качестве шаблона-рыбы. Или студентам, как лабораторка ;-)

Развитие темы Этюд по реализации Row Level Secutity в PostgreSQL и для развернутого ответа на комментарий.

Использованная стратегия подразумевает использование концепции «Бизнес-логика в БД», что было чуть подробнее описано здесь — Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL

Теоретическая часть отлично описана в документации Postgres Pro — Политики защиты строк. Ниже рассмотрена практическая реализация конкретной бизнес задачи — ролевая модель доступа к данным.

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

В качестве дополнения к Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL и в основном для развернутого ответа на комментарий.

Теоретическая часть отлично описана в документации Postgres Pro — Политики защиты строк. Ниже рассмотрена практическая реализация маленькой конкретной бизнес задачи — скрытия удаленных данных . Этюд посвященный реализации Ролевой модели с использованием RLS представлен отдельно.

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

Побудительным мотивом к написанию этюда послужила статья «В карантин нагрузка выросла в 5 раз, но мы были готовы». Как Lingualeo переехал на PostgreSQL с 23 млн юзеров. Так же показалось интересной статья опубликованная 4 года назад — Реализация бизнес-логики в MySQL.

Показалось интересным то, что одна и та же мысль-"реализовать бизнес-логику в БД".



пришла в голову не только мне одному.

Также на будущее хотелось сохранить, для себя в первую очередь, интересные наработки возникшие по ходу реализации. Особенно учитывая то, что относительно недавно было принято стратегическое решение о смене архитектуры и переносе бизнес-логики на уровень backend. Так, что все, что было наработано, скоро никому не понадобится и никому будет не интересно.

Описанные методы не являются каким то открытием и исключительным know how, все по классике и было реализовано неоднократно (я например подобный подход применил 20 лет назад на Oracle).Просто решил собрал все в одном месте. Вдруг кому пригодится. Как показала практика — довольно часто одна и та же идея приходит независимо разным людям. Да и для себя оставить на память, полезно.

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

В статье описаны общие идеи и наброски по реализации ориентированного графа в PostgreSQL.

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

Опыт оказался успешным, может быть кому-то пригодится и поможет сэкономить время. Я в свое время искал реализации именно на pqSQL, но видимо плохо искал. Пришлось реализовывать самому. Что в общем-то даже к лучшему, задача интересная, всегда приятно что-то сделать своими руками, так, что время потрачено не зря.

Продолжение статьи "Попытка создать аналог ASH для PostgreSQL ".

В статье будет рассмотрено и показано на конкретных запросах и примерах — какую же полезную информацию можно получить с помощью истории представления pg_locks.

Предупреждение.
В связи с новизной темы и незавершением периода тестирования, статья может содержать ошибки. Критика и замечания всячески приветствуются и ожидаются.

Продолжение статьи "Попытка создать аналог ASH для PostgreSQL ".

В статье будет рассмотрено и показано на конкретных запросах и примерах — какую же полезную информацию можно получить с помощью истории представления pg_stat_activity.

Предупреждение.
В связи с новизной темы и незавершением периода тестирования, статья может содержать ошибки. Критика и замечания всячески приветствуются и ожидаются.

В качестве короткого дополнения к статье Попытка создать аналог ASH для PostgreSQL.

Задача

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

Предупреждение.
В связи с новизной темы и незавершением периода тестирования, статья может содержать ошибки. Критика и замечания всячески приветствуются и ожидаются.

Постановка задачи

Для оптимизации запросов PostgreSQL, очень требуется возможность анализировать историю активности, в частности – ожидания, блокировки, статистика таблиц.

Имеющиеся возможности

Инструмент анализа исторической нагрузки или «AWR для Postgres»: очень интересное решение, но, нет истории pg_stat_activity и pg_locks.

Расширение pgsentinel :
"Вся накопленная информация хранится только в оперативной памяти, а потребляемый объём памяти регулируется количеством последних хранимых записей.

Добавляется поле queryid — тот самый queryid из расширения pg_stat_statements (требуется предварительная установка)."

Это конечно сильно бы помогло, но самая неприятность именно первый пункт “Вся накопленная информация хранится только в оперативной памяти ”, т.е. имеем место импакт на целевую базу. К тому, же нет истории блокировок и статистики таблиц. Т.е. решение вообще говоря неполное: “Готового пакета для установки пока нет. Предлагается скачать исходники и собрать библиотеку самостоятельно. Предварительно требуется установить «devel»-пакет для своего сервера и в переменную PATH прописать путь до pg_config.”.

В общем – возни много, а в случае серьезных продакшн баз, может быть, и не будет возможности что-то делать с сервером. Нужно опять, придумывать, что-то свое.

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