Авторские права

Учебное пособие, практические задания, презентации (далее документы) предназначены для учебных целей.

Документы защищены авторским правом и законодательством об интеллектуальной собственности.

Вы можете копировать и распечатывать документы для личного использования в целях самообучения, а также при обучении в авторизованных ООО «Tantor Labs» учебных центрах и образовательных учреждениях. Авторизованные ООО «Tantor Labs» учебные центры и образовательные учреждения могут создавать учебные курсы на основе документов и использовать документы в учебных программах с письменного разрешения ООО «Tantor Labs».

Вы не имеете права использовать документы для платного обучения сотрудников или других лиц без разрешения ООО «Tantor Labs». Вы не имеете права лицензировать, коммерчески использовать документы полностью или частично без разрешения ООО «Tantor Labs».

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

Текст документов не может быть изменен каким-либо образом.

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

Отказ от ответственности за содержание документа, продукты и услуги третьих лиц:

ООО «Tantor Labs» и связанные лица не несут ответственности и прямо отказываются от любых гарантий любого рода, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием документа. ООО «Tantor Labs» и связанные лица не несут ответственности за любые убытки, издержки или ущерб, возникшие в результате использования информации, содержащейся в документе или использования сторонних ссылок, продуктов или услуг.

Авторское право © 2025, ООО «Tantor Labs»

Автор: Олег Иванов

Слайд 1

Введение

Слайд 2

Предварительная подготовка

Для успешного прохождения курса достаточно базовых навыков работы в операционных системах семейства Linux и базовых знаний языка SQL: понимание команд SELECT, UPDATE, INSERT и DELETE. К навыкам работы в операционной системе относится: умение запускать терминал, просматривать в терминале содержимое директорий и файлов, копировать и редактировать текстовые файлы командами ls, cp, mv, cat, vi; понимать и менять разрешения на файлы (команды ls -al, chmod, chown).

В курсе будут рассмотрены основные задачи администрирования баз данных семейства PostgreSQL на примере СУБД Tantor Postgres и особенности эксплуатации СУБД Tantor Postgres.

Для успешного прохождения курса рекомендуется слушать инструктора, если будут возникать вопросы задавать их, читать текст практических заданий и самостоятельно их выполнять. При выполнении практических заданий рекомендуется набирать команды на клавиатуре, а не копировать в терминал из текста заданий. Ручной ввод команд, исправление опечаток, возникающих при наборе команд, изучение ошибок выдаваемых на неправильные команды позволяет лучше запомнить команды и смысл их использования. Ощущения «понимания» текста заданий недостаточно, при реальной работе важно вспомнить основные ключевые слова и возможности команд, чтобы быстро найти полный синтаксис. Копирование команд можно использовать, если вы с ними знакомы.

Слайд 3

Материалы курса

К материалам курса относятся:

1) Учебное пособие в форме книги в формате pdf, которое содержит теоретическую часть курса.

2) Практические задания в форме книги в формате pdf.

3) Виртуальная машина с установленной операционной системой Astra Linux 1.8 и СУБД Tantor Postgres версии 17.5. Может предоставляться доступ к виртуальной машине на время курса или образ в формате ova. Образ виртуальной машины можно использовать с Oracle VirtualBox версии 6.1 и выше.

Слайд 4

Разделы курса

Установка и управление СУБД

  Установка

  Управление экземпляром кластера баз данных

  Утилиты управления кластером баз данных

  Утилита psql

Архитектура

  Общие сведения и структуры памяти

  Многоверсионность

  Регламентные работы

  Выполнение запросов

  Расширяемость

  Конфигурирование

Базы данных

Логическая и Физическая реализация

Диагностический журнал

Безопасность

  Подключение и аутентификация

Резервное копирование

  Физическое и логическое резервирование

Репликация

  Физическая и логическая

Платформа Tantor, обзор возможностей

Новые возможности Tantor Postgres версии 17.5 

Слайд 5

О курсе

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

Примерное расписание:

1) начало в 10:00

2) перерыв на обед 13:00-14:00. Начало обеда может сдвигаться на полчаса в диапазоне от 12:30 до 13:30, так как обычно совмещается с перерывом между главами.

3) окончание теоретической части до 17:00 (в последний день курса до 15:00).

Курс состоит из теоретической части (глав) и практических заданий. Длительность глав примерно 20-40 минут. Точное время начала изложения глав и времени на практические задания определяет инструктор. Длительность выполнения упражнений может отличаться у разных слушателей и это не влияет на эффективность усвоения материала курса. Доделывать упражнения можно в перерывах между главами или в конце каждого дня. Порядок следования глав и упражнений на эффективность усвоения материала курса не влияет. Проверка выполнения заданий не производится. Для успешного усвоения материала курса достаточно:

1) слушать инструктора, просматривая в процессе изложения инструктора текст на слайдах и под слайдом

2) задавать инструктору вопросы, если возникает внутреннее несогласие (появляются вопросы)

3) выполнять практические задания и читать текст в практических заданиях

В материалы курса входят:

1) учебник в формате pdf

2) практические задания в формате pdf

3) образ виртуальной машины в формате ova

Слайд 6

О компании Тантор

С 2016 года команда Тантор работала на международном рынке поддержки эксплуатации СУБД PostgreSQL и обслуживала клиентов из Европы, Северной и Южной Америки,  Ближнего Востока. Команда Тантор разработала программное обеспечение Платформа Tantor и в последующем создала СУБД Tantor Postgres, основанную на программном коде свободно распространяемой СУБД PostgreSQL.

В 2021 году компания полностью переориентировались на российский рынок, где сконцентрировала свои основные направления деятельности на проектирование и разработку СУБД Tantor Postgres, а также развитие Платформы Tantor - инструмента управления и мониторинга БД, основанных на PostgreSQL.

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

В конце 2022 года компания вошла в Группу Астра.

Слайд 7

СУБД Tantor Postgres

СУБД Tantor Postgres - реляционная база данных семейства PostgreSQL с повышенной производительностью и стабильностью. Выпускается в нескольких редакциях (сборках): BE, SE, SE 1C, Certified. Редакция Special Edition для наиболее нагруженных OLTP-систем  и хранилищ данных размерами до 100ТБ. Редакция Special Edition 1C для приложений 1С.

Для всех редакций доступна техническая поддержка, помощь в построении архитектурных решений, миграции с СУБД других производителей. Программное обеспечение Тантор Лабс включено в "Единый реестр российских программ для электронных вычислительных машин и баз данных". При приобретении Tantor Postgres предоставляется лицензия на Платформу Tantor для управления приобретенными СУБД.

Слайд 8

Tantor xData

Программно-аппаратный комплекс (ПАК) Tantor XData обеспечивает рабочие нагрузки большого масштаба и критичности с высокой производительностью и доступностью. Консолидация различных рабочих нагрузок Tantor Postgres SE на машине баз данных XData в корпоративных центрах обработки данных помогает организациям повысить операционную эффективность, сократить объем администрирования и снизить затраты.

Программно-аппаратный комплекс (ПАК) Tantor XData предназначен миграции с комплексов иностранных производителей и обеспечивает аналогичную нагрузочную способность. Является заменой высоконагруженным СУБД размером до ~50Тб на один экземпляр, обслуживающие нагрузку типа OLTP, работающие на программно-аппаратных комплексах иностранных производителей. Для СУБД, обслуживающих хранилища данных размером до ~120Тб на один экземпляр.

Является заменой для тяжёлых ERP от 1C при миграции с СУБД иностранных  производителей. Позволяет консолидировать несколько СУБД в одном ПАК. Может использоваться при миграции с SAP на 1С:ERP.

Предназначен для создания облачных платформ.

Преимуществом при эксплуатации xData является наличие в составе ПАК удобной графической системы мониторинга работы СУБД: Платформы Tantor.

C 2025 года выпускается вторая версия ПАК:

xData 2A - на процессорах AMD EPYC и x86-64 на основе серверов Yadro.

xData 2B - на процессорах Baikal-S производства Байкал Электроникс (Группа Астра).

Слайд 9

Платформа Tantor

Платформа Tantor - программное обеспечение  для управления СУБД Tantor Postgres, форками PostgreSQL, кластерами Patroni. Позволяет удобно управлять большим количеством СУБД. Относится к классу программных продуктов, в который входит Oracle Enterprise Manager Cloud Control.

Преимущества использования Платформы Tantor:
1. Сбор показателей работы экземпляров PostgreSQL, хранение и обработка показателей, рекомендации по настройке производительности
2. Интуитивно понятный и функциональный графический интерфейс позволяет сосредоточиться на показателях  работы экземпляров PostgreSQL
3. Автоматизирует рутинные задачи, повышая эффективность работы и снижая вероятность ошибок
4. Управляет не только СУБД Tantor Postgres, но и другими СУБД семейства PostgreSQL
5. Интеграция с почтовыми системами, службами каталогов, мессенжерами
6. Простое внедрение: развертывание и ввод СУБД под обслуживание Платформой Tantor с помощью Ansible.

Платформа Tantor DLH

Тантор Лабс выпускает Платформу Tantor DLH - программное обеспечение, позволяющее организовать процесс трансформации и загрузки данных по логике Extract Transform Load или Extract Load Transform в СУБД Tantor Postgres для организации хранилищ и витрин данных. Относится к классу программных продуктов, в который входит Oracle Data Integrator.

Слайд 10

Расширение Tantor PipelineDB

Tantor PipelineDB - расширение для СУБД Tantor Postgres и PostgreSQL (свободная лицензия Apache 2.0), в отличие от ограниченной лицензии расширения timescaleDB. Позволяет непрерывно обрабатывать потоковые данные с инкрементальным сохранением результатов в таблицах. Данные обрабатываются в реальном времени, используя только запросы SQL. Имеет большой набор  аналитических функций, работающих с постоянно обновляемыми данными. Позволяет соединять потоковые данные с историческими данными для сопоставления в режиме реального времени. Устраняет необходимость использовать традиционную логику ETL (Extract, Transform, Load) с CDC (Change Data Capture). Дальше описана суть расширения для знакомых с термином "CDC".

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

Запросы к непрерывным представлениям моментально выдают актуальный результат. Это позволяет использовать TantorPipelineDB в классе приложений, где важна незамедлительная реакция.

Примеры создания непрерывных представлений:

Непрерывное представление для выдачи аналитических данных за последние пять минут:

CREATE VIEW imps WITH (action=materialize, sw = '5 minutes')

 AS SELECT count(*), avg(n), max(n) FROM imps_stream;

По умолчанию параметр action=materialize, поэтому параметр action можно не указывать при создании непрерывных представлений.

Непрерывное представление для вывода девяностого, девяносто пятого, девяносто девятого процентилей времени отклика:

CREATE VIEW latency AS

 SELECT percentile_cont(array[90, 95, 99])

 WITHIN GROUP (ORDER BY latency::integer)

 FROM latency_stream;

Непрерывное представление для вывода ежесуточного трафика, используемого топ десятью ip-адресов:

CREATE VIEW heavy_hitters AS

 SELECT day(arrival_timestamp), topk_agg(ip, 10, response_size)

 FROM requests_stream GROUP BY day;

https://tantorlabs.ru/products/pipelinedb

Слайд 11

Доработка расширений PostgreSQL

Сотрудники Тантор Лабс дорабатывают и создают расширения для СУБД  PostgreSQL.

Репозитории расширений:  https://github.com/orgs/TantorLabs

Список расширений:

1. pg_cluster

2. pg_anon

3. pg_perfbench  MIT License

4. ansible_tantor_agent  MIT License

5. pg_configurator MIT License

6. pg_store_plans

7. ldap2pg PostgreSQL License

8. citus GNU Affero General Public License v3.0

9. wal-g Apache License, Version 2.0 (lzo - GPL 3.0+)

10. odyssey BSD 3-Clause "New" or "Revised" License

11. plantuner

12. pg_orchestrator MIT License

13. pgtools

14. pipelinedb Apache License 2.0

Слайд 12

Конференции PGBootCamp

Тантор Лабс является активным участником организации конференций сообщества PostgreSQL в рамках глобальной инициативы PG BootCamp.

Участие в конференции бесплатно и возможно онлайн и оффлайн: https://pgbootcamp.ru/

Можно стать докладчиком на конференции.

Материалы докладов конференций в открытом доступе: https://github.com/PGBootCamp

Выступления https://www.youtube.com/@PGBootCampRussia

Конференция PGBootCamp проводилась:

Екатеринбург 10 апреля 2025

Казань 17 сентября 2024

Минск 16 апреля 2024

Москва 5 октября 2023

Слайд 13

1a Установка

Слайд 14

Предварительные требования

Tantor Postgres поставляется в скомпилированном виде в виде пакетов для пакетного менеджера операционной системы. Перед установкой нужно свериться со списком операционных систем и их версий, для которых выпускается СУБД Tantor Postgres. В список поддерживаемых операционных систем входят:

Операционные системы с пакетным менеджером RedHat Packet Manager (rpm)

Redos 7.3

AltLinux c9f2 (P8), p10

Centos 7

MSVSphere

Oracle Linux 8

Rocky 8, 9

Операционные системы с пакетным менеджером Debian (deb)

Astra Linux Common Edition 2.12

Astra Linux Special Edition 4.7, 1.7, 1.8

Ubuntu 18, 20, 22

Debian 10, 11, 12.

Установка на другие операционные системы не поддерживается.

Оборудование:

Количество ядер центральных процессоров: по крайней мере 4;

Оперативная память: по крайней мере 4ГБ;

Свободное место на диске: не менее 40ГБ (плюс место под пользовательские данные которые планируется хранить). Рекомендуется использование SSD.

https://docs.tantorlabs.ru/tdb/ru/17_5/se/install-binaries.html

Слайд 15

Проверка возможности установки

Программы пользуются разделяемыми библиотеками, которые обеспечивают полезный функционал и использовались при их сборке. Если библиотеки не будут установлены в операционной системе, то в процессе работы могут возникнуть ошибки, причину которых будет сложно выяснить. В дистрибутивах перечислены те библиотеки, к функционалу которых могут обратиться утилиты и процессы. Такие пакеты называются требуемые (requires) и относятся к зависимостям. К зависимостям могут относиться не только пакеты, но и потребности командных файлов, вызываемых при установке и других инструментов.

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

На практике же получение списка пакетов, которые нужно установить - актуальная задача.

Для получения полного списка зависимостей конкретного дистрибутива можно использовать команды:

Для пакетного менеджера Дебиан: dpkg -I tantor*.deb

Для пакетного менеджера RedHat: rpm -qp --requires tantor-se-server-*.x86_64.rpm

Ответ утилиты состоит из перечисления пакетов и, возможно, версий пакетов и библиотек. Например:

shadow-utils

grep

...

rpmlib(PayloadIsXz) <= 5.2-1

Значки <= и => указывают что требуются конкретные версии библиотек. В последней строке примера указаны ограничения версии пакетного менеджера, которые устанавливают проверку совместимости пакетного менеджера rpm для защиты от установки на несовместимую версию операционной системы.

Такая проверка может быть полезна для того, чтобы составить задачу на установку операционной системы. Запустить команду можно на любой операционной Linux где установлен пакетный менеджер rpm.

Для проверки перед установкой того, что зависимости выполнены можно использовать команду:

rpm -i --test  tantor*.rpm

Пример ошибки при проверке зависимостей дистрибутива Rocky 9 на Oracle Linux 7.

warning: tantor-be-server-17-17.5.1.el9.x86_64.rpm: Header V4 RSA/SHA512 Signature

error: Failed dependencies:

...

 python3-libs is needed by tantor-be-server-17-17.5.1-0.x86_64

 rpmlib(PayloadIsZstd) <= 5.4.18-1 is needed by tantor-be-server-17-17.5.1-0.x86_64

Ошибки, связанные с rpmlib указывают на неподходящий дистрибутив.

Слайд 16

Инсталлятор

Для упрощения установки Tantor Postgres может быть установлен инсталлятором. Инсталлятор скачивается командой:

wget https://public.tantorlabs.ru/db_installer.sh

После завершения загрузки можно поменять разрешения файловой системы, чтобы скрипт инсталлятора мог выполняться: chmod +x db_installer.sh

Дистрибутив можно скачать из личного кабинета https://lk.astra.ru/iso-images  и указать инсталлятору путь к скачанному файлу параметром --from-file:

./db_installer.sh --from-file=./tantor-se-server-17_17.5.0_amd64.deb

Инсталлятор может скачать дистрибутив из репозитория Тантор Лабс. Для этого нужно установить переменную окружения NEXUS_URL:

su -

export NEXUS_URL="nexus-public.tantorlabs.ru"

apt update

./db_installer.sh --edition=be

Для скачивания коммерческих версий нужно установить переменные окружения:

su -

export NEXUS_URL="nexus.tantorlabs.ru"

export NEXUS_USER="имя"

export NEXUS_USER_PASSWORD="пароль"

apt update

./db_installer.sh --edition=se

Возможные ошибки:

1) Конфликты. Например, был установлен клиент (tantor-se-client-17_17.4.0_amd64.deb), а пакет с tantor-se-server-17 включает в себя библиотеки tantor-se-client-17. В этом случае инсталлятор выдаст ошибку и команду для ее устранения путем деинсталляции пакета  с которым обнаружен конфликт:

E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).

После запуска apt --fix-broken install утилита запросит подтверждение на деинсталляцию пакета.

2) Инсталлятор создает файл /etc/apt/sources.list.d/tantorlabs.list или /etc/yum.repos.d/tantorlabs.repo и в последующем можно будет не устанавливать переменные окружения. Если будет ошибка аутентификации или захочется не аутентифицироваться, то нужно будет стереть указанные файлы. Аутентификационные данные для скачивания дистрибутива позволяет только скачать коммерческие дистрибутивы и его сохранение в файле tantorlabs.list не рассматривается как брешь в защите.

3) В директории /etc/apt/sources.list.d/ есть файлы с адресами несуществующих репозиториев. Нужно удалить такие файлы.

Примечание: команда apt update обновляет содержимое /var/lib/apt/lists/ , скачивая и раскрывая архивы с пакетами, содержащимися в репозиториях.

Слайд 17

Локальная установка

Tantor Postgres Basic Edition (BE) доступна для оценочного использования. Чтобы установить Tantor Postgres BE, нужно установить только одну переменную окружения:

export NEXUS_URL="nexus-public.tantorlabs.ru"

Обновить списки пакетов из репозиториев:

apt update

Запустить инсталлятор, задав желаемые параметры:

./db_installer.sh --edition=be --major-version=16 --do-initdb

Можно указать основную версию и нужно ли создавать кластер после инсталляции. Также кластер можно создать после инсталляции утилитой initdb.

Инсталлятор позволяет устанавливать любые сборки СУБД Tantor Postgres из файлов пакетов. Это может быть полезно, если хост не имеет доступа в интернет.

Прежде чем приступить к установке, убедитесь, что вы загрузили правильный бинарный пакет, совместимый с вашей операционной системой и архитектурой. Файл должен иметь расширение .deb для систем на основе Debian, .rpm для систем на основе Red Hat.

Чтобы начать установку, перейдите в каталог, где находится загруженный файл. Убедитесь, что установочный скрипт db_installer.sh присутствует и имеет нужные права на выполнение. Локальная установка выполняется командой:

./db_installer.sh --do-initdb --edition=se --major-version=17 --from-file=./tantor-se-server-17_5.0_amd64.deb

Нужно указать параметром --major-version=17 основную версию и она должна совпадать с версией (обычно присутствует в названии файла пакета), иначе инсталлятор может создать директорию с неверным номером версии.

Также можно установить пакет не пользуясь установочным скриптом, а используя пакетный менеджер операционной системы:

rpm -i tantor*.rpm или dpkg -i tantor*.deb

В этом случае кластер создаваться не будет и его можно создать позже утилитой initdb. Фактически инсталлятор может быть полезен при локальной установке тем, что может выполнить дополнительные действия. Недостатком может являться то, что программный код (обёрток над пакетным менеджером) может добавлять ошибки. Например, не предусматривать все возможные особенности конфигурирования операционной системы.

Слайд 18

Процесс установки

В процессе установки:

• создается или модифицируется пользователь для СУБД Tantor Postgres, из под которого будут запускаться процессы и который будет являться владельцем директорий, относящихся к кластерам баз данных. Пример команды создания пользователя и именем postgres:
• useradd -r -g postgres -c "Tantor database server" -d /var/lib/postgresql -s /bin/bash postgres
  Менять имя пользователя postgres на другое исходя из целей безопасности не нужно.
• создается директория /opt/tantor/db/17 в которой располагаются исполняемые и вспомогательные файлы Tantor Postgres.
• создается файл описателя службы /usr/lib/systemd/system/tantor-se-server-17.service для того чтобы можно было запускать экземпляр, обслуживающий кластер баз данных. Кластер баз данных представляет собой директорию в файловой системе хоста (синонимы: компьютер, узел, сервер) на котором было установлено программное обеспечение Tantor Postgres. Клиенты СУБД не имеют прямого доступа к файлам. Для того чтобы программы-клиенты могли "работать с СУБД" (посылать команды на языке SQL, получать данные) на хосте нужно запустить набор процессов, которые будут читать и писать в директорию кластера и держать соединение ("сокет") с клиентской программой. Такой набор процессов и память, которую они используют в операционной системе хоста называют "экземпляром" (instance) или экземпляром кластера баз данных. Проверить статус службы можно командой
  systemctl status tantor-se-server-17
• создается файл /etc/ld.so.conf.d/tantor-se-17.conf  Посмотреть список разделяемых библиотек, загруженных в кэш /etc/ld.so.cache можно командой ldconfig -p | grep tantor
  Можно проверить что в переменной окружения LD_PRELOAD отсутствуют библиотеки, которые могут перекрыть библиотеки PostreSQL, так как LD_PRELOAD превалирует
• создается директория /var/run/postgresql и файл /usr/lib/tmpfiles.d/tantor-db.conf
  Файл используется стандартной службой очистки временных файлов. Директория является директорией по умолчанию для файлов Unix socket Tantor Postgres (параметр конфигурации (unix_socket_directories). В старых версиях PostgreSQL использовалась директория "/tmp" которая по инерции может упоминаться в документации и руководствах.
  Можно проверить, что в директории /usr/lib/tmpfiles.d отсутствуют другие файлы, которые могли остатья от предыдущих инсталляций postgresql, в которых указана та же самая директория, но с другими параметрами systemctl status systemd-tmpfiles-*
  systemd-tmpfiles[]: /usr/lib/tmpfiles.d/tantor-db.conf:1: Duplicate line for path "/run/postgresql", ignoring
• создаётся директория /var/lib/postgresql/tantor-se-17/data
  это директория по умолчанию для файлов кластера. Утилиты Tantor Postgres узнают о местоположении директории кластера либо ключём (параметром утилиты) -D , либо из переменной окружения PGDATA. Поэтому в устной речи эту директорию называют "PGDATA".
• в конец файла /var/lib/postgresql/.bash_profile добавляются строки
  export PATH=$PATH
  export PATH=/opt/tantor/db/17/bin:$PATH
  для того, чтобы зайдя под пользователем postgres можно было запускать утилиты управления кластером баз данных, не указывая к ним путь.

  ---

Слайд 19

После установки

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

В некоторых пакетных дистрибутивах PostgreSQL имеются утилиты  pg_controlcluster,  pg_createcluster, являющиеся обёртками для стандартных утилит pg_ctl, initdb. Разработчики таких дистрибутивов предполагают, что это упрощает работу с несколькими кластерами на одном узле. Tantor Postgres эти утилиты не использует. Облачные дистрибутивы, где востребована работа большого числа экземпляров могут использовать другие сборки (синоним forks, "форки") PostgreSQL.

После установки можно сделать следующее:

• Установить переменную окружения в файле
  /var/lib/postgresql.bash_profile
  export PGDATA=/var/lib/postgresql/tantor-se-17/data
  это упростит запуск утилит управления кластером, которые мы рассмотрим позднее pg_ctl, pg_controldata, pg_backup и других - им не нужно будет указывать параметр запуска -D путь_к_PGDATA.
• создать кластер, если он не был ещё создан
• запустить кластер systemctl start tantor-se-server-17
• если автоматический запуск экземпляра был отключен (по умолчанию включён), то включить: systemctl enable tantor-se-server-17
• Версию Tantor Postgres можно узнать функцией tantor_version()
• установить начальные значения параметров кластера используя https://tantorlabs.ru/pgconfigurator
• настроить инструменты управления и мониторинга кластера Платформа Tantor, dBeaver.
• деинсталляция (например, предыдущей версии) выполняется пакетным менеджером. Для систем на основе Debian apt-get remove tantor-se-server-17

https://docs.tantorlabs.ru/tdb/ru/17_5/se/binary-download-execute.html

Слайд 20

Конфигураторы

Кластер баз данных создается утилитой initdb. Утилита создает файл postgresql.conf со значениями по умолчанию. Эти значения рассчитаны на обслуживание не очень нагруженного приложения так, чтобы СУБД можно было использовать на десктопе обычному разработчику. В Tantor Postgres  утилита initdb не меняет значения параметров по сравнению с initdb PostgreSQL. Предполагается, что настройка параметров для промышленного использования будет выполнена отдельно.

Для начальной настройки можно использовать утилиту pg_configurator, созданную и поддерживаемую Тантор Лабс. Утилита доступна на сайте https://tantorlabs.ru/pgconfigurator/ оболочка в виде утилиты командной строки https://github.com/TantorLabs/pg_configurator

Утилита принимает 7 или ~20 параметров и дает рекомендации на их основе.

Утилит начальной конфигурации не очень много. Из известных:

1. PGconfigurator www.cybertec-postgresql.com, веб-версия pgconfigurator.cybertec.at  дает рекомендации на основе 13 параметров

2. PGСonfig https://github.com/pgconfig/api, веб-версия www.pgconfig.org дает рекомендации на основе 8 параметров

3. PGTune github.com/le0pard/pgtune, создан сотрудником 2ndQuadrant, веб-версия pgtune.leopard.in.ua дает рекомендации на основе 7 параметров

В процессе эксплуатации СУБД параметры конфигурации может рекомендовать конфигуратор Платформы Tantor.  Конфигуратор Платформы дает рекомендации на основе ~25 параметров.

https://tantorlabs.ru/pgconfigurator

Настройка работы PostgreSQL с продуктами 1C:

https://wiki.astralinux.ru/tandocs/nastrojka-postgresql-tantor-dlya-raboty-1s-294394904.html

Дальше рассматриваются параметры, значения которых устанавливают в первую очередь. Конфигуратор даёт начальные значения. Важно понимать смысл параметров и на что они влияют.

Слайд 21

Создание кластера утилитой initdb

Кластер создается утилитой командной строки initdb. Утилита может вызываться инсталлятором.

initdb запускается из под пользователя операционной системы postgres.

Перед запуском утилиты нужно создать директорию, где будут находиться файлы создаваемого кластера PGDATA, установить разрешения и права владения на эту директорию и те директории, в которых она находится, для пользователя postgres. При запуске экземпляра проверяются разрешения на саму директорию PGDATA:

• разрешения должны быть 0700 (drwx --- ---) или 0750 (drwx r-x ---)
• владельцем должен быть пользователь postgres.

При создании кластера нужно выбрать настройки локализации, которые нельзя изменить после создания кластера (для создаваемых при создании кластера баз данных postgres template0 template1), но можно выбирать для баз данных создаваемых после создания кластера:

• LC_COLLATE правила сортировки текста
• LC_CTYPE классификация символов (заглавные буквы, прописные буквы, символы цифр и другие классы символов)

3) схему кодирования символов LOCALE - третью часть значения после точки. Эта часть должна быть UTF8 или одной из поддерживающих кириллицу кодировок. Не все комбинации доступны и для однобайтных кодировок можно выбрать. Можно выбрать ru_RU.iso88595 постольку поскольку она есть в поддерживаемых PostgreSQL операционных системах.

Параметры локализации можно задать в параметрах initdb --locale=en_US.UTF8 --lc-collate=en_US.UTF8 --lc-ctype= en_US.UTF8 --locale-provider={libc|icu} --encoding=UTF8

Если не указывать параметры, то используются переменные окружения. Получить их список можно командой locale. Список допустимых комбинаций locale -a. Настроить dpkg-reconfigure locales.  libc - стандартный провайдер. Параметр --encoding имеет смысл задавать, если в значении LOCALE нет кодировки (после точки) и есть несколько допустимых (совместимых) вариантов.

При выборе между UTF8 и iso88595 (или cp1251) можно учитывать, что в UTF8 кириллические символы занимают больше места - два байта вместо одного. Однако, приложениям может требоваться хранение, например, фамилии клиента на его национальном языке. Про однобайтную кодировку koi8: её не стоит использовать из-за того, что бинарное сравнение символов не соответствует лингвистическому.

Параметры, на которые обратить внимание:

• -k -g  явно включить контрольные суммы для блоков данных (для записей WAL они включены) и установить менее ограничивающие разрешения на создаваемые файлы и директории 0755 (ноль означает, что это  восьмеричное число).
• --auth --auth-host --auth-local
• -D путь к PGDATA
•  --wal-segsize число в мегабайтах. По умолчанию 16Мб. Значение должно быть степенью двойки. Этот параметр можно изменить после создания кластера утилитой pg_resetwal --wal-segsize=новый_размер
  Задает размер файла журнала предзаписи (синонимы WAL-файл, WAL-сегмент). Меняют либо по причине большого количества файлов одной директории, либо по причине того, что максимальный размер буфера журнала в разделяемой памяти (wal_buffers) ограничен размером WAL-файла. Влияние размера WAL-буфера на производительность нелинейная.

Слайд 22

1b Управление

Слайд 23

Утилита управления экземпляром pg_ctl

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

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

Основные команды, которые можно использовать с pg_ctl:

start - запуск экземпляра

stop  -m smart | fast | immediate - остановка

Перед остановкой промышленного кластера рекомендуется выполнять контрольную точку, то есть давать команду checkpoint. Это уменьшает время на остановку.

restart - перезапуск, эквивалентен остановке и запуску, поэтому могут использоваться параметры которые задаются при остановке.

reload - перечитывает файлы конфигурации без остановки экземпляра

status - выводит статус экземпляра

Для запуска экземпляра нужно указать директорию кластера - PGDATA. Это можно сделать установив переменную окружения перед запуском pg_ctl или указав в параметре -D путь к директории.

Слайд 24

Процесс postgres

pg_ctl запускает процесс postgres, который порождает (fork) остальные процессы экземпляра, и прослушивает входящие соединения. У процесса postgres есть параметры, которые ему может передать pg_ctl. В старых версиях PostgreSQL процесс postgres назывался postmaster.

Для передачи параметров конфигурации от pg_ctl к postgres используется параметр -o. Например,

pg_ctl start -o "--config_file=./postgresql.conf --work_mem=8MB"

также можно использовать синтаксис

pg_ctl start -o "-c config_file=./postgresql.conf -с work_mem=8MB"

Посмотреть список параметров, которые можно передавать postgres:

 postgres --help

Параметр --single запускает процесс postgres в режиме одного пользователя и одного процесса:

postgres --single

PostgreSQL stand-alone backend 17.5

backend> vacuum full

Для выхода из этого режима используется комбинация клавиш <ctrl+d>.

Это не psql, команд psql в этом режиме нет, только команды которые может принимать серверный процесс (синоним backend).

Параметр --single нельзя передать через pg_ctl, так как межпроцессного взаимодействия нет.

В этом режиме отсутствует межпроцессное взаимодействие и блокировки памяти. Благодаря этому команды выполняются быстрее. Этот режим используется в редких случаях для команд исправляющих содержимое кластера, например vacuum full.

Слайд 25

Управление экземпляром через systemctl

В поддерживаемых Tantor Postgtes операционных системах семейства Linux для запуска служб используется systemd. Tantor Postgres скомпилирован с опцией --with-systemd обеспечивающей поддержку всего функционала systemd. В дистрибутиве поставляется файл описания службы /usr/lib/systemd/system/tantor-se-server-17.service и администратору не требуется его создавать. По умолчанию используется Type=forking.

По умолчанию установлен таймаут 5 минут параметром TimeoutSec=300 в этом файле.

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

Во время работы сервера его PID сохраняется в файле postmaster.pid в PGDATA. Это используется для предотвращения запуска нескольких экземпляров сервера в одном каталоге данных и также может использоваться для выключения сервера.

В случае если процессы экземпляра погашены, а файл postmaster.pid мешает запустить экземпляр, файл postmaster.pid можно удалить.

systemctl - основная команда для работы с systemd. По умолчанию запускается с правами пользователя root.

Запуск экземпляра:

systemctl start tantor-se-server-17.service

Суффикс ".service" можно не писать, так как он используется по умолчанию.

Если при экземпляра утилитой systemctl выдается ошибка:

Starting Tantor Special Edition database server 17...

pg_ctl: another server might be running; trying to start server anyway

lock file "postmaster.pid" already exists

HINT:  Is another postmaster running in data directory "/var/lib/postgresql/tantor-se-16/data"?

pg_ctl: could not start server

это может означать, что экземпляр запущен не через systemd, а утилитой pg_ctl и systemd не может ни запустить, ни остановить экземпляр, так как он был запущен утилитой pg_ctl. Можно проверить список процессов в операционной системе. systemd использует для запуска/остановки и других действий утилиту pg_ctl.

Команда systemctl stop tantor-se-server-17 в таком случае не может остановить экземпляр, результат она не выдаёт и может возникнуть ложное впечатление, что экземпляр погашен.

Проверить добавлен ли экземпляр в автозапуск можно командой
systemctl is-enabled tantor-se-server-17

Родительский процесс имеет PID=1:

postgres@tantor:~$ ps -ef | grep init

root     1    0  0 /sbin/init splash

Слайд 26

Работа в контейнере docker

Номер процесса (PID) postmaster в контейнере не должен быть равен единице (1). Процесс с PID=1 это первый пользовательский процесс, который запускается после инициализации ядра linux. Процесс с 1 порождает (запускает) все остальные процессы. Он является родительским для всех остальных порождаемых им процессов. У всех процессов должен быть родительский процесс. У процесса 1 есть свойство: если родительский процесс какого-либо процесса умирает, ядро ​​автоматически назначает процесс 1 родительским для осиротевшего процесса. Процесс 1 должен усыновлять всех сирот.

Процесс postgres следит за состоянием дочерних процессов и получает статус выхода, когда какой-либо дочерний процесс останавливается. Обычное поведение postmaster, если дочерний процесс останавливается со статусом, отличным от 0 (нормальная остановка) - перезапуск экземпляра. Помимо разрыва сессий экземпляр будет недоступен на время восстановления по wal-журналу.

В контейнере Docker процесс 1 это процесс, который запускается для запуска контейнера. Процесс postgres не должен иметь PID=1:

root@tantor:~# docker exec -it контейнер /usr/bin/ps -ef

PID USER      TIME COMMAND

  1 postgres  0:38 postgres

Чтобы использовать tini для запуска экземпляра в контейнере нужно использовать параметр --init.

Изменяемые файлы, в частности PGDATA, должны лежать на томах (volumes), иначе при удалении контейнера данные будут потеряны. Пример создания и запуска контейнера:

sudo docker pull postgres

sudo docker run -d --init -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_INITDB_ARGS="--data-checksums" -e POSTGRES_HOST_AUTH_METHOD=trust -p 5434:5434 -e PGDATA=/var/lib/postgresql/data -d -v /root/data:/var/lib/postgresql/data --name postgres postgres

Работа экземпляра в контейнере не добавляет высокой доступности.

Работа экземпляра в контейнере даёт большую производительность по сравнению с работой в виртуальной машине.

Слайд 27

Три режима остановки экземпляра

Экземпляр можно остановить командой pg_ctl stop.

Синтаксис команды:

pg_ctl stop [-D каталог_данных]
[-m s[mart] | f[ast] | i[mmediate] ] [-W] [-t секунды] [-s]

На выбор есть три режима:

smart - запрещает новые подключения и ждёт добровольного отсоединения существующих сессий. А этого можно ждать часами, при этом новые подсоединения невозможны, а это простой. В Oracle Database такой режим называется "shutdown normal". Таким образом, режим smart не практичен. Однако, в отличие от Oracle Database, после подачи сигнала на остановку в режиме smart можно подать сигнал на остановку в режиме fast. В Oracle Database же можно будет погасить экземпляр только в режиме "abort".

Поэтому если вы запустили режим smart, то имеете возможность погасить экземпляр в режиме fast.

fast - запрещаются новые подключения, всем серверным процессам отправляется сигнал прервать транзакции и завершиться (сигнал linux SIGTERM 15). Затем завершаются оставшиеся фоновые процессы экземпляра в правильном порядке. Одним из последних действий выполняется контрольная точка. В Oracle Database такой режим называется "shutdown immediate". В отличие от Oracle Database откат транзакций в PostgreSQL выполняется моментально, поэтому задержка в остановке связана, в основном, определяется длительностью выполнения контрольной точки.

fast - режим остановки по умолчанию для остановки через pg_ctl stop и через systmemctl stop

На промышленных кластерах с большим объемом памяти используемой экземпляром можно минимизировать время остановки экземпляра, то есть время простоя. Для этого перед остановкой экземпляра нужно инициировать выполнение контрольной точки командой checkpoint. После выполнения checkpoint послать сигнал на остановку экземпляра. В этом случае, контрольной точке которая всё равно будет выполнена при остановке экземпляра (финальная контрольная точка) в режиме smart или fast, придется записать на диск меньше данных и финальная контрольная точка выполнится быстрее.

В режимах smart и fast все изменившиеся в памяти данные (которые нужно сохранить, "защищаемые журналом предзаписи") по контрольной точке записываются в файлы, все файлы синхронизируются на один момент, информация об успешной остановке экземпляра записывается в управляющий файл pg_control. Это называется "корректной остановкой". При последующем запуске экземпляра по управляющему файлу определяется, что экземпляр был корректно остановлен и чтения журналов WAL не требуется.

Слайд 28

Остановка экземпляра

immediate - режим немедленного выключения. Родительский процесс postmaster отправит сигнал немедленной остановки (SIGQUIT 3) всем остальным процессам и будет ожидать их завершения. Если какой-либо процесс не завершится в течение 5 секунд, ему будет отправлен сигнал SIGKILL (9). Дальше остановится сам процесс postmaster. Это приведет к "восстановлению" (путем повторного воспроизведения журнала WAL) при следующем запуске экземпляра. Рекомендуется использовать только в крайних случаях, например, подвисании (отсутствии дисковой активности, прогресса) остановки в режиме fast. В Oracle Database такой режим называется "shutdown abort".

Использование pg_ctl stop наиболее удобный способ погасить экземпляр, но можно послать сигнал процессу postgres напрямую:

kill -INT `head -1 $PGDATA/postmaster.pid`

Обратите внимание, что кавычки обратные, а не апострофы.

Сигнал SIGKILL (9) посылать процессу postgres не стоит, так как общая память и семафоры не освободятся до перезагрузки операционной системы или до их освобождения вручную командой ipcrm. Также серверные и фоновые процессы могут остаться в памяти. Посмотреть сегменты общей памяти и семафоры можно командой операционной системы ipcs, а освободить ipcrm.

Не стоит посылать сигнал SIGKILL (9) и другим процессам экземпляра, в том числе серверным (как это принято при работе с Oracle Database), это может привести  к немедленной остановке экземпляра.

Для отсоединения сессий и прерыванию выполняющейся команды (в чужой сессии без её прерывания) в PostgreSQL удобно использовать функции pg_terminate_backend (посылается SIGTERM 15 серверному процессу) и
pg_cancel_backend  (посылается SIGINT 2).

Перед выполнением процедур, требующих корректной остановки, следует убедиться что:
1) все процессы остановленного экземпляра были выгружены из памяти (отсутствуют в операционной системе)

2) в управляющий кластер был записан статус корректной остановки кластера:

pg_controldata | grep state

Database cluster state:      shut down

https://docs.tantorlabs.ru/tdb/ru/17_5/se/server-shutdown.html

Слайд 29

Остановка экземпляра

В диагностическом журнале кластера при выполнении контрольных точек (параметр log_checkpoints=on) будут присутствовать сообщения типа:

CООБЩЕНИЕ:  начата контрольная точка: shutdown immediate

или

LOG:  checkpoint starting: shutdown immediate

В PostgreSQL нет команды shutdown immediate.

Текст в журнале "shutdown immediate" в логе относится к свойствам контрольной точки, а не к режиму остановки экземпляра. При остановке экземпляра в режиме immediate

(команда  pg_ctl stop -m immediate) контрольная точка не выполняется.

Текст в сообщениях о контрольной точке (после LOG:  checkpoint starting: ) означает:

shutdown - контрольная точка вызвана остановкой экземпляра

immediate - выполнить контрольную точку с максимальной скоростью, игнорируя значение параметра  checkpoint_completion_target

force: выполнить контрольную точку даже если с прошлой контрольной точки в WAL ничего не было записано (в кластере не было активности), такое происходит если экземпляр останавливается (shutdown) или в конце восстановления (end-of-recovery)

wait: ждать завершения контрольной точки перед тем как вернуть управление процессу, вызвавшему контрольную точку (без wait процесс запустит контрольную точку и продолжит работать дальше).

end-of-recovery: контрольная точка по окончании наката журналов (восстановление кластера процессом startup)

xlog: контрольная точка вызвана достижением файлами журнала половины размера, заданного параметром max_wal_size ("по размеру", "по требованию")

time: контрольная точка вызвана достижением значения параметра checkpoint_timeout ("по времени")

Слайд 30

Утилиты управления (обёртки команд SQL)

В директории /opt/tantor/db/17/bin путь к которой добавляется для пользователя postgres в переменную окружения PATH в процессе инсталляции находятся утилиты для работы с кластером баз данных. Утилиту initdb мы рассмотрели. Дальше рассмотрим основную утилиту - терминальный клиент psql, которая позволяет передавать на выполнение команды SQL.

Часть действий администратора кластера выполняется не командами SQL (или удобнее выполнять) и для таких действий поставляются утилиты командной строки. Часть из них рассмотрим в течение курса.

Утилиты-оболочки (обёртки, wrappers) для некоторых команд SQL (которые можно послать на выполнение утилитой psql). Иногда удобно выполнять действия в кластере баз данных скриптами командной строки и в таких скриптах удобно использовать утилиты-оболочки вместо написания вызова команды через psql:

psql -c "КОМАНДА SQL"

Разницы в результате между использованием утилит-оболочек и команд SQL нет.

clusterdb - оболочка для команды SQL CLUSTER

createdb - оболочка для команды CREATE DATABASE. Разницы создавать базу данных этой утилитой или командой нет

createuser - оболочка для команды CREATE ROLE

dropdb - оболочка для команды DROP DATABASE

dropuser - оболочка для команды SQL DROP ROLE

reindexdb - оболочка для SQL-команды REINDEX

vacuumdb - оболочка для команды VACUUM

vacuumlo - к вакуумированию (VACUUM) не имеет отношение. vacuumlo удобная для периодического запуска утилита удаления (вычистки) осиротевших больших объектов из баз данных кластера. Автоматизировать удаление осиротевших больших объектов можно разными способами (например, триггерами), эта утилита один из них. Расширение "lo" содержит функцию lo_manage() для использования в триггерах, предотвращающих появление осиротевших больших объектов.

Описание утилит:

https://docs.tantorlabs.ru/tdb/ru/17_5/se/reference-client.html

Слайд 31

Утилиты управления резервированием

pg_archivecleanup используется в значении параметра archive_cleanup_command для удаления неужных файлов WAL на физической реплике (резервном кластере)

pg_basebackup - утилита создания резервных копий кластера (бэкапов) для клонов, реплик и просто для хранения. Может копировать директорию или вытягивать файлы по сети используя для этого протокол репликации

pg_combinebackup - накладывает инкрементальные бэкапы на полные

pg_dump - создает логическую копию объектов базы данных

pg_dumpall - создает логическую копию всего кластера или общих объектов кластера в виде текстового скрпита создания баз данных и объектов. Используется в процедурах обновления основной версии, переноса кластера на другие платформы, сборки, форки  PostgreSQL. Представляет интерес параметр -g позволяющий выгружать общие объекты кластера.

pgcopydb - утилита Tanor Postgres для автоматизации переноса данных на логиечском уровне между базами данных с максимальной скоростью, использует утилиты pg_dump, pg_restore и техники логичекого резервирования

pg_receivewal - используется для вытягивания по протоколу репликации содержимого файлов WAL (поточного архива). Обычно используется для организации хранения журналов WAL на узлах с бэкапами.

pg_recvlogical - для логической репликации, редко используется.

pg_resetwal очищает журнал WAL. Используется с параметром --wal-segsize для изменения размера WAL-сегментов, если захочется изменить их размер после создания кластера. Процедура требует аккуратности и знания что произойдёт с бэкапами и именами файлов WAL. Также для процедуры изменения размера WAL-сегментов критически важно, чтобы кластер был корректно остановлен. Меняют либо оп причине большого количества файлов одной директории, либо по причине того, что максимальный размер буфера журнала в разделяемой памяти (wal_buffers) ограничен размером WAL-файла. Влияние размера WAL-буфера на производительность нелинейна.

pg_restore - утилита восстановления из логических бэкапов созданных утилитой pg_dump в части режимов (в других режимах для восстановления используется psql)

pg_waldump - показывает содержимое WAL-сегментов, используется для отладки в сложных случаях восстановления

https://docs.tantorlabs.ru/tdb/ru/17_5/se/reference-server.html

Слайд 32

Утилиты управления (другие)

pg_amcheck - относится к стандартному расширению (PostgreSQL extension) amcheck, которое имеет набор функций для проверки отсутствия повреждений в объектах в которых физически хранятся данные, называемых отношения (relations). Отношениями (relation, синоним "класс") называются таблицы, индексы, последовательности, представления, внешние (foreign) таблицы, материализованные представления, составные типы. Если функционал amcheck сообщает о повреждениях, то они действительно есть, ложные срабатывания исключены.

pg_checksums - включение/отключение подсчета контрольных сумм блоков данных и проверка блоков данных кластера. В Oracle Database аналог - утилита dbv (dbverify)

pg_rewind - для синхронизации кластеров, обычно для восстановления бывшего мастера (основной, primary кластер) после аварийного переключения на физическую реплику (резервный, stadby кластер), а также в процедурах апгрейда (перехода на новую основную версию)

pg_upgrade - используется при обновлении на новую основную версию PostgreSQL, а также при миграции с ванильного PostgreSQL на Tantor Postgres

pg_test_fsync - используется при настройке параметров записи в WAL-журнал

pg_test_timing - измеряет скорость и стабильность получения меток времени

Полезные утилиты

pg_config - информация о параметрах инсталляции и сборки СУБД

pg_controldata - выводит в текстовом виде содержимое управляющего файла кластера $PGDATA/global/pg_control

pgbench - стандартная утилита PostgreSQL для нагрузочного тестирования

pgcompacttable - утилита Tantor Postgres для уменьшения размеров файлов таблиц

https://docs.tantorlabs.ru/tdb/ru/17_5/se/reference-client.html

Слайд 33

Утилиты управления (продолжение)

pg_isready проверка, что кластер принимает соединения, аналог psql -c "\q". Утилитой только удобнее получать результат, но в psql можно указать дополнительные команды для проверки доступности объектов с точки зрения конкретного клиентского приложения.

oid2name - удобная утилита для поиска к какому объекту относится файл в директории кластера (PGDATA) и табличных пространств, а также другой информации о принадлежности файлов и директорий объектам кластера. Аналогичные действия можно выполнить и командами SQL и функциями SQL, но это гораздо сложнее.

postgresql-check-db-dir - скрипт поверхностной проверки структуры директории PGDATA, вызывается systemd перед вызовом pg_ctl для запуска экземпляра, чтобы убедиться, что в директории PGDATA лежит что-то похоже на директорию кластера.

vacuum_maintenance.py и другие скрипты на языке Python, используются расширением  pg_partman секционирования ("партиционирования", partitioning) таблиц

pg_repack - расширение, которое позволяет не блокируя полностью объект реорганизовать файлы в которых хранятся данные. Аналог команды VACUUM FULL, только без монопольной блокировки.

Рассмотренные ранее в этой главе:

pg_ctl - управляет экземпляром кластера

initdb - создаёт кластера

https://docs.tantorlabs.ru/tdb/ru/17_5/se/reference-server.html

Слайд 34

1c Утилита psql

Слайд 35

Терминальный клиент psql

В PostgreSQL поставляется стандартный терминальный клиент (утилита командной строки) psql.

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

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

psql -f файл_скрипта.sql

psql -c "CREATE SCHEMA sh; CREATE TABLE sh.t (n numeric);"

У psql есть файлы конфигурации. Глобальный лежит в директории на которую указывает параметр pg_config --sysconfdir

для Tantor Postgres это файл /opt/tantor/db/17/etc/postgresql/psqlrc

Локальный для пользователя операционной системы лежит в его домашней директории, значение по умолчанию ~/.psqlrc Местоположение локального файла  может быть переопределено переменной окружения PGCONFIG.

По умолчанию файлы не созданы, но можно создать. В Oracle Database для sqlplus используется файл glogin.sql

Оба файла могут быть сделаны специфичными для версии psql путем добавления дефиса и идентификатора основной или минорной версии PostgreSQL к имени файла. Например, ~/.psqlrc-17 или ~/.psqlrc-16.8 Все файлы применяются, но более специфичный файл превалирует.

С помощью этих файлов можно сделать работу в psql удобнее.

https://docs.tantorlabs.ru/tdb/ru/17_5/se/app-psql.html#psql

Слайд 36

psql: подключение к базе данных

psql подключается к конкретной базе данных в кластере. Для подсоединения к базе нужно пройти аутентификацию, которая обычно настраивается отдельно для локальных подсоединений через Unix-сокеты, сетевых соединений с того же хоста на адрес localhost (127.0.0.1) и соединений с других хостов. PostgreSQL поддерживает разнообразные способы аутентификации, они будут рассмотрены в следующих главах курса. Аутентификация возможна и без пароля, но сессия должна быть сопоставлена с ролью (пользователем) кластера. Подсоединение без сопоставление с ролью заранее созданной в кластере возможно только в однопользовательском режиме (single mode). В однопользовательском режиме подключение выполняется под пользователем который неявно наделяется правами суперпользователя.

Роль (ROLE) и пользователь (USER) синонимы и абсолютно одинаковые понятия. Команды CREATE ROLE и CREATE USER абсолютно одинаковы.

После представления имени роли серверный процесс проверяет привилегии: может ли роль создать сессию (имеет ли она атрибут LOGIN) с конкретной базой данных. Атрибут SUPERUSER не включает в себя право создать сессию, могут существовать роли с атрибутами SUPERUSER и NOLOGIN одновременно.

Подключиться одновременно к нескольким базам даже из одного кластера кластере нельзя. Базы изолированы друг от друга с точки зрения безопасности и привилегий. Для одновременной работы с таблицами в разных базах данных даже если они находятся в одном кластере можно использовать расширения postgres_fdw (Foreign Data Wrapper) или dblink. Для копирования данных между базами данных можно использовать поточную передачу данных ("пайп") и утилиту pg_dump ... | psql ...

Слайд 37

psql: параметры подключения

Параметры командной строки psql которыми можно указать куда и под какой ролью подключаться:

-U роль или --username=роль значение по умолчанию имя пользователя операционной системы из под которого запущен psql

-d имя_базы или --dbname=имя_базы значение по умолчанию имя роли, заданное параметром -U

-h хост  или --host=хост значение по умолчанию /var/run/postgresql (на стороне экземпляра это же значение задано при сборке и отображается в параметре unix_socket_directories) то есть используется локальное соединение через Unix-сокет.

Если psql или другие утилиты выдают ошибку

could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

возможно запускается утилита старой версии (например, из пути /usr/bin/psql). Версия проверяется psql -V

помимо передачи параметра -h можно указать директорию Unix-сокетов в переменной окружения PGHOST, например, export PGHOST=/var/run/postgresql

-p порт или --port=порт  значение по умолчанию 5432
для локальных соединений через Unix-сокет порт тоже используется, так как директория одна и та же для всех кластеров. Если это директория в файловой системе, то в ней основной процесс postgres создаёт файл у которого суффикс является номером порта. Например, /run/postgresql/.s.PGSQL.5432

Также можно использовать сокращенный синтаксис psql имя_базы имя_пользователя. Например, psql postgres postgres

Полезная команда psql для вывода деталей подсоединения: \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Выдаётся имя роли, из под которой было создано соединение (пройдена аутентификация). Команды SET ROLE и SET SESSION AUTHORIZATION не меняют результат \conninfo

Для переподсоединения в psql используется команда

\c имя_базы имя_роли хост порт

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

\c - user1

You are now connected to database "postgres" as user "user1".

\c - - localhost

You are now connected to database "postgres" as user "user1" on host "localhost" (address "127.0.0.1") at port "5432".

Если новое соединение не сможет быть установлено, сохраняется прежнее.

Слайд 38

Получение справки по командам psql

После установки PostgreSQL можно запустить на сервере psql без параметров  и psql подсоединяется локально через Unix-сокет к базе данных postgres под ролью postgres.

команды psql начинаются на обратный слэш \

параметры командной строки psql --help

справка по командам psql  \?

список команд SQL \h
после \h можно ввести начальные слова команды и получить по этой команде помощь

Узнать какие команды SQL формирует psql чтобы исполнить команды, начинающиеся на \d (describe - получить описание объекта) можно установив параметр

\set ECHO_HIDDEN on

Если текст не помещается на экран используется функционал "постраничного выпода" (pager), вы увидите двоеточие.

По нажатии клавиши <ENTER> высветится еще одна строка.

Если нужно высветить следующую страницу, то после двоеточия нужно набрать символ "z"

Если вернуться к предыдущей странице - символ "b" (back)

если хочется прервать вывод можно набрать символ "q" (quit)

если хочется получить помощь и узнать какие есть еще комбинации клавиш то можно набрать после двоеточия букву "h" (help)

Отключить постраничный вывод можно командой \pset psger off

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

История команд по умолчанию доступна по нажатию стрелок вверх/вниз на клавиатуре. История команд набранных интерактивно в psql хранится в файле ~/.psql_history Его местоположение может быть переопределено переменными окружения HISTFILE или PSQL_HISTORY, но смысла в этом нет. Рядом с ~/.psql_history например лежит файл ~/.bash_history с историей команд терминала операционной системы. Названия файлов  начинающиеся с точки в Линукс считаются "скрытыми" файлами. Например, команда ls без параметров не показывает такие файлы.

psql может запускаться с клиентской машины из сборок, отличных от сборок Tantor Postgres. psql работает лучше с серверами той же или более старой основной версии. При подключении к более новой версии СУБД могут отказаться работать команды psql (те который начинаются на обратный слэш).

Слайд 39

Форматирование вывода psql

Посмотреть текущие настройки форматирования можно набрав \pset

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

\watch секунд (выход CTRL+X)

\a включить/выключить выравнивание столбцов по вертикали;

\t  включить/выключить отображение заголовка и итоговой строки (header and footer).

По умолчанию столбцы разделяет вертикальная черта, можно установить другой символ, например, пробел:

\pset fieldsep ' '

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

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

postgres=# \timing

Timing is on.

postgres=# \timing

Timing is off.

Слайд 40

Вывод результата в формате HTML

Если количество столбцов большое и терминальный клиент с пропорциональным шрифтом неудобен для отображения, psql может сформировать результат не в текстовом формате, а в формате HTML. За это отвечает параметр -H или параметр \pset format html

Пример команды, посылающей SQL команду на выполнение и запускающую браузер с результатом в формате HTML:

psql -c "команда;" -H -o f.html | xdg-open f.html

Одной строкой можно получить результат больших выборок в читаемом формате.

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

Слайд 41

Вывод результата в расширенном формате

Если строка результата имеет множество столбцов или длинные значения в полях, можно отображать данные построчно. Переключение вывода выполняется короткой командой \x

Вернуть обычный режим можно еще раз набрав \x

В запросе обычно указывается сортировка и ограничение количества выдаваемых строк: ORDER BY и LIMIT.

Слайд 42

Приглашение к вводу команд (промпт) psql

Случается, что администратор дал команду "не в том окне".
Уменьшить вероятность таких случаев помогает изменение приглашения psql (промпта).

Приглашение к вводу команд (промпт) имеет значения по умолчанию, которые различают первую набранную строку в команде и последующие.

По умолчанию PROMPT2 отличается от PROMPT1 незаметными символами: = и -. Стоит обращать на них внимание.

PROMPT1, PROMPT2 и PROMPT3 задают внешний вид приглашения.

PROMPT1 выдаётся, когда psql ожидает ввода новой команды.

PROMPT2 если в буфере есть строка, например потому что команда не была завершена точкой с запятой или не были закрыты кавычки.

Типичным вопросом является: за что отвечает третий промпт?

PROMPT3 выдаётся при выполнении команды COPY FROM stdin, когда в терминале вводятся данные для вставки в таблицу. Завершает такой режим \.<ENTER>

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

При промышленной эксплуатации удобно менять эти приглашения в файле ~\.psqlrc чтобы видеть к какой базе кластера подсоединены.

Пример установки промпта:

\set PROMPT1 '%[%033[0;31m%]%n%[%033[0m%]@%[%033[0;36m%]%/%[%033[0m%] %[%033[0;33m%]%[%033[5m%]%x%[%033[0m%]%[%033[0m%]%R%# '

\set PROMPT2 '%[%033[0;31m%]%n%[%033[0m%]@%[%033[0;36m%]%/%[%033[0m%] %[%033[0;33m%]%[%033[5m%]%x%[%033[0m%]%[%033[0m%]%R%# '

Слайд 43

Автофиксация транзакций и выполнение команд psql

Команда, начинающаяся на обратный слэш "\" обрабатывается psql. Посмотреть справку по таким командам можно командой \?

Командой \set можно посмотреть переменные psql. Часть переменных предопределена и управляет работой psql. Можно устанавливать на время до выхода из psql свои переменные и пользоваться ими как макросами.

Стоит отличать команды \set \pset set. Последняя относится к SQL и меняет параметры работы серверного процесса на уровне сессии (set session) или транзакции (set local). \pset это предопределённые параметры форматирования вывода psql.

Остальные команды посылаются как текст серверному процессу. Для отправки команды нужно ввести ";" и возврат каретки (клавиша <ENTER> на клавиатуре).

В psql есть нестандартные команды \g \gx \gexec \gset \g которые могут заменять стандартно используемый символ ";" Эти нестандартные команды имеют широкие возможности, но их использование в скриптах делает скрипты непереносимыми - скрипты не смогут выполняться нигде, кроме psql.

Если не набрать ";" а просто набрать возврат каретки, то psql считает что команда многострочная и предыдущие строки накапливаются в буфере.

Если вы хотите очистить буфер, можно набрать \r (сокращение от \reset)

Посмотреть содержимое буфера или последней команды если буфер пуст \p (сокращение от \print)

psql по умолчанию работает в режиме автоматической фиксации транзакции AUTOCOMMIT. Режим автофиксации по умолчанию используется также в программах на языке Java, в спецификации JDBC. Oracle Database в своём терминальном клиенте sqlplus не использует режим автофиксации.

Режим автофиксации означает, что psql неявно после каждой команды которая работает в рамках транзакции (и вместе с такой командой) отправляет команду COMMIT;

Если вы хотите отключить режим автофиксации, вы можете отключить этот режим в системном файле psqlrc или в вашем файле ~/.psqlrc или в своей сессии. За это отвечает параметр
\set AUTOCOMMIT on

\set AUTOCOMMIT off

Слайд 44

Переменные psql

Переменные psql устанавливаются командой \set имя значение. Срок жизни до выхода из psql или до выполнения команды \unset имя.

Переменные могут использоваться как макросы. Ссылаться на переменные можно префиксируя их символом двоеточия.

Пример:

postgres=# \set TEST1 'select user'

postgres=# :TEST1;

   user  

----------

 postgres

(1 row)

postgres=# select * from (:TEST1);

По умолчанию для команд редактирования \ef \ev \e используется vi. Переопределить редактор можно установив переменную окружения

export PSQL_EDITOR=/usr/bin/mcedit

Вместо PLQL_EDITOR можно использовать имена EDITOR или VISUAL.

Либо находясь в psql дать команду \setenv PSQL_EDITOR /usr/bin/mcedit

Либо вставить команду \setenv PSQL_EDITOR /usr/bin/mcedit в файле ~/.psqlrc или глобальном /opt/tantor/db/17/etc/postgresql/psqlrc

В разделе документации "Окружение"
https://docs.tantorlabs.ru/tdb/ru/17_5/se/app-psql.html#APP-PSQL-ENVIRONMENT

указаны переменные окружения операционной системы на которые реагирует psql.

Популярные переменные: PGUSER PGDATABASE PGHOST PGPORT. Они позволяют настроить подсоединение psql без указания параметров к любой базе.

Переменные окружения операционной системы  можно устанавливать командой \setenv в том числе в файле ~/.psqlrc или глобальном /opt/tantor/db/17/etc/postgresql/psqlrc. Другими командами типа \set \pset \! export переменные окружения не устанавливаются.

Слайд 45

Выполнение командных файлов в psql

В psql можно выполнить команду операционной системы не выходя из psql. Для этого используется команда \! команда_линукс

Для вывода результата выполнения команд (POSIX output stream) в файл операционной системы можно использовать команду \o имя_файла. На экран при этом результаты выдаваться не будут.

Для выполнения командного файла можно использовать \i имя_файла

\o checkpoint.sql

select 'checkpoint;' \g (tuples_only=on format=unaligned)

\o  вернуть вывод на экран

\i checkpoint.sql

Также команды из файла (скрппта) можно выполнить так:

psql < checkpoint.sql

psql -f checkpoint.sql

При этом ставить последней в файле команду выхода необязательно, psql сам закончит работу дойдя до конца файла (в отличие от утилиты sqlplus Oracle Database).

Более того, можно сформировать команды и выполнить их не создавая промежуточный файл скрипта. Для этого используется опция \gexec

postgres=# select 'checkpoint;' \gexec

CHECKPOINT

Слайд 46

Графические приложения: pgAdmin

Наиболее популярно универсальное (для разработки и для администрирования) приложение DBeaver, имеющее бесплатную версию.

Приложение можно скачать командой:

wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb

и установить командой:

sudo dpkg -i dbeaver-ce_latest_amd64.deb

Запустить приложение можно из меню Пуск -> Development -> dbeaver-ce или командой:

/usr/bin/dbeaver-ce

DBeaver позволяет отлаживать хранимые процедур и функции, используя интерфейс расширения pldebugger.

Для разработки приложений также может использоваться коммерческое приложение DataGrip от JetBrains, которое интегрируется со средами разработки этой компании : IntelliJ IDEA и PyCharm. Интеграция позволяет проверять синтаксис и автозавершение команд SQL при написании программного кода.

Слайд 47

Графические приложения: pgAdmin

pgAdmin - свободно распространяемая графическая программа для работы с кластерами PostgreSQL. pgAdmin получил популярность, так как появился до создания приложения DBeaver.

Утилита 3 версии pgAdmin3 имела оконный интерфейс, разработка завершилась в 2016 году. В 4 версии pgAdmin4  веб-интерфейс с возможностью создать ссылку на десктопе. Утилита позволяет использовать пошаговую отладку хранимых подпрограмм - является клиентским интерфейсом к функционалу свободно распространяемой библиотеки pldebugger реализующей серверную часть функционала отладчика. Клиентским интерфейсом также является приложение DBeaver, написаноое на языке Java.

pgAdmin3 не работает с PostgreSQL 15 и новее, так как при подсоединении обращается к столбцу datlastsysoid таблицы pg_database системного каталога кластера, который был удалён в 15 версии.

pgAdmin4 может быть установлен в Астра Линукс 1.8

postgres@tantor:~$ sudo apt list | grep pgadmin

pgadmin4-desktop/stable 8.13-astra.se1+ci5 amd64 [upgradable from: 7.4-astra.se1+ci5]

pgadmin4-server/stable 8.13-astra.se1+ci5 amd64 [upgradable from: 7.4-astra.se1+ci5]

pgadmin4-web/stable 8.13-astra.se1+ci5 all

pgadmin4/stable 8.13-astra.se1+ci5 all

Запускать из меню Start-> Development ->pgAdmin 4

В меню pgAdmin4: в File->Preferences-> Paths -> Binary Paths--> PostgreSQL 17 установить путь /opt/tantor/db/17/bin чтобы из меню Tools можно было запускать "PSQL Tool".

Слайд 48

Графические приложения: Платформа Tantor

Платформа Tantor - программное обеспечение  для управления любыми СУБД, основанными на PostgreSQL и кластерами Patroni. Позволяет удобно управлять большим числом кластеров. Относится к классу программных продуктов, в который входит Oracle Enterprise Manager Cloud Control.

Платформа Tantor активно развивается, чтобы удовлетворять потребностям администрирования PostgreSQL.

В Платформе Tantor есть SQL-редактор, в котором можно просматривать объекты, выполнять команды, создавать процедуры и функции.

https://docs.tantorlabs.ru/tp/5.3/instances/DB_browser.html

Слайд 49

Демонстрация

Скачивание инсталлятора

Установка разрешения на исполнение инсталлятора

Установка адреса расположения дистрибутивов

Установка с созданием базы данных

Проверка, что кластер работает

Остановка служб

Деинсталляция

Слайд 50

Практика

Создание кластера

Создание кластера утилитой initdb

Режим одного пользователя

Передача параметров экземпляру в командной строке

Локализация

Однобайтные кодировки

Использование утилит управления

Настройка терминального клиента psql

Использование терминального клиента psql

Восстановление сохраненного кластера

Слайд 51

2a Архитектура PostgreSQL

Слайд 52

Экземпляр PostgreSQL

Процесс postgres (устаревшее название postmaster) - процесс, обслуживающий PostgreSQL (database server). Это первый процесс, который запускается, прослушивает порты сетевых интерфейсов и создаёт файл Unix-сокета, через который принимает локальные соединения. Этот процесс запускает (порождает, fork) другие процессы и является их родительским процессом. Это серверные (традиционное название - backend) процессы, обслуживающие сессии клиентов и фоновые (background) процессы, которые выполняют полезные задачи по обслуживанию кластера баз данных.

Кластер баз данных PostgreSQL - набор баз данных, хранящихся в файловой системе в директории PGDATA в виде наборов файлов. Один экземпляр процесса postgres всегда обслуживает только один кластер баз данных, а кластер баз данных может обслуживаться только одним экземпляром процесса postgres. На одном хосте физическом или виртуальном, (в одной операционной системе) может работать несколько экземпляров postgres, обслуживающих разные кластера баз данных. Экземпляры postgres должны использовать разные порты как сетевых интерфейсов, так и разные файлы Unix-сокетов.

Экземпляр PostgreSQL - процесс postgres, порожденные им процессы операционной системы, память, которую используют эти процессы. У каждого процесса есть локальная память, доступ к которой имеет только этот процесс и разделяемая память (shared memory), доступ к которой имеет несколько процессов или даже все процессы экземпляра.

 Список процессов экземпляра PostgreSQL:

postgres@tantor:~$ ps -eLo ppid,pid,cmd | egrep 'PPID|postgres'

   PPID     PID CMD

      1  743184 /opt/tantor/db/17/bin/postgres                  основной процесс

 743184  743185 postgres: logger                                процесс записи в журнал logging collector

 743184  743186 postgres: checkpointer                          фоновый процесс контрольной точки

 743184  743187 postgres: background writer                     процесс фоновой записи

 743184  743189 postgres: walwriter                             процесс фоновой записи в журнал

 743184  743190 postgres: autovacuum launcher                   процесс запуска автовакуума

 743184  743191 postgres: pg_stat_advisor BackgroundTaskManager процесс расширения

 743184  743192 postgres: autoprewarm leader                    процесс расширения pg_prewarm

 743184  743193 postgres: logical replication launcher          процесс запуска логической репликации

 644740  795748 psql -d demo -U alice -h /var/run/postgresql    клиент, утилита psql

 743184  795749 postgres: alice demo [local] idle               процесс, обслуживающий psql

Клиент подсоединился через Unix-сокет к базе данных demo под пользователем alice. Клиент обслуживается своим серверным процессом с номером процесса 795749. Остальные процессы экземпляра фоновые.

Слайд 53

Экземпляр PostgreSQL

В PostgreSQL нет строгого закрепления действий за процессами. Серверные процессы могут читать файлы с данными в память (буферный кэш), посылать в операционную систему блоки на запись, посылать на запись из журнального буфера в журнальные файлы, выполнять вакуумирование по команде VACUUM.

Основные ресурсы, которые использует экземпляр: диск, память, процессор, сеть. Наиболее нагруженный ресурс: диск. Чтобы уменьшить нагрузку, используется кэширование содержимого файлов данных в кэше буферов. Кэш буферов - структура в разделяемой памяти, обычно, имеющая самый большой размер, поэтому кэшу буферов и вспомогательным структурам памяти и обслуживающим его процессам уделяется больше внимания. Это процессы контрольной точки (checkpointer) и фоновой записи (background writer, bgwriter). Все изменения в данных выполняются через кэш буферов, прямых изменений в файлах данных нет. Для временных таблиц используется аналог буферного кэша, но только в памяти серверного процесса.

Кэш буферов - это кэш на чтение и запись (изменения удерживаются в памяти). Отказоустойчивость реализуется журналированием изменений, которые выполняются в файлах данных через кэш буферов. Журнал называется WAL (Write Ahead Log, журнал предварительной записи) и состоит из файлов размером по 16Мб (по умолчанию). В файлы журналов пишут серверные процессы и любые другие процессы, которые вносят изменения в данные, но есть и вспомогательный процесс walwriter.

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

Процесс startup после того как завершит восстановление останавливается.

Процессы walsender запускаются при подсоединении клиентов (pg_basebackup, pg_receivewal, процессы реплик walreceiver) по протоколу репликации.

Слайд 54

Запуск экземпляра, процесс postgres

Основные шаги при запуске экземпляра:

1. Запускается процесс postgres ("postmaster")

2. Считываются файлы параметров конфигурации, параметры комбинируются с параметрами командной строки и переменными окружения.

3. Проверяются права на директорию PGDATA, они должны быть 0700 или 0750

4. Проверяется наличие управляющего файла pg_control, текущей директорией для процесса устанавливается PGDATA, в ней создается файл postmastr.pid, инициализируется TLS, загружаются разделяемые библиотеки, указанные в параметре shared_preload_libraries, регистрируется обработчик на случай исчезновения процесса для корректной остановки дочерних процессов, инициализируется (по параметрам конфигурации) менеджер памяти, регистрируется обработчик закрытия сетевых сокетов

В postmaster.pid  первая строка хранит PID запущенного postmaster. Файл проверяется раз раз в минуту. Если файла нет или хранящийся в нем PID не равен PID процесса , то процесс postgres остановится по сигналу SIGQUIT

5. регистрируются сокеты по всем адресам (параметр конфигурации listen_addresses). Создается файл UNIX-сокета

6. Считывается файл с настройками аутентификации pg_hba.conf

7. Запускается процесс startup, который определяет состояние кластера по управляющему файлу pg_control (если директория PGDATA не была восстановлена из бэкапа, то есть нет файла backup.label), выполняет восстановление кластера, если нужно. Экземпляр открывается на чтение-запись, если кластер не является физической репликой (нет файла standby.signal).

8. Пока процесс startup разбирается что ему делать, postgres запускает остальные фоновые процессы.

Серверные процессы запускаются, если есть запрос на создание сессии от клиентов.

Все порожденные процессы, в том числе серверные, периодически проверяются на существование.

Слайд 55

Запуск серверного процесса

Серверный процесс запускается процессом postgres, если клиент хочет подсоединиться (пришел запрос на порт серверного сокета или Unix-сокет).

Основные шаги при запуске серверного процесса:

1. При запуске процесс получает структуру (часть памяти) PGPROC из списка свободных и устанавливает поля в начальные значения. Структуры находятся в разделяемой памяти. PGPROC используют и фоновые процессы.

2. Процесс регистрирует таймауты в соответствии со значениями параметров конфигурации, которые можно посмотреть командой:

psql \dconfig *_timeout

чтобы серверный процесс мог прерваться при превышении значений этих параметров

3. Инициализируются три кэша в локальной памяти серверного процесса:

Кэш для быстрого доступа к таблицам (RelationCache)

Кэш таблиц системного каталога (CatalogCache)

Кэш планов выполнения команд (PlanCache)

4 . Выделяется память под менеджер "порталов" TopPortalContext. Портал - исполняющийся запрос, появляющийся в расширенном протоколе на этапе привязки, после парсинга. Порталы могут быть именованными (например, названием курсора) или безымянными - SELECT.

6. Обновляются значения параметров конфигурации. которые устанавливаются на этапе соединения. Выполняется задержка по параметру post_auth_delay

7. Обновляется структура PgBackendStatus.

8. Клиенту отправляются параметры: версия сервера, временная зона, параметры локализации, форматы типов данных, пара порядковый номер процесса (id) и токен отмены, по которым клиент может отменить выполнение запроса.

9. Серверный процесс загружает библиотеки, заданные в параметрах загружаются session_preload_libraries и local_preload_libraries. В процессе загрузки проверяется совместимость библиотек с версией PostgreSQL. Если библиотека была загружена ранее (shared_preload_libraries), то процесс просто получает указатель на загруженную библиотеку.

10. Выделяется память под обработку сообщений от клиента

11. Клиенту отправляется сообщение ReadyForQuery - серверный процесс готов принимать команды от клиента.

Слайд 56

Общая память процессов экземпляра

Примеры структур в разделяемой (общей) памяти экземпляра:

Proc Array, PROC, PROCLOCK, Lock Hashes, LOCK, Multi-XACT Buffers, Two-Phase Structs, Subtrans Buffers, CLOG Buffers (transaction), XLOG Buffers, Shared Invalidation, Lightweight Locks, Auto Vacuum, Btree Vacuum, Buffer Descriptors, Shared Buffers, Background Writer Synchronized Scan, Semaphores, Statistics. Всего структур - более 60.

К этим структурам имеют доступ процессы экземпляра. Расширения могут создавать собственные структуры. Список структур и их размеров:

select * from (select *, lead(off) over(order by off)-off as true from pg_shmem_allocations) as a order by 1;

       name        |    off    |   size    | allocated_size |   true_size    

-------------------+-----------+-----------+----------------+-----------

 <anonymous>       |           |   4946048 |        4946048 |          

 Archiver Data     | 147726208 |         8 |            128 |       128

...

 XLOG Recovery Ctl |   4377728 |       104 |            128 |       128

                   | 148145024 |   2849920 |        2849920 |          

(74 rows)

Строка с пустым (NULL) именем отражает неиспользуемую память. Строка с именем "<anonymous>" отражает суммарный размер структур, память под которые выделялась без назначения имени.

В представлении не показываются структуры, выделяемые и освобождаемые  "динамически" - в процессе работы экземпляра. Динамические структуры общей памяти используются рабочими процессами (workers). Рабочие процессы, например, используются для параллельного выполнения команд SQL.

Только две структуры разделяемой памяти экземпляра могут использовать огромные страницы (HugePages). Это кэш буферов (размер задаётся параметром конфигурации shared_buffers) и память, выделяемая фоновыми процессами (память для них резервируется параметром конфигурации min_dynamic_shared_memory ).

Слайд 57

Кэш таблиц системного каталога

CatalogCache выделяется в локальной памяти каждого процесса в контексте CacheMemoryContext. При обращении к таблицам системного каталога процесс ищет данные в этом кэше. Если данных не нашлось, то выбираются строки таблиц системного каталога и кэшируются. Для доступа к таблицам системного каталога используется индексный метод доступа. Если в таблице системного каталога не нашлась запись, то кэшируется признак отсутствия записи (negative entry). Например, ищется таблица, а такой таблицы нет, в локальном кэше процесса сохраняется запись, что таблицы с таким названием нет. Ограничений на размер CacheMemoryContext нет, это не круговой буфер и не стек.

При фиксации транзакции создающей, удаляющей, изменяющий объекта, приводящие к изменениям в таблицах системного каталога, процесс выполнивший изменения сохраняет сообщение о том, что объект изменён, в кольцевой буфер shmInvalBuffer в разделяемой памяти. Буфер может сохранить до 4096 сообщений (константа MAXNUMMESSAGES). Размер буфера:

select * from (select *, lead(off) over(order by off) - off as true_size from pg_shmem_allocations) as a where name='shmInvalBuffer' order by 1;

      name      |    off    | size  | allocated_size | true_size

----------------+-----------+-------+----------------+-----------

 shmInvalBuffer | 146865024 | 68128 |          68224 |     68224

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

Для того, чтобы кэши системного каталога процессов не сбрасывались слишком часто, нужно, чтобы объекты (в том числе временные таблицы) не создавались и удалялись слишком часто. Таблицы, в том числе временные не стоит часто создавать и удалять в течение сессии.

Статистика сброса кэшей и числа сообщений не собирается стандартными расширениями PostgreSQL.

Слайд 58

Представление pg_stat_slru

В PGDATA есть поддиректории, в которых сохраняются служебные данные кластера. Для ускорения доступа на чтение-запись в файлы этих директорий используются кэши в разделяемой памяти экземпляра. Файлы форматированы блоками по 8Кб. Кэши работают по простому алгоритму вытеснения давно неиспользуемых данных (simple least-recently-used, SLRU). Статистику использования кэшей можно посмотреть в представлении:

select name, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates from pg_stat_slru;

      name        | blks_hit | blks_read | blks_written | blks_exists | flushes | truncates

------------------+----------+-----------+--------------+-------------+---------+-----------

 commit_timestamp |        0 |         0 |            0 |           0 |     103 |         0

 multixact_member |        0 |         0 |            0 |           0 |     103 |         0

 multixact_offset |        0 |         3 |            2 |           0 |     103 |         0

 notify           |        0 |         0 |            0 |           0 |       0 |         0

 serializable     |        0 |         0 |            0 |           0 |       0 |         0

 subtransaction   |        0 |         0 |           26 |           0 |     103 |       102

 transaction      |   349634 |         4 |           87 |           0 |     103 |         0

 other            |        0 |         0 |            0 |           0 |       0 |         0

В PostgreSQL начиная с 17 версии (в Tantor Postgres начиная с 15 версии) размеры кэшей SLRU настраиваются.

Статистику из представления можно использовать для установки параметров конфигурации, задающих размеры SLRU-кэшей: \dconfig *_buffers

        Parameter         | Value

--------------------------+-------

 commit_timestamp_buffers | 256kB

 multixact_member_buffers | 256kB

 multixact_offset_buffers | 128kB

 notify_buffers           | 128kB

 serializable_buffers     | 256kB

 shared_buffers           | 128MB

 subtransaction_buffers   | 256kB

 temp_buffers             | 8MB

 transaction_buffers      | 256kB

 wal_buffers              | 4MB

https://docs.tantorlabs.ru/tdb/ru/17_5/se/monitoring-stats.html

Слайд 59

Локальная память процесса

Примеры структур в локальной памяти серверного процесса:

RelationСache, CatalogСache, PlanСache, work_mem, maintenans_work_mem, StringBuffer, temp_buffers

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

Параметры, наиболее сильно влияющие на выделение локальной памяти процесса:

work_mem - выделяется для обслуживание узлов (шагов) плана выполнения (если шаги способны выполняться одновременно), в том числе каждым параллельным процессом. Вместе с параметром hash_mem_multiplier влияет на память, выделяемую каждым серверным и параллельным процессом. Например, при соединении таблиц хэшированием (Hash Join) объем выделяемой памяти на обслуживание JOIN будет work_mem*hash_mem_multiplier*(Workers + 1).

maintenance_work_mem значение по умолчанию 64MB. Задает объем памяти, выделяемый каждым процессом (серверным, параллельным), участвующем в выполнении команд VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Число параллельных процессов ограничивается параметром max_parallel_maintenance_workers. Распараллеливается создание индексов и обычный (без FULL) вакуум. При вакуумировании только на фазе вакуумирования индексов (другие фазы не распараллеливаются) один индекс может обрабатывать один (а не несколько) параллельный процесс. Будут ли использоваться параллельные процессы зависит от размера индексов.

В Tantor Postgres есть параметры конфигурации, позволяющие настроить использование локальной памяти enable_temp_memory_catalog и enable_large_allocations.

Слайд 60

Представление pg_backend_memory_contexts

Представление показывает память, выделенную серверным процессом, обслуживающим текущую сессию. Контекст памяти (memory contexts) - набор частей памяти (chunks), которые выделяются процессом для выполнения какой-то задачи. Если памяти не хватает, она выделяется дополнительно. Для выполнения подзадачи может выделяться дочерний контекст. Контексты образуют дерево (иерархию). Корень дерева - TopMemoryContext. Цель такой организации выделения и учета памяти, чтобы при освобождении памяти не забыть освободить какую-нибудь часть, иначе возникнет "утечка" памяти. Когда освобождается контекст памяти, то освобождаются все дочерние контексты памяти.

В представлении pg_backend_memory_contexts иерархию отображают столбцы: name (название контекста памяти), parent (название родительского контекста памяти, level. В столбце ident содержится детализация того, что хранится в контексте. Пример иерархического запроса:

with recursive dep as

(select name, total_bytes as total, ident, parent, 1 as level, name as path from pg_backend_memory_contexts where parent is null

union all

select c.name, c.total_bytes, c.ident, c.parent, p.level + 1, p.path || '->' || c.name

from dep p, pg_backend_memory_contexts c

where c.parent = p.name)

select * from dep limit 3;

        name         |total|ident|      parent      |level|             path

---------------------+-----+-----+------------------+-----+--------------------------------

TopMemoryContext     |97664|     |                  |   1 |TopMemoryContext

TopTransactionContext| 8192|     | TopMemoryContext |   2 |TopMemoryContext->TopTransactionContext

PLpgSQL cast cache   | 8192|     | TopMemoryContext |   2 |TopMemoryContext->PLpgSQL cast cache

(3 rows)

Память, выделенная текущему серверному процессу:

select sum(total_bytes), sum(used_bytes), sum(free_bytes) from pg_backend_memory_contexts;

   sum   |   sum   |  sum  

---------+---------+--------

 2114816 | 1380760 | 734056

В 18 версии в представление будут добавлены столбцы id , parent_id, path.

Слайд 61

Функция pg_log_backend_memory_contexts(PID)

Память чужих сессий можно вывести в диагностический лог кластера функцией:

select pg_log_backend_memory_contexts(PID);

В журнал будут выведены сообщения:

LOG: statement: SELECT pg_log_backend_memory_contexts(111);

...

LOG: logging memory contexts of PID 111

LOG: level: 0; TopMemoryContext: 60528 total in 5 blocks; 16224 free (6 chunks); 44304 used

LOG: level: 1; TopTransactionContext: 8192 total in 1 blocks; 6728 free (0 chunks); 1464 used

...

LOG: level: 2; AV dblist: 8192 total in 1 blocks; 7840 free (0 chunks); 352 used

LOG: Grand total: 658848 bytes in 38 blocks; 270616 free (32 chunks); 388232 used

Начиная с 17 версии в команду EXPLAIN добавлена опция memory (по умолчанию отключена), которая выдает сколько памяти использовал планировщик и общую память серверного  процесса в виде строки в конце плана:

Memory: used=N bytes, allocated=N bytes

На этапе планирования при использовании большого (тысячи) числа секций секционированной таблицы может использоваться много памяти.

Память под "TID store" при вакуумировании учитывается в строках:

level: 1; TopTransactionContext: 33570864 total in 3 blocks; 11056 free (405 chunks); 33559808 used

level: 2; _bt_pagedel: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used

Grand total: 35510408 bytes in 234 blocks; 736144 free (626 chunks); 34774264 used

Выделяется память размером maintenance_work_mem в контексте (памяти для) транзакции. После выполнения транзакции в процессе вакуумирования, память контекста транзакции освобождается.

Слайд 62

Структуры памяти, обслуживающие буферный кэш

Доступ к данным кластера идёт через буферный кэш. Для настройки производительности стоит познакомиться в общих чертах с моделью его работы. Это может пригодиться для предположения о том, где и в каких случаях могут возникать узкие места. Случаи: необычное или экстремальное использование функционала баз данных. Как пример: частое создание и удаление таблиц, прогрев кэша.

Дальше приводятся названия структур в разделяемой памяти экземпляра, относящиеся к буферному кэшу и формулы расчета их размера в байтах. Названия приведены как в представлении pg_shmem_allocations. Названия типов, макросов приведены, чтобы было удобно искать текст в исходном коде PostgreSQL, если захочется детально изучить алгоритмы.

Buffer Blocks - сам буферный кэш. Размер каждого буфера равен размеру блока. Точный размер выделенной памяти: NBuffers * (BLKSZ=8196) + (PG_IO_ALIGN_SIZE=4096). NBuffers - количество разделяемых буферов задается параметром конфигурации shared_buffers (по умолчанию 16384, максимум 1073741823=30 бит).

Buffer Descriptors - описатели (заголовки) буферов. Структура описателя называется  BufferDesc. Располагается в отдельной части памяти, один описатель для каждого буфера. Размер: NBuffers * (BufferDescPadded = 64) - описатели выровнены по cache line, который у современных процессоров обычно 64 байта. В этих 64 байтах находятся:

1) структура BufferTag, в которой указан прямой (самодостаточный, то есть хранящий всё чтобы найти файл и в нём блок) адрес блока на диске:

typedef struct buftag

{

 Oid  spcOid; oid табличного пространства (название симлинка в PGDATA/pg_tblspc)

 Oid  dbOid; oid базы данных (поддиректория)

 RelFileNumber relNumber; название файла, представляет собой число

 ForkNumber  forkNum; номер форка (enum на 5 возможных значений: -1 invalid, 0 main, 1 fsm, 2 vm, 3 init)

 BlockNumber blockNum; номер блока относительно 0 блока 0 файла, размер 4 байта, максимум задается макросом MaxBlockNumber

} BufferTag;

Размер BufferTag 17 байт. Размер с выравниванием 20 байт.

2) int buf_id - порядковый номер буфера в кэше буферов начиная с нуля.

Слайд 63

Структуры памяти, обслуживающие буферный кэш (продолжение)

3) 32 бита, которые содержат: 18 бит refcount, 4 бита usage count (от 0 до BM_MAX_USAGE_COUNT=5, всего 6 градаций), 10 бит флагов, в которых отражается:

1 - BM_LOCKED стоит блокировка на заголовок буфера

2 - BM_DIRTY грязный

3 - BM_VALID блок не поврежден

4 - BM_TAG_VALID блок существует в файле на диске

5 - BM_IO_IN_PROGRESS буфер в процессе заполнения образом с диска или записи на диск

6 - BM_IO_ERROR предыдущая операция ввода-вывода сбойнула

7 - BM_JUST_DIRTIED в процессе записи на диск загрязнился

8 - BM_PIN_COUNT_WAITER ждет снятия закреплений другими процессами чтобы заблокировать буфер для изменения

9 - BM_CHECKPOINT_NEEDED помечен процессом контрольной точки для записи на диск

10 - BM_PERMANENT относится к журналируемому объекту.

Часть этих флагов используют bgwriter и checkpointer для отслеживания не поменялся ли блок в процессе его записи на диск, так как в процессе записи (операция ввода-вывода) устанавливаются разделяемые блокировки. Это ускоряет работу СУБД.

4) int wait_backend_pgprocno - идентификатор процесса, который ждет снятия закреплений буфера другими процессами (waiting for pincount 1)

Если процесс хочет работать с блоком, то он его ищет в буферном кэше. Если находит, то закрепляет. Множество процессов может закрепить буфер. Если процессу буфер не нужен, то процесс снимает закрепление.

Закрепление препятствует замене в буфере блока другим блоком.

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

5) int freeNext - ссылка на номер следующего свободного блока. После запуска экземпляра, пока все буфера свободны указывает на следующий по порядку буфер. Используется логика связанного списка ("Linked List").

6) LWLock content_lock - легковесная блокировка на содержимое буфера

Легковесная блокировка buffer content_lock устанавливаются процессами на короткое время. Два вида: Exclusive и Shared.

Слайд 64

Поиск свободного буфера

Если процесс хочет работать с блоком, то он его ищет в буферном кэше. Если находит, то закрепляет. Несколько  процессов могут одновременно закрепить ("pin") буфер. Если процессу буфер не нужен, то процесс снимает закрепление. Закрепление препятствует замене в буфере блока другим блоком.

Buffer Strategy Status размер: BufferStrategyControl = 128. Хранит данные для поиска свободных блоков:

slock_t buffer_strategy_lock;  Spinlock - для доступа к этой структуре

pg_atomic_uint32 nextVictimBuffer; счетчик поиска свободных буферов, указатель на следующий свободный буфер получается остатком от деления на NBuffers

int firstFreeBuffer; первый неиспользовавшийся (после рестарта экземпляра) буфер, после того как все буфера начнут использоваться примет значение -1.

int lastFreeBuffer;

uint32 completePasses; используется для статистики

pg_atomic_uint32 numBufferAllocs; используется для статистики

int bgwprocno; номер процесса bgwriter для уведомления

Слово "стратегия" используется в смысле "метод" из фразы "buffer cache replacement strategy".

Буфера могут возвращаться в список свободных и цепочка свободных буферов может дополняться и и даже создаваться заново после того как firstFreeBuffer стал равен -1.  Такое происходит после удаления объекта или при удалении отдельного файла объекта или усечении файла (в том числе вакуумом) или при удалении базы данных.

Один блок не может находиться в двух и более буферах, он может находиться только в одном буфере.

Слайд 65

Алгоритм вытеснения грязных буферов

Checkpoint BufferIds размер: NBuffers*(CkptSortItem=20) 320Кб если буферный пул 128Мб. Память выделяемая с с запасом для сортировки грязных буферов, которые будут записаны на диск по контрольной точке. Процесс checkpointer перед посылкой грязных блоков на запись сортирует их отдельно по каждому файлу. Если файл на HDD, это уменьшает перемещения головок HDD.

Команда на запись на диск посылается через checkpoint_flush_after блоков (от 0 до 2Мб, по умолчанию 256Кб), bgwriter_flush_after=512Кб, wal_writer_flush_after=1Мб

Checkpointer Data размер: CheckpointerShmemSize() = 524416

Buffer IO Condition Variables NBuffers * (ConditionVariableMinimallyPadded = 16) флаги (переменные), которые переводят процесс в сон и пробуждают его если флаг изменится. Реализуют метод ожидания.

Shared Buffer Lookup Table, прежнее название Buffer Mapping Table.Третья по важности и вторая по размеру структура. В отличие от других структур является хэш-таблицей. Не самая оптимальная (нет поиска по диапазону), но приемлемая.

Размеры девяти структур, в том числе Shared Buffer Lookup (Mapping)Table в pg_shmem_allocations выдаются не верные. Оценить размеры структур можно по столбцу off (offset) от соседней структуры:

select * from (select *, lead(off) over(order by off) - off as true_size from pg_shmem_allocations) as a where a.true<>a.allocated_size order by 1;

            name            |    off    | size | allocated_size | true_size  

----------------------------+-----------+------+----------------+---------

...

 Shared Buffer Lookup Table | 141706624 | 2896 |           2944 |  928640