Раздел 5 — PostgreSQL: установка и первоначальная настройка

PostgreSQL построен на процессах, а не на потоках. На каждое клиентское соединение операционная система создаёт отдельный процесс. Это решение объясняет и надёжность сервера, и стоимость соединения.

                        ┌─────────────────────┐
   клиент 1  ──────────▶│  backend процесс 1  │──┐
   клиент 2  ──────────▶│  backend процесс 2  │──┤
   клиент N  ──────────▶│  backend процесс N  │──┤
                        └─────────────────────┘  │
                                                 ▼
     postmaster                          ┌───────────────┐
   (главный процесс,                     │ Shared Buffers│
    принимает                            │  общая память │
    соединения)                          └───────┬───────┘
                                                 │
   фоновые процессы:                             ▼
     background writer   ──────────────▶  ┌─────────────┐
     WAL writer          ──────────────▶  │  файлы на   │
     checkpointer        ──────────────▶  │    диске    │
     autovacuum launcher ──────────────▶  └─────────────┘
     stats collector

Shared Buffers — общая область памяти, через которую все процессы работают с данными. Запрос никогда не читает файл напрямую: страница сначала попадает в общий буфер, а изменения сначала пишутся в журнал (WAL), и только потом — в файлы данных. Отсюда правило: если сервер выключился внезапно, при старте PostgreSQL воспроизведёт журнал и восстановит согласованное состояние.

Четыре понятия, которые постоянно путают. В терминологии PostgreSQL они означают строго определённые вещи.

Сервер (машина)
└── Экземпляр PostgreSQL (порт 5432)
    └── Кластер = /var/lib/postgresql/17/main
        ├── База postgres      (служебная)
        ├── База template0     (эталон, не трогать)
        ├── База template1     (шаблон для новых баз)
        └── База nevabit
            ├── Схема public
            │   ├── Таблица users
            │   └── Таблица posts
            └── Схема reports
                └── Представление monthly_activity

На одной машине можно запустить несколько экземпляров — разных версий или с разными настройками. Каждый займёт свой порт и свой каталог данных. В Debian это штатный сценарий, поддержанный инструментами.

Debian 13 (Trixie) содержит PostgreSQL 17 в основном репозитории, поэтому установка сводится к одной команде.

# Обновить список пакетов и установить сервер с клиентом
sudo apt update
sudo apt install postgresql postgresql-client

# Проверить, что установилось
psql --version
# psql (PostgreSQL) 17.x

Если нужна версия новее той, что в дистрибутиве, подключают официальный репозиторий проекта — PGDG:

# Репозиторий PostgreSQL Global Development Group
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
     --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt trixie-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt update
sudo apt install postgresql-17

Что установка сделала с системой:

Debian раскладывает файлы иначе, чем это делает initdb по умолчанию: конфигурация отделена от данных.

В Debian есть свой слой управления кластерами — пакет postgresql-common. Он позволяет держать несколько версий и несколько кластеров одновременно.

# Список всех кластеров на машине
pg_lsclusters
# Ver Cluster Port Status Owner    Data directory              Log file
# 17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/...

# Управление конкретным кластером: версия, имя, действие
sudo pg_ctlcluster 17 main start
sudo pg_ctlcluster 17 main stop
sudo pg_ctlcluster 17 main restart
sudo pg_ctlcluster 17 main reload    # перечитать конфигурацию без разрыва соединений

# Создать второй кластер на другом порту
sudo pg_createcluster 17 test --port=5433 --start

Параллельно работает обычное управление через systemd:

sudo systemctl status postgresql@17-main
sudo systemctl restart postgresql@17-main
sudo systemctl enable postgresql@17-main    # автозапуск

# postgresql.service — это обёртка, управляющая всеми кластерами сразу
sudo systemctl restart postgresql

Если сервер не поднимается — смотреть нужно журнал, а не гадать:

sudo tail -n 50 /var/log/postgresql/postgresql-17-main.log
sudo journalctl -u postgresql@17-main -n 50 --no-pager

Основной файл конфигурации. Формат простой: параметр = значение, комментарии после #.

Контексты применения

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

-- Узнать текущее значение и контекст параметра
SHOW shared_buffers;

SELECT name, setting, unit, context, source
FROM   pg_settings
WHERE  name IN ('shared_buffers', 'work_mem', 'max_connections');

-- Какие параметры изменены относительно умолчаний
SELECT name, setting, source
FROM   pg_settings
WHERE  source NOT IN ('default', 'override');

-- Где лежат файлы конфигурации
SHOW config_file;
SHOW hba_file;
SHOW data_directory;

Три способа задать параметр

-- Изменить параметр из SQL
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

-- Отменить своё изменение и вернуться к значению из postgresql.conf
ALTER SYSTEM RESET log_min_duration_statement;
SELECT pg_reload_conf();

HBA расшифровывается как Host-Based Authentication. Этот файл решает, кому разрешено подключаться и каким способом он должен доказать, что он — это он.

Формат строки — пять или шесть полей:

# ТИП    БАЗА     ПОЛЬЗОВАТЕЛЬ  АДРЕС            МЕТОД

local    all      postgres                       peer
local    all      all                            peer
host     all      all           127.0.0.1/32     scram-sha-256
host     all      all           ::1/128          scram-sha-256
host     nevabit  nevabit_app   10.0.0.0/24      scram-sha-256
hostssl  nevabit  nevabit_app   0.0.0.0/0        scram-sha-256
host     all      all           0.0.0.0/0        reject

Метод peer объясняет, почему сразу после установки работает такая команда:

# Переключиться на системного пользователя postgres и войти в psql
sudo -u postgres psql

Пароль не спрашивается: PostgreSQL спрашивает у ядра, от чьего имени пришло соединение через сокет, видит системного пользователя postgres и пускает его в роль с тем же именем.

-- Проверить файл на ошибки, не перезагружая конфигурацию
SELECT line_number, type, database, user_name, address, auth_method, error
FROM   pg_hba_file_rules
WHERE  error IS NOT NULL;

psql — штатный клиент командной строки. Он не только выполняет SQL, но и умеет исследовать структуру базы метакомандами, начинающимися с обратной косой черты.

# Подключение: пользователь, база, хост, порт
psql -U nevabit_app -d nevabit -h localhost -p 5432

# Строка подключения одним аргументом
psql "postgresql://nevabit_app@localhost:5432/nevabit"

# Выполнить один запрос и выйти
psql -U postgres -c "SELECT version();"

# Выполнить файл
psql -U nevabit_app -d nevabit -f socialnet_db.sql

Создание рабочей базы и пользователя приложения — минимальный сценарий после установки:

-- От имени postgres
CREATE ROLE nevabit_app LOGIN PASSWORD 'смените_этот_пароль';
CREATE DATABASE nevabit OWNER nevabit_app ENCODING 'UTF8';

-- Проверка
\l
\du

Значения по умолчанию у PostgreSQL намеренно скромные — они рассчитаны на то, чтобы сервер запустился где угодно. На типичном VPS их нужно поднимать; на очень маленьком — следить, чтобы сервер не съел всю память.

Пример расчёта для машины 1 CPU / 1 ГБ ОЗУ — распространённая конфигурация недорогого VPS:

Настройки уровня операционной системы, без которых маленький сервер живёт хуже:

# Swap обязателен даже при быстром диске — как страховка от OOM
# Но обращаться к нему система должна неохотно
sudo sysctl -w vm.swappiness=10
echo "vm.swappiness=10" | sudo tee -a /etc/sysctl.conf

# Посмотреть, сколько памяти реально занято
free -h
ps -o pid,rss,cmd -C postgres

Свежеустановленный PostgreSQL закрыт разумно, но первые же шаги по «настройке доступа» обычно эту защиту снимают.

# Проверить, что снаружи порт закрыт
sudo ss -tlnp | grep 5432
# LISTEN 127.0.0.1:5432 — хорошо
# LISTEN 0.0.0.0:5432   — открыт на всех интерфейсах, проверьте файрвол

sudo ufw status