db_practice

1. Базы данных. Практика

2.

План вебинара
Часть I. Теория
Часть II. Практика
• Что такое база данных и СУБД
• История PostgreSQL
• Типы СУБД
• PostgreSQL: архитектура, логическая
и физическая организация хранения
данных
• Реляционные СУБД
• SQL
• Joins
• ACID и транзакции
• Индексы
• Роли
• Конфигурирование
• Резервное копирование
• Репликация
• Расширения и утилиты
1

3.

История
• Произносится Post-greS-Q-L или postgres, не postgre и не postgre-se-qu-el
• Основные исторические вехи:
1977 по 1985 – создание проекта Ingres Майклом Стоунбрейкером
1986 по 1994 – Стоунбрейкер создал проект postgres (post ingres), чтобы
поддержать сложные типы данных и объектно-реляционную модель
1995 – появление Postgres95, переработали собственный язык запросов
PostQUEL в подмножество SQL
С 1996 по наши дни – стабилизирование Postgres95 и переименование в
PostgreSQL
История PostgreSQL
2

4.

Место PostgreSQL в мире СУБД
• Топ 4 в мире
• Топ 3 в России
Мировой рейтинг 2023
Российское исследование 2019
3

5.

Почему PostgreSQL?
Обширный список типов данных: стандартные + uuid, json, xml, гео, массивы и многие
другие
Расширения
Много open-source проектов
Ежегодные мажорные релизы
Размеры данных:
4

6.

Подключение к PostgreSQL
• psql – стандартный cli, входит в пакет postgresql-client
• GUI: DBeaver (35k stars), pgAdmin4 (1.8k stars)
5

7.

Устройство PostgreSQL
6

8.

Логическая организация данных
7

9.

Специальные базы данных
• template0 – используется для восстановления из логической резервной
копии. Никогда не должна меняться
• template1 – шаблон для всех новых баз данных. Может меняться
• postgres – база данных по умолчанию
8

10.

Специальные схемы
• public – схема по умолчанию
• pg_catalog – таблицы системного каталога
• information_schema – аналог pg_catalog, обязательна по стандарту SQL
• pg_temp – для временных таблиц
• pg_toast – для TOAST таблиц. TOAST – технология хранения длинных
строк, которые не помещаются на страницу (8Кб)
9

11.

Физическая организация
данных
• PGDATA – директория с
данными сервера PostgreSQL
Данные физически хранятся в
табличных пространствах
Табличное пространство –
это директория на диске
Специальные таб. простр.:
1. pg_global – глобальные
объекты, например схема
pg_catalog
2. pg_default – таб. простр. по
умолчанию
10

12.

Клиент-серверная модель
11

13.

Архитектура
1.
2.
3.
4.
Клиент подключается к PostgreSQL
Процесс postmaster fork’ает процесс backend
Клиент читает или записывает данные
Postmaster завершает процесс клиента
12

14.

Пулеры соединений
PostgreSQL создает процесс под каждого клиента
Пулеры эффективно используют backend’ы
max_connections = 100
Популярные пулеры:
1. odyssey (3k stars)
2. pgbouncer (2.4k stars)
3. pgpool-II (~300 stars)
Доклад про пулеры и odyssey
13

15.

Выполнение запроса
1. Синтаксический и
семантический разбор
2. Переписывание запроса
3. Планирование
4. Выполнение
Подробнее про выполнение запросов
14

16.

Роли
15

17.

Роли
• Роль == пользователь
• Роль может быть включена в другую и наследовать атрибуты
• Атрибуты:
1. LOGIN – право подключения
2. CREATEDB – право создания базы данных
3. CREATEROLE – право создания других ролей
4. REPLICATION – право на запуск потоковой репликации
5. PASSWORD ’sec_pass’ – использование пароля
6. NOINHERIT – запрет наследования ролей
7. CONNECTION LIMIT ‘число’ – лимит подключений для роли
8. BYPASSRLS – игнорирование защиты на уровне строк
Документация по атрибутам
16

18.

Роли. Практика
1. Создадим пользователя user
2. Создадим групповую роль dba
3. Включим пользователя user в роль dba
4. Проверим наличие прав у пользователя user
17

19.

Конфигурация
18

20.

Конфигурационные файлы
• 300+ параметров
• postgresql.conf – основной конфигурационный файл, меняется вручную
• postgresql.auto.conf – меняется только через ALTER SYSTEM. Позволяет
настраивать PostgreSQL через SQL
• pg_settings – представление с актуальными настройками. Важный атрибут
представления context: [postmaster (restart), sighup (reload), user / backend
(no need to restart)]
• select pg_reload_conf() – перечитать конфигурацию (sighup)
Справка по всем параметрам
19

21.

Адрес прослушивания
• listen_addresses (localhost) – какой IP-адрес прослушивать для подключений
Лучше никогда не прослушивать external IP.
Рекомендация:
1. listen localhost (127.0.0.1) + connection poller (pgbouncer / odyssey)
2. listen private IP (192.168.1.123)
20

22.

Максимальное количество бекэндов
• max_connections (100)
• На каждое соединение – отдельный процесс (backend)
• Чем меньше значение max_connections, тем больше памяти work_mem
можно выделить для каждого бекэнда
• Для уменьшения этого параметра используются пулеры соединений
21

23.

Параметры памяти
• shared_buffers (32 МБ) – общая память для всех
бекэндов
Рекомендованное значение – 25% от общей
памяти
• work_mem (4 МБ) – память отдельного бекэнда
для операций сортировки и хэширования
Рекомендованное значение – его нет, но
значение должно быть степенью двойки (4, 8,
16, 32)
Общая память будет равна max_connections *
work_mem
22

24.

Параметры Write-Ahead-Log
• checkpoint_timeout (5min) – как часто
запускать фоновый процесс checkpointer для
выгрузки грязных страниц на диск.
Рекомендованное значение – 30-60 минут
• max_wal_size (1GB) – запускать процесс
checkpointer, если превысили этот порог
Рекомендованное значение – 8-16 GB
• archive_mode (off) – архивировать WALфайлы для Point-In-Time-Recovery
восстановления
• archive_command (none) – команда архивации
23

25.

Параметры логирования
• log_rotation_age (1d) – ротация (создание нового лог файла)
Рекомендованное значение – 1 день (по умолчанию)
• log_min_duration_statement (-1) – запросы медленнее этого значения
попадут в лог
Рекомендованное значение – 1000ms (1 second)
24

26.

Резервное копирование
1. Физическое
2. Логическое
25

27.

Физическое резервное копирование
• Физический бэкап – копируем всю структуру данных и WAL.
Плюсы:
1. скорость восстановления
2. можно реализовать Point-In-Time-Recovery
Минусы:
1. нельзя восстановить отдельную БД или таблицу
2. восстановление только на той же версии и архитектуре
Утилиты:
1. pg_basebackup – стандартная утилита
2. wal-g (2.8k stars) – утилита от Яндекса
3. pgbackrest (2k stars)
26

28.

Логическое резервное копирование
• Логический бэкап – выгрузка SQL команд для отдельных БД или объектов БД.
Плюсы:
1. можно сделать бэкап одной БД или таблицы
2. можно восстановить на кластере другой версии или архитектуры
Минусы:
1. на порядок медленней, чем восстановление физической копии
Утилиты:
1. COPY TO / COPY FROM
2. pg_dump – создание SQL-скрипта или архива с оглавлением БД или таблицы
3. pg_dumpall – создание SQL-скрипта с глоб. объектами (роли и табл. пр.)
4. pg_restore – восстановление архива с оглавлением
5. psql -f <sql_script> – восстановление SQL-скрипта
27

29.

Репликация
28

30.

Виды репликации
• Физическая:
1. мастер-реплика: поток в одну сторону
2. репликация всего кластера
3. одинаковые версии и архитектура
• Логическая:
1. pub/sub: поток возможен в обе стороны
2. происходит обмен SQL-команд
3. возможна репликация отдельных БД или таблиц
• Синхронная – коммит фиксируется после сохранения на реплике
• Асинхронная – коммит фиксируется после сохранения на мастере
29

31.

Расширения и утилиты
30

32.

Расширения и утилиты
Расширения:
• pg_stat_statements – анализ запросов
• pg_stat_kcache – анализ тяжелых запросов для диска и CPU
• auto_explain – запишет в лог медленный запрос с EXPLAIN
• pg_repack – для борьбы с bloat, но без полной блокировки таблиц
Утилиты:
• pgbench – утилита нагрузочного тестирования PostgreSQL
• pgbadger – отчеты производительности на основе логов
31

33.

Итоги
• История PostgreSQL
• Как подключаться к PostgreSQL
• Внутреннее устройство PostgreSQL
• Роли
• Конфигурация
• Резервное копирование
• Репликация
32

34.

Полезные ссылки
1.
2.
3.
4.
5.
Подкаст postgres.fm
Статьи planet postgresql
Рассылка postgres weekly
Документация по всем конфигурационным параметрам
Мой телеграм канал
33

35.

Что почитать?
Купить
Купить
Купить
Читать
34

36.

Что посмотреть?
Postgres Pro DBA1
PostgreSQL In-Depth
Training
35

37. Спасибо за внимание!

Максим Дубакин,
Системный инженер,
Яндекс.Практикум
maximdubakin@yandex-team.ru
tg: @engineer_another_yet
practicum.yandex.ru
36