Разработка серверной части приложений PostgreSQL. Базовый курс

Курс сохраняет официальный порядок тем. Русские материалы извлечены из PDF в читаемый нативный Markdown для дальнейшей редакторской работы.

Состояние материалов: 官方 PDF 已提取为原生 Markdown

Базовый язык: Базовый язык: English. Текущий английский архив используется как базовый; русская версия сохранена для сверки глав.

О курсе

Функции, процедуры, триггеры, динамические команды и серверная логика на SQL и PL/pgSQL.

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

Содержание

Дополнительные материалы

• catalogs.pdf
• datatypes.pdf
• dev1_new_features.pdf
• dev1_student_guide.pdf
• unix_commands.pdf

Редакторский статус

Русский комплект доступен в виде нативного Markdown. Если английский комплект не найден, английский маршрут оставлен для будущего источника.

• Матрица версий
• Рабочий список
• Источники и разрешения

00 Занятие 00: Разработка серверной части приложений / Базовый курс

Page 1: Разработка серверной части приложений / Базовый курс

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Разработчикам

{#source-figure-002 caption=“Source slide page 2: Разработчикам”}

• DEV2, 4 дня

Комментарии преподавателя

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

Для прохождения этих курсов необходимы предварительные знания основ языка SQL. Специального курса по языку SQL в нашей линейке нет, но существует множество книг и других образовательных ресурсов, с помощью которых можно освоить SQL. Мы рекомендуем книгу Евгения Моргунова «PostgreSQL. Основы языка SQL»:

https://postgrespro.ru/education/books/sqlprimer

Базовым курсом для разработчиков является данный курс DEV1.

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

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

Курс DBS рассказывает про настройку подключений клиентов к СУБД и про управление доступом внутри самой СУБД.

В курсе PGPRO рассматриваются дополнительные возможности, которые предоставляет СУБД Postgres Pro Enterprise.

https://postgrespro.ru/education/courses

Page 3: Администраторам

{#source-figure-003 caption=“Source slide page 3: Администраторам”}

• DBA2, 4 дня

Комментарии преподавателя

Для администраторов мы предлагаем следующие курсы.

В базовом курсе DBA1 даются общие сведения об архитектуре PostgreSQL, процессе установки, базовой настройке и управлении сервером. Рассматриваются основные задачи администрирования, вопросы управления доступом. Приводится обзор резервного копирования и репликации.

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

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

Курсы DBS, QPT и PGPRO являются общими для разработчиков и администраторов.

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

Page 4: О курсе DEV1

Продолжительность: 4 дня

• Предварительные знания
  • основы SQL
  • опыт работы с любым процедурным языком программирования
  • минимальные сведения о работе в Unix
• Какие навыки будут получены
  • общие сведения об архитектуре PostgreSQL
  • использование основных объектов БД: таблиц, индексов, представлений
  • программирование на стороне сервера на языках SQL и PL/pgSQL
  • использование основных типов данных, включая записи и массивы
  • организация взаимодействия с клиентской частью приложения

Комментарии преподавателя

Базовый курс знакомит разработчиков приложений, работающих над серверной частью, с основами PostgreSQL и написанием хранимых процедур и функций на языках SQL и PL/pgSQL.

Page 5: Используемые материалы

Подготовленная виртуальная машина

• ОС Xubuntu 24.04
• PostgreSQL 16 с документацией на русском языке
• учебное веб-приложение «Книжный магазин»
• DBeaver
• Учебные материалы
  • руководство слушателя
  • презентации, демонстрации, практические задания и их решение
  • (в форматах html и pdf)
  • справочные материалы — функции и типы данных PostgreSQL,
  • схема основных таблиц системного каталога с командами psql,
  • некоторые команды Unix

Комментарии преподавателя

Если вы проходите курс самостоятельно, обязательно начните со знакомства с Руководством слушателя. В числе прочего в нем написано, где скачать и как использовать виртуальную машину курса и прочие материалы. Все материалы курса доступны по адресу:

https://postgrespro.ru/education/courses/DEV1

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

Учебные материалы (презентации, демонстрации, практические задания и их решения) доступны в двух форматах. Формат html удобен для онлайн-работы, он позволяет копировать фрагменты текста и кода. Документ в формате pdf разбит на страницы и удобен для печати.

Дополнительные справочные материалы помогут быстро найти нужную информацию.

В виртуальной машине установлен DBeaver. В курсе мы используем только psql, но при желании можно воспользоваться и графической средой.

Page 6: Организационные вопросы

День: ~8 академических часов + обед (1час)

• Каждая тема, как правило, состоит из
  • презентации и демонстраций: ~25–60 мин
  • практических заданий: ~20–30 мин, включая перерыв

Page 7: День 1

Базовый инструментарий

• 1. Установка и управление, psql
• Архитектура
  • 2. Общее устройство PostgreSQL
  • 3. Изоляция и многоверсионность
  • 4. Буферный кеш и журнал
• Организация данных
  • 5. Логическая структура
  • 6. Физическая структура

Комментарии преподавателя

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

Page 8: День 2

{#source-figure-008 caption=“Source slide page 8: День 2”}

Учебное приложение

• 7. Книжный магазин
• SQL
  • 8. Функции
  • 9. Процедуры
  • 10. Составные типы
• PL/pgSQL
  • 11. Обзор и конструкции языка

Комментарии преподавателя

Начиная с модуля «Учебное приложение» второго дня занятий каждая тема содержит два набора практических заданий: основных, связанных с приложением «Книжный магазин» (они отмечены значком книги) и дополнительных. За время, которое отводится на задания (около 30 минут), выполнить всю практику невозможно. Используйте дополнительные задания для самостоятельной работы.

Page 9: День 3

PL/pgSQL (продолжение)

• 12. Выполнение запросов
• 13. Курсоры
• 14. Динамические команды
• 15. Массивы
• 16. Обработка ошибок

Page 10: День 4

PL/pgSQL (продолжение)

• 17. Триггеры
• 18. Отладка
• Управление доступом
  • 19. Обзор
• Резервное копирование
  • 20. Логическое резервирование

01 Занятие 01: Обзор базового инструментария / Установка и управление, psql

Page 1: Обзор базового инструментария / Установка и управление, psql

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Темы

• Варианты установки PostgreSQL
• Управление сервером
• Журнал сообщений сервера
• Настройка параметров конфигурации
• Использование psql

Page 3: Кластер БД, сервер, клиент

{#source-figure-003 caption=“Source slide page 3: Кластер БД, сервер, клиент”}

• клиент

Комментарии преподавателя

Начнем с основных понятий.

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

Когда эта программа выполняется, мы называем ее сервером PostgreSQL или экземпляром сервера. Пока сервер представляется для нас «черным ящиком», но постепенно мы познакомимся с тем, как он устроен.

Данные, которыми управляет PostgreSQL, хранятся в базах данных. Один экземпляр PostgreSQL одновременно работает с несколькими базами данных. Этот набор баз данных называется кластером баз данных. Подробнее мы будем говорить о базах данных в теме «Организация данных. Логическая структура».

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

Итак: кластер баз данных — это данные в файлах; сервер или экземпляр сервера — программа, управляющая кластером баз данных, а клиент — программа, позволяющая «общаться» с сервером.

Page 4: Установка

Варианты

• готовые пакеты (предпочтительный способ)
• установка из исходных кодов
• без установки — облачные сервисы
• Расширения
  • дополнительный функционал
  • устанавливаются отдельно
  • в поставке с сервером — модули и программы (~50 штук)

Комментарии преподавателя

Предпочтительный вариант установки PostgreSQL — использование пакетных менеджеров (таких, как apt или rpm) и готовых пакетов. В этом случае получается понятная, поддерживаемая и легко обновляемая установка. Пакеты существуют для большинства операционных систем.

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

Готовые пакеты и исходные коды: http://www.postgresql.org/download/

Кроме того, можно использовать готовые облачные решения, что позволяет обойтись вообще без установки. Такую возможность дают многие ведущие зарубежные (Amazon RDS, Google Cloud SQL, Microsoft Azure) и отечественные (Yandex Cloud, Облако Mail.ru) платформы.

В курсе мы будем использовать виртуальную машину с ОС Xubuntu и сервер PostgreSQL 16, установленный из пакета для этой ОС. В этом случае сразу настраивается автоматический запуск и останов сервера PostgreSQL при запуске и останове операционной системы.

Для PostgreSQL существует большое количество расширений, которые подключают новый функционал к СУБД «на лету», без изменения ядра системы. В состав дистрибутива входит 50 расширений.

https://postgrespro.ru/docs/postgresql/16/contrib

https://postgrespro.ru/docs/postgresql/16/contrib-prog

Список доступных расширений и статус их установки можно посмотреть в представлении pg_available_extensions.

Page 5: Управление сервером

Утилита для управления

• pg_ctlcluster
• pg_ctl
• Основные задачи
  • запуск сервера
  • останов сервера
  • обновление параметров конфигурации

Комментарии преподавателя

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

В пакетном дистрибутиве для Ubuntu доступ к утилите pg_ctl осуществляется не напрямую, а через специальную обертку pg_ctlcluster. Справку по использованию pg_ctlcluster можно получить командой:

$ man pg_ctlcluster

Также можно получить информацию об установленных кластерах и их текущем состоянии при помощи команд:

$ pg_lsclusters

$ pg_ctlcluster status

Более подробная информация об управлении сервером для администраторов баз данных:

https://postgrespro.ru/docs/postgresql/16/app-pg-ctl

https://postgrespro.ru/docs/postgresql/16/runtime

Page 6: Установка и управление

В виртуальной машине курса установка выполнена из пакета. Каталог установки PostgreSQL:

student$ ls -l /usr/lib/postgresql/16

total 8
drwxr-xr-x 2 root root 4096 ноя 25 17:25 bin
drwxr-xr-x 4 root root 4096 ноя 25 18:28 lib

Владелец ПО сервера — пользователь root. Кластер баз данных автоматически инициализируется при установке из пакета и находится в каталоге /var/lib/postgresql/16/main. В последующих темах мы будем ссылаться на этот каталог как PGDATA, по имени переменной ОС, которую можно установить для использования в некоторых утилитах сервера. Владельцем каталога является пользователь postgres. Вот содержимое каталога:

student$ sudo ls -l /var/lib/postgresql/16/main

total 96
drwx------ 1 postgres postgres 4096 апр 10 15:33 base
drwx------ 1 postgres postgres 4096 апр 10 15:33 global
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_commit_ts
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_dynshmem
drwx------ 4 postgres postgres 4096 ноя 25 18:29 pg_logical
drwx------ 4 postgres postgres 4096 ноя 25 18:28 pg_multixact
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_notify
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_replslot
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_serial
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_snapshots
drwx------ 1 postgres postgres 4096 апр 10 15:33 pg_stat
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_stat_tmp
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_subtrans
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_tblspc
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_twophase
-rw------- 1 postgres postgres    3 ноя 25 18:28 PG_VERSION
drwx------ 1 postgres postgres 4096 ноя 25 18:28 pg_wal
drwx------ 2 postgres postgres 4096 ноя 25 18:28 pg_xact
-rw------- 1 postgres postgres   88 ноя 25 18:28 postgresql.auto.conf
-rw------- 1 postgres postgres  130 апр 10 15:33 postmaster.opts
-rw------- 1 postgres postgres  109 апр 10 15:33 postmaster.pid

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

student$ sudo pg_ctlcluster 16 main stop

Запустить сервер:

student$ sudo pg_ctlcluster 16 main start

Перезапустить:

student$ sudo pg_ctlcluster 16 main restart

Обновить конфигурацию:

student$ sudo pg_ctlcluster 16 main reload

Получить информацию о сервере:

student$ sudo pg_ctlcluster 16 main status

pg_ctl: server is running (PID: 88989)
/usr/lib/postgresql/16/bin/postgres "-D" "/var/lib/postgresql/16/main" "-c"
"config_file=/etc/postgresql/16/main/postgresql.conf"

Список установленных экземпляров (можно без sudo):

student$ pg_lsclusters

Page 7: Page 7

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main
/var/log/postgresql/postgresql-16-main.log

Page 8: Журнал сообщений

В журнал записываются

• служебные сообщения сервера
• сообщения пользовательских сеансов
• сообщения приложений
• Настройка журнала
  • расположение
  • формат записей
  • какие события регистрировать

Комментарии преподавателя

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

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

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

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

Например, вывод в форматах csv и json удобен для автоматизации анализа журнала.

https://postgrespro.ru/docs/postgresql/16/runtime-config-logging

Page 9: Журнал сообщений сервера

Журнал сообщений сервера находится здесь:

student$ ls -l /var/log/postgresql/postgresql-16-main.log

-rw-r----- 1 postgres adm 51181 апр 10 15:33 /var/log/postgresql/postgresql-16-main.log

Заглянем в конец журнала:

student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log

2026-04-10 15:33:20.897 MSK [82414] LOG:  background worker "logical replication
launcher" (PID 82420) exited with exit code 1
2026-04-10 15:33:20.898 MSK [82415] LOG:  shutting down
2026-04-10 15:33:20.912 MSK [82415] LOG:  checkpoint starting: shutdown immediate
2026-04-10 15:33:20.926 MSK [82415] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.029 s;
sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=3504 kB;
lsn=0/1D95E50, redo lsn=0/1D95E50
2026-04-10 15:33:20.937 MSK [82414] LOG:  database system is shut down
2026-04-10 15:33:21.808 MSK [88989] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2026-04-10 15:33:21.808 MSK [88989] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-04-10 15:33:21.824 MSK [88989] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-04-10 15:33:21.843 MSK [88992] LOG:  database system was shut down at 2025-11-25
18:29:40 MSK
2026-04-10 15:33:21.872 MSK [88989] LOG:  database system is ready to accept connections

Page 10: Параметры конфигурации

Для всего экземпляра

• основной файл параметров — postgresql.conf

ALTER SYSTEM — postgresql.auto.conf

• Для текущего сеанса

SET/RESET
set_config()

• Просмотр текущего значения

SHOW
current_setting()
pg_settings
\dconfig

Комментарии преподавателя

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

Настройки всего сервера обычно задаются в конфигурационных файлах. Основной конфигурационный файл — postgresql.conf, он редактируется вручную. Второй конфигурационный файл — postgresql.auto.conf — предназначен для изменения специальной командой ALTER SYSTEM. Параметры, установленные через ALTER SYSTEM, имеют приоритет над параметрами в postgresql.conf.

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

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

Варианты установки и управления параметрами: https://postgrespro.ru/docs/postgresql/16/config-setting Текущие значения параметров доступны в представлении pg_settings: https://postgrespro.ru/docs/postgresql/16/view-pg-settings

Page 11: Параметры конфигурации

Основной файл конфигурации postgresql.conf расположен в этом каталоге:

student$ ls -l /etc/postgresql/16/main

total 60
drwxr-xr-x 2 postgres postgres  4096 ноя 25 18:28 conf.d
-rw-r--r-- 1 postgres postgres   315 ноя 25 18:28 environment
-rw-r--r-- 1 postgres postgres   143 ноя 25 18:28 pg_ctl.conf
-rw-r----- 1 postgres postgres  5743 ноя 25 18:28 pg_hba.conf
-rw-r----- 1 postgres postgres  2640 ноя 25 18:28 pg_ident.conf
-rw-r--r-- 1 postgres postgres 29960 ноя 25 18:28 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 ноя 25 18:28 start.conf

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

=> SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

Параметр work_mem задает объем памяти, который будет использоваться для внутренних операций сортировки и размещения хеш-таблиц, прежде чем будут задействованы временные файлы на диске. 4MB — это значение по умолчанию и оно слишком мало. Допустим, мы хотим увеличить его до 16MB для всего экземпляра. Для этого есть различные пути. Во-первых, можно внести изменение в postgresql.conf, раскомментировав и изменив строку, где определяется параметр:

student$ grep '#work_mem' /etc/postgresql/16/main/postgresql.conf

#work_mem = 4MB
# min 64kB

Во-вторых, можно поместить определение параметра в файл с суффиксом .conf в каталоге /etc/postgresql/16/main/conf.d или в пользовательский файл конфигурации, местоположение которого следует задать в директиве include основного конфигурационного файла postgresql.conf. В-третьих, можно изменить значение параметра с помощью команды SQL — что мы и сделаем:

=> ALTER SYSTEM SET work_mem TO '16MB';

ALTER SYSTEM

Такое изменение попадает не в postgresql.conf, а в файл postgresql.auto.conf, который находится в каталоге PGDATA:

student$ sudo cat /var/lib/postgresql/16/main/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'

Чтобы изменение вступило в силу, нужно перечитать конфигурационные файлы. Для этого можно воспользоваться pg_ctlcluster, либо использовать функцию SQL:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Убедимся, что новое значение параметра применилось. Кроме команды SHOW, можно сделать это таким образом:

=> SELECT current_setting('work_mem');

 current_setting
-----------------
 16MB
(1 row)

Чтобы восстановить значение параметра по умолчанию, достаточно вместо SET использовать команду RESET (и, конечно, перечитать конфигурационные файлы):

Page 12: Page 12

=> ALTER SYSTEM RESET work_mem;

ALTER SYSTEM

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Большинству параметров можно установить новое значение для текущего сеанса прямо во время его выполнения. Например, если мы собираемся выполнить запрос, сортирующий большой объем данных, то для сеанса можно увеличить значение work_mem:

=> SET work_mem = '64MB';

SET

Новое значение действует только в текущем сеансе или даже в текущей транзакции (при указании SET LOCAL). Еще один способ проверить текущее значение — выполнить запрос к представлению:

=> SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';

   name   | setting | unit
----------+---------+------
 work_mem | 65536   | kB
(1 row)

Можно увидеть значение параметра и с помощью команды \dconfig:

=> \dconfig work_mem

List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 64MB
(1 row)

Page 13: Клиент psql

Терминальный клиент для работы с PostgreSQL

• Поставляется вместе с СУБД
• Используется администраторами и разработчиками для интерактивной работы и выполнения скриптов

Комментарии преподавателя

Для работы с СУБД PostgreSQL существуют различные сторонние инструменты, рассмотрение которых не входит в рамки курса.

В курсе мы будем использовать терминальный клиент psql:

1. psql — это единственный клиент, поставляемый вместе с СУБД.

2. Навыки работы c psql пригодятся разработчикам и администраторам вне зависимости от того, с каким инструментом они будут работать.

Для интерактивной работы в psql встроена поддержка readline, программ постраничного просмотра результатов запросов (таких, как less и pspg), а также подключения внешних редакторов. Возможности psql позволяют взаимодействовать с ОС, просматривать содержимое системного каталога, создавать скрипты для автоматизации повторяющихся задач.

https://postgrespro.ru/docs/postgresql/16/app-psql

Page 14: Подключение

При запуске psql нужно указать параметры подключения. К обязательным параметрам относятся: имя базы данных, по умолчанию совпадает с именем пользователя; имя пользователя (роль), по умолчанию совпадает с именем пользователя ОС; узел (host), по умолчанию — локальное соединение; порт, по умолчанию — обычно 5432. Параметры указываются так:

student$ psql -d база -U роль -h узел -p порт

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

student$ psql

Проверим текущее подключение:

=> \conninfo

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

Команда \connect выполняет новое подключение, не покидая psql. Ее можно сократить до \c. Мы будем указывать необязательную часть имени команды в квадратных скобках: \c[onnect].

Справочная информация

Справку по psql можно получить не только в документации, но и прямо в системе. Команда

student$ psql --help

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

student$ man psql

Утилита psql умеет выполнять команды SQL и свои собственные команды, которые начинаются с обратной косой черты, как \conninfo. Команды psql всегда однострочные — в отличие от команд SQL. Внутри psql есть возможность получить список и краткое описание его собственных команд: \? выдает список команд psql, \h[elp] выдает список команд SQL, которые поддерживает сервер, а также синтаксис конкретной команды SQL.

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

Клиент psql умеет выводить результаты запросов в разных форматах: формат с выравниванием значений; формат без выравнивания; расширенный формат. Формат с выравниванием используется по умолчанию:

=> SELECT name, setting, unit FROM pg_settings LIMIT 7;

            name            |  setting   | unit
----------------------------+------------+------
 allow_in_place_tablespaces | off        |
 allow_system_table_mods    | off        |
 application_name           | psql       |
 archive_cleanup_command    |            |
 archive_command            | (disabled) |
 archive_library            |            |
 archive_mode               | off        |
(7 rows)

Ширина столбцов выровнена по значениям. Также выводится строка заголовков и итоговая строка. Команды psql для переключения режима выравнивания: \a — переключатель режима: с выравниванием/без выравнивания. \t — переключатель отображения строки заголовка и итоговой строки. Отключим выравнивание, заголовок и итоговую строку:

=> \a \t

Output format is unaligned.
Tuples only is on.

=> SELECT name, setting, unit FROM pg_settings LIMIT 7;

allow_in_place_tablespaces|off|
allow_system_table_mods|off|
application_name|psql|
archive_cleanup_command||
archive_command|(disabled)|
archive_library||
archive_mode|off|

Page 15: Page 15

=> \a \t

Output format is aligned.
Tuples only is off.

Такой формат неудобен для просмотра, но может оказаться полезным для автоматической обработки результатов. Расширенный формат удобен, когда нужно вывести много столбцов для одной или нескольких записей. Для этого вместо точки с запятой указываем в конце команды \gx:

=> SELECT name, setting, unit, category, context, vartype,
    min_val, max_val, boot_val, reset_val
    FROM pg_settings
    WHERE name = 'work_mem' \gx

-[ RECORD 1 ]----------------------
name      | work_mem
setting   | 4096
unit      | kB
category  | Resource Usage / Memory
context   | user
vartype   | integer
min_val   | 64
max_val   | 2147483647
boot_val  | 4096
reset_val | 4096

Если расширенный формат нужен не для одной команды, а постоянно, можно включить его переключателем \x. Все возможности форматирования результатов запросов доступны через команду \pset.

Взаимодействие с ОС и выполнение скриптов

Из psql можно выполнять команды shell:

=> \! pwd

/home/student

С помощью запроса SQL можно сформировать несколько других запросов SQL и записать их в файл, используя команду \o[ut]:

=> \a \t \pset fieldsep ''

Output format is unaligned.
Tuples only is on.
Field separator is "".

=> \o dev1_tools.sql

=> SELECT format('SELECT %L AS tbl, count(*) FROM %I;', tablename, tablename)
FROM pg_tables LIMIT 3;

На экран (в стандартный вывод) ничего не попало. Посмотрим в файле:

=> \! cat dev1_tools.sql

SELECT 'pg_statistic' AS tbl, count(*) FROM pg_statistic;
SELECT 'pg_type' AS tbl, count(*) FROM pg_type;
SELECT 'pg_foreign_table' AS tbl, count(*) FROM pg_foreign_table;

Вернем вывод на экран и восстановим форматирование по умолчанию.

=> \o \t \a

Tuples only is off.
Output format is aligned.

Выполним теперь эти команды из файла с помощью \i[nclude]:

=> \i dev1_tools.sql

     tbl      | count
--------------+-------
 pg_statistic |   409
(1 row)

   tbl   | count
---------+-------
 pg_type |   613
(1 row)

       tbl        | count
------------------+-------
 pg_foreign_table |     0
(1 row)

Есть и другие способы выполнить команды, в том числе из файлов. После выполнения команд сеанс psql будет завершен: psql < имя_файла psql -f имя_файла psql -c ‘команда’ (работает только для одной команды)

Переменные psql

По аналогии с shell, psql имеет собственные переменные. Установим переменную:

Page 16: Page 16

=> \set TEST Hi!

Чтобы подставить значение переменной, надо предварить ее имя двоеточием:

=> \echo :TEST

Hi!

Значение переменной можно сбросить:

=> \unset TEST

=> \echo :TEST

:TEST

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

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Для выполнения запроса достаточно набрать:

=> :top5

   tablename    |  bytes
----------------+---------
 pg_proc        | 1245184
 pg_rewrite     |  745472
 pg_attribute   |  720896
 pg_description |  630784
 pg_statistic   |  294912
(5 rows)

Присвоение значения переменной top5 удобно записать в стартовый файл .psqlrc в домашнем каталоге пользователя. Команды из .psqlrc будут автоматически выполняться каждый раз при старте psql. Без параметров \set выдает значения всех переменных, включая встроенные. Справку по встроенным переменным можно получить так:

\? variables

Выйти из psql можно с помощью команд \q[uit], quit, exit или нажав Ctrl+D:

=> \q

Page 17: Итоги

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

• Пакетные дистрибутивы учитывают особенности ОС, которые нужно знать
  • как запускать и останавливать сервер
  • расположение файлов конфигурации
  • расположение журнала сервера
• psql — клиент для работы с PostgreSQL

Page 18: Практика

1. Установите для всех сеансов значение параметра work_mem равным 8 Мбайт. Обновите конфигурацию и проверьте, что изменения вступили в силу. Восстановите значение по умолчанию. 2. Запишите в файл ddl.sql команду CREATE TABLE на создание любой таблицы. Запишите в файл populate.sql команды на вставку строк в эту таблицу. Выполните оба скрипта и проверьте, что таблица создалась и в ней появились записи. 3. Найдите в журнале сервера строки за сегодняшний день.

Комментарии преподавателя

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

student:~$ psql

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

student/student=# CREATE DATABASE tools_overview;

CREATE DATABASE

student/student=# \c tools_overview
You are now connected to database "tools_overview" as user
"student".
student/tools_overview=#

1. Воспользуйтесь командой ALTER SYSTEM.

2. Если результат запроса не помещается в окно терминала, psql передает его утилите less для постраничной выдачи. Чтобы вернуться в режим ввода команд, нажмите клавишу q.

Page 19: 1. Параметры конфигурации

Подключаемся к базе данных:

student$ psql

Задаем значение параметра:

=> ALTER SYSTEM SET work_mem = '8MB';

ALTER SYSTEM

Обновляем конфигурацию:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Проверяем значение параметра:

=> \dconfig work_mem

List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 8MB
(1 row)

Вернем значение по умолчанию:

=> ALTER SYSTEM RESET work_mem;

ALTER SYSTEM

Применяем изменения:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

2. Выполнение скриптов в psql

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

student$ cat > ~/ddl.sql <<EOF
CREATE TABLE keywords (
    word text,
    category text,
    description text
);
EOF

Запишем команды для заполнения таблицы keywords в файл populate.sql:

student$ cat > ~/populate.sql <<EOF
INSERT INTO keywords
    SELECT word, catcode, catdesc FROM pg_get_keywords();
EOF

Создаем базу данных и подключаемся к ней:

=> CREATE DATABASE tools_overview;

CREATE DATABASE

=> \c tools_overview

You are now connected to database "tools_overview" as user "student".

Выполняем скрипты и проверяем записи в таблице:

Page 20: Page 20

=> \i ddl.sql

CREATE TABLE

=> \i populate.sql

INSERT 0 471

=> SELECT * FROM keywords LIMIT 10;

   word    | category | description
-----------+----------+-------------
 abort     | U        | unreserved
 absent    | U        | unreserved
 absolute  | U        | unreserved
 access    | U        | unreserved
 action    | U        | unreserved
 add       | U        | unreserved
 admin     | U        | unreserved
 after     | U        | unreserved
 aggregate | U        | unreserved
 all       | R        | reserved
(10 rows)

3. Просмотр журнала

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

student$ tail /var/log/postgresql/postgresql-16-main.log

2026-04-10 15:42:47.674 MSK [131116] LOG:  database system is shut down
2026-04-10 15:42:48.608 MSK [133485] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2026-04-10 15:42:48.608 MSK [133485] LOG:  listening on IPv4 address "127.0.0.1", port
5432
2026-04-10 15:42:48.632 MSK [133485] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-04-10 15:42:48.649 MSK [133488] LOG:  database system was shut down at 2025-11-25
18:29:40 MSK
2026-04-10 15:42:48.675 MSK [133485] LOG:  database system is ready to accept connections
2026-04-10 15:42:51.592 MSK [133485] LOG:  received SIGHUP, reloading configuration files
2026-04-10 15:42:51.594 MSK [133485] LOG:  parameter "work_mem" changed to "8MB"
2026-04-10 15:42:52.216 MSK [133485] LOG:  received SIGHUP, reloading configuration files
2026-04-10 15:42:52.217 MSK [133485] LOG:  parameter "work_mem" removed from
configuration file, reset to default

Созданную нами базу можно удалить, предварительно отключившись от нее (подробности будут в модуле «Организация данных»)

=> \c postgres

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

=> DROP DATABASE tools_overview;

DROP DATABASE

02 Занятие 02: Архитектура / Общее устройство PostgreSQL

Page 1: Архитектура / Общее устройство PostgreSQL

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Темы

• Клиент-серверный протокол
• Транзакционность и механизмы ее реализации
• Схема обработки и способы выполнения запросов
• Процессы и структуры памяти
• Хранение данных на диске и работа с ними
• Расширяемость системы

Page 3: Клиент и сервер

{#source-figure-003 caption=“Source slide page 3: Клиент и сервер”}

подключение аутентификация формирование запросов выполнение запросов управление транзакциями поддержка транзакционности

Комментарии преподавателя

Клиентское приложение — например, psql или любая другая программа, написанная на любом языке программирования, — подключается к серверу и «общается» с ним. Чтобы клиент и сервер понимали друг друга, они должны использовать один и тот же протокол взаимодействия. Обычно клиент использует драйвер, реализующий протокол и предоставляющий набор функций для использования в программе. Внутри драйвер может пользоваться стандартной реализацией протокола (библиотекой libpq), либо реализовывать этот протокол самостоятельно.

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

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

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

Другая особенность — поддержка согласованной работы транзакций.

https://postgrespro.ru/docs/postgresql/16/protocol

Page 4: Транзакции

{#source-figure-004 caption=“Source slide page 4: Транзакции”}

• атомарность — все или ничего согласованность — ограничения целостности и пользовательские ограничения изоляция — влияние параллельных процессов
• долговечность — сохранность данных даже после сбоя

Комментарии преподавателя

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

От транзакций ожидают выполнения четырех свойств (ACID):

● Атомарность: транзакция либо выполняется полностью, либо не выполняется вовсе. Для этого начало транзакции отмечается командой BEGIN, а конец — либо COMMIT (фиксация изменений), либо ROLLBACK (отмена изменений).

● Согласованность: транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние (согласованность - соблюдение установленных ограничений).

● Изоляция: другие транзакции, выполняющиеся одновременно с данной, не должны оказывать на нее влияния.

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

За управление транзакциями (то есть за определение команд, составляющих транзакцию, и за фиксацию или отмену транзакции) в PostgreSQL, как правило, отвечает клиентское приложение. На стороне сервера управлять транзакциями могут хранимые процедуры.

https://postgrespro.ru/docs/postgresql/16/sql-begin

https://postgrespro.ru/docs/postgresql/16/sql-savepoint

https://postgrespro.ru/docs/postgresql/16/transactions

Page 5: Управление транзакциями

По умолчанию psql работает в режиме автофиксации:

=> \echo :AUTOCOMMIT

on

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

=> CREATE TABLE t(
  id integer,
  s text
);

CREATE TABLE

=> INSERT INTO t(id, s) VALUES (1, 'foo');

INSERT 0 1

Увидит ли таблицу и строку другая транзакция?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

Да. Сравните:

=> BEGIN; -- явно начинаем транзакцию

BEGIN

=> INSERT INTO t(id, s) VALUES (2, 'bar');

INSERT 0 1

Что увидит другая транзакция на этот раз?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

Изменения еще не зафиксированы, поэтому не видны другой транзакции.

=> COMMIT;

COMMIT

А теперь?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

Режим без автофиксации неявно начинает транзакцию при первой выданной команде; изменения надо фиксировать самостоятельно.

=> \set AUTOCOMMIT off

=> INSERT INTO t(id, s) VALUES (3, 'baz');

INSERT 0 1

Page 6: Page 6

Что на этот раз?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

Изменения не видны; транзакция была начата неявно.

=> COMMIT;

COMMIT

Ну и наконец:

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

Восстановим режим, в котором psql работает по умолчанию.

=> \set AUTOCOMMIT on

Отдельные изменения можно откатывать, не прерывая транзакцию целиком (хотя необходимость в этом возникает нечасто).

=> BEGIN;

BEGIN

=> SAVEPOINT sp; -- точка сохранения

SAVEPOINT

=> INSERT INTO t(id, s) VALUES (4, 'qux');

INSERT 0 1

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | qux
(4 rows)

Обратите внимание: свои собственные изменения транзакция видит, даже если они не зафиксированы. Теперь откатим все до точки сохранения. Откат к точке сохранения не подразумевает передачу управления (то есть не работает как GOTO); отменяются только те изменения состояния БД, которые были выполнены от момента установки точки до текущего момента.

=> ROLLBACK TO sp;

ROLLBACK

Что увидим?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

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

=> INSERT INTO t(id, s) VALUES (4, 'xyz');

Page 7: Page 7

INSERT 0 1

=> COMMIT;

COMMIT

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Page 8: Выполнение запроса

{#source-figure-008 caption=“Source slide page 8: Выполнение запроса”}

разбор ← системный каталог переписывание ← правила планирование ← статистика выполнение ← данные

Комментарии преподавателя

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

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

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

Далее запрос выполняется в соответствии с планом и результат возвращается клиенту — целиком и полностью:

https://postgrespro.ru/docs/postgresql/16/query-path

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

Page 9: Подготовка операторов

{#source-figure-009 caption=“Source slide page 9: Подготовка операторов”}

разбор переписывание

• привязка ← значения параметров планирование выполнение

Комментарии преподавателя

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

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

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

При выполнении запроса выполняется привязка конкретных значений параметров. Если необходимо, здесь выполняется планирование (в некоторых случаях PostgreSQL запоминает план запроса и не выполняет повторно этот шаг). Затем запрос выполняется.

Еще одно преимущество использования подготовленных операторов — невозможность внедрения SQL-кода.

https://postgrespro.ru/docs/postgresql/16/sql-prepare

https://postgrespro.ru/docs/postgresql/16/sql-execute

Page 10: Подготовленные операторы

В SQL оператор подготавливается командой PREPARE (эта команда является расширением PostgreSQL, она отсутствует в стандарте):

=> PREPARE q(integer) AS
  SELECT * FROM t WHERE id = $1;

PREPARE

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

=> EXECUTE q(1);

 id |  s
----+-----
  1 | foo
(1 row)

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

=> SELECT * FROM pg_prepared_statements \gx

-[ RECORD 1 ]---+---------------------------------
name            | q
statement       | PREPARE q(integer) AS           +
                |   SELECT * FROM t WHERE id = $1;
prepare_time    | 2026-04-10 15:33:49.332002+03
parameter_types | {integer}
result_types    | {integer,text}
from_sql        | t
generic_plans   | 0
custom_plans    | 1

Page 11: Курсоры

{#source-figure-011 caption=“Source slide page 11: Курсоры”}

разбор переписывание

• привязка ← значения параметров планирование выполнение
• получение результата

Комментарии преподавателя

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

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

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

Открытый курсор представлен на сервере так называемым порталом. Это слово встречается в документации; в первом приближении можно считать «курсор» и «портал» синонимами.

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

https://postgrespro.ru/docs/postgresql/16/sql-declare

https://postgrespro.ru/docs/postgresql/16/sql-fetch

Page 12: Курсоры

При выполнении команды SELECT сервер передает, а клиент получает сразу все строки:

=> SELECT * FROM t ORDER BY id;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Курсор позволяет получать данные построчно.

=> BEGIN;

BEGIN

=> DECLARE c CURSOR FOR
  SELECT * FROM t ORDER BY id;

DECLARE CURSOR

=> FETCH c;

 id |  s
----+-----
  1 | foo
(1 row)

Размер выборки можно указывать:

=> FETCH 2 c;

 id |  s
----+-----
  2 | bar
  3 | baz
(2 rows)

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

=> FETCH 2 c;

 id |  s
----+-----
  4 | xyz
(1 row)

=> FETCH 2 c;

 id | s
----+---
(0 rows)

FETCH просто перестанет возвращать строки. В обычных языках программирования всегда есть возможность проверить это условие. Как в вашем языке программирования получать данные построчно с помощью курсора? Есть ли возможность НЕ пользоваться курсором и получить все строки сразу? Как настраивается размер выборки для курсора? По окончании работы открытый курсор закрывают, освобождая ресурсы:

=> CLOSE c;

CLOSE CURSOR

Однако курсоры автоматически закрываются по завершению транзакции, так что можно не закрывать их явно. (Исключение составляют курсоры, открытые с указанием WITH HOLD.)

Page 13: Page 13

=> COMMIT;

COMMIT

Page 14: Процессы и память

{#source-figure-014 caption=“Source slide page 14: Процессы и память”}

Комментарии преподавателя

Между обращениями клиента сервер должен хранить вспомогательную информацию: разобранные запросы и их планы, состояние открытых курсоров (порталы). Где и как он это делает?

Сервер PostgreSQL состоит из нескольких взаимодействующих процессов. В первую очередь при старте сервера запускается процесс, традиционно называемый postmaster. Он запускает все остальные процессы (с помощью системного вызова fork в Unix) и «присматривает» за ними — если какой-нибудь процесс завершится аварийно, postmaster перезапустит его (или перезапустит весь сервер, если сочтет, что процесс мог повредить общие данные).

Работу сервера обеспечивает ряд фоновых служебных процессов. В следующих темах этого модуля мы поговорим об основных из них.

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

Postmaster слушает входящие соединения. Для каждого вновь подключающегося клиента postmaster порождает обслуживающий процесс (backend) и дальше клиент общается уже с этим процессом. Обслуживающий процесс, в том числе, производит аутентификацию.

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

Page 15: Много клиентов

{#source-figure-015 caption=“Source slide page 15: Много клиентов”}

Комментарии преподавателя

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

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

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

С таблицами сложнее, поскольку блокировки нужно удерживать до конца транзакций (то есть потенциально в течение длительного времени), из-за чего масштабируемость может пострадать. Поэтому PostgreSQL использует механизм многоверсионности (MVCC, multiversion concurrency control) и изоляцию на основе снимков данных: одни и те же данные могут одновременно существовать в разных версиях, а каждый процесс видит собственную (но всегда согласованную) картину данных. Это позволяет блокировать только те процессы, которые пытаются изменить данные, уже измененные, но еще не зафиксированные другими процессами.

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

Page 16: Пул соединений

{#source-figure-016 caption=“Source slide page 16: Пул соединений”}

Комментарии преподавателя

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

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

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

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

Подробнее вопросы применения пула соединений рассматриваются в курсе DEV2.

Page 17: Хранение данных

{#source-figure-017 caption=“Source slide page 17: Хранение данных”}

Комментарии преподавателя

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

Логически файлы разделены на страницы (иногда используется термин блок). Страница обычно имеет размер 8 Кбайт. Его в некоторых пределах можно поменять (16 или 32 Кбайта), но только при сборке сервера. Таким образом, собранный и запущенный кластер может работать со страницами только одного размера.

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

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

Page 18: Хранение данных

{#source-figure-018 caption=“Source slide page 18: Хранение данных”}

Комментарии преподавателя

Буферный кеш PostgreSQL располагается в общей памяти, чтобы все процессы имели к нему доступ.

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

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

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

{#source-figure-019 caption=“Source slide page 19: Расширяемость”}

Комментарии преподавателя

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

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

● добавляют поддержку языков программирования (помимо стандартных SQL, PL/pgSQL, PL/Perl, PL/Python и PL/Tcl);

● вводят новые типы данных и операторы для работы с ними;

● создают новые типы индексов для эффективной работы с разнообразными типами данных (помимо стандартных B-деревьев, хеш-индексов, GiST, SP-GiST, GIN, BRIN);

● запускают служебные фоновые процессы для выполнения дополнительных задач;

● позволяют подключаться к внешним источникам данных;

● собирают информацию о нагрузке на систему, выполняют мониторинг и строят отчеты;

● позволяют исследовать системные структуры данных.

Подробнее расширения рассматриваются в курсах DBA2 и DEV2.

Page 20: Итоги

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

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

03 Занятие 03: Архитектура / Изоляция и многоверсионность

Page 1: Архитектура / Изоляция и многоверсионность

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Темы

• Многоверсионность
• Снимок данных
• Уровни изоляции
• Очистка и ее горизонт
• Блокировки
• Статус транзакций

Page 3: Многоверсионность

{#source-figure-003 caption=“Source slide page 3: Многоверсионность”}

Наличие нескольких версий одной и той же строки

• версии различаются временем действия
• время = номер транзакции (номера выдаются по возрастанию)

Комментарии преподавателя

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

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

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

Версии надо как-то отличать друг от друга. Для этого каждая из них хранит две отметки, определяющие «время действия» данной версии.

В качестве времени здесь используются всегда возрастающие номера транзакций (в действительности все немного сложнее, но это детали). Когда строка создается, она помечается номером транзакции, выполнившей команду INSERT. Когда удаляется — версия помечается номером транзакции, выполнившей DELETE (но физически не удаляется). UPDATE состоит из двух операций DELETE и INSERT.

https://postgrespro.ru/docs/postgresql/16/mvcc-intro

Page 4: Снимок данных

{#source-figure-004 caption=“Source slide page 4: Снимок данных”}

Согласованный срез на определенный момент времени

• номер транзакции — определяет момент времени
• список активных транзакций — чтобы не смотреть
• на еще не зафиксированные изменения

Комментарии преподавателя

В PostgreSQL применяется изоляция на основе снимков данных.

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

Снимок — это не физическая копия данных, а всего несколько чисел:

● номер последней зафиксированной транзакции на момент создания снимка;

● список активных транзакций на этот момент.

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

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

Какие-то строки вовсе не попадут в снимок: строка 3 удалена до того, как был построен снимок, поэтому в снимке ее нет.

Page 5: Уровни изоляции

Read Uncommitted

• не поддерживается PostgreSQL: работает как Read Committed
• Read Committed — используется по умолчанию
  • снимок строится на момент начала оператора
  • одинаковые запросы могут каждый раз получать разные данные
• Repeatable Read
  • снимок строится на момент начала первого оператора транзакции
  • транзакция может завершиться ошибкой сериализации
• Serializable
  • полная изоляция, но дополнительные накладные расходы
  • транзакция может завершиться ошибкой сериализации

Комментарии преподавателя

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

Самый нестрогий уровень Read Uncommitted допускает грязные чтения. Он не поддерживается PostgreSQL, поскольку не представляет практической ценности и не дает выигрыша в производительности.

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

На уровне Repeatable Read снимок строится в начале транзакции (при выполнении первого оператора) — поэтому все запросы в одной транзакции видят одни и те же данные. Этот уровень удобен, например, для отчетов, состоящих из нескольких запросов.

Уровень Serializable гарантирует полную изоляцию: можно писать операторы так, как будто транзакция работает одна. Но при этом некоторая доля транзакций будет прерываться; приложение должно уметь повторять такие транзакции.

https://postgrespro.ru/docs/postgresql/16/transaction-iso

Page 6: Видимость версий строк

Как убедиться в том, что одна и та же строка может существовать в нескольких версиях? Создадим таблицу:

=> CREATE TABLE t(s text);

CREATE TABLE

И вставим одну строку. Напомним, что если не начать транзакцию явно командой BEGIN, psql выполняет команду и немедленно фиксирует результат:

=> INSERT INTO t VALUES ('Первая версия');

INSERT 0 1

Начнем транзакцию и выведем ее номер:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                736
(1 row)

Транзакция видит первую (и пока единственную) версию строки:

=> SELECT *, xmin, xmax FROM t;

       s       | xmin | xmax
---------------+------+------
 Первая версия |  735 |    0
(1 row)

Здесь скрытые столбцы показывают номера транзакций, ограничивающих видимость версии строки: xmin — номер предыдущей транзакции, которая создала версию, а xmax=0 означает, что эта версия актуальна. Теперь начнем другую транзакцию в другом сеансе:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                737
(1 row)

Транзакция видит ту же единственную версию:

=> SELECT *, xmin, xmax FROM t;

       s       | xmin | xmax
---------------+------+------
 Первая версия |  735 |    0
(1 row)

Теперь изменим строку во второй транзакции.

=> UPDATE t SET s = 'Вторая версия';

UPDATE 1

Вот что получилось:

=> SELECT *, xmin, xmax FROM t;

Page 7: Page 7

       s       | xmin | xmax
---------------+------+------
 Вторая версия |  737 |    0
(1 row)

А что увидит первая транзакция?

=> SELECT *, xmin, xmax FROM t;

       s       | xmin | xmax
---------------+------+------
 Первая версия |  735 |  737
(1 row)

Поскольку изменение не зафиксировано, первая транзакция продолжает видеть первую версию строки. Обратите внимание на xmax — значение показывает, что в настоящий момент другая транзакция меняет строку. Вообще говоря, такое «подглядывание» нарушает изоляцию, поэтому поля xmin и xmax скрыты и в реальной работе их использовать не стоит. Теперь зафиксируем изменения.

=> COMMIT;

COMMIT

Что теперь увидит первая транзакция?

=> SELECT *, xmin, xmax FROM t;

       s       | xmin | xmax
---------------+------+------
 Вторая версия |  737 |    0
(1 row)

Теперь и первая транзакция видит вторую версию строки. После фиксации первая версия строки больше не будет видна ни в одной транзакции.

=> COMMIT;

COMMIT

Page 8: Очистка и ее горизонт

{#source-figure-008 caption=“Source slide page 8: Очистка и ее горизонт”}

Горизонт один на каждую базу данных

• Долгие транзакции могут удерживать горизонт
  • мешая тем самым вычищать неактуальные версии строк

Комментарии преподавателя

Механизм многоверсионности позволяет эффективно реализовать изоляцию на основе снимков, но в результате в табличных страницах накапливаются старые («мертвые») версии строк.

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

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

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

https://postgrespro.ru/docs/postgresql/16/routine-vacuuming

Page 9: Блокировки

Блокировки строк

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

Комментарии преподавателя

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

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

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

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

Можно также установить и дополнительные пользовательские блокировки; необходимость в этом возникает не часто.

https://postgrespro.ru/docs/postgresql/16/explicit-locking

Page 10: Блокировки

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

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Третья версия' RETURNING *;

       s
---------------
 Третья версия
(1 row)

UPDATE 1

И во второй транзакции:

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Четвертая версия' RETURNING *;

Вторая транзакция «повисла»: она не может изменить строку, пока первая транзакция не снимет блокировку.

=> COMMIT;

COMMIT

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

        s
------------------
 Четвертая версия
(1 row)

UPDATE 1

=> COMMIT;

COMMIT

Обе транзакции зафиксировали свои изменения. Первый сеанс снова читает таблицу и видит актуальную строку — это результат, зафиксированный второй транзакцией:

=> SELECT * FROM t;

        s
------------------
 Четвертая версия
(1 row)

Page 11: Статус транзакций

Статус транзакций (clog)

• служебная информация; два бита на транзакцию
• специальные файлы на диске
• буферы в общей памяти
• Фиксация
  • устанавливается бит «транзакция зафиксирована»
• Обрыв
  • устанавливается бит «транзакция прервана»
  • выполняется так же быстро, как и фиксация (не нужен откат данных)

Комментарии преподавателя

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

Статусы транзакций (commit log, clog) хранятся в специальных служебных файлах в каталоге PGDATA/pg_xact, а работа с ними происходит в общей памяти сервера, чтобы не приходилось постоянно обращаться к диску.

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

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

Page 12: Итоги

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

• Транзакции работают со снимками данных — согласованными срезами на определенный момент времени
• Уровни изоляции отличаются временем создания снимка
• Мертвые версии строк за горизонтом очистки должны периодически вычищаться
• Писатели не блокируют читателей, читатели не блокируют никого

Page 13: Практика

1. Создайте таблицу с одной строкой. Начните первую транзакцию на уровне изоляции Read Committed и выполните запрос к таблице. Во втором сеансе удалите строку и зафиксируйте изменения. Сколько строк увидит первая транзакция, выполнив тот же запрос повторно? Проверьте. Завершите первую транзакцию. 2. Повторите то же самое, но пусть теперь транзакция работает на уровне изоляции Repeatable Read: BEGIN ISOLATION LEVEL REPEATABLE READ;

• Объясните отличия.

Page 14: 1. Уровень изоляции Read Committed

Создаем таблицу:

=> CREATE TABLE t(n integer);

CREATE TABLE

=> INSERT INTO t VALUES (42);

INSERT 0 1

Запрос из первой транзакции (по умолчанию используется уровень изоляции Read Committed):

=> BEGIN;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Удаляем строку во второй транзакции и фиксируем изменения:

=> DELETE FROM t;

DELETE 1

Повторим запрос:

=> SELECT * FROM t;

 n
---
(0 rows)

Первая транзакция видит произошедшие изменения: строка удалена.

=> COMMIT;

COMMIT

2. Уровень изоляции Repeatable Read

Вернем строку:

=> INSERT INTO t VALUES (42);

INSERT 0 1

Запрос из первой транзакции:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Удаляем строку во второй транзакции и фиксируем изменения:

=> DELETE FROM t;

DELETE 1

Повторим запрос:

=> SELECT * FROM t;

Page 15: Page 15

 n
----
 42
(1 row)

На этом уровне изоляции первая транзакция не видит изменений: для нее строка по-прежнему существует.

=> COMMIT;

COMMIT

Page 16: Практика+

1. Начните транзакцию и создайте новую таблицу с одной строкой. Не завершая транзакцию, откройте второй сеанс и выполните в нем запрос к таблице. Проверьте, что увидит транзакция во втором сеансе.

• Зафиксируйте транзакцию в первом сеансе и повторите
• запрос к таблице во втором сеансе.
• 2. Повторите задание 1, но откатите, а не зафиксируйте транзакцию в первом сеансе. Что изменилось?
• 3. В первом сеансе начните транзакцию и выполните запрос к созданной ранее таблице. Получится ли удалить эту таблицу во втором сеансе, пока первая транзакция не завершена? Проверьте.

Page 17: 1. Транзакции и команды DDL — фиксация

Начнем транзакцию и создадим новую таблицу:

=> BEGIN;

BEGIN

=> CREATE TABLE t1(n integer);

CREATE TABLE

=> INSERT INTO t1 VALUES (42);

INSERT 0 1

Во втором сеансе сделаем запрос к таблице:

=> SELECT * FROM t1;

ERROR:  relation "t1" does not exist
LINE 1: SELECT * FROM t1;
                      ^

Пока создавшая таблицу транзакция не завершена, все остальные транзакции таблицу не видят. Таблица будет видна только после завершения создавшей ее транзакции:

=> COMMIT;

COMMIT

=> SELECT * FROM t1;

 n
----
 42
(1 row)

2. Транзакции и команды DDL — откат

Снова начнем транзакцию и создадим новую таблицу:

=> BEGIN;

BEGIN

=> CREATE TABLE t2(n integer);

CREATE TABLE

=> INSERT INTO t2 VALUES (42);

INSERT 0 1

Запрос во втором сеансе ожидаемо не видит изменений:

=> SELECT * FROM t2;

ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^

При откате первой транзакции команда создания таблицы тоже откатывается:

=> ROLLBACK;

ROLLBACK

=> SELECT * FROM t2;

ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^

Команды DDL в PostgreSQL являются транзакционными.

3. Блокировки таблиц

Page 18: Page 18

Начав транзакцию, обратимся к таблице:

=> BEGIN;

BEGIN

=> SELECT * FROM t1;

 n
----
 42
(1 row)

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

=> DROP TABLE t1;

Таблица будет удалена только после завершения первой транзакции:

=> COMMIT;

COMMIT

DROP TABLE

04 Занятие 04: Архитектура / Буферный кеш и журнал

Page 1: Архитектура / Буферный кеш и журнал

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Темы

• Устройство буферного кеша
• Алгоритм вытеснения
• Журнал предзаписи
• Контрольная точка
• Процессы, связанные с буферным кешем и журналом

Page 3: Буферный кеш

{#source-figure-003 caption=“Source slide page 3: Буферный кеш”}

Массив буферов

• страница данных (8 Кбайт)
• доп. информация
• «Грязные» буферы
  • асинхронная запись
• Блокировки в памяти
  • для совместного доступа

Комментарии преподавателя

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

Размер страницы обычно составляет 8 Кбайт; размер можно изменить только при сборке PostgreSQL.

Любая работа со страницами данных проходит через буферный кеш. Если какой-либо процесс собирается работать со страницей, он в первую очередь пытается найти ее в кеше. Если страницы нет, он обращается к операционной системе с просьбой прочитать эту страницу и помещает ее в буферный кеш. (Обратите внимание, что ОС может прочитать страницу с диска, а может обнаружить ее в собственном кеше.)

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

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

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

Page 4: Вытеснение

{#source-figure-004 caption=“Source slide page 4: Вытеснение”}

Вытеснение редко используемых страниц

• грязный буфер
• записывается на диск
• на освободившееся место
• читается другая страница

Комментарии преподавателя

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

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

Такой алгоритм вытеснения называется LRU — Least Recently Used. Он сохраняет в кеше данные, с которыми происходит активная работа. Таких «горячих» данных обычно не слишком много, и при достаточном объеме буферного кеша получается существенно сократить количество обращений к ОС (и дисковых операций).

Page 5: Влияние буферного кеша на выполнение запросов

Создаем новую БД в кластере и подключаемся к ней (подробнее про базы данных — в модуле «Организация данных»):

=> CREATE DATABASE arch_wal_overview;

CREATE DATABASE

=> \c arch_wal_overview

You are now connected to database "arch_wal_overview" as user "student".

Создадим таблицу:

=> CREATE TABLE t(n integer);

CREATE TABLE

Заполним таблицу некоторым количеством строк:

=> INSERT INTO t SELECT id FROM generate_series(1,100_000) AS id;

INSERT 0 100000

Размер буферного кеша показывает параметр shared_buffers:

=> SHOW shared_buffers;

 shared_buffers
----------------
 128MB
(1 row)

Значение по умолчанию слишком мало; в любой реальной системе его требуется увеличить сразу после установки сервера (изменение требует перезапуска). Теперь перезапустим сервер, чтобы содержимое буферного кеша сбросилось.

=> \q

student$ sudo pg_ctlcluster 16 main restart

student$ psql arch_wal_overview

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

=> EXPLAIN (analyze, buffers, costs off, timing off)
SELECT * FROM t;

                 QUERY PLAN
--------------------------------------------
 Seq Scan on t (actual rows=100000 loops=1)
   Buffers: shared read=443
 Planning:
   Buffers: shared hit=12 read=8 dirtied=1
 Planning Time: 0.266 ms
 Execution Time: 20.084 ms
(6 rows)

Строка «Buffers: shared» показывает использование буферного кеша. read — количество буферов, в которые пришлось прочитать страницы с диска.

=> EXPLAIN (analyze, buffers, costs off, timing off)
SELECT * FROM t;

                 QUERY PLAN
--------------------------------------------
 Seq Scan on t (actual rows=100000 loops=1)
   Buffers: shared hit=443
 Planning Time: 0.034 ms
 Execution Time: 7.612 ms
(4 rows)

Page 6: Page 6

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

Page 7: Журнал предзаписи (WAL)

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

• Журнал
  • поток информации о выполняемых действиях,
  • позволяющий повторно выполнить потерянные при сбое операции
  • запись попадает на диск раньше, чем измененные данные
• Журнал защищает
  • страницы таблиц, индексов и других объектов
  • статус транзакций (clog)
• Журнал не защищает
  • временные и нежурналируемые таблицы

Комментарии преподавателя

Наличие буферного кеша (и других буферов в оперативной памяти) увеличивает производительность, но уменьшает надежность. В случае сбоя в СУБД содержимое буферного кеша потеряется. Если сбой произойдет в операционной системе или на аппаратном уровне, то пропадет содержимое и буферов ОС (но с этим справляется сама операционная система).

Для обеспечения надежности PostgreSQL использует журналирование. При выполнении любой операции формируется запись, содержащая минимально необходимую информацию для того, чтобы операцию можно было выполнить повторно. Такая запись должна попасть на диск (или другой энергонезависимый накопитель) раньше, чем будут записаны изменяемые операцией данные (поэтому журнал и называется журналом предзаписи, write-ahead log).

Файлы журнала располагаются в каталоге PGDATA/pg_wal.

Журнал защищает все объекты, работа с которыми ведется в оперативной памяти: таблицы, индексы и другие объекты, статус транзакций.

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

https://postgrespro.ru/docs/postgresql/16/wal-intro

Page 8: Журнал предзаписи

Логически журнал можно представить в виде непрерывного потока записей. Каждая запись имеет номер, называемый LSN (Log Sequence Number). Это 64-разрядное число — смещение записи в байтах относительно начала журнала. Текущую позицию показывает функция pg_current_wal_lsn:

=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn
--------------------
 0/2378B68
(1 row)

Позиция записывается как два 32-разрядных числа через косую черту. Запомним это значение. Выполним теперь какие-нибудь операции и посмотрим, как изменилась позиция.

=> UPDATE t SET n = 100_001 WHERE n = 1;

UPDATE 1

=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn
--------------------
 0/237BB80
(1 row)

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

=> SELECT '0/237BB80'::pg_lsn - '0/2378B68'::pg_lsn AS bytes;

 bytes
-------
 12312
(1 row)

Физически журнал хранится в файлах в отдельном каталоге (PGDATA/pg_wal). По умолчанию файлы имеют размер 16 Мбайт, задать другой размер можно при инициализации кластера баз данных. На файлы можно взглянуть не только средствами файловой системы, но и с помощью функции:

=> SELECT * FROM pg_ls_waldir() ORDER BY name LIMIT 10;

           name           |   size   |      modification
--------------------------+----------+------------------------
 000000010000000000000002 | 16777216 | 2026-04-10 15:34:23+03
 000000010000000000000003 | 16777216 | 2026-04-10 15:34:17+03
(2 rows)

Page 9: Контрольная точка

{#source-figure-009 caption=“Source slide page 9: Контрольная точка”}

Периодический сброс всех грязных буферов на диск

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

Комментарии преподавателя

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

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

Однако объем журнала за время работы сервера мог бы достигнуть гигантских размеров. Хранить его целиком и просматривать при сбое совершенно не реально. Поэтому СУБД периодически выполняет контрольную точку (КТ): принудительно сбрасывает на диск все грязные буферы (включая буферы clog, хранящие состояние транзакций).

Собственно «точка» — это момент начала записи буферов, которые были грязными на этот момент. Но точка считается выполненной только после того, как все такие буферы записаны. Это гарантирует, что все изменения, сделанные до момента КТ, находятся на диске.

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

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

Page 10: Восстановление при помощи журнала

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

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid)

При старте произойдет восстановление согласованности данных с помощью журнала. Проверим:

student$ sudo pg_ctlcluster 16 main start

student$ psql arch_wal_overview

=> SELECT min(n), max(n) FROM t;

 min |  max
-----+--------
   2 | 100001
(1 row)

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

Page 11: Производительность

{#source-figure-011 caption=“Source slide page 11: Производительность”}

Синхронный режим

• запись при фиксации
• обслуживающий процесс
• Асинхронный режим
  • фоновая запись
  • walwriter

Комментарии преподавателя

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

На эффективность можно также влиять настройкой. Если запись происходит сразу (синхронно), то гарантируется, что зафиксированная транзакция не пропадет. Но запись — довольно дорогая операция, в течение которой обслуживающий процесс, выполняющий фиксацию, вынужден ждать. Чтобы журнальная запись не «застряла» в кеше операционной системы, выполняется системный вызов fsync: PostgreSQL полагается на то, что это гарантирует попадание данных на энергонезависимый носитель.

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

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

Page 12: Основные процессы

{#source-figure-012 caption=“Source slide page 12: Основные процессы”}

Запись журнала

• Контрольная точка
  • сброс всех
  • грязных буферов
• Фоновая запись
  • сброс части
  • грязных буферов
• Обслуживающие процессы
  • сброс вытесняемого
  • грязного буфера

Комментарии преподавателя

Вернемся еще раз к процессам, связанным с обслуживанием буферного кеша и журнала.

Во-первых, это процесс walwriter, занимающийся асинхронной записью журнала на диск. При синхронном режиме записью журнала занимается тот процесс, который выполняет фиксацию транзакции.

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

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

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

Page 13: Уровни журнала

Minimal

• гарантия восстановления после сбоя
• Replica (по умолчанию)
  • резервное копирование
  • репликация: передача и проигрывание журнала на другом сервере
• Logical
  • логическая репликация: информация о добавлении, изменении
  • и удалении табличных строк

Комментарии преподавателя

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

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

Объем данных, который попадает в журнал, регулируется параметром wal_level.

● На уровне minimal журнал обеспечивает только восстановление после сбоя.

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

● На уровне logical в журнал добавляется информация, позволяющая декодировать «физические» журнальные записи и сформировать из них «логические» записи о добавлении, изменении и удалении табличных строк. Это позволяет организовать логическую репликацию (рассматривается в соответствующих темах курсов DEV2 и DBA3).

Page 14: Итоги

Буферный кеш существенно ускоряет работу, уменьшая число дисковых операций

• Надежность обеспечивается журналированием
• Размер журнала ограничен благодаря контрольным точкам
• Журнал удобен и используется во многих случаях
  • для восстановления после сбоя
  • при резервном копировании
  • для репликации между серверами

Page 15: Практика

1. Средствами операционной системы найдите процессы, отвечающие за работу буферного кеша и журнала WAL. 2. Остановите PostgreSQL в режиме fast; снова запустите его. Просмотрите журнал сообщений сервера. 3. Теперь остановите в режиме immediate и снова запустите. Просмотрите журнал сообщений сервера и сравните с предыдущим разом.

Комментарии преподавателя

2. Для останова в режиме fast используйте команду

pg_ctlcluster 16 main stop

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

3. Для останова в режиме immediate используйте команду

pg_ctlcluster 16 main stop -m immediate --skip-systemctl-redirect

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

Для PostgreSQL, собранного из исходных кодов, останов в режиме fast выполняется командой

pg_ctl stop

а останов в режиме immediate — командой

pg_ctl stop -m immediate

Page 16: 1. Процессы операционной системы

Сначала получим идентификатор процесса postmaster. Он записан в первой строке файла postmaster.pid. Этот файл расположен в каталоге с данными и создается каждый раз при старте сервера.

student$ sudo cat /var/lib/postgresql/16/main/postmaster.pid

135904
/var/lib/postgresql/16/main
1775825008
5432
/var/run/postgresql
localhost
  1343416     32816
ready

Теперь найдем все процессы, порожденные процессом postmaster:

student$ sudo ps -o pid,command --ppid 135904

    PID COMMAND
 135905 postgres: 16/main: checkpointer
 135906 postgres: 16/main: background writer
 135908 postgres: 16/main: walwriter
 135909 postgres: 16/main: autovacuum launcher
 135910 postgres: 16/main: logical replication launcher
 135953 postgres: 16/main: student student [local] idle

К процессам, обслуживающим буферный кеш и журнал, можно отнести: checkpointer; background writer; walwriter.

2. Остановка в режиме fast

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

student$ sudo -u postgres truncate -cs0 /var/log/postgresql/postgresql-16-main.log

student$ sudo pg_ctlcluster 16 main stop

student$ sudo pg_ctlcluster 16 main start

Журнал сообщений сервера:

student$ cat /var/log/postgresql/postgresql-16-main.log

2026-04-10 15:43:32.541 MSK [135904] LOG:  received fast shutdown request
2026-04-10 15:43:32.562 MSK [135904] LOG:  aborting any active transactions
2026-04-10 15:43:32.562 MSK [135953] student@student FATAL:  terminating connection due
to administrator command
2026-04-10 15:43:32.582 MSK [135904] LOG:  background worker "logical replication
launcher" (PID 135910) exited with exit code 1
2026-04-10 15:43:32.582 MSK [135905] LOG:  shutting down
2026-04-10 15:43:32.595 MSK [135905] LOG:  checkpoint starting: shutdown immediate
2026-04-10 15:43:32.745 MSK [135905] LOG:  checkpoint complete: wrote 5 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.034 s, sync=0.012 s, total=0.163 s;
sync files=4, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1921F18, redo lsn=0/1921F18
2026-04-10 15:43:32.754 MSK [135904] LOG:  database system is shut down
2026-04-10 15:43:33.236 MSK [136206] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2026-04-10 15:43:33.237 MSK [136206] LOG:  listening on IPv4 address "127.0.0.1", port
5432
2026-04-10 15:43:33.254 MSK [136206] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-04-10 15:43:33.268 MSK [136209] LOG:  database system was shut down at 2026-04-10
15:43:32 MSK
2026-04-10 15:43:33.281 MSK [136206] LOG:  database system is ready to accept connections

3. Остановка в режиме immediate

student$ sudo -u postgres truncate -cs0 /var/log/postgresql/postgresql-16-main.log

Page 17: Page 17

student$ sudo pg_ctlcluster 16 main stop -m immediate --skip-systemctl-redirect

student$ sudo pg_ctlcluster 16 main start

Журнал сообщений сервера:

student$ cat /var/log/postgresql/postgresql-16-main.log

2026-04-10 15:43:35.919 MSK [136206] LOG:  received immediate shutdown request
2026-04-10 15:43:35.937 MSK [136206] LOG:  database system is shut down
2026-04-10 15:43:36.410 MSK [136394] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2026-04-10 15:43:36.410 MSK [136394] LOG:  listening on IPv4 address "127.0.0.1", port
5432
2026-04-10 15:43:36.432 MSK [136394] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-04-10 15:43:36.447 MSK [136397] LOG:  database system was interrupted; last known up
at 2026-04-10 15:43:33 MSK
2026-04-10 15:43:46.246 MSK [136397] LOG:  database system was not properly shut down;
automatic recovery in progress
2026-04-10 15:43:46.255 MSK [136397] LOG:  invalid record length at 0/1921F90: expected
at least 24, got 0
2026-04-10 15:43:46.255 MSK [136397] LOG:  redo is not required
2026-04-10 15:43:46.273 MSK [136395] LOG:  checkpoint starting: end-of-recovery immediate
wait
2026-04-10 15:43:46.304 MSK [136395] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.013 s, sync=0.005 s, total=0.035 s;
sync files=2, longest=0.003 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1921F90, redo lsn=0/1921F90
2026-04-10 15:43:46.316 MSK [136394] LOG:  database system is ready to accept connections

Перед тем, как начать принимать соединения, СУБД выполнила восстановление (automatic recovery in progress).

05 Занятие 05: Организация данных / Логическая структура

Page 1: Организация данных / Логическая структура

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

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

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

Page 2: Темы

• Базы данных и шаблоны
• Схемы и путь поиска
• Специальные схемы
• Системный каталог

Page 3: Кластер баз данных

{#source-figure-003 caption=“Source slide page 3: Кластер баз данных”}

Инициализация кластера создает три базы данных

• Новая база всегда клонируется из существующей

CREATE DATABASE

Комментарии преподавателя

Экземпляр PostgreSQL управляет несколькими базами данных — кластером. При инициализации кластера (автоматически при установке PostgreSQL либо вручную командой initdb) создаются три одинаковые базы данных. Все остальные БД, создаваемые пользователем, клонируются из какой-либо существующей.

Шаблонная БД template1 используется по умолчанию для создания новых баз данных. В нее можно добавить объекты и расширения, которые будут копироваться в каждую новую базу данных.

Шаблон template0 не должен изменяться. Он нужен как минимум в двух ситуациях. Во-первых, для восстановления БД из резервной копии, выполненной утилитой pg_dump (это рассматривается в теме «Резервное копирование. Логическое резервирование»). Во-вторых, при создании новой БД с кодировкой, отличной от указанной при инициализации кластера (подробнее обсуждается в курсе DBA2).

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

https://postgrespro.ru/docs/postgresql/16/manage-ag-templatedbs

Page 4: Базы данных

Список баз данных можно получить в psql такой командой:

=> \l

                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+----------
--+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |
 student   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres
(4 rows)

База данных student была создана для удобства подключения одноименного пользователя. В выводе команды присутствует ряд столбцов, которые нас сейчас не интересуют. Когда мы создаем новую базу данных, она (по умолчанию) копируется из шаблона template1.

=> CREATE DATABASE data_logical;

CREATE DATABASE

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> \l

                                                         List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
--------------+----------+----------+-----------------+-------------+-------------+-------
-----+-----------+-----------------------
 data_logical | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 student      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
(5 rows)

Page 5: Схемы

Пространство имен для объектов

• разделение объектов на логические группы
• предотвращение конфликта имен между приложениями
• Схема и пользователь — разные сущности
• Специальные схемы
  • public — по умолчанию в ней создаются все объекты
  • pg_catalog — системный каталог
  • information_schema — вариант системного каталога
  • pg_temp — для временных таблиц
  • …

Комментарии преподавателя

Схемы представляют собой пространства имен для объектов БД. Они позволяют разделить объекты на логические группы для управления ими, предотвратить конфликты имен при работе нескольких пользователей или при установке приложений и расширений.

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

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

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

Схема pg_catalog хранит объекты системного каталога. Системный каталог — это метаинформация об объектах, принадлежащих кластеру, которая хранится в самом кластере в виде таблиц. Альтернативное представление системного каталога (определенное в стандарте SQL) дает схема information_schema.

Схема pg_temp служит для хранения временных таблиц. (На самом деле таблицы создаются в схемах pg_temp_1, pg_temp_2 и т. п. — у каждого пользователя своя схема. Но обращаются все пользователи к ней как к pg_temp.)

Есть и другие схемы, но они носят технический характер.

https://postgrespro.ru/docs/postgresql/16/ddl-schemas

Page 6: Базы и схемы кластера

{#source-figure-006 caption=“Source slide page 6: Базы и схемы кластера”}

Комментарии преподавателя

Схемы принадлежат базам данных, все объекты БД распределены по каким-либо схемам.

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

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

Page 7: Схемы

Для вывода списка схем в psql есть специальная команда (\dn = describe namespace):

=> \dn

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

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

=> \dnS

            List of schemas
        Name        |       Owner
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
(4 rows)

Про некоторые из этих схем (public, pg_catalog, information_schema) мы уже говорили; про остальные поговорим позже в других темах. Еще один полезный модификатор — знак «плюс», который выводит дополнительную информацию:

=> \dn+

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+--------------------
----
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public
schema
        |                   | =U/pg_database_owner                   |
(1 row)

Создадим новую схему:

=> CREATE SCHEMA special;

CREATE SCHEMA

=> \dn

       List of schemas
  Name   |       Owner
---------+-------------------
 public  | pg_database_owner
 special | student
(2 rows)

Создадим таблицу:

=> CREATE TABLE t(n integer);

CREATE TABLE

По умолчанию таблица будет создана в схеме public. Список таблиц в этой схеме можно получить командой \dt с указанием шаблона для имен схем и таблиц:

=> \dt public.*

Page 8: Page 8

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t    | table | student
(1 row)

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

=> ALTER TABLE t SET SCHEMA special;

ALTER TABLE

Что останется в схеме public?

=> \dt public.*

Did not find any relation named "public.*".

Ничего. А в special?

=> \dt special.*

        List of relations
 Schema  | Name | Type  |  Owner
---------+------+-------+---------
 special | t    | table | student
(1 row)

Таблица переместилась. Теперь к ней можно обращаться с явным указанием схемы:

=> SELECT * FROM special.t;

 n
---
(0 rows)

Но если опустить имя схемы, таблица не будет найдена:

=> SELECT * FROM t;

ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^

Page 9: Путь поиска

Определение схемы объекта

• квалифицированное имя (схема.имя) явно определяет схему
• имя без квалификатора проверяется в схемах, указанных в пути поиска
• Путь поиска
  • определяется параметром search_path,
  • реальное значение — функция current_schemas
  • исключаются несуществующие схемы и схемы, к которым нет доступа
  • первая явно указанная в пути схема используется для создания объектов
  • схемы pg_temp и pg_catalog неявно включаются первыми,
  • если не указаны в search_path

Комментарии преподавателя

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

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

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

● схема pg_catalog, чтобы всегда иметь доступ к системному каталогу

● схема pg_temp, если пользователь создавал временные объекты

Реальный путь поиска, включающий неявные схемы, возвращает вызов функции: current_schemas(true).

Можно провести аналогию между путем поиска search_path и путем PATH в операционных системах.

https://postgrespro.ru/docs/postgresql/16/runtime-config-client#GUC-SEAR CH-PATH

Page 10: Путь поиска

Путь поиска по умолчанию имеет такое значение:

=> SHOW search_path;

   search_path
-----------------
 "$user", public
(1 row)

Конструкция «$user» обозначает схему с тем же именем, что и имя текущего пользователя (в нашем случае — student). Поскольку такой схемы нет, она игнорируется. Чтобы не думать над тем, какие схемы есть, каких нет, а какие недоступны, можно воспользоваться функцией:

=> SELECT current_schemas(false);

 current_schemas
-----------------
 {public}
(1 row)

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

=> SELECT current_schemas(true);

   current_schemas
---------------------
 {pg_catalog,public}
(1 row)

Установим путь поиска, например, так:

=> SET search_path = public, special;

SET

Теперь таблица будет найдена.

=> SELECT * FROM t;

 n
---
(0 rows)

Здесь мы установили конфигурационный параметр на уровне сеанса и при переподключении его значение пропадет. Устанавливать такое значение на уровне всего кластера тоже неправильно — возможно, этот путь нужен не всегда и не всем, к тому же в разных БД может быть разный набор схем. Но параметр можно установить и на уровне отдельной базы данных:

=> ALTER DATABASE data_logical SET search_path = public, special;

ALTER DATABASE

Теперь он будет устанавливаться для всех новых подключений к БД data_logical. Проверим:

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> SHOW search_path;

   search_path
-----------------
 public, special
(1 row)

Page 11: Системный каталог

Описание всех объектов кластера

• набор таблиц в каждой базе данных (схема pg_catalog)
• и несколько глобальных объектов кластера
• набор представлений для удобства
• Доступ
  • запросы SQL, специальные команды psql
• Правила организации
  • названия таблиц начинаются с pg_
  • имена столбцов содержат трехбуквенный префикс
  • в качестве ключа используется столбец oid типа oid
  • названия объектов хранятся в нижнем регистре

Комментарии преподавателя

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

https://postgrespro.ru/docs/postgresql/16/catalogs

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

https://postgrespro.ru/docs/postgresql/16/app-psql

Все имена таблиц системного каталога начинаются с pg_, например, pg_database. Столбцы таблиц начинаются с префикса, обычно соответствующего имени таблицы, например, datname. Имена объектов хранятся в нижнем регистре, например, ‘postgres’.

Таблицы системного каталога имеют первичные ключи — как правило, это столбцы с именем oid и типом oid (object identifier, целое 32-битное число). Эти идентификаторы встречаются и в других столбцах в виде отдельных значений или массивов, обеспечивая логические связи между таблицами. Внешние ключи в системном каталоге явно не определены.

https://postgrespro.ru/docs/postgresql/16/datatype-oid

Page 12: Системный каталог

Для того, чтобы вывести информацию о любых объектах, psql (как и другие интерактивные пользовательские средства) обращается к таблицам системного каталога. Например, команда \l для получения списка баз данных кластера, обращается к таблице:

=> SELECT datname FROM pg_database;

   datname
--------------
 postgres
 student
 template1
 template0
 data_logical
(5 rows)

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

=> \set ECHO_HIDDEN on

=> \l

********* QUERY **********
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  d.daticulocale as "ICU Locale",
  d.daticurules as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                                         List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
--------------+----------+----------+-----------------+-------------+-------------+-------
-----+-----------+-----------------------
 data_logical | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 student      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
(5 rows)

Таким образом можно исследовать системный каталог. Отключим вывод команд.

=> \set ECHO_HIDDEN off

Список схем находится в таблице:

=> SELECT nspname FROM pg_namespace;

Page 13: Page 13

      nspname
--------------------
 pg_toast
 pg_catalog
 public
 information_schema
 special
(5 rows)

А на такие объекты, как таблицы и индексы, можно посмотреть так:

=> SELECT relname, relkind, relnamespace FROM pg_class WHERE relname = 't';

 relname | relkind | relnamespace
---------+---------+--------------
 t       | r       |        16387
(1 row)

Все столбцы здесь начинаются на rel (relation, отношение). relkind — тип объекта (r — таблица, i — индекс и т. п.); relnamespace — схема. Поле relnamespace имеет тип oid; вот соответствующая строка таблицы pg_namespace:

=> SELECT oid, nspname FROM pg_namespace WHERE oid = 16387;

  oid  | nspname
-------+---------
 16387 | special
(1 row)

Для удобства преобразования между текстовым представлением и oid можно воспользоваться приведением к специальному типу-псевдониму regnamespace:

=> SELECT relname, relkind, relnamespace::regnamespace::text
FROM pg_class WHERE relname = 't';

 relname | relkind | relnamespace
---------+---------+--------------
 t       | r       | special
(1 row)

А вот как можно получить список объектов в схеме, например, pg_catalog:

=> SELECT relname, relkind FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace LIMIT 5;

            relname             | relkind
--------------------------------+---------
 pg_statistic                   | r
 pg_type                        | r
 pg_foreign_table               | r
 pg_proc_oid_index              | i
 pg_proc_proname_args_nsp_index | i
(5 rows)

Аналогичные reg-типы определены и для некоторых других таблиц системного каталога. Они позволяют упростить запросы и обойтись без явного соединения таблиц.

Удаление объектов

Можно ли удалить схему special?

=> DROP SCHEMA special;

ERROR:  cannot drop schema special because other objects depend on it
DETAIL:  table t depends on schema special
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

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

=> DROP SCHEMA special CASCADE;

Page 14: Page 14

NOTICE:  drop cascades to table t
DROP SCHEMA

А что с удалением базы данных целиком? Во-первых, нельзя удалить базу, к которой вы подключены в данный момент, поэтому отключимся от нее.

=> \conninfo

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

=> \c postgres

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

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

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> DROP DATABASE data_logical;

ERROR:  database "data_logical" is being accessed by other users
DETAIL:  There is 1 other session using the database.