Установка и настройка PostgreSQL

PostgreSQL после установки по умолчанию слушает только localhost и использует настройки под слабую машину. Ниже: два способа поставить сервер (пакет дистрибутива и Docker), создать кластер, настроить доступы через pg_hba.conf и выставить базовые параметры под нагрузку с проверкой каждого шага

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

Симптом

Свежий инстанс отвечает на psql под локальным пользователем postgres, но приложение с другого хоста получает отказ. Под нагрузкой картина другая: сортировки уходят на диск, а число соединений упирается в лимит. Оба симптома растут из дефолтов: конфигурация рассчитана на минимальную машину и локальный доступ

$ sudo -u postgres psql -Atc "show listen_addresses;"
localhost
$ sudo -u postgres psql -Atc "show shared_buffers;"
128MB

Приложение с другого хоста к такому серверу не подключится, а на машине с 16 ГБ памяти 128 МБ под кеш страниц почти ничего не значат. Это не ошибка установки, а заводские значения

Диагностика

Сначала проверьте, что сервер установлен и какой кластер обслуживается

psql --version
pg_lsclusters
systemctl status postgresql --no-pager

pg_lsclusters есть только в пакетах postgresql-common (Debian, Ubuntu). Он показывает версию, имя кластера, порт, статус, владельца, каталог данных и лог. В RHEL и производных команды другие: кластер создаёт postgresql-setup --initdb, а состояние смотрят через systemctl status postgresql

Если pg_lsclusters не найден, это отдельный сценарий: linux: «команда не найдена»

Дальше выясните, какой конфиг читает работающий сервер. Путь зависит от способа установки, поэтому его не угадывают, а спрашивают у самого сервера

sudo -u postgres psql -Atc "show config_file;"
sudo -u postgres psql -Atc "show hba_file;"
sudo -u postgres psql -Atc "show data_directory;"

config_file — это postgresql.conf, hba_file — pg_hba.conf, data_directory — каталог с самими базами. В Debian и Ubuntu конфиги обычно лежат в /etc/postgresql/<version>/main/, в Docker — внутри каталога данных

Что сервер реально слушает, показывает ss

sudo ss -ltnp | grep postgres

Если в выводе только 127.0.0.1:5432 или [::1]:5432, TCP-доступ ограничен локальной машиной. Если строки нет вообще, сервер может работать только через Unix-сокет

Причина

apt-пакет создаёт кластер автоматически при установке, а initdb задаёт минимальные дефолты. listen_addresses = 'localhost' привязывает сервер к петлевому интерфейсу, потому что открытый наружу PostgreSQL без настроенных правил доступа был бы дырой

Второй слой — pg_hba.conf (host-based authentication, правила доступа по типу подключения). Файл читается сверху вниз, и срабатывает первое подходящее правило. Методы аутентификации: peer сверяет имя ОС-пользователя с именем роли и работает только через Unix-сокет, md5 проверяет пароль по MD5-хешу, scram-sha-256 — по современной схеме SCRAM. В PostgreSQL 14 и новее scram-sha-256 используется по умолчанию

Третий слой — параметры памяти. shared_buffers задаёт кеш страниц самого сервера и по умолчанию мал. max_connections ограничивает число соединений, а каждое соединение в PostgreSQL — отдельный процесс со своей памятью. Поэтому инстанс с дефолтами не разгоняется под нагрузкой, хотя формально работает

Решение

Сначала установка через apt в Debian и Ubuntu

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Пакет postgresql-contrib добавляет расширения, например pg_stat_statements и pgcrypto. Установка сразу создаёт кластер и запускает службу

Проверьте результат

pg_lsclusters

В выводе должна быть строка с версией, именем main, портом 5432 и статусом online. Если кластера нет, создайте его вручную

sudo pg_createcluster <version> main --start

Второй путь — Docker

docker run -d --name pg \
  -e POSTGRES_USER=app \
  -e POSTGRES_DB=app \
  -e POSTGRES_PASSWORD='<password>' \
  -p 127.0.0.1:5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

Официальный образ при первом запуске сам вызывает initdb, создаёт пользователя, базу и файлы доступа. Публикация порта через -p 127.0.0.1:5432:5432 оставляет базу доступной только с хоста, для внешнего доступа адрес меняют осознанно

Кластер можно создать и вручную, через initdb

sudo -u postgres initdb -D /var/lib/postgresql/data
sudo -u postgres pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb создаёт каталог данных и базовые конфиги. Запускать его от root нельзя, поэтому команда идёт через sudo -u postgres

Сетевые параметры и порт задайте в postgresql.conf по пути из show config_file. Дефолтный listen_addresses = 'localhost' и есть причина отказа для клиента с другого хоста, поэтому для внешнего доступа его меняют на конкретный интерфейс или на все адреса:

# postgresql.conf
listen_addresses = '*'    # дефолт 'localhost' слушает только петлевой интерфейс
port = 5432

listen_addresses — список интерфейсов, на которых сервер принимает TCP-соединения. Значение 'localhost' оставляет только петлевой интерфейс, '*' слушает все адреса. Открывать '*' безопасно только за файрволом или в приватной сети: сам параметр не ограничивает, кто может подключиться. Внешний доступ дополнительно разрешают в pg_hba.conf строкой host для подсети приложения и отдельным правилом файрвола

port меняют, если 5432 занят другим инстансом. После правки listen_addresses и port нужен перезапуск, а не перечитывание конфига

sudo systemctl restart postgresql

Правила доступа задайте в pg_hba.conf

# TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 peer
host    app       app   127.0.0.1/32  scram-sha-256
host    app       app   ::1/128       scram-sha-256

Порядок строк важен: сервер идёт по файлу сверху вниз и останавливается на первом совпадении, поэтому широкая строка выше узкой перекроет её. Метод trust пускает без пароля и в продакшене недопустим. peer удобен для локального администрирования: sudo -u postgres psql работает без пароля, потому что имя ОС-пользователя совпадает с именем роли

После правки pg_hba.conf достаточно перечитать конфигурацию

sudo systemctl reload postgresql

Перезапуск не нужен: правила доступа перечитываются на лету

Дальше создайте отдельную роль и базу

sudo -u postgres psql

CREATE ROLE app LOGIN PASSWORD '<password>';
CREATE DATABASE app OWNER app;

Пароль не оседает в открытом виде в psql_history, если использовать \password app: команда запросит пароль и применит password_encryption из конфига. Для приложения пароль хранят в менеджере секретов или в ~/.pgpass с правами 600

Проверьте подключение под новой ролью

psql "postgresql://app@127.0.0.1:5432/app" -c "select current_user, current_database();"

Строка подключения postgresql:// состоит из роли, хоста, порта и базы. Пароль запрашивается отдельно или берётся из ~/.pgpass

Параметры под нагрузку начинают с памяти

shared_buffers — примерно 25% от RAM, effective_cache_size — оценка доступного кеша (порядка 50–75% RAM) для планировщика, work_mem — память на одну операцию сортировки или хеширования в одном соединении

# postgresql.conf, пример для машины с 16 ГБ RAM
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 8MB
maintenance_work_mem = 512MB
max_connections = 200

effective_cache_size не выделяет память: это подсказка планировщику, сколько данных помещается в кеш сервера и ОС. work_mem выделяется на каждую операцию сортировки или хеша, а соединений может быть много, поэтому верхняя граница потребления — это max_connections × work_mem × число одновременных операций. maintenance_work_mem используют VACUUM, CREATE INDEX и другие служебные операции

shared_buffers, max_connections и port требуют перезапуска, work_mem, effective_cache_size и maintenance_work_mem применяются при перечитывании

sudo systemctl reload postgresql

Логическая копия делается через pg_dump и не блокирует чтение

pg_dump -Fc -f /backup/app.dump "postgresql://app@127.0.0.1:5432/app"

Ключ -Fc пишет архив в сжатом формате, который распаковывает pg_restore

pg_restore -d app /backup/app.dump

Роли и права уровня кластера в одну базу не входят, для них нужен pg_dumpall --globals-only. Копировать каталог данных на работающем сервере нельзя: получится несогласованный снимок

Проверка

pg_isready -h 127.0.0.1 -p 5432

pg_isready показывает, принимает ли сервер соединения. Ответ accepting connections — рабочий вариант

sudo -u postgres psql -c "show shared_buffers; show max_connections;"

Убедитесь, что параметры применились и вернулись заданные значения

psql "postgresql://app@127.0.0.1:5432/app" -c "\conninfo"

\conninfo печатает роль, базу, хост и порт текущего подключения. Если приложение в контейнере не видит базу, проверьте, что публикация порта и listen_addresses согласованы

Восстановление копии проверяют на отдельной базе, а не поверх рабочей

createdb app_restore && pg_restore -d app_restore /backup/app.dump

Подводные камни

• Широкая строка pg_hba.conf перекрывает узкую. Первое совпадение выигрывает, поэтому правило host all all 0.0.0.0/0 md5 выше прикладного отключает проверку, которую вы настроили ниже
• listen_addresses = '*' без файрвола открывает базу в сеть. Параметр не фильтрует клиентов, от этого защищает только pg_hba.conf в связке с сетевой политикой
• peer не работает по TCP. Метод сверяет ОС-пользователя, а у сетевого соединения его нет, поэтому для удалённых клиентов нужен scram-sha-256
• Пароль роли не меняется сменой POSTGRES_PASSWORD в контейнере. Переменная обрабатывается только при первом initdb, для существующего кластера выполняйте ALTER ROLE ... PASSWORD
• work_mem умножается на соединения. Большое значение на сервере с сотнями соединений приводит к нехватке памяти, ограничивайте через пул соединений, например PgBouncer
• pg_dump не заменяет WAL-архивирование. Для восстановления на момент времени нужен поток физических копий, логическая копия даёт снимок на момент запуска
• Различия дистрибутивов. pg_lsclusters и pg_createcluster есть в Debian и Ubuntu, в RHEL кластер создаёт postgresql-setup --initdb

Итог

Установка PostgreSQL сводится к трём слоям: пакет или контейнер создаёт кластер и службу, listen_addresses и pg_hba.conf определяют, кто и как подключается, а параметры памяти и соединений определяют поведение под нагрузкой. Состояние проверяйте через pg_lsclusters и pg_isready, а копии делайте через pg_dump

Если сервер запущен, но соединения отклоняются, это отдельный сценарий: postgresql: connection refused

Для настройки под конкретную нагрузку и аудита запросов есть услуги консалтинга по PostgreSQL и разработки backend