PostgreSQL для прода: установка, настройка и бэкап

Задача пришла от команды разработки: нужен новый инстанс PostgreSQL для продакшн-окружения. Установить, создать базу и пользователя, настроить подключения из подсети приложения, выкрутить параметры под нагрузку, настроить логирование и автобэкапы. Стандартная задача, разбираем по шагам.

PostgreSQL, реляционные базы и когда что использовать

Прежде чем переходить к установке, стоит разобраться: почему PostgreSQL, а не что-то другое?

Типы баз данных

Все базы данных делятся на категории в зависимости от того, как они хранят и организуют данные.

graph TD
    DB[Базы данных] --> REL[Реляционные SQL]
    DB --> DOC[Документные]
    DB --> KV[Ключ-значение]
    DB --> WIDE[Широкие таблицы]
    DB --> GRAPH[Графовые]

    REL --> PG[PostgreSQL]
    REL --> MY[MySQL / MariaDB]
    REL --> LITE[SQLite]

    DOC --> MONGO[MongoDB]
    DOC --> COUCH[CouchDB]

    KV --> REDIS[Redis]
    KV --> MEM[Memcached]

    WIDE --> CASS[Cassandra]

    GRAPH --> NEO[Neo4j]

Реляционные (SQL) — данные хранятся в таблицах со строгой схемой. Таблицы связаны через ключи. Запросы — SQL. Полный ACID: если транзакция упала — никаких частичных изменений. Выбирают когда данные структурированы, важна целостность и нужны сложные выборки с joins.

Документные (MongoDB, CouchDB) — данные хранятся как JSON-документы без фиксированной схемы. Каждый документ может иметь разный набор полей. Хорошо подходят для каталогов товаров, CMS, профилей пользователей — там где структура часто меняется или отличается от записи к записи.

Ключ-значение (Redis, Memcached) — простейшая структура: ключ → значение. Работает в памяти, невероятно быстро. Используется для кеширования, сессий, счётчиков, очередей задач. Redis умеет персистентность — данные не теряются при перезапуске.

Широкие таблицы (Cassandra) — данные распределяются по кластеру горизонтально. Написано для петабайтов и миллионов запросов в секунду. Используется в IoT, аналитике, таймсериях. Жертвует строгим ACID ради линейной масштабируемости.

Графовые (Neo4j) — хранят сущности и связи между ними нативно. Идеальны для социальных сетей, рекомендательных систем, карт зависимостей.

Когда выбирать PostgreSQL

PostgreSQL — реляционная база. Выбирают когда:

• Данные структурированы и связаны (заказы → товары → пользователи)
• Нужна транзакционность — деньги нельзя списать дважды или потерять перевод на полпути
• Нужны сложные запросы: агрегации, оконные функции, полнотекстовый поиск
• Нужна расширяемость: PostGIS для геоданных, TimescaleDB для таймсерий — всё это расширения поверх PostgreSQL

Instagram, GitLab, Heroku, Twitch используют PostgreSQL. Бесплатный, надёжный, с активным развитием уже 30 лет. MySQL — ближайший конкурент, исторически чуть быстрее на простых SELECT, но PostgreSQL давно обогнал его по функциональности. SQLite — для локального хранилища в приложениях и тестов: вся база — один файл, никакого сервера.

Что будем делать

На сервере уже работает блог. Разворачиваем PostgreSQL рядом, не трогая существующие сервисы. Трафик будет идти двумя путями: с localhost (для миграций, cron-задач) и из подсети 192.168.1.0/24 (серверы приложения).

graph LR
    APP["Серверы приложения<br/>192.168.1.x"] -->|"порт 5432"| PG["PostgreSQL"]
    LOCAL["localhost<br/>:5432"] --> PG
    PG --> DB[("app_production")]
    DB --> USER["app_user"]

Шаг 1. Установка PostgreSQL

1apt update
2apt install -y postgresql postgresql-contrib

Проверяем статус и включаем автозапуск:

1systemctl status postgresql
2systemctl enable postgresql

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
     Active: active (running)

Шаг 2. Создание базы и пользователя

Создаём базу, пользователя, выдаём права — всё через psql -c:

1sudo -u postgres psql -c "CREATE DATABASE app_production;"
2sudo -u postgres psql -c "CREATE USER app_user WITH ENCRYPTED PASSWORD 'str0ngPassw0rd!';"
3sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE app_production TO app_user;"

CREATE DATABASE
CREATE ROLE
GRANT

Проверяем что база появилась:

1sudo -u postgres psql -c "\l"

                                  List of databases
      Name       |  Owner   |
-----------------+----------+
 app_production  | postgres |
 postgres        | postgres |
 template0       | postgres |
 template1       | postgres |

Шаг 3. Настройка подключений

PostgreSQL управляет доступом через два файла: postgresql.conf (основные параметры сервера) и pg_hba.conf (правила аутентификации — кто и откуда может подключиться).

Находим где они лежат:

1sudo -u postgres psql -c "SHOW config_file; SHOW hba_file;"

            config_file
------------------------------------
 /etc/postgresql/16/main/postgresql.conf

              hba_file
------------------------------------
 /etc/postgresql/16/main/pg_hba.conf

pg_hba.conf — правила доступа

Добавляем разрешения для localhost и подсети приложения:

 1# TYPE  DATABASE        USER            ADDRESS                 METHOD
 2
 3# Системный доступ (оставляем)
 4local   all             postgres                                peer
 5
 6# Localhost — по паролю
 7local   all             all                                     md5
 8host    all             all             127.0.0.1/32            md5
 9host    all             all             ::1/128                 md5
10
11# Подсеть серверов приложения
12host    app_production  app_user        192.168.1.0/24          md5

postgresql.conf — слушаем на всех интерфейсах

По умолчанию PostgreSQL слушает только localhost. Чтобы принимать подключения из подсети — нужно слушать на всех интерфейсах:

1sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" \
2  /etc/postgresql/16/main/postgresql.conf

Перезапускаем чтобы изменения вступили в силу:

1systemctl restart postgresql

Проверяем что слушает нужные адреса:

1ss -tlnp | grep 5432

LISTEN  0  244  0.0.0.0:5432  0.0.0.0:*  users:(("postgres",pid=12345,fd=7))
LISTEN  0  244     [::]:5432     [::]:*  users:(("postgres",pid=12345,fd=8))

Шаг 4. Параметры производительности

Дефолтные настройки PostgreSQL рассчитаны на минимальное железо — их обязательно нужно подстраивать под реальный сервер. Устанавливаем параметры для машины с 1 GB RAM:

1# Память
2shared_buffers = 256MB
3effective_cache_size = 768MB
4work_mem = 4MB
5maintenance_work_mem = 64MB
6
7# Подключения
8max_connections = 200

Применяем:

1systemctl restart postgresql

Проверяем что параметры применились:

1sudo -u postgres psql -c "SHOW shared_buffers; SHOW max_connections;"

 shared_buffers
----------------
 256MB

 max_connections
-----------------
 200

Шаг 5. Настройка логирования

Включаем логирование медленных запросов и DDL-операций в postgresql.conf:

1# Логировать запросы дольше 1 секунды
2log_min_duration_statement = 1000
3
4# Логировать все DDL (CREATE, ALTER, DROP)
5log_statement = 'ddl'

Перезапускаем PostgreSQL:

1systemctl restart postgresql

Проверяем что лог пишется:

1tail -f /var/log/postgresql/postgresql-16-main.log

Шаг 6. Настройка бэкапа

Создаём директорию:

1mkdir -p /opt/backups/postgresql

Создаём скрипт бэкапа:

 1#!/bin/bash
 2set -e
 3
 4DATE=$(date +%Y%m%d_%H%M%S)
 5BACKUP_DIR=/opt/backups/postgresql
 6DB=app_production
 7USER=postgres
 8
 9pg_dump -U $USER $DB | gzip > $BACKUP_DIR/${DB}_$DATE.sql.gz
10
11# Удаляем бэкапы старше 7 дней
12find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
13
14echo "Backup completed: ${DB}_$DATE.sql.gz"

Делаем исполняемым:

1chmod +x /opt/backup-postgres.sh

Добавляем в cron — запуск каждый день в 3:00:

1echo "0 3 * * * root /opt/backup-postgres.sh >> /var/log/pg-backup.log 2>&1" \
2  > /etc/cron.d/pg-backup

Шаг 7. Проверяем всё

Подключение с localhost

1psql -U app_user -h 127.0.0.1 -d app_production -c "SELECT current_database(), current_user, version();"

 current_database | current_user |         version
------------------+--------------+---------------------------
 app_production   | app_user     | PostgreSQL 16.x on x86_64

Подключение из подсети 192.168.1.0/24

С другого хоста в подсети:

1psql -U app_user -h 192.168.1.10 -d app_production -c "SELECT 1;"

 ?column?
----------
        1

Проверяем логирование

Выполняем запрос дольше 1 секунды чтобы он попал в лог:

1psql -U app_user -h 127.0.0.1 -d app_production -c "SELECT pg_sleep(2);"

Смотрим лог:

1grep "duration" /var/log/postgresql/postgresql-16-main.log | tail -3

2026-05-06 03:15:12 UTC [1234]: LOG:  duration: 2003.456 ms  statement: SELECT pg_sleep(2);

Запись появилась. Логирование работает.

Проверяем бэкап

1/opt/backup-postgres.sh
2ls -lh /opt/backups/postgresql/

Backup completed: app_production_20260506_031500.sql.gz
-rw-r--r-- 1 root root 2.1K May  6 03:15 app_production_20260506_031500.sql.gz

Убеждаемся что бэкап можно восстановить. Берём последний файл динамически, подключаемся через sudo -u postgres чтобы не упираться в аутентификацию:

1LATEST=$(ls -t /opt/backups/postgresql/*.sql.gz | head -1)
2sudo -u postgres createdb app_production_test
3gunzip -c "$LATEST" | sudo -u postgres psql app_production_test

SET
SET
SET
...

Чистим тестовую базу. Если висят открытые сессии — DROP упадёт с ошибкой, сначала разрываем их принудительно:

1sudo -u postgres psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'app_production_test';"
2sudo -u postgres psql -c "DROP DATABASE app_production_test;"

Как это всё работает — разбираем по частям

pg_hba.conf — таблица правил доступа

pg_hba.conf (Host-Based Authentication) проверяется сверху вниз при каждом подключении. Первое совпавшее правило применяется, остальные игнорируются.

host    app_production  app_user        192.168.1.0/24          md5

• host — TCP-подключение (в отличие от local — Unix-сокета)
• app_production — к какой базе
• app_user — под каким пользователем
• 192.168.1.0/24 — откуда (CIDR-нотация: весь диапазон 192.168.1.1–254)
• md5 — метод аутентификации

Методы аутентификации — почему именно md5:

trust — смертельно опасен на любом сетевом интерфейсе: любой кто дотянется до порта 5432 войдёт без пароля.

peer работает только для local (Unix-сокет). Именно поэтому sudo -u postgres psql работает без пароля — системный пользователь postgres = пользователь PostgreSQL postgres. Для TCP это не применимо.

md5 выбираем из соображений совместимости — его понимают все версии клиентских библиотек. На новых проектах лучше сразу ставить scram-sha-256: он не передаёт пароль в открытом виде даже в хешированном MD5-формате, защищает от атак повторного воспроизведения.

shared_buffers и effective_cache_size

shared_buffers = 256MB

Кеш страниц данных внутри PostgreSQL. Часто используемые блоки хранятся в памяти — при повторном чтении диск не нужен.

Почему именно 25% от RAM, а не больше: PostgreSQL работает поверх кеша операционной системы. Если отдать PostgreSQL слишком много — ОС не сможет нормально кешировать файлы сама, и общее число обращений к диску вырастет. Оптимальный баланс: PostgreSQL держит горячие данные в shared_buffers, ОС кеширует остальное в page cache.

На выделенном сервере только под PostgreSQL можно поднять до 40%, но это уже требует нагрузочного тестирования — универсальной формулы нет.

effective_cache_size = 768MB

Это не выделяемая память и не настройка кеша — это подсказка планировщику запросов. Планировщик оценивает стоимость операций: при маленьком effective_cache_size он думает что памяти мало, предпочитает sequential scan (перебрать всё подряд) вместо index scan (прыгать по индексу). При реалистичном значении — выбирает индексы там где это выгоднее.

Значение = shared_buffers + то что ОС может отдать под page cache. На 1 GB RAM: 256 MB уже в shared_buffers, остаток ~768 MB доступен ОС → effective_cache_size = 768MB.

work_mem и maintenance_work_mem

work_mem = 4MB

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

Почему нельзя просто поставить 64MB: это значение умножается. Один сложный запрос с тремя JOIN может создать три независимые операции сортировки. При 200 соединениях и каждый с таким запросом: 200 × 3 × 64 MB = 38 GB — сервер уйдёт в своп. 4 MB — консервативный старт. Если в логах появляются temporary file сообщения — поднимаем.

Найти запросы которые пишут временные файлы:

1SELECT query, temp_blks_written
2FROM pg_stat_statements
3WHERE temp_blks_written > 0
4ORDER BY temp_blks_written DESC;

maintenance_work_mem = 64MB

Память для VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE. Правило умножения здесь не работает — эти операции выполняются по одной за раз (autovacuum запускает несколько воркеров, но не десятки). Поэтому можно выделить значительно больше чем work_mem.

Практический эффект: CREATE INDEX на таблице в 10 GB при maintenance_work_mem = 64MB строит индекс за несколько минут, при 1GB — за секунды, потому что умещает всё дерево индекса в памяти и не трогает диск при сортировке.

log_statement — что именно логировать

log_statement = 'ddl'

У параметра четыре значения, от тихого к шумному:

none — подходит если логирование вообще не нужно или всё делается через log_min_duration_statement.

ddl выбираем для прода по конкретной причине: изменения схемы — самые опасные и необратимые операции. Удалённую колонку не вернёшь без бэкапа. Если завтра кто-то выполнит DROP TABLE — в логах будет точная запись: кто, когда, с какого хоста. SELECT и DML логировать постоянно не нужно — для медленных запросов есть log_min_duration_statement.

mod — добавляем когда нужен аудит изменений данных. Например, в финансовых системах важно знать кто удалил транзакцию. На нагруженной базе это существенный объём логов — взвешивайте.

all — только для краткосрочной отладки конкретной проблемы. На продакшн-нагрузке all может записывать гигабайты в час и сам по себе замедляет базу из-за I/O логов.

log_min_duration_statement — ловим медленные запросы

log_min_duration_statement = 1000

Логировать запросы которые выполнялись дольше указанного порога (в миллисекундах).

0 — то же что log_statement = 'all', только с таймингами. Генерирует огромный объём логов, практически не используется на нагруженном проде.

1000 — разумный порог для OLTP: запрос дольше секунды уже проблема. В логе будет сам запрос, время выполнения и параметры. Дальше берёшь запрос, идёшь в EXPLAIN ANALYZE и смотришь почему тормозит.

Для нового прода с неизвестной нагрузкой можно начать с 500 — поймаешь больше проблемных запросов при первом прогоне. Если логов слишком много — поднять до 1000 или 2000.

log_min_duration_statement и log_statement работают независимо. Итоговая конфигурация: DDL всегда в логах, медленные запросы — при превышении порога.

pg_dump — как работает бэкап

pg_dump делает логический дамп: читает данные через SQL и сохраняет их как последовательность CREATE TABLE, COPY и INSERT команд. Бэкап консистентный — snapshot в момент старта дампа, активные транзакции не блокируются.

1pg_dump -U postgres app_production | gzip > backup.sql.gz

gzip сжимает поток на лету — база в 100 MB сжимается до 10–20 MB.

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

1sudo -u postgres createdb app_production_restored
2gunzip -c backup.sql.gz | sudo -u postgres psql app_production_restored

Для больших баз лучше использовать pg_dump -Fc (custom format) — он сжимает сам и поддерживает параллельное восстановление через pg_restore -j N.

max_connections и почему не нужно ставить 1000

max_connections = 200

Каждое соединение в PostgreSQL — отдельный процесс операционной системы. Не поток, а именно процесс. 200 соединений = 200 процессов одновременно.

При слишком большом значении:

• Каждый процесс потребляет память (~5–10 MB на соединение в состоянии покоя)
• ОС тратит время на переключение контекста между процессами
• Блокировки и shared memory structures масштабируются плохо

Почему не 100: при деплое нескольких инстансов приложения с пулом по 20 соединений на инстанс + служебные подключения (мониторинг, миграции, cron) — 100 быстро заканчивается.

Почему не 1000: на машине с 1 GB RAM и work_mem = 4MB — при 1000 соединениях, каждое из которых делает сортировку, потребление только под work_mem будет 4 GB. Плюс shared_buffers плюс сам PostgreSQL плюс ОС — машина уйдёт в своп.

В реальных продакшн-системах перед PostgreSQL ставят PgBouncer — connection pooler. Приложение открывает сотни соединений к PgBouncer, а тот держит небольшой пул реальных соединений к PostgreSQL. Значительно снижает нагрузку при многопоточных приложениях.

graph LR
    A1["app instance #1<br/>50 conn"] --> PB["PgBouncer<br/>port 6432"]
    A2["app instance #2<br/>50 conn"] --> PB
    A3["app instance #3<br/>50 conn"] --> PB
    PB -->|"20 реальных conn"| PG["PostgreSQL<br/>port 5432"]