Раздел 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 это штатный сценарий, поддержанный инструментами.

Курс рассчитан на PostgreSQL 18 — эта версия стоит на учебных ПК с Windows. Debian 13 (Trixie) содержит в основном репозитории PostgreSQL 17, и примеры для Debian ниже — на ней. Команды курса в 17 и 18 одинаковы, отличается местами только вид вывода: например, \conninfo в 18 печатает таблицу, а не одну строку. Установка штатной версии сводится к одной команде.

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

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

PostgreSQL 18 на Debian ставят из официального репозитория проекта — PGDG. Тогда во всех путях и командах этого раздела вместо 17 будет 18: /etc/postgresql/18/main/, pg_ctlcluster 18 main.

# Репозиторий 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-18

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

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

Путь	Что там
/etc/postgresql/17/main/	postgresql.conf, pg_hba.conf, pg_ident.conf
/var/lib/postgresql/17/main/	каталог данных: файлы таблиц, WAL, служебные каталоги
/var/log/postgresql/	журналы сервера
/usr/lib/postgresql/17/bin/	исполняемые файлы: postgres, initdb, pg_dump
/var/run/postgresql/	сокет и pid-файл

Windows. На Windows PostgreSQL ставят установщиком EDB — файл вида postgresql-18.6-5-windows-x64.exe со страницы загрузки postgresql.org (Download → Windows). Установщик задаёт несколько вопросов.

После установки сервер работает как служба Windows postgresql-x64-18 (число — версия) и запускается вместе с компьютером. Всё лежит в C:\Program Files\PostgreSQL\18\, а конфигурация — внутри каталога данных, как задумано разработчиками.

Каталог	Что там
bin\	программы: psql.exe, pg_dump.exe, pg_restore.exe
data\	каталог данных, в нём же postgresql.conf и pg_hba.conf

В отличие от Debian, сервер на Windows по умолчанию слушает все сетевые адреса (listen_addresses = '*') и пускает только по паролю (scram-sha-256), SSL выключен. Службой управляют в оснастке «Службы» (Win+R → services.msc) или из PowerShell, запущенного от имени администратора:

# Состояние службы
Get-Service postgresql*

# Перезапуск — для параметров с контекстом postmaster
Restart-Service postgresql-x64-18

# Перечитать конфигурацию без перезапуска — из psql или Query Tool
SELECT pg_reload_conf();

Утилиты в командной строке. Установщик не добавляет каталог bin в переменную PATH, поэтому команда pg_dump в PowerShell отвечает, что такой программы нет. Её запускают по полному пути или добавляют каталог в PATH на время сеанса:

# PowerShell: полный путь, перед ним — знак &
& "C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -U nevabit_app -d nevabit -f nevabit.sql

# Или один раз добавить каталог в PATH до закрытия окна
$env:Path += ";C:\Program Files\PostgreSQL\18\bin"
pg_dump -U nevabit_app -d nevabit -f nevabit.sql

# Размер получившегося файла в байтах
(Get-Item nevabit.sql).Length

pgAdmin — графический клиент: в нём удобнее выполнять SQL с русскими данными и смотреть, что получилось. Окно, меню и работа в Query Tool — в пункте 10.

В 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

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

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

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

Контекст	Что требуется	Примеры
postmaster	перезапуск сервера	shared_buffers, max_connections, listen_addresses, port
sighup	reload	log_min_duration_statement, autovacuum
superuser	изменение в сессии суперпользователем	log_statement
user	изменение в своей сессии любым пользователем	work_mem, search_path
internal	задаётся при сборке или initdb, изменить нельзя	block_size, lc_collate

-- Узнать текущее значение и контекст параметра
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

Метод	Как проверяет	Когда применять
scram-sha-256	пароль по протоколу SCRAM	основной метод для сетевых подключений
peer	имя системного пользователя ОС	локальные подключения через сокет
ident	внешняя служба ident	практически не используется
md5	устаревшее хеширование пароля	только для старых клиентов
cert	клиентский TLS-сертификат	сервис-сервис без паролей
trust	пускает без проверки	никогда вне изолированного стенда
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:

Параметр	По умолчанию	Для 1 ГБ	Обоснование
shared_buffers	128MB	256MB	25% ОЗУ
effective_cache_size	4GB	512MB	подсказка планировщику, память не занимает
work_mem	4MB	4MB	умножается на число операций — повышать опасно
maintenance_work_mem	64MB	64MB	разово при VACUUM и построении индексов
max_connections	100	40	остальное закрывается пулом соединений
max_worker_processes	8	2	одно ядро
max_parallel_workers_per_gather	2	0	параллелизм на одном ядре только вредит

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

# 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

pgAdmin — графический клиент PostgreSQL. Данных он не хранит: подключается к серверу так же, как psql или приложение, и каждое его окно с запросами — отдельное соединение, то есть отдельный backend-процесс из пункта 01. Всё, что делается мышью, pgAdmin превращает в обычный SQL или в запуск pg_dump / pg_restore. Снимки ниже — pgAdmin 9.18 на учебном ПК с Windows 11; интерфейс английский. На телефоне снимок открывается в полном размере нажатием.

Меню	Главное
File	Preferences — настройки: клавиши, пути к утилитам (Paths → Binary paths)
Object	действия с выделенным в дереве объектом — то же, что его контекстное меню: Create, Refresh, Delete, Properties, подключение к серверу
Tools	Query Tool (Alt+Shift+Q) — окно для SQL; PSQL Tool — psql внутри pgAdmin; Backup… и Restore… — pg_dump и pg_restore в виде диалога; Import/Export Data — \copy; Maintenance — VACUUM, ANALYZE, REINDEX; Search Objects — поиск по имени; ERD Tool — схема таблиц; Schema Diff — сравнение двух баз; Grant Wizard — выдача прав
Edit, View, Window	правка текста, вид и расположение панелей; для работы по курсу не нужны
Help	документация pgAdmin и PostgreSQL

Ниже — как в pgAdmin сделать каждый пункт самостоятельной работы «Начало работы с PostgreSQL». Имена — варианта 1 (база sklad, роль sklad_user, таблица tovary), подставьте свои.

1. Параметры сервера

Выделите базу postgres под сервером «PostgreSQL 18» (он подключён как postgres), откройте Query Tool и выполните запросы по одному — выделяя строку и нажимая F5, иначе в Data Output останется только результат последнего.

SELECT version();
SHOW shared_buffers;

-- Несколько параметров и их контекст одним запросом
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'port');

SHOW выводит память в понятных единицах (128MB), pg_settings — числом в единицах из столбца unit (16384 × 8kB). Это одно и то же значение. Те же параметры видны у сервера: выделите в дереве «PostgreSQL 18» → «⋮» → Open → Dashboard → вкладка Configuration, в поле поиска — имя параметра. Значение там как в pg_settings — числом и единицей (16384, 8kB), а столбца context нет, его смотрят запросом. Что означает контекст — пункт 05.

2. Роль и база

В том же Query Tool под postgres. CREATE DATABASE нельзя выполнять внутри транзакции, поэтому каждую команду — отдельным F5: выделить и выполнить.

CREATE ROLE sklad_user LOGIN PASSWORD 'придумайте_пароль';
CREATE DATABASE sklad OWNER sklad_user ENCODING 'UTF8';

То же мышью. Роль: правой кнопкой по Login/Group Roles → Create → Login/Group Role… → General: Name; Definition: Password; Privileges: Can login? — включить, остальные переключатели не трогать. База: правой кнопкой по Databases → Create → Database… → General: Database и Owner (выбрать свою роль — список ролей берётся с сервера, поэтому роль создают первой); Definition: Encoding — там уже стоит UTF8. В каждом диалоге есть вкладка SQL с командой, которую pgAdmin выполнит по кнопке Save, — её и записывают на лист.

На снимке владелец — postgres: так диалог заполняет поле по умолчанию. В работе выберите в Owner свою роль, и в команде будет OWNER = sklad_user.

Подключение под новой ролью: правой кнопкой по Servers → Register → Server…; General → Name, например sklad_user; Connection → Host localhost, Port 5432, Maintenance database sklad, Username sklad_user и пароль роли. В дереве будет два сервера — это два подключения к одному и тому же PostgreSQL. Дальше Query Tool открывайте на базе sklad под этим вторым подключением. Проверка — запрос SELECT current_user, current_database();.

Быстрее — в уже открытом Query Tool, без второго сервера в дереве: щелчок по строке подключения → < New Connection... > → окно Add New Connection: Database — sklad, User — sklad_user, Role не заполнять → Save. pgAdmin предупредит, что несохранённый текст запроса пропадёт, — Yes. Затем спросит пароль роли. Строка подключения станет sklad/sklad_user@PostgreSQL 18.

\conninfo — метакоманда, она выполняется в psql: SQL Shell, на вопросы ответить localhost, sklad, 5432, sklad_user.

4. Метакоманды

Только в psql: SQL Shell или Tools → PSQL Tool (откроется psql, уже подключённый к выделенной в дереве базе). Список всех метакоманд — \?, справка по SQL-команде — \h; подробно — пункт 07. Если русские буквы в SQL Shell выводятся непонятными символами — пункт 03, про кодовую страницу.

PSQL Tool — это окно командной строки Windows, в котором pgAdmin запускает psql.exe из каталога утилит (Binary paths, пункт 5) под пользователем сервера в дереве, обычно postgres. Русские буквы в нём нечитаемы: и подписи самого psql, и данные. Данные исправляет команда \encoding WIN866 — после неё SELECT выводит названия по-русски; подписи psql (\conninfo, «(4 строки)») так и остаются значками. Метакоманды в PSQL Tool работают, а запросы с русскими данными удобнее выполнять в Query Tool.

5. Резервная копия и восстановление

На лист нужна команда pg_dump — её удобнее выполнить в PowerShell (пункт 03, «Утилиты в командной строке»). В pgAdmin то же самое делают диалоги.

Как указать путь: File → Preferences (или шестерёнка внизу слева) → Paths → Binary paths. В таблице PostgreSQL Binary Path, в строке PostgreSQL 18, впишите C:\Program Files\PostgreSQL\18\bin и нажмите значок проверки справа от поля — откроется окно с версиями pg_dump, pg_restore, psql: 18.6. Затем Save — значок дискеты вверху вкладки. Без Save путь не запоминается. Строку PostgreSQL 13 с $DIR/../runtime и отметку Set as default не трогайте: для сервера 18 pgAdmin берёт путь из строки 18. Настройка хранится в профиле пользователя Windows, поэтому под другой учётной записью её делают заново.