Что такое PostgreSQL server и его логические уровни

Иерархия такая: instance (database cluster) → database → schema → object. Официальная документация PostgreSQL прямо говорит: database cluster содержит одну или более баз данных, а клиентское соединение работает только с одной выбранной базой (PostgreSQL: Schemas).

Иерархия объектов PostgreSQL: от инстанса до таблицы

Roles и часть объектов - shared на весь cluster. Одно подключение работает только с одной базой. Это подтверждается документацией: каждый backend process обслуживает ровно один client connection (PostgreSQL: How Connections Are Established). Кросс-базовые запросы - только через внешние механизмы: postgres_fdw, dblink. Схемы внутри одной базы запрашиваются свободно:

SQL
SELECT * FROM analytics.reports r
JOIN public.orders o ON r.order_id = o.id;

Что лежит на диске

Логическая иерархия - это хорошо, но практика живёт на файловой системе.

Файловая система PostgreSQL: что лежит в PGDATA

PGDATA

Корневая директория кластера. Обычно /var/lib/postgresql/data. Физический layout документирован в разделе Database File Layout (PostgreSQL: Storage). Содержит:

  • base/ - данные всех баз данных, каждая в поддиректории по OID (из pg_database.oid)
  • global/ - общие кластерные каталоги (pg_database, pg_authid, pg_tablespace)
  • pg_wal/ - WAL-сегменты, обычно по 16 MB каждый
  • pg_stat/, pg_stat_tmp/ - статистика
  • postgresql.conf, pg_hba.conf, pg_ident.conf - конфигурация
  • postmaster.pid - PID-файл работающего инстанса

pg_wal

WAL (Write-Ahead Log) - backbone репликации и восстановления. Все изменения сначала пишутся в WAL, потом - в data files. Размер сегмента задаётся при инициализации кластера через initdb --wal-segsize (по умолчанию 16 MB). Подробнее - в WAL Internals (PostgreSQL: WAL Internals).

Ключевые параметры:

  • max_wal_size (default 1GB) - триггерит checkpoint
  • min_wal_size (default 80MB) - минимальный объём WAL для переработки
  • wal_keep_size - сколько WAL удерживать для репликации
  • wal_level - minimal, replica, или logical

Tablespace

Tablespace - cluster-wide описание альтернативного пути для файлов объектов БД. Можно вынести тяжёлые таблицы на отдельный volume:

SQL
CREATE TABLESPACE fast_ssd LOCATION '/mnt/nvme/pgdata';
ALTER TABLE big_reports SET TABLESPACE fast_ssd;
CREATE DATABASE analytics TABLESPACE fast_ssd;

Но tablespace - это не автономная мини-БД. Он остаётся частью того же кластера. Несколько баз могут использовать один tablespace - каждая получит свою поддиректорию по OID. Подробно - в документации по tablespaces (PostgreSQL: Tablespaces).

Дисковых квот на tablespace нет нативно. PostgreSQL прямо отсылает к квотам файловой системы и пользователю ОС, под которым запущен сервер. CREATE DATABASE ... TABLESPACE ... поддерживается официально (PostgreSQL: CREATE DATABASE).

Где заканчивается логическая изоляция

На уровне базы данных можно:

SQL
-- Ограничить подключения
ALTER DATABASE app_db CONNECTION LIMIT 50;

-- Задать default tablespace
ALTER DATABASE app_db SET TABLESPACE fast_ssd;

-- Переопределить параметры по умолчанию
ALTER DATABASE app_db SET work_mem = '64MB';

Подробнее - в ALTER DATABASE (PostgreSQL: ALTER DATABASE) и Connections and Authentication (PostgreSQL: Connection Settings).

Нельзя:

  • Дать одной logical database гарантированный CPU budget
  • Ограничить RAM consumption per database
  • Установить hard disk quota нативными средствами PostgreSQL

Жёсткие лимиты PostgreSQL

  • Размер таблицы: 32 TB
  • Колонок в таблице: 1 600
  • Баз данных на кластер: ~4.3 млрд (реально - сотни, дальше WAL - бутылочное горлышко)
  • Длина идентификатора: 63 байта
  • Полей в индексе: 32

Полный список - в Appendix K (PostgreSQL: Limits).

Зачем PgBouncer

PostgreSQL использует process-per-connection модель: каждый клиент получает отдельный backend process, а рост max_connections увеличивает потребление shared memory и других ресурсов. Точный расход памяти зависит от настроек и характера запросов - красивой универсальной цифры нет.

PgBouncer стоит между приложением и PostgreSQL, мультиплексируя клиентские подключения через небольшой пул реальных серверных соединений. Это ответ именно на архитектурную особенность PostgreSQL, а не "ещё одна штука в стеке". Подробнее - в документации PgBouncer (pgbouncer.org: Features, Usage).

Стек подключений: от приложения до PostgreSQL

Режимы пулинга

  • session - подключение возвращается в пул только когда клиент отключается. Ничего не ломается, но pooling минимальный.
  • transaction - подключение возвращается после COMMIT/ROLLBACK. Практический дефолт. Session state не гарантируется: SET-переменные, temp tables, advisory locks требуют осторожности.
  • statement - подключение возвращается после каждого запроса. Максимальный мультиплексинг, но нет транзакций.

transaction - практический дефолт для большинства workloads. Protocol-level prepared statements (которые используют драйверы JDBC) поддерживаются с PgBouncer 1.21 через max_prepared_statements, а в 1.24.0 включены по умолчанию. SQL-level PREPARE/EXECUTE/DEALLOCATE PgBouncer просто форвардит в PostgreSQL без обработки - это не одно и то же.

Пример конфигурации

Text
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction

max_client_conn = 2000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

server_lifetime = 3600
server_idle_timeout = 600

На стороне PostgreSQL max_connections должен вместить все серверные пулы PgBouncer + прямые подключения.

Что нового

  • v1.24.0 (2025-01): prepared statements включены по умолчанию (max_prepared_statements = 200), per-user pool_size, max_user_client_connections, rolling restart через SIGTERM
  • v1.25.0 (2025-11): LDAP-аутентификация, transaction_timeout, TLS 1.3 cipher suites

Зачем Patroni

Patroni - менеджер состояния primary/replica для PostgreSQL. Не "кластер сам по себе", а HA-оркестрация: хранит состояние в DCS, управляет failover, следит за lag.

Patroni: HA-оркестрация PostgreSQL кластера

Patroni делегирует консенсус DCS - etcd, Consul, ZooKeeper или Kubernetes API. DCS хранит лидерский ключ с TTL. Пока primary жив, он продлевает ключ. Упал - TTL истёк, реплики соревнуются за захват лидерства.

Как работает failover

Failover: что происходит при падении primary

Окно failover определяется настройками, а не является универсальной константой. Ключевые параметры из Dynamic Configuration Settings (Patroni docs):

  • ttl (default 30) - TTL лидерского ключа в DCS, в секундах
  • loop_wait (default 10) - интервал между проверками
  • retry_timeout (default 10) - таймаут на повторные попытки
  • primary_start_timeout (default 300) - сколько ждать перезапуска primary перед тем, как признать его мёртвым
  • Правило: loop_wait + 2 * retry_timeout <= ttl

Worst case для primary failure: loop_wait + primary_start_timeout + loop_wait. При дефолтном primary_start_timeout = 300 это значительно дольше, чем просто TTL. То есть время failover зависит от конкретных настроек и топологии, и универсального числового диапазона нет.

maximum_lag_on_failover ограничивает, насколько реплика может отставать, чтобы стать новым primary. Если все реплики отстали больше - failover не произойдёт.

Что нового в Patroni v4.x (2024-2025)

  • Quorum-based synchronous replication - failover-кандидат выбирается с максимальным LSN
  • sync_priority - ручной приоритет выбора синхронных реплик
  • Citus integration - регистрация Citus-реплик в pg_dist_node
  • demote-cluster и promote-cluster команды
  • LSN и lag info в REST API и patronictl list
  • DCS failsafe mode - переживает временную недоступность DCS без демоута primary

Конфигурация

YAML
scope: pg-cluster
name: node1

etcd:
  hosts: 10.0.0.1:2379,10.0.0.2:2379,10.0.0.3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true

postgresql:
  data_dir: /var/lib/postgresql/data
  parameters:
    max_connections: 100
    shared_buffers: 4GB
    wal_level: replica
    hot_standby: "on"

Альтернативы

Из альтернатив - repmgr (от EDB) и Stolon (Kubernetes-native). Patroni - один из самых распространённых open-source менеджеров HA для PostgreSQL, с поддержкой etcd, Consul, ZooKeeper и Kubernetes API.

Как это выглядит со стороны Java-приложения

До этого мы говорили про инфраструктуру PostgreSQL. Но инфраструктура не живёт сама по себе - есть клиент. В enterprise это чаще всего Java с JDBC-пулом (HikariCP). Вот как стек выглядит с его стороны.

Java-приложение и PgBouncer: два слоя пулинга

Зачем Java-пулу мало одного себя

У приложения свой pool - HikariCP, Tomcat JDBC, c3p0. Он нужен для управления потоками внутри JVM: поток берёт connection из пула, делает запрос, возвращает. Но это не отменяет PgBouncer. HikariCP мультиплексирует внутри одной JVM, а PostgreSQL плохо масштабируется по числу прямых соединений от всех JVM сразу. Десять инстансов приложения с maximumPoolSize = 20 = 200 прямых подключений к PostgreSQL. PgBouncer сжимает эти 200 до 25-30 реальных.

Где Java ломается с PgBouncer

Если PgBouncer в режиме transaction pooling, то всё, что привязано к session state, может вести себя не так, как ожидает приложение:

  • Session state в transaction pooling не гарантируется
  • Temp tables безопасны только в варианте ON COMMIT DROP
  • Session-level SET/RESET, PREPARE/DEALLOCATE и advisory locks требуют осторожности или session pooling

Что происходит при failover

Patroni переключает primary. С точки зрения Java-приложения это выглядит так:

  1. Текущие соединения обрываются - java.sql.SQLException: Connection reset или PSQLException: This connection has been closed
  2. HikariCP помечает эти connections как dead, создаёт новые
  3. Новые соединения идут через HAProxy/PgBouncer к новому primary
  4. В транзитном окне (пока TTL не истёк, пока promote не завершён) - таймауты и ошибки

Failover - это не "магия без боли", а нормальная часть жизненного цикла клиента. Приложение должно быть готово к обрывам и ретраям.

Критичные настройки

YAML
# HikariCP
maximumPoolSize: 20          # размер JVM-пула подбирается отдельно; суммарные server connections из PgBouncer должны помещаться в PostgreSQL max_connections
connectionTimeout: 3000       # ms, ждать connection из пула
validationTimeout: 1000       # ms, на проверку alive
maxLifetime: 300000           # 5 min, recycle connections до failover
keepaliveTime: 60000          # 1 min, проверять idle connections
connectionTestQuery: "SELECT 1"
Text
# JDBC URL параметры
socketTimeout=10              # seconds, read timeout
connectTimeout=5              # seconds, connection attempt
tcpKeepAlive=true
loadBalanceHosts=true         # если несколько хостов
targetServerType=primary      # JDBC driver умеет фильтровать по роли

targetServerType=primary в JDBC URL - полезная фича: драйвер сам определяет, кто primary, и подключается к нему. Но это не заменяет HAProxy, а работает как fallback.

Практический вывод

  • Если нужна логическая изоляция сервисов - отдельные databases достаточно
  • Если нужны гарантированные ресурсы и разные SLA - отдельные instances
  • PgBouncer решает проблему process-per-connection, transaction mode - практический дефолт
  • Patroni управляет HA через DCS, окно failover настраивается через ttl, loop_wait, retry_timeout
  • Связка App → PgBouncer → HAProxy → Patroni-managed PostgreSQL покрывает большинство продакшен-сценариев
Bash
# Проверяем состояние кластера
patronictl -c patroni.yml list

# Ручной switchover (плановый)
patronictl -c patroni.yml switchover
SQL
-- Проверяем репликацию
SELECT client_addr, state, sent_lsn, replay_lsn,
       replay_lag
FROM pg_stat_replication;