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

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

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

Справочник по 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. Системные представления (System 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. Встроенные функции

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`	Проверка доступности сервера

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;

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: настраивается через ssl = on и сертификаты.
Расширение 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 — активность по таблице.
blks_read vs blks_hit — эффективность кэша (blks_hit / (blks_read + blks_hit)).
wait_event в pg_stat_activity — причины ожидания (I/O, блокировки).

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

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

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 data->>'name' FROM products WHERE data @> '{"category": "electronics"}';

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

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

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/data stop

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

# Запустите pg_upgrade
pg_upgrade \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/old/data \
  --new-datadir=/new/data \
  --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. Системные представления (System 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. Инструменты мониторинга​

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

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

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

12.3. Full-Text Search​

12.4. JSON/JSONB​

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

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

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

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

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

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

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

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

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

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

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

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

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

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