Администрирование PostgreSQL. Базовый курс

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

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

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

О курсе

Установка, архитектура сервера, базовая настройка и повседневное администрирование PostgreSQL.

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

Содержание

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

• catalogs.pdf
• dba1_new_features.pdf
• dba1_student_guide.pdf
• unix_commands.pdf

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

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

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

00 Занятие 00: Администрирование PostgreSQL 16 / Базовый курс

Page 1: Администрирование PostgreSQL 16 / Базовый курс

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

© 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: Администраторам”}

• DBA2, 4 дня

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

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

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

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

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

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

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

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

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

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

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

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

• DEV2, 4 дня

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

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

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

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

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

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

https://postgrespro.ru/education/courses

Page 4: О курсе DBA1

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

• Предварительные знания
  • минимальные сведения о базах данных и SQL
  • знакомство с Unix
• Какие навыки будут получены
  • общие сведения об архитектуре PostgreSQL
  • установка, базовая настройка, управление сервером
  • организация данных на логическом и физическом уровнях
  • базовые задачи администрирования
  • основные сведения об управлении доступом
  • представление о резервном копировании и репликации

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

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

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

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

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

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

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

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

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

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

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

Page 7: День 1

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

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

Page 8: День 2

Архитектура (продолжение)

• 7. Буферный кеш и журнал
• Организация данных
  • 8. Базы данных и схемы
  • 9. Системный каталог
  • 10. Табличные пространства
  • 11. Низкий уровень
• Задачи администрирования
  • 12. Мониторинг

Page 9: День 3

Управление доступом

• 13. Обзор
• Резервное копирование
  • 14. Обзор
• Репликация
  • 15. Обзор физической репликации
  • 16. Обзор логической репликации

01 Занятие 01: Базовый инструментарий / Установка и управление сервером

Page 1: Базовый инструментарий / Установка и управление сервером

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

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

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

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

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

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

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

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

Page 2: Темы занятия

Основные понятия

• Установка из исходных кодов
• Управление сервером
• Пакетная установка
• Управление сервером в Ubuntu
• Облачные решения

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

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

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

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

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

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

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

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

Page 4: Исходные коды

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

• стабильная версия сервера
• можно использовать нестандартные параметры
• или собрать на специфичной архитектуре
• Установка из репозитория git
  • текущая версия сервера
  • необходимо в первую очередь для разработчиков ядра;
  • требует более широкого набора инструментов

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

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

По адресу https://www.postgresql.org/ftp/source доступен исходный код в двух вариантах: gzip и bzip2. В виртуальной машине необходимый файл уже находится в домашнем каталоге пользователя student.

Исходные коды можно взять и непосредственно из git-репозитория проекта: git://git.postgresql.org/git/postgresql.git (проект имеет зеркала, в том числе на github).

В этом случае можно собрать не только стабильную версию, но и версию на любой конкретный коммит, в том числе наиболее актуальную.

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

Page 5: Необходимое ПО

Обязательно

• tar, gzip/bzip2, GNU make, компилятор Си (C89)
• Используются, но можно отключить
  • библиотеки GNU Readline, zlib, lz4, zstd
• Дополнительно
  • языки программирования Perl, Python, Tcl
  • для использования PL/Perl, PL/Python, PL/Tcl
  • Kerberos, OpenSSL, OpenLDAP, PAM
  • для аутентификации и шифрования
  • библиотека ICU для кроссплатформенной поддержки UNICODE
• Отдельные инструменты при сборке из репозитория git

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

Сборка и установка традиционно основывается на GNU Make и Autoconf:

https://postgrespro.ru/docs/postgresql/16/install-make

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

Начиная с версии 16 можно также использовать систему сборки программного обеспечения Meson:

https://postgrespro.ru/docs/postgresql/16/install-meson

Для сборки требуется ряд программ и утилит.

Библиотека readline дает возможность редактировать командную строку, пользоваться историей команд и автодополнением. В серверной инсталляции может быть и не нужна, если не предполагается запускать psql в консоли.

Библиотеки zlib, lz4 и zstd используются для сжатия.

Page 6: Распаковка архива

Архив с исходными кодами находится в домашнем каталоге пользователя student. Распаковываем архив:

student$ cd

student$ tar xzf /home/student/postgresql-16.0.tar.gz

student$ ls -l /home/student/postgresql-16.0

total 876
-rw-r--r--  1 student student    365 сен 11  2023 aclocal.m4
drwxrwxr-x  2 student student   4096 сен 11  2023 config
-rwxr-xr-x  1 student student 584200 сен 11  2023 configure
-rw-r--r--  1 student student  87156 сен 11  2023 configure.ac
drwxrwxr-x 61 student student   4096 сен 11  2023 contrib
-rw-r--r--  1 student student   1192 сен 11  2023 COPYRIGHT
drwxrwxr-x  3 student student   4096 сен 11  2023 doc
-rw-r--r--  1 student student   4288 сен 11  2023 GNUmakefile.in
-rw-r--r--  1 student student    277 сен 11  2023 HISTORY
-rw-r--r--  1 student student  64592 сен 11  2023 INSTALL
-rw-r--r--  1 student student   1875 сен 11  2023 Makefile
-rw-r--r--  1 student student 101920 сен 11  2023 meson.build
-rw-r--r--  1 student student   6266 сен 11  2023 meson_options.txt
-rw-r--r--  1 student student   1213 сен 11  2023 README
drwxrwxr-x 16 student student   4096 сен 11  2023 src

Создание конфигурации

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

student$ cd /home/student/postgresql-16.0

student$ make distclean

В команде configure можно указать различные параметры конфигурации. Например: –prefix — каталог установки, по умолчанию /usr/local/pgsql; –enable-debug — для включения отладочной информации. Полный список параметров есть в документации. Также принимаются во внимание переменные окружения. Например, CC и CFLAGS настраивают компилятор C. Скрипт configure по умолчанию подготавливает сборку и установку исполняемых файлов, библиотек и прочих файлов PostgreSQL в каталог /usr/local/pgsql. Обычно каталог установки доступен для записи только суперпользователю ОС. В целях демонстрации ключом –prefix укажем каталог установки ПО сервера в подкаталог pgsql16 домашнего каталога пользователя student. Кроме того, будем использовать порт 5555 вместо порта 5432 по умолчанию:

student$ ./configure --prefix=/home/student/pgsql16 --with-pgport=5555

Вывод этой и следующих команд занимает очень много места, мы не будем его показывать.

Сборка и установка PostgreSQL

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

student$ make

Теперь устанавливаем сервер.

student$ make install

Для сборки расширений нужно повторить make и make install в подкаталоге contrib.

Page 7: Page 7

student$ cd contrib

student$ make

student$ make install

Если бы мы собирали сервер с расширениями и документацией (make world), то можно было бы установить все вместе командой make install-world.

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

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

Утилита

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

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

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

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

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

В процессе инициализации кластера утилита initdb создает конфигурационные файлы в этом же каталоге PGDATA. Подробнее о файлах конфигурации мы расскажем в одной из следующих тем.

Утилита initdb имеет множество ключей, влияющих на ее работу. Один из важных — -k, или –data-checksums — включает подсчет контрольных сумм на страницах данных. Проверка контрольной суммы будет выполняться при обращении к любой странице данных в кластере. Это несколько снижает производительность, но позволяет оперативно обнаружить повреждения в данных.

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

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

Теперь необходимо создать каталог для данных. Для промышленного использования владельцем каталога обычно назначают отдельного пользователя ОС. В нашем случае, пусть это будет каталог пользователя student внутри /home/student/pgsql16

student$ mkdir /home/student/pgsql16/data

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

student$ export PGDATA=/home/student/pgsql16/data

Все утилиты сервера установлены в подкаталоге bin, который стоит добавить в переменную PATH:

student$ export PATH=/home/student/pgsql16/bin:$PATH

Для инициализации кластера баз данных используется утилита initdb. В ключе -U можно указать имя суперпользователя СУБД. Если его не задать, то используется имя пользователя ОС, в нашем случае student. Обычно таким пользователем является postgres, так что зададим это имя явно. Ключ -k включает подсчет контрольных сумм страниц, что позволяет своевременно обнаруживать повреждения данных. Если переменная PGDATA не установлена, то в ключе -D следует указать каталог для данных. В нашем случае этот ключ можно было бы не указывать.

student$ initdb -U postgres -k -D /home/student/pgsql16/data

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

Утилита

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

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

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

Для выполнения этих действий предназначена утилита pg_ctl, идущая в составе PostgreSQL.

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

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

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

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

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

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

student$ pg_ctl start -l /home/student/logfile

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

student$ psql -U postgres -p 5555 -c 'SELECT now();'

              now
-------------------------------
 2026-04-10 17:02:06.104584+03
(1 row)

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

student$ pg_ctl stop

Page 12: Установка пакета

{#source-figure-012 caption=“Source slide page 12: Установка пакета”}

Готовые пакеты — предпочтительный способ

• Linux ( , Debian, Red Hat, SUSE и другие)
  • входит в дистрибутив ОС
  • репозиторий (yum, apt) или пакеты RPM, DEB
• FreeBSD, OpenBSD
  • пакеты из Ports and Packages Collection
• Mac OS X
• Windows

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

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

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

В пакетные репозитории некоторых систем уже входит PostgreSQL, но, обычно, не самой последней версии. Актуальная версия может быть взята из репозитория PostgreSQL Global Development Group (PGDG):

https://www.postgresql.org/download/

Мы рассмотрим особенности пакета PostgreSQL для Ubuntu.

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

{#source-figure-013 caption=“Source slide page 13: Создание кластера”}

Утилита

• pg_createcluster
• initdb
• Особенности
  • инициализация выполняется при установке пакета
  • и создает кластер «main»
  • подсчет контрольных сумм по умолчанию не включен
  • расположение файлов конфигурации и журнала сервера
  • автоматический запуск сервера при старте ОС

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

Для инициализации кластера в Ubuntu создана обертка pg_createcluster над утилитой initdb.

Справку по использованию pg_createcluster можно получить командой: $ man pg_createcluster

Утилита pg_createcluster автоматически запускается при установке пакета и создает кластер баз данных с именем «main».

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

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

Кроме того, настраивается автоматический запуск и останов сервера PostgreSQL при запуске и останове операционной системы.

Для удаления кластера используется утилита pg_dropcluster.

Утилиты pg_createcluster и pg_dropcluster специфичны для Ubuntu. В других системах нужно явно инициализировать кластер с помощью initdb и задействовать подходящие средства операционной системы.

Page 14: Создание кластера в Ubuntu

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

student$ sudo apt install -y postgresql-16

Каталог установки PostgreSQL:

student$ sudo 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 17:25 lib

Владелец ПО сервера — пользователь root. Утилита pg_config показывает, с какими параметрами был собран сервер:

student$ sudo /usr/lib/postgresql/16/bin/pg_config --configure

 '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include'
'--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc'
'--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules'
'--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run'
'--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt'
'--mandir=/usr/share/postgresql/16/man' '--docdir=/usr/share/doc/postgresql-doc-16'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/16' '--bindir=/usr/lib/postgresql/16/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu
16.11-1.pgdg24.04+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug'
'--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk'
'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar'
'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fno-omit-frame-pointer
-mno-omit-leaf-frame-pointer -flto=auto -ffat-lto-objects -fstack-protector-strong
-fstack-clash-protection -Wformat -Werror=format-security -fcf-protection
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects
-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm'
'LLVM_CONFIG=/usr/bin/llvm-config-19' 'CLANG=/usr/bin/clang-19' '--with-lz4'
'--with-zstd' '--with-systemd' '--with-selinux' '--enable-dtrace'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=3' 'CXXFLAGS=-g
-O2 -fno-omit-frame-pointer -mno-omit-leaf-frame-pointer -flto=auto -ffat-lto-objects
-fstack-protector-strong -fstack-clash-protection -Wformat -Werror=format-security
-fcf-protection'

Кластер баз данных с именем main уже инициализирован и находится в каталоге /var/lib/postgresql/16/main. Владельцем каталога является пользователь postgres. Вот содержимое каталога:

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

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

Page 15: Page 15

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

Утилита

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

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

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

В других системах утилита pg_ctl может использоваться непосредственно.

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

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

student$ sudo pg_ctlcluster 16 main stop

student$ pg_lsclusters 16 main

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

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

student$ sudo pg_ctlcluster 16 main start

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

student$ sudo pg_ctlcluster 16 main restart

Получить текущий статус сервера:

student$ sudo pg_ctlcluster 16 main status

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

Перечитать файлы конфигурации:

student$ sudo pg_ctlcluster 16 main reload

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

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

-rw-r----- 1 postgres adm 46002 апр 10 17:02 /var/log/postgresql/postgresql-16-main.log

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

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

2026-04-10 17:02:10.646 MSK [58935] LOG:  shutting down
2026-04-10 17:02:10.650 MSK [58935] LOG:  checkpoint starting: shutdown immediate
2026-04-10 17:02:10.676 MSK [58935] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.008 s, sync=0.005 s, total=0.030 s;
sync files=2, longest=0.003 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1922118, redo lsn=0/1922118
2026-04-10 17:02:10.683 MSK [58934] LOG:  database system is shut down
2026-04-10 17:02:10.891 MSK [58989] 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 17:02:10.891 MSK [58989] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-04-10 17:02:10.894 MSK [58989] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-04-10 17:02:10.906 MSK [58992] LOG:  database system was shut down at 2026-04-10
17:02:10 MSK
2026-04-10 17:02:10.917 MSK [58989] LOG:  database system is ready to accept connections
2026-04-10 17:02:13.344 MSK [58989] LOG:  received SIGHUP, reloading configuration files

Page 18: Облачные решения

PostgreSQL в виртуальной среде

• накладные расходы на виртуализацию
• администратор имеет полный доступ к экземпляру
• PostgreSQL as a Service
  • предлагается многими облачными провайдерами
  • основное администрирование берет на себя провайдер
  • доступ только к ограниченным инструментам для управления,
  • резервного копирования, мониторинга

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

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

Кроме того, многие ведущие зарубежные и российские облачные провайдеры предлагают использовать PostgreSQL как сервис (Database as a Service, managed database).

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

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

Page 19: Итоги

Два варианта установки — пакеты или исходный код

• Доступны облачные решения
• Сервер выполняется под выделенным пользователем ОС
• Перед использованием сервера необходимо инициализировать кластер баз данных
• Специальные команды для управления сервером

Page 20: Практика

Включение расчета контрольных сумм в кластере.

• 1. Остановите сервер.
• 2. Проверьте, рассчитываются ли контрольные суммы в кластере.
• 3. Включите расчет контрольных сумм.
• 4. Запустите сервер.

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

2, 3. Используйте утилиту pg_checksums. Для ее запуска укажите полный путь: /usr/lib/postgresql/16/bin/pg_checksums

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

Page 21: 1. Останов сервера

student$ sudo pg_ctlcluster 16 main stop

2. Проверка

Чтобы узнать, включен ли расчет контрольных сумм страниц, запустим утилиту pg_checksums с ключом –check:

student$ sudo /usr/lib/postgresql/16/bin/pg_checksums --check -D /var/lib/postgresql/16/main

pg_checksums: error: data checksums are not enabled in cluster

3. Включение расчета контрольных сумм

Выполняем pg_checksums с ключом –enable:

student$ sudo /usr/lib/postgresql/16/bin/pg_checksums --enable -D /var/lib/postgresql/16/main

Checksum operation completed
Files scanned:   1244
Blocks scanned:  3749
Files written:  1026
Blocks written: 3749
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster

Расчет контрольных сумм включен.

4. Запуск сервера

student$ sudo pg_ctlcluster 16 main start

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

1. Установите PostgreSQL из исходных кодов так, как это показано в демонстрации.

• Создайте кластер баз данных, запустите сервер.
• Убедитесь, что сервер работает.
• Остановите сервер.

Page 23: 1. Сборка PostgreSQL из исходных кодов

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

02 Занятие 02: Базовый инструментарий / Использование psql

Page 1: Базовый инструментарий / Использование psql

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

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

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

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

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

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

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

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

Page 2: Темы занятия

Запуск psql и подключение к БД

• Получение справочной информации
• Работа в psql
• Настройка

Page 3: Назначение

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

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

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

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

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

Это единственный клиент, поставляемый вместе с СУБД. Навыки работы c psql пригодятся разработчикам и администраторам БД вне зависимости от того, с каким инструментом они будут работать дальше.

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

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

Запуск

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

• Новое подключение в psql

=> \c[onnect] база пользователь узел порт

• Информация о текущем подключении

=> \conninfo

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

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

● база — совпадает с именем пользователя;

● пользователь — совпадает с именем пользователя ОС;

● узел — соединение через Unix socket;

● порт — обычно 5432.

Если требуется выполнить новое подключение не выходя из psql, нужно выполнить команду \connect. Значения по умолчанию для нее берутся из текущего соединения.

Команда \conninfo выдает информацию о текущем подключении.

Дополнительная информация о возможностях настройки подключения:

https://postgrespro.ru/docs/postgresql/16/libpq-envars

https://postgrespro.ru/docs/postgresql/16/libpq-pgservice

https://postgrespro.ru/docs/postgresql/16/libpq-pgpass

Page 5: Получение справки

В командной строке ОС

$ psql --help
$ man psql

• В psql
  • => \?
  • список команд psql
  • => \? variables
  • переменные psql
  • => \h[elp]
  • список команд SQL
  • => \h команда
  • синтаксис команды SQL
  • => \q
  • выход

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

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

psql с ключом –help выдает справку по запуску. А если в системе была установлена документация, то справочное руководство можно получить командой man psql.

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

Внутри psql есть возможность получить список и краткое описание команд psql: \?.

Команда \help выдает список команд SQL, которые поддерживает сервер, а также синтаксис конкретной команды SQL.

И еще одна команда, которую полезно знать, хоть она и не имеет отношения к справке: это \q — выход из psql. В качестве альтернативы для выхода можно использовать команды exit и quit.

Page 6: Выполнение команд SQL и форматирование результатов

Запускаем psql:

student$ psql

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

=> \conninfo

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

С параметрами по умолчанию мы подключились к базе данных student под пользователем student. Подробнее о базах данных и пользователях будет рассказано в следующих темах курса. Команда \c[onnect] выполняет новое подключение, не покидая psql. Команды SQL, в отличие от команд psql, могут располагаться на нескольких строках. Для отправки команды SQL на выполнение завершаем ее точкой с запятой:

=> SELECT schemaname, tablename, tableowner
FROM pg_tables
LIMIT 5;

 schemaname |       tablename       | tableowner
------------+-----------------------+------------
 pg_catalog | pg_statistic          | postgres
 pg_catalog | pg_type               | postgres
 pg_catalog | pg_foreign_table      | postgres
 pg_catalog | pg_authid             | postgres
 pg_catalog | pg_statistic_ext_data | postgres
(5 rows)

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

=> \t \a

Tuples only is on.
Output format is unaligned.

=> \pset fieldsep ' '

Field separator is " ".

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

pg_catalog pg_statistic postgres
pg_catalog pg_type postgres
pg_catalog pg_foreign_table postgres
pg_catalog pg_authid postgres
pg_catalog pg_statistic_ext_data postgres

=> \t \a

Tuples only is off.
Output format is aligned.

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

=> \x

Expanded display is on.

=> SELECT * FROM pg_tables WHERE tablename = 'pg_class';

-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_class
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

=> \x

Expanded display is off.

Page 7: Page 7

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

=> SELECT * FROM pg_tables WHERE tablename = 'pg_proc' \gx

-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_proc
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

Все возможности форматирования результатов запросов доступны через команду \pset. А без параметров она покажет текущие настройки форматирования:

=> \pset

border                   1
columns                  0
csv_fieldsep             ','
expanded                 off
fieldsep                 ' '
fieldsep_zero            off
footer                   on
format                   aligned
linestyle                ascii
null                     ''
numericlocale            off
pager                    1
pager_min_lines          0
recordsep                '\n'
recordsep_zero           off
tableattr
title
tuples_only              off
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width            full

Взаимодействие с ОС

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

=> \! pwd

/home/student

Можно установить переменную окружения операционной системы:

=> \setenv HELLO Hello

=> \! echo $HELLO

Hello

Можно записать вывод команды в файл с помощью \o[ut]:

=> \o tmp/dba1_log

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

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

=> \! cat tmp/dba1_log

 schemaname |       tablename       | tableowner
------------+-----------------------+------------
 pg_catalog | pg_statistic          | postgres
 pg_catalog | pg_type               | postgres
 pg_catalog | pg_foreign_table      | postgres
 pg_catalog | pg_authid             | postgres
 pg_catalog | pg_statistic_ext_data | postgres
(5 rows)

Вернем вывод на экран:

=> \o

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

Еще один вариант отправить запрос на выполнение — команда \g. В скобках можно указать параметры форматирования только для этого запроса. Вывод запроса можно направить команде ОС, если указать ее после вертикальной черты. Например, можно вывести результат запроса на экран, пронумеровав строки:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\g (tuples_only=on format=unaligned) | cat -n

     1 SELECT count(*) FROM pg_statistic;
     2 SELECT count(*) FROM pg_type;
     3 SELECT count(*) FROM pg_foreign_table;

Page 8: Page 8

В команде \g можно указать имя файла, в который будет направлен вывод:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\g (tuples_only=on format=unaligned) tmp/dba1_log

Вот что получилось в файле:

=> \! cat tmp/dba1_log

SELECT count(*) FROM pg_statistic;
SELECT count(*) FROM pg_type;
SELECT count(*) FROM pg_foreign_table;

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

=> \i tmp/dba1_log

 count
-------
   409
(1 row)

 count
-------
   613
(1 row)

 count
-------
     0
(1 row)

Другие способы выполнить команды из файла: psql < filename psql -f filename В предыдущем примере можно обойтись и без создания файла, если завершить запрос командой \gexec:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\gexec

 count
-------
   409
(1 row)

 count
-------
   613
(1 row)

 count
-------
     0
(1 row)

Команда gexec считает, что в каждом столбце каждой строки выборки содержится SQL-оператор, и выполняет эти операторы один за другим.

Переменные psql и управляющие конструкции

По аналогии с shell, psql имеет собственные переменные, среди которых есть ряд встроенных (имеющих особый смысл для psql). Запомним в переменной psql User значение переменной окружения USER:

=> \getenv User USER

И установим переменную Test:

=> \set Test Hi

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

=> \echo :Test :User!

Hi student!

Сбросить переменную можно так:

=> \unset Test

=> \echo :Test

:Test

Результат запроса можно записать в переменную. Для этого запрос нужно завершить командой \gset:

=> SELECT now() AS curr_time \gset

=> \echo :curr_time

2026-04-10 17:02:29.77686+03

Page 9: Page 9

Запрос должен возвращать только одну запись. Без параметров \set выдает значения всех установленных переменных:

=> \set

AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'student'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTFILE = 'hist'
HISTSIZE = '500'
HOST = '/var/run/postgresql'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
ROW_COUNT = '1'
SERVER_VERSION_NAME = '16.11 (Ubuntu 16.11-1.pgdg24.04+1)'
SERVER_VERSION_NUM = '160011'
SHELL_ERROR = 'false'
SHELL_EXIT_CODE = '0'
SHOW_ALL_RESULTS = 'on'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
SQLSTATE = '00000'
USER = 'student'
User = 'student'
VERBOSITY = 'default'
VERSION = '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'
VERSION_NAME = '16.11 (Ubuntu 16.11-1.pgdg24.04+1)'
VERSION_NUM = '160011'
curr_time = '2026-04-10 17:02:29.77686+03'

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

=> \echo :{?working_dir}

FALSE

Следующий условный оператор psql проверяет существование переменной и при необходимости устанавливает значение по умолчанию:

=> \if :{?working_dir}
   -- переменная определена
\else
   -- в качестве значения можно установить результат выполнения команды ОС
   \set working_dir `pwd`
\endif

Теперь мы можем быть уверены, что переменная working_dir определена:

=> \echo :working_dir

/home/student

Команды для работы с системным каталогом

С помощью серии команд, в основном начинающихся на \d, можно быстро и удобно получать информацию об объектах БД. Например:

=> \d pg_tables

              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          |
 tablename   | name    |           |          |
 tableowner  | name    |           |          |
 tablespace  | name    |           |          |
 hasindexes  | boolean |           |          |
 hasrules    | boolean |           |          |
 hastriggers | boolean |           |          |
 rowsecurity | boolean |           |          |

Подробнее такие команды будут рассмотрены позже.

Page 10: Настройка psql

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

student$ pg_config --sysconfdir

/etc/postgresql-common

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

=> \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)

Если записать эту команду \set в файл ~/.psqlrc, переменная top5 будет доступна сразу после запуска psql. Благодаря поддержке readline, в psql работает автодополнение ключевых слов и имен объектов, а также сохраняется история команд. Имя и размер файла истории настраиваются переменными HISTFILE, HISTSIZE.

Page 11: Итоги

psql — терминальный клиент для работы с СУБД

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

Page 12: Практика

1. Запустите psql и проверьте информацию о текущем подключении. 2. Выведите список баз данных в подробном виде. 3. По умолчанию psql использует команду «less» для постраничного просмотра результатов запроса. Замените ее на команду «less -XS» и снова выведите подробный список баз данных. 4. По умолчанию приглашение psql показывает только имя базы данных. Настройте вид приглашения так: пользователь@база=#. 5. Настройте psql так, чтобы для всех команд выводилась длительность выполнения. Убедитесь, что при повторном запуске эта настройка сохраняется.

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

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

2. Используйте команду \l+.

3. Программа постраничного просмотра настраивается переменной окружения PSQL_PAGER. Настройку можно сделать в файле .psqlrc с помощью команды \setenv. Это позволит задать значение «less -XS» только для работы в psql, а в остальных случаях будут действовать обычные настройки ОС. По умолчанию less переносит длинные строки при просмотре и очищает свой вывод при выходе. Параметр -XS отменяет это поведение.

4. Описание настройки приглашения в документации:

https://postgrespro.ru/docs/postgresql/16/app-psql#APP-PSQL-PROMPTIN G

5. Команду psql для вывода длительности выполнения запросов можно найти в документации или с помощью команды \?.

Page 13: 1. Запуск psql и просмотр информации о подключении

student$ psql

=> \conninfo

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

2. Список баз данных

Команда \l+ выводит подробную информацию о базах данных. Если результат запроса не помещается в окне терминала, psql передает его программе less. В ней можно просматривать результаты запроса, используя клавиши навигации. Команда h выводит справку по less, команда q завершает просмотр.

=> \l+

                                                                                         L
ist of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Local
e | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+----------
--+-----------+-----------------------+---------+------------+----------------------------
----------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |                       | 7361 kB | pg_default | default administrative conn
ection database
 student   | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |                       | 7516 kB | pg_default |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +| 7361 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +| 7516 kB | pg_default | default template for new da
tabases
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres |         |            |
(4 rows)

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

3. Команда постраничного просмотра

Если использовать утилиту less с параметрами -XS, длинные строки не будут разбиваться переносами, а при выходе из less предыдущий вывод будет сохраняться. Для такой настройки достаточно установить переменную PSQL_PAGER с помощью \setenv. Запишем эту настройку в скрипт ~/.psqlrc:

student$ echo "\setenv PSQL_PAGER 'less -XS'" > ~/.psqlrc

4. Настройка приглашения

Для добавления информации о роли нужно в начало переменных PROMPT1 и PROMPT2 добавить %n@.

student$ echo "\set PROMPT1 '%n@%/%R%x%# '" >> ~/.psqlrc

student$ echo "\set PROMPT2 '%n@%/%R%x%# '" >> ~/.psqlrc

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

5. Вывод длительности выполнения команд SQL

student$ echo '\timing on' >> ~/.psqlrc

В итоге содержимое файла .psqlrc станет таким:

Page 14: Page 14

student$ cat ~/.psqlrc

\setenv PSQL_PAGER 'less -XS'
\set PROMPT1 '%n@%/%R%x%# '
\set PROMPT2 '%n@%/%R%x%# '
\timing on

Чтобы изменения вступили в силу, нужно выйти и заново войти в psql.

=> \q

student$ psql

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

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

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

• 2. Задайте переменной ON_ERROR_ROLLBACK значение on и убедитесь, что после ошибки можно выполнять команды внутри транзакции.

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

1. Чтобы открыть транзакцию, выполните команду

BEGIN;

2. Значение ON_ERROR_ROLLBACK = on заставляет psql устанавливать точку сохранения (SAVEPOINT) перед каждой командой SQL в открытой транзакции и в случае ошибки откатываться к этой точке сохранения.

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

Page 16: 1. Утилита psql и обработка ошибок внутри транзакций

student$ psql

Утилита psql по умолчанию работает в режиме автоматической фиксации транзакций. Поэтому любая команда SQL выполняется в отдельной транзакции. Чтобы явно начать транзакцию, нужно выполнить команду BEGIN:

student@student=# BEGIN;

BEGIN

Обратите внимание на то, что приглашение psql изменилось. Символ «звездочка» говорит о том, что транзакция сейчас активна.

student@student=*# CREATE TABLE t (id int);

CREATE TABLE

Предположим, мы случайно сделали ошибку в следующей команде:

student@student=*# INSERTINTO t VALUES(1);

ERROR:  syntax error at or near "INSERTINTO"
LINE 1: INSERTINTO t VALUES(1);
        ^

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

student@student=!# INSERT INTO t VALUES(1);

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Но PostgreSQL не умеет откатывать только одну команду транзакции, поэтому транзакция обрывается и откатывается целиком. Чтобы продолжить работу, мы должны выполнить команду завершения транзакции. Не важно, будет ли это COMMIT или ROLLBACK, ведь транзакция уже отменена.

student@student=!# COMMIT;

ROLLBACK

Создание таблицы было отменено, поэтому ее нет в базе данных:

student@student=# SELECT * FROM t;

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

2. Переменная ON\_ERROR\_ROLLBACK

Изменим поведение psql.

student@student=# \set ON_ERROR_ROLLBACK on

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

student@student=# BEGIN;

BEGIN

student@student=*# CREATE TABLE t (id int);

CREATE TABLE

student@student=*# INSERTINTO t VALUES(1);

ERROR:  syntax error at or near "INSERTINTO"
LINE 1: INSERTINTO t VALUES(1);
        ^

student@student=*# INSERT INTO t VALUES(1);

INSERT 0 1

student@student=*# COMMIT;

COMMIT

Page 17: Page 17

student@student=# SELECT * FROM t;

 id
----
  1
(1 row)

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

student@student=# DROP TABLE t;

DROP TABLE

Уборка

student@student=# \set ON_ERROR_ROLLBACK off

03 Занятие 03: Базовый инструментарий / Конфигурирование сервера

Page 1: Базовый инструментарий / Конфигурирование сервера

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

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

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

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

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

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

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

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

Page 2: Темы

• Параметры конфигурации
• Файлы конфигурации
• Управление параметрами на уровнях экземпляра и сеанса

Page 3: Параметры

Задача

• управление работой и поведением СУБД
• Установка
  • для экземпляра — файлы конфигурации
  • для отдельной базы или пользователя
  • для текущего сеанса

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

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

Например, при помощи параметра max_connections можно ограничить количество одновременных подключений к серверу.

Полный список и описание параметров конфигурации: https://postgrespro.ru/docs/postgresql/16/runtime-config

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

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

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

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

Page 4: postgresql.conf

Основной файл конфигурации

• считывается при старте сервера
• возможность подключения дополнительных файлов
• по умолчанию находится в каталоге с данными (PGDATA)

/etc/postgresql/16/main

• При изменении параметров необходимо перечитать файл

$ pg_ctl reload

$ pg_ctlcluster 16 main reload

• => SELECT pg_reload_conf();
• изменение ряда параметров требует перезапуска сервера

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

Основной конфигурационный файл — postgresql.conf.

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

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

Это текстовый, хорошо документированный файл, хранящий параметры в формате «ключ=значение».

Есть возможность подключать дополнительные файлы. По умолчанию в Ubuntu подключаются все файлы из каталога /etc/postgresql/16/main/conf.d.

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

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

Page 5: Файл postgresql.conf и представление pg_file_settings

Имя конфигурационного файла содержится в доступном для чтения параметре config_file. Имя конфигурационного файла можно указать с помощью ключа командной строки при запуске postgres.

=> SHOW config_file;

               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf
(1 row)

Посмотрим небольшой фрагмент конфигурационного файла.

=> SELECT pg_read_file('/etc/postgresql/16/main/postgresql.conf', 1516, 861) \g (tuples_only=on format=unaligned)

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/16/main'
# use data in another directory
# (change requires restart)
hba_file = '/etc/postgresql/16/main/pg_hba.conf'
# host-based authentication file
# (change requires restart)
ident_file = '/etc/postgresql/16/main/pg_ident.conf'
# ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/16-main.pid'
# write an extra
PID file
# (change requires restart)

К основному конфигурационному файлу postgresql.conf можно подключать дополнительные файлы конфигурации. Директивы подключения: include_dir — каталог с дополнительными файлами конфигурации; include — включает дополнительный файл конфигурации; include_if_exists — включает дополнительный файл конфигурации, если он существует. Обычно эти директивы располагаются в завершающей части файла postgresql.conf:

student$ sudo grep -A3 ^include /etc/postgresql/16/main/postgresql.conf

include_dir = 'conf.d'
# include files ending in '.conf' from
# a directory, e.g., 'conf.d'
#include_if_exists = '...'
# include file only if it exists
#include = '...'
# include file

Чтобы увидеть настройки в конфигурационных файлах, можно обратиться к представлению pg_file_settings:

=> SELECT sourceline, name, setting, applied, error FROM pg_file_settings;

Page 6: Page 6

 sourceline |            name            |                setting                 |
applied | error
------------+----------------------------+----------------------------------------+-------
--+-------
         42 | data_directory             | /var/lib/postgresql/16/main            | t
  |
         44 | hba_file                   | /etc/postgresql/16/main/pg_hba.conf    | t
  |
         46 | ident_file                 | /etc/postgresql/16/main/pg_ident.conf  | t
  |
         50 | external_pid_file          | /var/run/postgresql/16-main.pid        | t
  |
         64 | port                       | 5432                                   | t
  |
         65 | max_connections            | 100                                    | t
  |
         68 | unix_socket_directories    | /var/run/postgresql                    | t
  |
        108 | ssl                        | on                                     | t
  |
        110 | ssl_cert_file              | /etc/ssl/certs/ssl-cert-snakeoil.pem   | t
  |
        113 | ssl_key_file               | /etc/ssl/private/ssl-cert-snakeoil.key | t
  |
        130 | shared_buffers             | 128MB                                  | t
  |
        153 | dynamic_shared_memory_type | posix                                  | t
  |
        247 | max_wal_size               | 1GB                                    | t
  |
        248 | min_wal_size               | 80MB                                   | t
  |
        565 | log_line_prefix            | %m [%p] %q%u@%d                        | t
  |
        603 | log_timezone               | Europe/Moscow                          | t
  |
        607 | cluster_name               | 16/main                                | t
  |
        715 | datestyle                  | iso, dmy                               | t
  |
        717 | timezone                   | Europe/Moscow                          | t
  |
        731 | lc_messages                | en_US.UTF-8                            | t
  |
        733 | lc_monetary                | ru_RU.UTF-8                            | t
  |
        734 | lc_numeric                 | ru_RU.UTF-8                            | t
  |
        735 | lc_time                    | ru_RU.UTF-8                            | t
  |
        741 | default_text_search_config | pg_catalog.english                     | t
  |
(24 rows)

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

Представление pg\_settings

Возьмем для примера параметр work_mem. Он определяет объем памяти, выделяемый для таких операций, как сортировка или хеш-соединение. Не для всех запросов значения по умолчанию может быть достаточно. Подробнее о параметре work_mem можно узнать в курсе QPT «Оптимизация запросов». Действующие значения всех параметров доступны в представлении pg_settings. Вот что в нем содержится для параметра work_mem:

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem' \gx

Page 7: Page 7

{#source-figure-007 caption=“Source slide page 7: Page 7”}

-[ RECORD 1 ]---+---------
name            | work_mem
unit            | kB
setting         | 4096
boot_val        | 4096
reset_val       | 4096
source          | default
sourcefile      |
sourceline      |
pending_restart | f
context         | user

Рассмотрим ключевые столбцы представления pg_settings: name, unit — название и единица измерения параметра; setting — текущее значение; boot_val — значение по умолчанию; reset_val — начальное значение для сеансов; source — источник текущего значения параметра; sourcefile, sourceline — файл конфигурации и номер строки, если текущее значение было задано в файле; pending_restart — true, если значение изменено в файле конфигурации, но для применения требуется перезапуск сервера. Столбец context определяет действия, необходимые для применения параметра. Среди возможных значений: internal — изменить нельзя, значение задано при установке; postmaster — требуется перезапуск сервера; sighup — требуется перечитать файлы конфигурации, superuser — суперпользователь может изменить для своего сеанса; user — любой пользователь может изменить для своего сеанса.

Порядок применения строк

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

student$ echo work_mem=12MB | sudo tee /etc/postgresql/16/main/conf.d/work_mem.conf

work_mem=12MB

student$ echo work_mem=8MB | sudo tee -a /etc/postgresql/16/main/conf.d/work_mem.conf

work_mem=8MB

Содержимое файла /etc/postgresql/16/main/conf.d/work_mem.conf:

=> SELECT sourcefile, sourceline, name, setting, applied
FROM pg_file_settings WHERE sourcefile LIKE '%/work_mem.conf';

                  sourcefile                  | sourceline |   name   | setting | applied
----------------------------------------------+------------+----------+---------+---------
 /etc/postgresql/16/main/conf.d/work_mem.conf |          1 | work_mem | 12MB    | f
 /etc/postgresql/16/main/conf.d/work_mem.conf |          2 | work_mem | 8MB     | t
(2 rows)

Значение applied = f для первой строки показывает, что она не будет применена. Для параметра work_mem поле context имеет значение user. Значит, параметр можно менять прямо во время сеанса, и позже мы увидим, как это сделать. А чтобы изменить значение во всех сеансах, достаточно перечитать файлы конфигурации:

=> SELECT pg_reload_conf();

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

Убедимся, что параметр work_mem получил значение из второй строки:

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

Page 8: Page 8

-[ RECORD 1 ]---+---------------------------------------------
name            | work_mem
unit            | kB
setting         | 8192
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /etc/postgresql/16/main/conf.d/work_mem.conf
sourceline      | 2
pending_restart | f
context         | user

Page 9: postgresql.auto.conf

Файл конфигурации, управляемый командами SQL

• ALTER SYSTEM
• добавляет или изменяет строку
• SET параметр TO значение;
• ALTER SYSTEM RESET параметр;
• удаляет строку
• ALTER SYSTEM RESET ALL;
• удаляет все строки

считывается после postgresql.conf

• Расположение
  • всегда в каталоге с данными (PGDATA)
• Действия при изменении

аналогично postgresql.conf

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

Последним считывается файл postgresql.auto.conf. Он всегда располагается в каталоге данных (PGDATA).

Этот файл не следует изменять вручную, для его редактирования предназначена команда ALTER SYSTEM. По сути, ALTER SYSTEM представляет собой SQL-интерфейс для управления параметрами конфигурации.

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

Содержимое файлов конфигурации можно увидеть в представлении pg_file_settings. А действующие значения параметров — в представлении pg_settings.

Более подробная информация о команде ALTER SYSTEM:

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

Page 10: Команда ALTER SYSTEM и файл postgresql.auto.conf

Для примера установим параметр work_mem:

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

ERROR:  invalid value for parameter "work_mem": "16mb"
HINT:  Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".

Что случилось? ALTER SYSTEM выполняет проверку на допустимые значения.

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

ALTER SYSTEM

Вот теперь все правильно. В результате выполнения команды значение 16MB записано в файл postgresql.auto.conf:

=> SELECT pg_read_file('postgresql.auto.conf')
\g (tuples_only=on format=unaligned)

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

Но это значение не применено:

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

Чтобы применить изменение work_mem, перечитаем файлы конфигурации:

=> SELECT pg_reload_conf();

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

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

-[ RECORD 1 ]---+-------------------------------------------------
name            | work_mem
unit            | kB
setting         | 16384
boot_val        | 4096
reset_val       | 16384
source          | configuration file
sourcefile      | /var/lib/postgresql/16/main/postgresql.auto.conf
sourceline      | 3
pending_restart | f
context         | user

Для удаления строк из postgresql.auto.conf используется команда ALTER SYSTEM RESET:

=> ALTER SYSTEM RESET work_mem;

ALTER SYSTEM

=> SELECT pg_read_file('postgresql.auto.conf')
\g (tuples_only=on format=unaligned)

Page 11: Page 11

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

Еще раз перечитаем конфигурацию. Теперь восстановится значение из work_mem.conf:

=> SELECT pg_reload_conf();

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

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

-[ RECORD 1 ]---+---------------------------------------------
name            | work_mem
unit            | kB
setting         | 8192
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /etc/postgresql/16/main/conf.d/work_mem.conf
sourceline      | 2
pending_restart | f
context         | user

Page 12: В текущем сеансе

{#source-figure-012 caption=“Source slide page 12: В текущем сеансе”}

установка до конца сеанса или транзакции

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

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

Значения параметров можно изменить прямо во время сеанса командой SET или функцией set_config. А для получения текущего значения служит команда SHOW или функция current_setting.

Устанавливая новое значение, можно указать срок его действия: до конца сеанса (по умолчанию) или до конца транзакции (SET LOCAL).

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

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

Page 13: Установка параметров для текущего сеанса

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

=> SET work_mem TO '24MB';

SET

Или функцию set_config:

=> SELECT set_config('work_mem', '32MB', false);

 set_config
------------
 32MB
(1 row)

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

Чтение значений параметров во время выполнения

Получить значение параметра можно разными способами:

=> SHOW work_mem;

 work_mem
----------
 32MB
(1 row)

=> \dconfig work_mem

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

=> SELECT current_setting('work_mem');

 current_setting
-----------------
 32MB
(1 row)

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

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

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

=> RESET work_mem;

RESET

Установка параметров внутри транзакции

Откроем транзакцию и установим новое значение work_mem:

=> BEGIN;

BEGIN

=> SET work_mem TO '64MB';

SET

Page 14: Page 14

=> SHOW work_mem;

 work_mem
----------
 64MB
(1 row)

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

=> ROLLBACK;

ROLLBACK

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

Можно установить значение только до конца текущей транзакции:

=> BEGIN;

BEGIN

=> SET LOCAL work_mem TO '64MB'; -- или set_config('work_mem','64MB',true);

SET

=> SHOW work_mem;

 work_mem
----------
 64MB
(1 row)

=> COMMIT;

COMMIT

По завершении транзакции значение восстанавливается:

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

Пользовательские параметры

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

=> SELECT CASE
  WHEN current_setting('myapp.currency_code', true) IS NULL
    THEN set_config('myapp.currency_code', 'RUB', false)
  ELSE
    current_setting('myapp.currency_code')
END;

 current_setting
-----------------
 RUB
(1 row)

Теперь myapp.currency_code можно использовать как глобальную переменную сеанса:

=> SELECT current_setting('myapp.currency_code');

 current_setting
-----------------
 RUB
(1 row)

Page 15: Page 15

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

Page 16: Итоги

Основной файл конфигурации — postgresql.conf

• ALTER SYSTEM — SQL-интерфейс для управления параметрами конфигурации в postgresql.auto.conf
• При изменениях в файлах нужно перечитать конфигурацию
• Многие параметры можно изменять для текущего сеанса
• Изменение части параметров требует перезапуска сервера

Page 17: Практика

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

• 2. В дополнительном подключаемом файле конфигурации сделайте ошибку при изменении параметра max_connections.
  • Перезапустите сервер. Убедитесь, что сервер не стартует,
  • и проверьте журнал сообщений.
  • Исправьте ошибку и запустите сервер.

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

2. Расположение файла postgresql.conf можно найти, посмотрев значение параметра config_file, как это сделано в демонстрации.

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

Создайте в указанном этой директивой каталоге файл max_connections.conf, отредактировав его либо от имени пользователя postgres, либо от имени суперпользователя операционной системы.

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

Используйте знакомый вам текстовый редактор. Например, так: sudo nano /etc/postgresql/16/main/conf.d/max_connections.conf

Для выхода из nano используйте сочетание клавиш Ctrl+X.

Page 18: 1. Параметры, изменение которых требует перезапуска сервера

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

                name                 |                 setting                 | unit
-------------------------------------+-----------------------------------------+------
 archive_mode                        | off                                     |
 autovacuum_freeze_max_age           | 200000000                               |
 autovacuum_max_workers              | 3                                       |
 autovacuum_multixact_freeze_max_age | 400000000                               |
 bonjour                             | off                                     |
 bonjour_name                        |                                         |
 cluster_name                        | 16/main                                 |
 config_file                         | /etc/postgresql/16/main/postgresql.conf |
 data_directory                      | /var/lib/postgresql/16/main             |
 data_sync_retry                     | off                                     |
 debug_io_direct                     |                                         |
 dynamic_shared_memory_type          | posix                                   |
 event_source                        | PostgreSQL                              |
 external_pid_file                   | /var/run/postgresql/16-main.pid         |
 hba_file                            | /etc/postgresql/16/main/pg_hba.conf     |
 hot_standby                         | on                                      |
 huge_page_size                      | 0                                       | kB
 huge_pages                          | try                                     |
 ident_file                          | /etc/postgresql/16/main/pg_ident.conf   |
 ignore_invalid_pages                | off                                     |
 jit_provider                        | llvmjit                                 |
 listen_addresses                    | localhost                               |
 logging_collector                   | off                                     |
 max_connections                     | 100                                     |
 max_files_per_process               | 1000                                    |
 max_locks_per_transaction           | 64                                      |
 max_logical_replication_workers     | 4                                       |
 max_pred_locks_per_transaction      | 64                                      |
 max_prepared_transactions           | 0                                       |
 max_replication_slots               | 10                                      |
 max_wal_senders                     | 10                                      |
 max_worker_processes                | 8                                       |
 min_dynamic_shared_memory           | 0                                       | MB
 old_snapshot_threshold              | -1                                      | min
 port                                | 5432                                    |
 recovery_target                     |                                         |
 recovery_target_action              | pause                                   |
 recovery_target_inclusive           | on                                      |
 recovery_target_lsn                 |                                         |
 recovery_target_name                |                                         |
 recovery_target_time                |                                         |
 recovery_target_timeline            | latest                                  |
 recovery_target_xid                 |                                         |
 reserved_connections                | 0                                       |
 shared_buffers                      | 16384                                   | 8kB
 shared_memory_type                  | mmap                                    |
 shared_preload_libraries            |                                         |
 superuser_reserved_connections      | 3                                       |
 track_activity_query_size           | 1024                                    | B
 track_commit_timestamp              | off                                     |
 unix_socket_directories             | /var/run/postgresql                     |
 unix_socket_group                   |                                         |
 unix_socket_permissions             | 0777                                    |
 wal_buffers                         | 512                                     | 8kB
 wal_decode_buffer_size              | 524288                                  | B
 wal_level                           | replica                                 |
 wal_log_hints                       | off                                     |
(57 rows)

2. Установка параметра max\_connections

Текущее значение параметра max_connections:

=> \dconfig max_conn*

Page 19: Page 19

List of configuration parameters
    Parameter    | Value
-----------------+-------
 max_connections | 100
(1 row)

Допустим, мы решили уменьшить это значение до 50, но ошиблись и вместо нуля написали букву O:

student$ echo max_connections=5O | sudo tee /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=5O

Обнаружить ошибку можно, посмотрев в представление pg_file_settings:

=> SELECT * FROM pg_file_settings WHERE name = 'max_connections'\gx

-[ RECORD 1 ]---------------------------------------------------
sourcefile | /etc/postgresql/16/main/postgresql.conf
sourceline | 65
seqno      | 6
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]---------------------------------------------------
sourcefile | /etc/postgresql/16/main/conf.d/max_connections.conf
sourceline | 1
seqno      | 25
name       | max_connections
setting    | 5O
applied    | f
error      | setting could not be applied

Предположим, мы не посмотрели в pg_file_settings и все-таки решили перезапустить сервер:

=> \q

student$ sudo pg_ctlcluster 16 main restart

Job for postgresql@16-main.service failed because the service did not take the steps
required by its unit configuration.
See "systemctl status postgresql@16-main.service" and "journalctl -xeu
postgresql@16-main.service" for details.

Сервер не запускается. Причина записана в журнал сообщений сервера. Вот последние строки журнала:

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

2026-04-10 17:08:08.829 MSK [87310] LOG:  database system is shut down
2026-04-10 17:08:08.998 MSK [87943] LOG:  invalid value for parameter "max_connections":
"5O"
2026-04-10 17:08:08.998 MSK [87943] FATAL:  configuration file
"/etc/postgresql/16/main/conf.d/max_connections.conf" contains errors
pg_ctl: could not start server
Examine the log output.

Исправим ошибку в файле конфигурации:

student$ echo max_connections=50 | sudo tee /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=50

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

student$ cat /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=50

Пробуем запустить сервер:

student$ sudo pg_ctlcluster 16 main start

Сервер успешно стартовал, проверяем значение max_connections:

student$ psql

=> SHOW max_connections;

 max_connections
-----------------
 50
(1 row)

Page 20: Page 20

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

1. Установите параметр work_mem = 32MB в командной строке запуска утилиты psql. 2. В пакетном дистрибутиве для Ubuntu файл postgresql.conf находится не в каталоге PGDATA. Каким образом сервер находит этот файл конфигурации при запуске?

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

1. Используйте один из двух способов: ключ options в строке подключения или переменная среды PGOPTIONS.

Информация о формировании строки подключения:

https://postgrespro.ru/docs/postgresql/16/libpq-connect#LIBPQ-CONNSTRING

2. Расположение файла postgresql.conf можно посмотреть в параметре config_file.

Чтобы узнать, где задается этот параметр, посмотрите вывод команды ps для основного процесса postgres. Идентификатор процесса (PID) записан в первой строке файла postmaster.pid, который находится в каталоге с данными (PGDATA).

Page 22: 1. Установка параметров при запуске приложения

Если приложение использует библиотеку libpq для подключения к серверу, установить параметры при запуске можно двумя способами. Первый способ — указать ключ options в строке параметров подключения:

student$ psql "options='-c work_mem=32MB'" -c 'SHOW work_mem'

 work_mem
----------
 32MB
(1 row)

Второй способ — установить переменную окружения PGOPTIONS:

student$ export PGOPTIONS='-c work_mem=32MB'; psql -c 'SHOW work_mem'

 work_mem
----------
 32MB
(1 row)

2. Где определяется config\_file

Файл postgresql.conf расположен не в каталоге с данными:

=> \dconfig (config_file|data_directory)

             List of configuration parameters
   Parameter    |                  Value
----------------+-----------------------------------------
 config_file    | /etc/postgresql/16/main/postgresql.conf
 data_directory | /var/lib/postgresql/16/main
(2 rows)

Каким же образом сервер находит postgresql.conf? В пакетном дистрибутиве для Ubuntu значение параметра config_file указано в командной строке запуска сервера. Это позволяет разместить postgresql.conf в каталоге, отличном от PGDATA. Командную строку запуска сервера можно посмотреть с помощью команды ps. Сначала найдем идентификатор (PID) основного процесса сервера, он находится в первой строке файла postmaster.pid, расположенного в каталоге PGDATA:

student$ sudo cat /var/lib/postgresql/16/main/postmaster.pid | head -n 1

88285

Теперь мы можем получить командную строку запуска процесса postmaster:

student$ ps -p 88285 -ho command

/usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c
config_file=/etc/postgresql/16/main/postgresql.conf

04 Занятие 04: Архитектура / Общее устройство 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 17:02:58.678396+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: Итоги

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

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

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: Многоверсионность”}

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

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

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

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

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

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;