RoadMap - PostgreSQL

СОДЕРЖАНИЕ

1. Установка +

2. АРХИТЕКТУРА PostgreSQL (Структурная схема psql.drawio)

   • Общая информация +
   • Postmaster process +
   • Backend processes +
   • Shared memory + Subprocesses Postmaster:
   • Checkpointer +
   • Background writer +
   • WAL writer +
   • WAL archiver +
   • WAL sender +
   • Syslogger +
   • Autovacuum launcher - Autovacuum worker +
     • Autovacuum worker +
   • Startup process (standby) +
     • WAL receiver (standby) +
   • Logical replication launcher (standby) +
     • Logical replication worker (standby) = Logical Apply Worker + Table Sync Worker +
   • Stats collector +
   • Еще подпроцессы: +
     • Two-phase commit (2PC) recovery processes (условно)
     • Parallel worker (условный процесс)

3. Структура каталогов кластера (etc, PGDATA) +

   • postgresql - Древо файлов и каталогов, их применение и особенности
   • Каталог данных кластера PGDATA - Древо файлов и каталогов
   • Другие каталоги - Файлы с PID процессом и др.
   • Файлы конфигурации:
     • environment - это переменные окружения, которые устанавливаются для процесса postmaster и всех его дочерних процессов (backend’ов, фоновых процессов и т.д.).
     • pg_ctl.conf - альтернатива systemd
     • pg_hba.conf - Кто, откуда и как может подключиться к каким базам данных в кластере
     • pg_ident.conf - файл отображения имён пользователей ОС в роли PostgreSQL, используемый только при аутентификации методом ident (для TCP-подключений) или peer (для локальных Unix-сокетов, хотя для peer он обычно не нужен).
       • postgresql.conf - postgresql.conf первичная информация - основной конфигурационный файл
         • postgresql.conf default major-18 - default конфиг Применение изменений
         • postgresql.conf info - Подробное описание настроек
         • conf.d - Доп. файлы конфигурации. Приоритеты файлов конфигурации
         • postgresql.auto.conf - postgresql.auto.conf -Это рекомендуемый способ изменения глобальных параметров в production. Так же меняется через SQL-команду ALTER SYSTEM
     • start.conf - Файл содержит ровно одно слово — режим автозапуска: auto

4. Принципы и Механизмы

   • Принцип работы - WAL +
   • MVCC механизм, tuple +
   • Принцип работы - VACUUM, autoVACUUM +
   • Принцип работы - ANALYZE (pg_statistic) +
   • Принцип работы - Planner (планировщик) +
   • Логические схемы простых операций (Select, Insert, Update):
     • Select (см. на схеме) +
     • Insert (см. на схеме) +
     • Update (см. на схеме) +

5. РЕПЛИКАЦИЯ в PostgreSQL

   • Физическая (STREAMING) репликация - синхронная/асинхронная +
     • Настройка streaming репликации +
       • Мониторинг streaming репликации +
   • Логическая (PUBLICATION) репликация+
     • Настройка publication репликации
       • Мониторинг publication репликации +
     • Сравнение STREAMING & PUBLICATION +

6. РЕЗЕРВНОЕ КОПИРОВАНИЕ в PostgreSQL (Backup)

   • Физический (pg_basebackup) vs Логический (pg_dump) +
   • Архивация WAL, Point-in-Time Recovery (PITR) +
     • Восстановление из Archive-WAL до определенной точки с помощью PITR +
     • Скрипт автоматического backup и восстановление PITR
   • Утилиты:
     • pg_basebackup — Применение. Физический backup +
     • pg_dump, pg_dumpall, pg_restore — Применение. Логический backup. Частичный backup +
     • pg_upgrade — для быстрого обновления кластера между major-версиями

7. Команды

   • Навигация по командам - RoadMap
     • Системные команды администрирования PostgreSQL - на уровне операционной системы
     • DDL, DML, DCL - SQL
     • Мета-команды psql - \?
     • Базовое АДМИНИСТРИРОВАНИЕ PostgreSQL - необходимый минимум

8. Табличное пространство psql - ?

9. МИГРАЦИЯ

   • Миграция с MySQL на psql - Pgloader +
   • Миграция между версиями. PG 14 → PG 16 с минимальным downtime (временем отключения).

10. Распределение нагрузки, Проксирование

    • PgPool-II - что это? +
      • Чтение с реплик (Read Scaling)
      • Пул соединений (Connection Pooling)
      • Автоматический переход на резервную систему (Failover)
      • Кэширование запросов (Query Caching)

---

Дополнительная информация psql - Ссылки на ресурсы

---

Задачки

• pg_basebackup - Сделать холодный физический backup кластера +

• pg_basebackup - Развернуть Standby используя +

• Проверить репликацию +

• Удалить Standby, отключить его и почистить конфиги +

• Настроить Физическую (streaming) репликацию (без WAL archiving и Repl. slot) и промониторить

  • Без WAL archiving и Replication slots +
  • Добавить Physical Replication Slot +
  • Добавить WAL archive +
  • Отключение streaming репликации +

• Настроить Логическую (publication) репликацию и промониторить

  • Объединение данных из нескольких источников +
  • Реплика одной определенной таблицы +

• Организовать автоматическое резервное копирование на основе “pg_basebackup”, Архивации WAL” и протестировать восстановление “PITR”

  • Скрипт автоматического backup и восстановление PITR

• Как вручную прочитать t_xmin/t_xmax через системный каталог heap_page_items?

• Как отследить visibility map и понять, почему VACUUM пропустил страницу?

• Или как ограничение 2^32 XID приводит к wraparound, и почему VACUUM обязателен?

• Как мониторить прогресс checkpoint’а через pg_stat_bgwriter,

• Или как настроить checkpoint’ы под SSD/NVMe?

• Как настроить autovacuum для таблицы с 1 млрд строк,

• Или как принудительно запустить freeze vacuum?

---

Ответы на вопросы

• Коэффициент попаданий в кэш
• Сколько сейчас активных сеансов к базе и к кластеру в общем
• Количество сессий, в которых создана хотя бы одна временная таблица
• Сколько временных файлов создаётся
• Анализ размеров временных таблиц
• Просмотреть физическое использование диска и памяти на уровне ОС
• Проверить, что VACUUM работает