PostgreSQL

Ссылки

• Официальный международный сайт проекта PostgreSQL
  • Матрица возможностей (feature matrix)
  • PostgreSQL 18.1 Documentation

Сторонние сборки

• Postgres Pro Enterprise

Обзор

PostgreSQL Community

Последний стабильный релиз PostgreSQL 18.1

Компоненты PostgreSQL

• Основной процесс сервера (Postmaster) - Главный управляющий процесс, который запускает и координирует все остальные процессы PostgreSQL. Он отвечает за управление соединениями, восстановление после сбоев и поддержание общей работоспособности системы.
• Фоновые процессы (Background Processes) - Различные процессы, работающие в фоновом режиме для выполнения основных задач, таких как:
  • Запись WAL (Write-Ahead Log) Writer - отвечает за запись из буферов памяти WAL (WAL Buffers) в файлы транзакционных логов (сегменты WAL) для обеспечения целостности данных.
  • Очистка Autovacuum Launcher/Worker - Автоматически запускает процессы VACUUM для очистки "мертвых" строк (удаленных или обновленных записей) и сбора статистики для оптимизатора запросов.
  • Контрольные точки (Checkpointer) - Периодически сбрасывает все измененные страницы данных из общей памяти на диск для обеспечения согласованности и минимизации времени восстановления.
  • Фоновые рабочие процессы (Background Workers) - Введены в более поздних версиях, используются для выполнения параллельных операций, например, при параллельном сканировании, параллельных индексах GIN и асинхронном I/O.
• Бэкенд-процессы Backend Processes - Отдельный процесс, который создается postmaster'ом для обработки каждого клиентского соединения и выполнения запросов. Каждый процесс управляет синтаксическим анализом, планированием и выполнением SQL-запросов.
• Разделяемая память (Shared Memory) - Область памяти, доступная для всех процессов PostgreSQL. Ключевые ее части:
  • Разделяемые буферы (Shared Buffers) - Основной кэш, где хранятся блоки данных, считанные с диска, для ускорения повторного доступа.
  • Буферы WAL (WAL Buffers) - Временное хранилище для записей WAL перед их записью на диск.
  • Кэш системных каталогов - Кэширование информации о структуре базы данных.
• Физическое хранилище данных (Data Directory/Storage) - Каталог на диске, где хранятся все физические файлы базы данных, включая файлы данных (heap files), файлы индексов, файлы WAL и системные каталоги (системную метаинформацию).

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

Табличные пространства

Физическая структура табличного пространства

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

/path/to/my_space/ 
    ├── 6354/  <-- OID конкретной базы данных (например, mydb)
    │      ├── 12345   <-- файл форка main, сегмент 1 (1 ГБ)
    │      ├── 12345.1 <-- файл форка main, сегмент 2 (1 ГБ)
    │      ├── 12345_vm <-- файл форка vm, сегмент 1 (1 ГБ)
    │      ├── 12345_fsm <-- файл форка fsm, сегмент 1 (1 ГБ)
    │      └── 12326 <-- файл форка main, сегмент 1 (1 ГБ)
    │      └── 12326_init <-- файл форка init, сегмент 1 (1 ГБ)
    └── 6355/  <-- OID другой базы данных
        └── ... 

Важные особенности

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

Структура объекта

• объект (таблица,индекс,...) - логическое понятие, состоит из форков
• форк - логическое понятие, состоит из файлов сегментов и может быть нескольких типов
  • форк main содержит данные объекта и состоит из файлов сегментов <oid>,<oid>.1,...
  • форк vm (Visibility Map) содержит мета данные о видимости строк на странциах и состоит из файлов сегментов <oid>_vm,<oid>_vm.1,...
  • форк fsm (Free Space Map) содержит метаданные о свободном месте на странциах и состоит из файлов сегментов <oid>_fsm,<oid>_fsm.1,...
  • форк init содержит метаданные для быстрого восстановления не журналируемых объектов, состоит из файлов сегментов <oid>_init,<oid>_init.1,...
• сегмент - физический файл, имеет заголовок и состоит из страниц (блоков) фиксированного размера, по умолчанию 8Кб (размер блока можно указать при сборке PostgreSQL)

WAL

Когда данные WAL записываются в файлы данных?

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

Запись WAL в файлы данных происходит:

1. при выполнении контрольной точки
2. при заполнении буферного кеша (Write Ahead)
3. при восстановлении после сбоя (Crash Recovery)

Write Ahead

Если PostgreSQL нуждается в свободной памяти для загрузки новых страниц данных в свой буферный кеш (shared buffers), а кеш полон, он может вытеснить некоторые старые, измененные страницы на диск, чтобы освободить место. Этот процесс называется write ahead (запись наперед) или buffer flush.

Checkpoint

Что такое Checkpoint?

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

Основная цель: Синхронизация данных в оперативной памяти с данными на диске.

Благодаря контрольным точкам, в случае аварийного сбоя (например, отключения питания), PostgreSQL не нужно "проигрывать" (replay) всю историю операций из WAL-журналов с момента последнего запуска сервера. Ему достаточно начать восстановление только с момента последней успешной точки контроля.

Когда выполняется Checkpoint?

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

1. По истечении временного интервала

Параметр: checkpoint_timeout
Значение по умолчанию: 5 минут.

2. По достижении лимита объема WAL

Параметр: max_wal_size
Значение по умолчанию: 1 ГБ.

Другие случаи выполнения Checkpoint:

• Команда администратора SQL: CHECKPOINT;
• Остановка сервера: При чистой (нормальной) остановке сервера PostgreSQL всегда выполняет финальную контрольную точку, чтобы гарантировать запись всех данных на диск перед выключением.
• Начало или конец процесса резервного копирования: При использовании функций pg_start_backup() или pg_basebackup.

Битые блоки

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

Установка

Rocky Linux 10

1. установите пакет официального репозитория PostgreSQL
2. установите PostreSQL нужной версии
3. Инизиализируйте базу
4. Включите и запустите сервис

# повышение привилегий до root
sudo -s

# установка пакета официального репозитория
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm

dnf repolist
#---------------------------------------------------------------------------------------------------
...
pgdg-common   PostgreSQL common RPMs for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg13        PostgreSQL 13 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg14        PostgreSQL 14 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg15        PostgreSQL 15 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg16        PostgreSQL 16 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg17        PostgreSQL 17 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg18        PostgreSQL 18 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
...
#---------------------------------------------------------------------------------------------------

dnf install postgresql18-server

# инициализация кластера баз данных (здесь слово кластер означает совокупность базы данных)
/usr/pgsql-18/bin/postgresql-18-setup initdb

systemctl enable postgresql-18
systemctl start postgresql-18
systemctl status postgresql-18

# проверка сервисной учетки postgres
id postgres
 uid=26(postgres) gid=26(postgres) группы=26(postgres)

passwd -S postgres
 postgres L 2025-12-05 -1 -1 -1 -1

# выход из root
exit

Проверка подключения

# вход под пользователем postgres
sudo su -l postgres

# подключение к кластеру баз данных
psql

#---------------------------------------------------------------------------------------------------
# список баз данных
postgres-# \l
                                                       Список баз данных
    Имя    | Владелец | Кодировка | Провайдер локали | LC_COLLATE  |  LC_CTYPE   | Локаль | Правила ICU |     Права доступа     
-----------+----------+-----------+------------------+-------------+-------------+--------+-------------+-----------------------
 postgres  | postgres | UTF8      | libc             | ru_RU.UTF-8 | ru_RU.UTF-8 |        |             | 
 template0 | postgres | UTF8      | libc             | ru_RU.UTF-8 | ru_RU.UTF-8 |        |             | =c/postgres          +
           |          |           |                  |             |             |        |             | postgres=CTc/postgres
 template1 | postgres | UTF8      | libc             | ru_RU.UTF-8 | ru_RU.UTF-8 |        |             | =c/postgres          +
           |          |           |                  |             |             |        |             | postgres=CTc/postgres
(3 строки)

# список табличных пространств
# столбец "Расположение" показывает путь к данным ТП, если этот путь отличается от базового каталога данных
postgres=# \db             
     Список табличных пространств
    Имя     | Владелец | Расположение 
------------+----------+--------------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 строки)

# базовый каталог данных
postgres=# show data_directory;
     data_directory     
------------------------
 /var/lib/pgsql/18/data
(1 строка)


# выход
postgres-# \q
#---------------------------------------------------------------------------------------------------

Администрирование

Автоматическая архивация WAL

По умолчанию автоматическая архивация WAL выключена.

Включение автоматической архивации WAL

/var/lib/pgsql/18/data/postgresql.conf
#-------------------------------------------------------------------------------
# включение режима архивации
wal_level = replica

# включение функции архивации
archive_mode = on

# определение команды архивации, которая будет выполняться после заполнения файла WAL
# команда должна возвращать нулевой код выхода (успех), только если файл WAL был успешно скопирован
# команда будет периодически выполняться до тех пор, пока она не завершится успешно для текущего файла WAL
# %p - полный путь к файлу WAL
# %f - имя файла WAL
archive_command = 'cp "%p" "/path/to/wal/archive/%f"'
#-------------------------------------------------------------------------------

# перезапуск кластера баз данных

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

Параметры min_wal_size и max_wal_size определяют не жесткий желательный суммарный объем WAL файлов к которому стремится база данных.

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

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

Срочная остановка роста WAL

# установка параметра включающего режим read only
ALTER SYSTEM SET default_transaction_read_only = on;

# перезагрузка конфигурации
SELECT pg_reload_conf();)

# текущие транзакции вносящие изменения продолжатся (?)
# новые транзакции вносящие изменения будут отвергаться

archive_timeout - определяет интервал через который будут повторяться попытки архивации WAL файлов (по умолчанию 30 сек)

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

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

• pg_basebackup - стандартная утилита для создания полной физической копии всего каталога данных кластера
  • Создает согласованную базовую резервную копию, которую можно использовать в сочетании с архивированием журналов предзаписи (WAL) для восстановления на любой момент времени PITR (Point-In-Time Recovery)
  • Поддерживает потоковую передачу данных и различные форматы (plain, tar, directory)
• Сторонние инструменты
  • pg_probackup
  • pgBackRest
  • Barman
• Снимки (snapshot) на уровне файловой системы
  • для этого база должна быть либо остановлена (холодный бэкап)
  • либо переключена в режим резервного копирования pg_start_backup(), pg_stop_backup()

Логическое резервное копирование

• pg_dump - дамп отдельной базы в виде sql скрипта, выполнение которого полностью восстанавливает структуру и данные базы
• pg_dumpall - дамп всего кластера баз данных в виде sql скрипта, выполнение которого полностью восстанавливает структуру и данные всех баз кластера, включая табличные пространства роли и прочие объекты кластера
• команда copy