Ссылки

Официальный международный сайт проекта 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 в файлы данных происходит:

при выполнении контрольной точки
при заполнении буферного кеша (Write Ahead)
при восстановлении после сбоя (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

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

# повышение привилегий до 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
#---------------------------------------------------------------------------------------------------

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

Утилиты

Каталог расположения для postgresql-13 /usr/pgsql-13/bin

Управление БД

createdb
dropdb
clusterdb
reindexdb
vacuumdb
vacuumlo - это вспомогательная утилита для очистки базы данных от «осиротевших» (потерянных) больших объектов (Large Objects, LO)

Управление пользователями

createuser
dropuser

Работа с бэкапами и репликацией

pg_dump - создание дампа определенной базы
pg_dumpall - создание полного дампа всех баз
pg_basebackup - полный бэкап базы
pg_restore - восстановление дампа определенной базы
pg_receivewal
pg_recvlogical
pg_verifybackup

Инициализация и управление службой

initdb
pg_ctl
postgresql-13-setup
postgresql-13-check-db-dir

Восстановление и низкоуровневая работа

pg_rewind - откат старого PRIMARY на точку ветвления timeline и переключение в STANDBY к новому PRIMARY
pg_resetwal
pg_archivecleanup
pg_standby
pg_controldata
pg_waldump

Диагностика и тесты

pg_isready
pgbench
pg_test_fsync
pg_test_timing
pg_checksums

Интерфейс и инфо

psql - основная утилита работы с базой
pg_config - получение информации об опциях с которыми был скомпилирован postgres, используется при установке расширений
oid2name
pg_upgrade

Список исполняемых файлов из /usr/pgsql-13/bin

postgres - исполняемый файл сервиса и всех фоновых процессов
vacuumlo

Управление пользователями/ролями

В современной PostgreSQL понятие пользователь и роль идентичны и связаны с одним типом объектов.

Пользователи это роли с правом подключения (атрибут rolcanlogin=true)
Роли(Группы) это роли бес права подключения (атрибут rolcanlogin=false)

Команды

CREATE USER <имя> идентична команде CREATE ROLE <имя> WITH LOGIN
CREATE ROLE <имя> идентична команде CREATE ROLE <имя> WITH NOLOGIN
ALTER USER <имя> ... идентична команде ALTER ROLE <имя> ...

Атрибуты - это параметры, связанные с объектом пользователь/роль, предоставляющие ему особые системные привилегии

rolcanlogin = { true | false} # разрешает подключение
rolsuper = { true | false} # отключение проверки доступов
rolcreaterole = { true | false} # право создавать/изменять/удалять роли
rolcreatedb = { true | false} # право создавать/удалять базы
rolreplication = { true | false} # право выполнять репликацию
rolbypassrls = { true | false} # отключение проверки доступов на уровне строк
rolinherit = { true | false} # включает автоматическое наследование прав от ролей в которые включен данный пользователь/роль

Ключевые слова, задающие значения атрибутам

SUPERUSER/NOSUPERUSER (rolsuper=true/false)
REPLICATION/NOREPLICATION (rolreplication=true/false)
LOGIN/NOLOGIN (rolcanlogin = true/false)
CREATEDB/NOCREATEDB (rolcreatedb = true/false)
CREATEROLE/NOCREATEROLE (rolcreaterole = true/false)
BYPASSRLS/NOBYPASSRLS (rolbypassrls = true/false)
INHERIT/NOINHERIT (rolinherit = true/false)

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

SET ROLE <имя_роли>

Процессы

# все процессы запускаются по символической ссылке
/usr/pgsql-<ver>/bin/postmaster

# которая указывает на исполняемый файл
/usr/pgsql-<ver>/bin/postgres

postmaster                   # главный процесс, является родительским по отношению к остальным
logger                       # 
checkpointer                 # 
background writer            #
walwriter                    #
autovacuum launcher          #
archiver last was            #
stats collector              #
logical replication launcher #
walsender replicator         #

Схемы

Скрытые системные схемы, которые есть в каждой базе

pg_catalog
information_schema

Управление сессиями

-- список сессий
SELECT pid, usename, datname, state, client_addr, client_port FROM pg_stat_activity;

-- мягкое прерывание текущего запроса/транзакции без завершения сессии
SELECT pg_cancel_backend(<pid>);

-- полное завершение (Terminate)
-- немедленно обрывает текущий запрос и разрывает сетевое соединение клиента с сервером, процесс в ОС завершается
SELECT pg_terminate_backend(<pid>);

Гранты

Права пользователям могут задаваться на следующих уровнях

на уровне экземпляра postgers, системные привилегии определяемые атрибутами пользователя/роли
на уровне базы данных
на уровне схемы
на уровне объектов
на уровне столбцов
на уровне строк (Row Level Security — RLS)
на уровне процедур и функций

Права на уровне базы

# список баз с выводом прав на уровне базы
\l
select datname, datacl from pg_database;

# в столбце "Права доступа", доступы заданы в формате
грант_получатель=список_прав/грант_даритель

# список прав
C (Create)    - право создавать новые схемы внутри базы
c (Connect)   - право подключаться к базе
T (Temporary) - право создавать временные таблицы
X (Execute)   - право на выполнение функций

Если вместо "грант_получатель" пусто, то это public, т.е. все пользователи

# пример предоставления доступа на уровне базы
GRANT CONNECT ON DATABASE mydb TO myuser

Права на уровне схемы

U (Usage) - право использовать схему
C (Create) - право создавать таблицы, индексы в схеме

Пример

# список доступных схем в текущей базе
\dn+

GRANT USAGE ON SCHEMA public TO myuser

Права на уровне объектов

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER

Пример

GRANT SELECT ON TABLE my_table TO myuser

Права на уровне столбцов

Можно разрешить видеть только определенные столбцы в таблице/представлении

Пример

GRANT SELECT (id, name) ON TABLE users TO assistant

Права на уровне строк

Позволяет настроить права так, что пользователь выполняет SELECT *, но видит только те строки, которые относятся к его отделу или его авторству.

Пример

CREATE POLICY ... ON table_name

Права на уровне процедур и функций

EXECUTE

  GRANT EXECUTE ON <имя_объекта>;

Автоматическая архивация 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

Восстановление

Восстановление из полного бэкапа

Общий план

Остановите сервис базы
Удалите содержимое каталога данных и каталога архивов WAL
Распакуйте tar архив полного бэкапа в каталог данных
Создайте сигнальный файл standby.signal
Восстановите содержимое каталога архивов WAL с момента полного бэкапа
Скорректируйте параметры в файле конфигурации
- корректировка путей
- определение целевой точки восстановления
- определение действия после восстановления
Запустите сервис базы и проверьте состояние базы
Если необходимо, откройте базу на изменение

Point-in-Time Recovery (PITR) - восстановление на заданный момент времени

Подробная инструкция

# перед восстановлением желательно убедиться, что в исходной базе нет табличных пространств для которых заданы абсолютные пути
# если есть табличные пространства для которых поле расположения не пусто, то это необходимо учитывать при восстановлении и в данной инструкции не рассматривается
psql -c '\db+'

# сохраняем в переменные путь к каталогу данных и архивам WAL
export PGDATA=/path/to/pgdata
export PGARCH=/path/to/archive

# остановка сервиса (если запущен)
systemctl stop postgresql-13

# зачистка каталога данных и архивов WAL
find $PGDATA $PGARCH -mindepth 1 -delete

# подключение под пользователем postgres и задание umask
sudo -i -u postgres
umask 0077

# восстановление каталога данных и архивов WAL из полного бэкапа базы и бэкапа архивов WAL
tar -xvf full_backup.tar.gz -C $PGDATA/
tar -xvf wal_backup.tar.gz -C $PGARCH/

# убедитесь что права назначены следующим образом
chown -R postgres:postgres $PGDATA $PGARCH
find $PGDATA $PGARCH -type d -exec chmod 700 {} +
find $PGDATA $PGARCH -type f -exec chmod 600 {} +

# !!! ВАЖНО, создайте сигнальный файл
# без сигнального файла база сразу откроется в режиме изменения и накат WAL будет невозможен
# этот сигнальный файл автоматически удаляется только при явном переключении базы в режим изменений
touch $PGDATA/standby.signal

# (НЕ РЕКОМЕНДУЕТСЯ) можно использовать одноразовый сигнальный файл, который автоматически удаляется сразу после достижения первой целевой точки
# если просто перезапустить сервис после первой итерации восстановления, база сразу откроется на запись, т.к. сигнального файла уже не будет
touch $PGDATA/recovery.signal

# внесение необходимых корректировок в параметры
vi $PGDATA/postgresql.conf
#-----------------------------------------------------------
# определение команды восстановления архивных WAL
restore_command = 'cp /exp/postgres/archive/%f %p'

# оставаться в режиме восстановления после восстановления
recovery_target_action = 'pause'

# корректировка путей, там где необходимо

#-----------------------------------------------------------

# Восстановление
systemctl start postgresql-13

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

# проверка состояния в процессе восстановления
SELECT
    pg_is_in_recovery() AS "В_режиме_восстановления",
    pg_is_wal_replay_paused() AS "Цель_достигнута_и_пауза",
    pg_last_xact_replay_timestamp() AS "Время_последней_транзакции";

# открытие базы на изменение
#   удаляются сигнальные файлы recovery.signal и/или standby.signal
#   база открыается на изменение
SELECT pg_promote();

Перенос каталога данных

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

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

# определение пути к файлу сервиса
systemctl status postgresql-13

# определение пути к каталогу данных
vi /usr/lib/systemd/system/postgresql-13.service
#---------------------------------------------------------------------------
Environment=PGDATA=/path/to/pgdata/
#---------------------------------------------------------------------------

# проверка расположения табличных пространств
psql -c '\db+'

-- перенос всех объектов из одного табличного пространства в другое
ALTER TABLESPACE old_name MOVE ALL TO new_name;

Контрольные суммы

show data_checksums;

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

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

Full Page Writes (FPW)

При включенном FPW В WAL пишется полная копия блока перед его первым изменением после каждой контрольной точки (checkpoint).

SWITCHOVER

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

Для работы pg_rewind, необходимо чтобы на базе работал FPW.

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

wal_log_hints - параметр в файле конфигурации, включает FPW.

PostgreSQL

Содержание

Ссылки

Обзор

Компоненты PostgreSQL

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

WAL

Checkpoint

Битые блоки

Установка

Rocky Linux 10

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

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

Утилиты

Управление пользователями/ролями

Процессы

Схемы

Управление сессиями

Гранты

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

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

Утилиты

Восстановление

Восстановление из полного бэкапа

Контрольные суммы

Full Page Writes (FPW)

SWITCHOVER

Навигация

PostgreSQL

Ссылки

Обзор

Компоненты PostgreSQL

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

WAL

Checkpoint

Битые блоки

Установка

Rocky Linux 10

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

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

Утилиты

Управление пользователями/ролями

Процессы

Схемы

Управление сессиями

Гранты

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

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

Утилиты

Восстановление

Восстановление из полного бэкапа

Контрольные суммы

Full Page Writes (FPW)

SWITCHOVER

Навигация

Поиск