Справочник по PostgreSQL

Play ITЗагрузка интерактивного демо…

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Справочник по PostgreSQL

1. Конфигурационные параметры (postgresql.conf)

PostgreSQL управляет поведением сервера через параметры, задаваемые в файле postgresql.conf или через SQL-команду SET. Параметры разделены на категории:

1.1. Общие параметры

Параметр	Описание	Возможные значения	По умолчанию
`cluster_name`	Имя кластера для мониторинга	строка	''
`data_directory`	Каталог с данными кластера	абсолютный путь	$PGDATA
`config_file`	Путь к основному конфигурационному файлу	абсолютный путь	$PGDATA/postgresql.conf
`hba_file`	Файл управления доступом клиентов	абсолютный путь	$PGDATA/pg_hba.conf
`ident_file`	Файл сопоставления пользователей ОС и PostgreSQL	абсолютный путь	$PGDATA/pg_ident.conf

1.2. Память

Параметр	Описание	Возможные значения	По умолчанию
`shared_buffers`	Объём разделяемой памяти для кэширования страниц	128kB–8GB	128MB
`work_mem`	Память на одну операцию сортировки/хэширования	64kB–2GB	4MB
`maintenance_work_mem`	Память для операций обслуживания (VACUUM, CREATE INDEX)	1MB–2GB	64MB
`effective_cache_size`	Оценка размера дискового кэша ОС	128kB–8TB	4GB
`huge_pages`	Использование huge pages	on, off, try	try

1.3. WAL (Write-Ahead Logging)

Параметр	Описание	Возможные значения	По умолчанию
`wal_level`	Уровень детализации WAL	minimal, replica, logical	replica
`max_wal_size`	Максимальный объём WAL перед контрольной точкой	2MB–INT_MAX	1GB
`min_wal_size`	Минимальный объём WAL, сохраняемый между контрольными точками	2MB–INT_MAX	80MB
`checkpoint_timeout`	Интервал между контрольными точками	30s–1d	5min
`checkpoint_completion_target`	Доля времени до следующей контрольной точки, отводимая на завершение текущей	0.0–1.0	0.9
`wal_keep_size`	Объём WAL, сохраняемый для реплик	0–INT_MAX	0
`archive_mode`	Включение архивации WAL	on, off, always	off
`archive_command`	Команда для архивации WAL-файлов	команда ОС	''

1.4. Репликация

Параметр	Описание	Возможные значения	По умолчанию
`max_replication_slots`	Максимальное число слотов репликации	0–INT_MAX	10
`max_wal_senders`	Максимальное число процессов отправки WAL	0–INT_MAX	10
`hot_standby`	Разрешение чтения на standby-сервере	on, off	on
`primary_conninfo`	Строка подключения к первичному серверу (в standby)	строка подключения	''
`recovery_target_timeline`	Целевая временная линия при восстановлении	'current', 'latest', номер	'latest'

1.5. Параллелизм и фоновые процессы

Параметр	Описание	Возможные значения	По умолчанию
`max_connections`	Максимальное число одновременных подключений	1–INT_MAX	100
`superuser_reserved_connections`	Подключения, зарезервированные для суперпользователей	0–max_connections-1	3
`max_worker_processes`	Максимальное число фоновых рабочих процессов	0–INT_MAX	8
`max_parallel_workers_per_gather`	Максимум параллельных рабочих на один запрос	0–max_parallel_workers	2
`max_parallel_workers`	Общее число параллельных рабочих	0–max_worker_processes	8

1.6. Журналирование

Параметр	Описание	Возможные значения	По умолчанию
`log_destination`	Назначение логов	stderr, csvlog, syslog, eventlog	stderr
`logging_collector`	Включение сборщика логов	on, off	off
`log_filename`	Шаблон имени файла лога	строка	postgresql-%Y-%m-%d_%H%M%S.log
`log_statement`	Уровень логирования SQL-запросов	none, ddl, mod, all	none
`log_min_duration_statement`	Минимальная длительность запроса для логирования	-1 (откл), 0–INT_MAX	-1
`log_line_prefix`	Префикс каждой строки лога	строка с переменными	'%t [%p]: '

1.7. Безопасность и аутентификация

Параметр	Описание	Возможные значения	По умолчанию
`password_encryption`	Алгоритм хэширования паролей	md5, scram-sha-256	scram-sha-256
`ssl`	Включение SSL	on, off	off
`ssl_cert_file`	Путь к сертификату	абсолютный путь	server.crt
`ssl_key_file`	Путь к приватному ключу	абсолютный путь	server.key
`authentication_timeout`	Таймаут ожидания аутентификации	1s–600s	1min

2. Системные представления (Система Catalog Views)

PostgreSQL предоставляет множество системных представлений в схеме pg_catalog и information_schema.

2.1. Основные каталоги

Представление	Описание
`pg_database`	Информация о всех базах данных
`pg_namespace`	Схемы (namespaces)
`pg_class`	Таблицы, индексы, последовательности, представления
`pg_attribute`	Столбцы таблиц и представлений
`pg_index`	Информация об индексах
`pg_constraint`	Ограничения (PRIMARY KEY, FOREIGN KEY, CHECK)
`pg_proc`	Процедуры и функции
`pg_type`	Типы данных
`pg_extension`	Установленные расширения
`pg_settings`	Текущие значения всех параметров конфигурации

2.2. Статистика и мониторинг

Представление	Описание
`pg_stat_database`	Статистика по базам данных
`pg_stat_user_tables`	Статистика по пользовательским таблицам
`pg_stat_user_indexes`	Статистика по индексам
`pg_stat_bgwriter`	Статистика фонового записывающего процесса
`pg_stat_replication`	Состояние репликации
`pg_stat_activity`	Текущие активные сессии
`pg_locks`	Информация о блокировках
`pg_prepared_xacts`	Подготовленные транзакции (двухфазный коммит)

2.3. WAL и репликация

Представление	Описание
`pg_stat_wal`	Статистика по WAL
`pg_replication_slots`	Информация о слотах репликации
`pg_stat_subscription`	Статус подписок логической репликации
`pg_stat_publication`	Статус публикаций

3. Типы данных

3.1. Числовые

Тип	Диапазон	Занимаемое место	Примечание
`smallint`	-32768 – 32767	2 байта	16-битное целое
`integer`	-2147483648 – 2147483647	4 байта	32-битное целое
`bigint`	-9223372036854775808 – 9223372036854775807	8 байт	64-битное целое
`numeric(p,s)`	до 131072 цифр до точки, до 16383 после	переменное	Точный десятичный
`real`	6 десятичных знаков	4 байта	IEEE 754 float
`double precision`	15 десятичных знаков	8 байт	IEEE 754 double

3.2. Строковые

Тип	Описание
`text`	Неограниченная строка
`varchar(n)`	Строка до n символов
`char(n)`	Строка фиксированной длины n, дополняется пробелами

3.3. Временные

Тип	Описание
`timestamp`	Дата и время без часового пояса
`timestamptz`	Дата и время с часовым поясом
`date`	Только дата
`time`	Только время
`timetz`	Время с часовым поясом
`interval`	Промежуток времени

3.4. Логические

Тип	Значения
`boolean`	true, false, null

3.5. Специальные

Тип	Описание
`bytea`	Бинарные данные
`json`	JSON-данные (текстовое хранилище)
`jsonb`	JSON-данные (бинарное, индексируемое)
`uuid`	Универсальный уникальный идентификатор
`inet`	IPv4/IPv6 адрес
`cidr`	CIDR-нотация сети
`macaddr`	MAC-адрес
`tsvector`	Вектор текста для полнотекстового поиска
`tsquery`	Запрос для полнотекстового поиска
`array`	Массив любого типа, например `integer[]`
`range`	Диапазон значений (например, `int4range`, `tsrange`)

4. Встроенные функции

Общая база: именованный вызов с аргументами и возвратом в коде — функции в коде. В SQL выражения COUNT(), SUM(), OVER — тот же приём на уровне запроса, другой синтаксис.

4.1. Строковые функции

Функция	Описание
`length(text)`	Длина строки
`upper(text)`, `lower(text)`	Преобразование регистра
`trim(text)`, `ltrim(text)`, `rtrim(text)`	Удаление пробелов
`substring(text from pattern)`	Извлечение подстроки
`replace(text, from, to)`	Замена подстроки
`concat(str1, str2, ...)`	Конкатенация строк
`split_part(text, delimiter, field)`	Разделение строки и выбор части
`regexp_replace(text, pattern, replacement)`	Замена по регулярному выражению

4.2. Числовые функции

Функция	Описание
`abs(x)`	Абсолютное значение
`ceil(x)`, `floor(x)`	Округление вверх/вниз
`round(x, d)`	Округление до d знаков
`random()`	Случайное число от 0 до 1
`mod(dividend, divisor)`	Остаток от деления
`power(base, exponent)`	Возведение в степень
`sqrt(x)`	Квадратный корень

4.3. Функции даты и времени

Функция	Описание
`now()`	Текущая дата и время с часовым поясом
`current_timestamp`	То же, что `now()`
`clock_timestamp()`	Точное время выполнения (меняется внутри запроса)
`date_part(field, timestamp)`	Извлечение части (год, месяц и т.д.)
`age(timestamp)`	Разница между now() и timestamp
`extract(field from timestamp)`	Альтернатива `date_part`
`to_char(timestamp, format)`	Форматирование даты в строку
`to_timestamp(text, format)`	Преобразование строки в timestamp

4.4. Агрегатные функции

Функция	Описание
`count(*)`	Число строк
`sum(column)`	Сумма значений
`avg(column)`	Среднее значение
`min(column)`, `max(column)`	Минимум и максимум
`array_agg(column)`	Агрегация в массив
`string_agg(column, delimiter)`	Конкатенация строк с разделителем
`json_agg(row)`, `jsonb_agg(row)`	Агрегация в JSON

4.5. Функции JSON

Функция	Описание
`jsonb_build_object(key, value, ...)`	Создание объекта JSONB
`jsonb_array_elements(jsonb)`	Расщепление массива на строки
`jsonb_extract_path(jsonb, VARIADIC path)`	Извлечение по пути
`jsonb_set(target, path, new_value)`	Изменение значения по пути
`jsonb_pretty(jsonb)`	Красивое форматирование JSONB

4.6. Функции полнотекстового поиска

Функция	Описание
`to_tsvector(config, document)`	Преобразование текста в tsvector
`to_tsquery(config, query)`	Преобразование запроса в tsquery
`plainto_tsquery(config, query)`	Простой запрос без операторов
`phraseto_tsquery(config, phrase)`	Фразовый запрос
`ts_rank(tsvector, tsquery)`	Ранжирование совпадений

Базис: Операторы — оператор, операнд и приоритет в коде.

5. Операторы

5.1. Логические

Оператор	Описание
`AND`, `OR`, `NOT`	Логические операции
`=`, `<>`, `<`, `<=`, `>`, `>=`	Сравнение
`BETWEEN a AND b`	В диапазоне
`IN (val1, val2, ...)`	Совпадение со списком
`LIKE`, `ILIKE`	Сравнение с шаблоном (регистрозависимо/независимо)
`~`, `~`, `!~`, `!~`	Регулярные выражения (чувствительные/нечувствительные к регистру)

5.2. JSON

Оператор	Описание
`->`	Извлечение элемента по ключу (возвращает jsonb)
`->>`	Извлечение как текст
`#>`	Извлечение по массиву путей
`#>>`	Извлечение по массиву путей как текст
`@>`	Левый операнд содержит правый
`<@`	Левый операнд содержится в правом
`?`	Проверка наличия ключа
`?	`
`?&`	Проверка наличия всех ключей

5.3. Массивы

Оператор	Описание
`=`, `<>`	Сравнение массивов
`@>`, `<@`	Содержание/содержится
`&&`	Пересечение массивов
`array[1,2,3]`	Литерал массива
`ANY(array)`	Сравнение с любым элементом массива

6. Утилиты командной строки

PostgreSQL поставляется с набором утилит для администрирования, резервного копирования, миграции и диагностики.

6.1. Основные утилиты

Утилита	Назначение
`psql`	Интерактивный клиент SQL, поддерживает мета-команды (`\l`, `\d`, `\du`, `\dt`, `\x`, `\timing`)
`pg_dump`	Логический дамп одной базы данных в текстовом или кастомном формате
`pg_dumpall`	Дамп всех баз данных, включая глобальные объекты (роли, табличные пространства)
`pg_restore`	Восстановление из кастомного/тар-архива, созданного `pg_dump -Fc`
`pg_basebackup`	Физическое резервное копирование всего кластера данных
`pg_ctl`	Управление сервером: запуск, остановка, перезагрузка, проверка статуса
`initdb`	Инициализация нового кластера данных
`createdb` / `dropdb`	Создание и удаление базы данных
`createuser` / `dropuser`	Создание и удаление роли (устаревшие, лучше использовать SQL)
`vacuumdb`	Запуск `VACUUM` из командной строки
`reindexdb`	Перестроение индексов
`clusterdb`	Кластеризация таблиц по индексу
`pg_isready`	Проверка доступности сервера

Типичные вызовы:

pg_ctl status -D /var/lib/postgresql/16/main
pg_isready -h localhost -p 5432
pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P
vacuumdb -U postgres --analyze app_db

6.2. Расширенные утилиты

Утилита	Назначение
`pg_receivewal`	Приём WAL-файлов от основного сервера (для архивации или репликации)
`pg_receivexlog`	Устаревшее имя для `pg_receivewal` (до версии 10)
`pg_archivecleanup`	Удаление старых WAL-файлов, не нужных для реплик или PITR
`pg_checksums`	Включение/отключение контрольных сумм на уровне страниц данных
`pg_verifybackup`	Проверка целостности резервной копии (начиная с v13)
`pg_waldump`	Декодирование содержимого WAL-файлов в читаемый вид
`pg_config`	Отображение путей и параметров сборки PostgreSQL

7. Административные команды SQL

7.1. Управление базами данных и ролями

-- Создание базы данных
CREATE DATABASE app_db OWNER app_user;

-- Создание роли с правами входа
CREATE ROLE app_user WITH LOGIN PASSWORD 'strongpass';

-- Назначение привилегий
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Отзыв привилегий
REVOKE ALL PRIVILEGES ON DATABASE app_db FROM app_user;

7.2. Обслуживание

-- Анализ статистики (для оптимизатора)
ANALYZE;

-- Полная очистка и анализ
VACUUM ANALYZE;

-- Принудительная очистка (может блокировать таблицу)
VACUUM FULL table_name;

-- Перестроение индекса
REINDEX TABLE table_name;
REINDEX INDEX index_name;

-- Кластеризация по индексу (физическая сортировка строк)
CLUSTER table_name USING index_name;

PostgreSQL 16. Ручной VACUUM может обрабатывать таблицы по списку схем, отдельно TOAST-таблицы и управлять этапом обновления статистики. Объём буфера очистки задаётся параметром vacuum_buffer_usage_limit или опцией VACUUM (BUFFER_USAGE_LIMIT …). Параметр vacuum_defer_cleanup_age удалён. Заморозка transaction ID выполняется на уровне страницы, а не построчно.

7.3. Управление расширениями

-- Установка расширения
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "postgis";

-- Список установленных расширений
SELECT * FROM pg_extension;

7.4. Управление репликацией

-- Создание слота репликации
SELECT pg_create_physical_replication_slot('standby_slot');

-- Просмотр слотов
SELECT * FROM pg_replication_slots;

-- Отключение слота
SELECT pg_drop_replication_slot('standby_slot');

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

8.1. Логическое резервное копирование

# Дамп одной БД в SQL
pg_dump -U user -h localhost app_db > app_db.sql

# Дамп в кастомный формат (поддерживает параллелизм и выборочное восстановление)
pg_dump -Fc -U user -h localhost app_db -f app_db.dump

# Восстановление
psql -U user -d new_db < app_db.sql
pg_restore -U user -d new_db app_db.dump

8.2. Физическое резервное копирование

# Горячее резервное копирование
pg_basebackup -h primary_host -U replicator -D /backup/pgdata -P -v -R

# Архивация WAL (в postgresql.conf)
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

8.3. Восстановление на момент времени (PITR)

Остановить сервер.
Заменить каталог данных на резервную копию.
Создать файл recovery.signal.
Настроить postgresql.auto.conf:

restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-01-25 14:30:00'

Запустить сервер — он восстановится до указанного момента.

9. Безопасность

9.1. Файл `pg_hba.conf`

Определяет правила аутентификации клиентов. Формат строки:

local   DATABASE  USER  METHOD  [OPTIONS]
host    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Примеры:

# Локальный суперпользователь — без пароля через peer
local   all   postgres   peer

# Локальные пользователи — scram-sha-256
local   all   all        scram-sha-256

# Удалённый доступ только с определённого IP
host    app_db  app_user  192.168.1.100/32  scram-sha-256

# Доступ с любого IP (осторожно!)
host    all     all       0.0.0.0/0         md5

После изменения требуется перезагрузка или pg_ctl reload.

9.2. Шифрование

Поддержка SSL/TLS в postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Расширение pgcrypto предоставляет функции шифрования (pgp_sym_encrypt, digest, gen_salt).
TDE (Transparent Data Encryption) не встроен, но реализуется через расширения (например, pg_tde в разработке).

10. Расширения (Extensions)

PostgreSQL легко расширяется. Популярные расширения:

Расширение	Назначение
`uuid-ossp`	Генерация UUID
`pgcrypto`	Криптографические функции
`postgis`	Поддержка геоданных и пространственных запросов
`hstore`	Хранение пар "ключ-значение"
`ltree`	Деревья и иерархические пути
`citext`	Регистронезависимые строки
`pg_stat_statements`	Сбор статистики по выполненным запросам
`auto_explain`	Автоматический EXPLAIN для медленных запросов
`pg_partman`	Управление партициями
`timescaledb`	Масштабирование временных рядов

Активация:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

Настройка в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

11. Производительность и мониторинг

11.1. EXPLAIN и EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

Вывод показывает план выполнения, реальное время, использование буферов.

11.2. Ключевые метрики

seq_scan vs idx_scan — соотношение полных сканирований и индексных.
n_tup_ins, n_tup_upd, n_tup_del — активность по таблице.
n_tup_newpage_upd (с PG16) — оценка эффективности HOT-обновлений.
blks_read vs blks_hit — эффективность кэша (blks_hit / (blks_read + blks_hit)).
wait_event в pg_stat_activity — причины ожидания (I/O, блокировки).

Ввод-вывод (PostgreSQL 16). Представление pg_stat_io даёт детальную статистику I/O по backend, autovacuum и checkpointer — объём и время операций. Раньше pg_stat_bgwriter не различал клиентские процессы и autovacuum.

SELECT backend_type, object, context,
       reads, read_time, writes, write_time
FROM pg_stat_io
ORDER BY read_time DESC NULLS LAST
LIMIT 20;

В pg_stat_statements (с PG16) нормализуются тексты DDL — меньше дубликатов планов. В pg_stat_all_tables / pg_stat_all_indexes отслеживается время последнего сканирования.

11.3. Инструменты мониторинга

Встроенные: pg_stat_statements, auto_explain.
Внешние — pgBadger (анализ логов), Prometheus + postgres_exporter, Zabbix — практикум, Datadog.

11.4. Новинки PostgreSQL 16 (краткий обзор)

Полный список — release notes. Ниже — то, что чаще всего встречается в эксплуатации и разработке.

Область	Изменение
SQL	Необязательные алиасы подзапросов в `FROM`; автоимена extended statistics; `REINDEX` без имени БД; стратегия TOAST в `CREATE TABLE`; литералы `0x` / `0b` / `0o` с `_`; `COPY FROM` → значение столбца по умолчанию
JSON	`json_array`, `json_object`, `json_agg`; предикаты `IS JSON`*, `IS JSON ARRAY`, `IS JSON OBJECT`, `IS JSON SCALAR` (функция `json_table` — в планах стандарта)
Логическая репликация	С физической реплики; двунаправленная (не полноценный multimaster); репликация при неуникальном индексе, если нет UNIQUE; бинарная initial sync; роль `pg_create_subscription`; apply от владельца таблицы
Vacuum	Заморозка на уровне страницы; удалён `vacuum_defer_cleanup_age`; `vacuum_buffer_usage_limit`; гибкий ручной `VACUUM` (см. §7.2)
Мониторинг	`pg_stat_io`, `n_tup_newpage_upd`, нормализация DDL в `pg_stat_statements` (см. §11.2)
Прочее	`random_normal`, `array_shuffle` / `array_sample`; `pg_input_is_valid`; опция generic plan в `EXPLAIN`

Установка и первый psql — Первые шаги с SQL; обзор для разработчика — PostgreSQL — практическая работа и API.

12. Продвинутые возможности

12.1. Партиционирование

Поддержка декларативного партиционирования (с v10):

CREATE TABLE measurements (
    id serial,
    logdate date not null,
    value numeric
) PARTITION BY RANGE (logdate);

CREATE TABLE measurements_y2025m01
    PARTITION OF measurements
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

12.2. Логическая репликация

Позволяет реплицировать отдельные таблицы:

-- На издателе
CREATE PUBLICATION sales_pub FOR TABLE sales;

-- На подписчике
CREATE SUBSCRIPTION sales_sub
    CONNECTION 'host=publisher dbname=app_db user=replicator'
    PUBLICATION sales_pub;

12.3. Full-Text Search

-- Создание tsvector-колонки
ALTER TABLE articles ADD COLUMN body_ts tsvector;
UPDATE articles SET body_ts = to_tsvector('russian', body);

-- Поиск
SELECT title FROM articles
WHERE body_ts @@ to_tsquery('russian', 'база & данных');

-- Индекс для ускорения
CREATE INDEX idx_fts_body ON articles USING GIN(body_ts);

12.4. JSON/JSONB

-- Поиск по ключу
SELECT Данные->>'name' FROM products WHERE Данные @> '{"category": "electronics"}';

-- Обновление
UPDATE products
SET data = jsonb_set(Данные, '{price}', '199.99')
WHERE id = 1;

12.5. Foreign Data Wrapper (FDW)

FDW (стандарт SQL/MED) позволяет обращаться к внешним таблицам как к локальным. Сторонняя таблица не хранит строки в demo — запросы перенаправляются обёрткой (wrapper) к Oracle, MySQL, другому PostgreSQL или файлу.

Типовая последовательность:

-- 1. Обёртка (в дистрибутиве: postgres_fdw, file_fdw)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2. Сервер-источник
CREATE SERVER remote_pg
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'db2.example.com', dbname 'app_db', port '5432');

-- 3. Сопоставление учётных записей
CREATE USER MAPPING FOR app_user
  SERVER remote_pg
  OPTIONS (user 'remote_user', password 'secret');

-- 4. Сторонняя таблица (или IMPORT FOREIGN SCHEMA)
CREATE FOREIGN TABLE remote_orders (
  id bigint,
  amount numeric
) SERVER remote_pg
  OPTIONS (schema_name 'public', table_name 'orders');

Обёртка	Назначение
postgres_fdw	Удалённый PostgreSQL
file_fdw	Файлы CSV/текст на сервере
oracle_fdw, mysql_fdw, tds_fdw	Сторонние СУБД (устанавливаются отдельно, см. pgxn.org/tag/fdw)

Расширение dblink (в составе contrib) — альтернатива с явным управлением соединениями и асинхронными запросами между кластерами PostgreSQL.

Секции и шарды

Секции на одном сервере и шарды на разных — разные уровни масштабирования. Секция из foreign table не даёт распределённой транзакции между узлами; для промышленного шардирования смотрите Citus, Shardman и архитектуру в обзоре DBA.

13. Типовые конфигурации для разных сценариев

13.1. Разработка (локальная машина)

Цель — минимальные ресурсы, быстрый старт, удобство отладки.

shared_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 128MB
max_connections = 20
fsync = off                 # только для разработки!
synchronous_commit = off    # только для разработки!
log_statement = 'all'
log_min_duration_statement = 0

Важно: fsync = off и synchronous_commit = off ускоряют операции, но делают систему уязвимой к потере данных при сбое. Никогда не используйте их в production.

13.2. Малый production-сервер (2–4 ядра, 8–16 ГБ RAM)

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 64MB
maintenance_work_mem = 1GB
max_connections = 100
random_page_cost = 1.1     # для SSD
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
log_min_duration_statement = 1000  # логировать запросы >1 сек

13.3. Высоконагруженный OLTP-сервер (16+ ядер, 64+ ГБ RAM)

shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 256MB
maintenance_work_mem = 4GB
max_connections = 300
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
random_page_cost = 1.0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
wal_buffers = 64MB
commit_delay = 10
commit_siblings = 5

13.4. Аналитический сервер (OLAP, тяжёлые агрегаты)

work_mem = 1GB              # много памяти на сортировку
shared_buffers = 8GB
effective_cache_size = 48GB
max_parallel_workers_per_gather = 8
jit = on                    # JIT-компиляция запросов
from_collapse_limit = 12
join_collapse_limit = 12

13.5. PostgreSQL и 1С

1С:Предприятие официально поддерживает PostgreSQL как СУБД для клиент–серверного режима — см. работу с БД в 1С. Для production нужна сборка с патчами 1С (дистрибутив на releases.1c.ru) или Postgres Pro Standard / Enterprise, где патчи уже включены. "Vanilla" PostgreSQL с PGDG подходит для обучения SQL, но не для сертифицированной связки с 1С.

Ориентир по железу (выделенный сервер, ~100 ГБ БД, до ~50 пользователей, до ~2000 документов/день) — 8 ядер, 8 ГБ ОЗУ, SSD RAID1.

Пример стартовой конфигурации (8 ГБ RAM, Postgres Pro с модулями 1С):

# Специфично для связки 1С + Postgres Pro
standard_conforming_strings = off
escape_string_warning = off
shared_preload_libraries = 'online_analyze, plantuner'
plantuner.fix_empty_table = on
online_analyze.enable = on
online_analyze.table_type = 'temporary'
online_analyze.local_tracking = on

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 64MB
maintenance_work_mem = 256MB
temp_buffers = 32MB
max_locks_per_transaction = 256

listen_addresses = '*'

В pg_hba.conf (в начало файла) — доступ с сервера 1С:

host  all  all  192.168.1.50/32  scram-sha-256

После изменения shared_preload_libraries и shared_buffers — перезапуск службы PostgreSQL. Остальное — SELECT pg_reload_conf(); и проверка SHOW.

В мастере создания информационной базы 1С укажите тип СУБД PostgreSQL, хост, порт, имя базы и учётную запись (для быстрого старта часто используют роль postgres с паролем; в production выделите отдельную роль и минимальные права).

Не для учебного vanilla PostgreSQL

Параметры plantuner и online_analyze относятся к сборкам с патчами 1С / Postgres Pro. На обычном PostgreSQL 16 из PGDG их не включайте — достаточно базовой настройки памяти и autovacuum из §13.2.

14. Диагностика и решение типовых проблем

14.1. Запросы выполняются медленно

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

   EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Убедитесь, что используются индексы:

   SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Проверьте актуальность статистики:

   ANALYZE table_name;

Увеличьте work_mem, если есть много сортировок на диске (Disk: ...kB в EXPLAIN).

14.2. Блокировки и зависшие транзакции

-- Текущие блокировки
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Завершение зависшей транзакции:

SELECT pg_terminate_backend(pid);

14.3. Рост размера таблицы без удаления строк

PostgreSQL не освобождает место сразу после DELETE. Используйте:

VACUUM — помечает место как свободное.
VACUUM FULL — перезаписывает таблицу, освобождая место на диске (блокирует таблицу).
pg_repack — внешний инструмент для онлайн-дефрагментации без блокировки.

14.4. WAL-файлы не удаляются

Причина: активный слот репликации или отключённая архивация.

Проверка:

SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

Решение:

Удалите неиспользуемый слот

SELECT pg_drop_replication_slot('name');

Настройте archive_cleanup_command или pg_archivecleanup.

15. Обновление и миграция

15.1. Обновление in-place (major version)

Используйте pg_upgrade:

# Остановите старый кластер
pg_ctl -D /old/Данные stop

# Инициализируйте новый кластер
initdb -D /new/Данные

# Запустите pg_upgrade
pg_upgrade \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/old/Данные \
  --new-datadir=/new/Данные \
  --link  # использует hard links для экономии места и времени

После успешного обновления запустите новый кластер и скрипт анализа:

./analyze_new_cluster.sh

15.2. Логическая миграция

Подходит при переходе между разными ОС или архитектурами:

pg_dumpall -h old_host -U user -f full_backup.sql
psql -h new_host -U user -f full_backup.sql

15.3. Минимизация downtime

Настройте логическую репликацию на новую версию.
Переключите приложение на новую базу.
Отключите подписку.

Справочник по PostgreSQL​

1. Конфигурационные параметры (postgresql.conf)​

1.1. Общие параметры​

1.2. Память​

1.3. WAL (Write-Ahead Logging)​

1.4. Репликация​

1.5. Параллелизм и фоновые процессы​

1.6. Журналирование​

1.7. Безопасность и аутентификация​

2. Системные представления (Система Catalog Views)​

2.1. Основные каталоги​

2.2. Статистика и мониторинг​

2.3. WAL и репликация​

3. Типы данных​

3.1. Числовые​

3.2. Строковые​

3.3. Временные​

3.4. Логические​

3.5. Специальные​

4. Встроенные функции​

4.1. Строковые функции​

4.2. Числовые функции​

4.3. Функции даты и времени​

4.4. Агрегатные функции​

4.5. Функции JSON​

4.6. Функции полнотекстового поиска​

5. Операторы​

5.1. Логические​

5.2. JSON​

5.3. Массивы​

6. Утилиты командной строки​

6.1. Основные утилиты​

6.2. Расширенные утилиты​

7. Административные команды SQL​

7.1. Управление базами данных и ролями​

7.2. Обслуживание​

7.3. Управление расширениями​

7.4. Управление репликацией​

8. Резервное копирование и восстановление​

8.1. Логическое резервное копирование​

8.2. Физическое резервное копирование​

8.3. Восстановление на момент времени (PITR)​

9. Безопасность​

9.1. Файл pg_hba.conf​

9.2. Шифрование​

10. Расширения (Extensions)​

11. Производительность и мониторинг​

11.1. EXPLAIN и EXPLAIN ANALYZE​

11.2. Ключевые метрики​

11.3. Инструменты мониторинга​

11.4. Новинки PostgreSQL 16 (краткий обзор)​

12. Продвинутые возможности​

12.1. Партиционирование​

12.2. Логическая репликация​

12.3. Full-Text Search​

12.4. JSON/JSONB​

12.5. Foreign Data Wrapper (FDW)​

13. Типовые конфигурации для разных сценариев​

13.1. Разработка (локальная машина)​

13.2. Малый production-сервер (2–4 ядра, 8–16 ГБ RAM)​

13.3. Высоконагруженный OLTP-сервер (16+ ядер, 64+ ГБ RAM)​

13.4. Аналитический сервер (OLAP, тяжёлые агрегаты)​

13.5. PostgreSQL и 1С​

14. Диагностика и решение типовых проблем​

14.1. Запросы выполняются медленно​

14.2. Блокировки и зависшие транзакции​

14.3. Рост размера таблицы без удаления строк​

14.4. WAL-файлы не удаляются​

15. Обновление и миграция​

15.1. Обновление in-place (major version)​

15.2. Логическая миграция​

15.3. Минимизация downtime​

См. также

Управление реляционными СУБД

Справочник по MySQL

Справочник по Microsoft SQL Server

OraСправочник по Oracle DB

Администрирование БД в облаке

📄️Управление реляционными СУБД — итоги

📄️Управление реляционными СУБД — чек-лист

Справочник по PostgreSQL

1. Конфигурационные параметры (postgresql.conf)

1.1. Общие параметры

1.2. Память

1.3. WAL (Write-Ahead Logging)

1.4. Репликация

1.5. Параллелизм и фоновые процессы

1.6. Журналирование

1.7. Безопасность и аутентификация

2. Системные представления (Система Catalog Views)

2.1. Основные каталоги

2.2. Статистика и мониторинг

2.3. WAL и репликация

3. Типы данных

3.1. Числовые

3.2. Строковые

3.3. Временные

3.4. Логические

3.5. Специальные

4. Встроенные функции

4.1. Строковые функции

4.2. Числовые функции

4.3. Функции даты и времени

4.4. Агрегатные функции

4.5. Функции JSON

4.6. Функции полнотекстового поиска

5. Операторы

5.1. Логические

5.2. JSON

5.3. Массивы

6. Утилиты командной строки

6.1. Основные утилиты

6.2. Расширенные утилиты

7. Административные команды SQL

7.1. Управление базами данных и ролями

7.2. Обслуживание

7.3. Управление расширениями

7.4. Управление репликацией

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

8.1. Логическое резервное копирование

8.2. Физическое резервное копирование

8.3. Восстановление на момент времени (PITR)

9. Безопасность

9.1. Файл `pg_hba.conf`

9.2. Шифрование

10. Расширения (Extensions)

11. Производительность и мониторинг

11.1. EXPLAIN и EXPLAIN ANALYZE

11.2. Ключевые метрики

11.3. Инструменты мониторинга

11.4. Новинки PostgreSQL 16 (краткий обзор)

12. Продвинутые возможности

12.1. Партиционирование

12.2. Логическая репликация

12.3. Full-Text Search

12.4. JSON/JSONB

12.5. Foreign Data Wrapper (FDW)

13. Типовые конфигурации для разных сценариев

13.1. Разработка (локальная машина)

13.2. Малый production-сервер (2–4 ядра, 8–16 ГБ RAM)

13.3. Высоконагруженный OLTP-сервер (16+ ядер, 64+ ГБ RAM)

13.4. Аналитический сервер (OLAP, тяжёлые агрегаты)

13.5. PostgreSQL и 1С

14. Диагностика и решение типовых проблем

14.1. Запросы выполняются медленно

14.2. Блокировки и зависшие транзакции

14.3. Рост размера таблицы без удаления строк

14.4. WAL-файлы не удаляются

15. Обновление и миграция

15.1. Обновление in-place (major version)

15.2. Логическая миграция

15.3. Минимизация downtime

Справочник по Oracle DB