Репликация и высокая доступность в PostgreSQL

Цели урока

После прохождения этого урока вы будете уметь:

• Понимать архитектуру WAL и её роль в репликации
• Настраивать потоковую (streaming) репликацию с нуля
• Настраивать логическую (logical) репликацию для выборочной синхронизации данных
• Выполнять ручной и автоматический failover
• Сравнивать подходы и выбирать оптимальный для конкретной задачи
• Понимать принципы работы Patroni, PgBouncer и других инструментов HA-кластера

---

1. Фундамент: WAL (Write-Ahead Log)

Прежде чем говорить о репликации, необходимо понять WAL — механизм, на котором построена вся система надёжности и репликации PostgreSQL.

1.1. Что такое WAL?

Write-Ahead Log (журнал упреждающей записи) — это последовательный лог всех изменений, внесённых в базу данных. Принцип работы прост: прежде чем изменить реальные файлы данных на диске, PostgreSQL записывает описание этого изменения в WAL-файл.

Это даёт три критических свойства:

1. Durability (надёжность) — после подтверждения транзакции (COMMIT) данные гарантированно сохранены, даже при аварийном отключении питания.
2. Crash recovery (восстановление) — после сбоя PostgreSQL «проигрывает» WAL-записи и восстанавливает консистентное состояние.
3. Replication (репликация) — WAL-записи можно передать на другой сервер и «проиграть» там, получив точную копию базы.

1.2. Структура WAL-файлов

# Где лежат WAL-файлы
ls -la /var/lib/postgresql/16/main/pg_wal/

# Типичный вывод:
# 000000010000000000000001   (16 МБ каждый файл)
# 000000010000000000000002
# 000000010000000000000003
# archive_status/

Каждый WAL-файл по умолчанию имеет размер 16 МБ и именуется 24-символьным hex-числом. Имя состоит из трёх частей:

Часть	Значение
00000001 (8 символов)	Timeline ID — «ветка» истории (увеличивается при failover)
00000000 (8 символов)	Старшие 32 бита позиции в WAL
00000001 (8 символов)	Младшие 32 бита позиции в WAL

-- Текущая позиция записи в WAL
SELECT pg_current_wal_lsn();
-- Пример: 0/16B3780

-- Текущий WAL-файл
SELECT pg_walfile_name(pg_current_wal_lsn());
-- Пример: 000000010000000000000001

-- Размер WAL, накопленного с последнего checkpoint
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS wal_bytes;

-- Количество WAL-файлов
SELECT count(*) FROM pg_ls_waldir();

-- Общий размер WAL на диске
SELECT sum(size) / 1024 / 1024 AS wal_mb FROM pg_ls_waldir();

1.3. Ключевые параметры WAL

# postgresql.conf

# Уровень WAL — определяет, сколько информации записывается
# minimal     → только для crash recovery (репликация невозможна)
# replica     → достаточно для физической репликации (по умолчанию с PG 10)
# logical     → добавляет информацию для логического декодирования
wal_level = replica

# Сколько WAL-файлов хранить (мин. для реплик)
wal_keep_size = 1GB          # PG 13+; ранее — wal_keep_segments

# Максимум одновременных WAL-sender процессов (отправка WAL репликам)
max_wal_senders = 10

# Максимум слотов репликации
max_replication_slots = 10

# Синхронная запись WAL на диск
synchronous_commit = on       # on | remote_apply | remote_write | local | off

1.4. WAL Archiving

Архивация WAL — копирование завершённых WAL-файлов в надёжное хранилище. Это основа Point-in-Time Recovery (PITR) и страховка при отставании реплик:

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
# %p — полный путь к WAL-файлу
# %f — имя WAL-файла

# Для продакшена используют pgBackRest, WAL-G или barman:
# archive_command = 'pgbackrest --stanza=main archive-push %p'
# archive_command = 'wal-g wal-push %p'

---

2. Потоковая (Streaming) репликация

2.1. Обзор

Потоковая репликация — это физическая репликация, при которой WAL-записи передаются с primary-сервера на один или несколько standby-серверов в режиме реального времени. Standby воспроизводит эти записи и поддерживает полную побайтовую копию primary.

┌─────────────┐      WAL stream      ┌──────────────┐
│   PRIMARY    │ ──────────────────► │   STANDBY     │
│  (read/write)│      TCP/IP         │  (read-only)  │
│              │ ◄─────────────────  │               │
│  Port 5432   │   feedback/status   │  Port 5433    │
└─────────────┘                      └──────────────┘

Ключевые характеристики:

Свойство	Описание
Единица репликации	Вся база данных целиком (весь кластер PostgreSQL)
Тип данных	Физические WAL-записи (бинарные изменения страниц)
Standby	Read-only (можно использовать для SELECT-запросов — «hot standby»)
Задержка	Обычно миллисекунды (async) или гарантированно нулевая (sync)
DDL	Реплицируется автоматически (всё реплицируется)
Совместимость версий	Primary и standby должны иметь одинаковую мажорную версию

2.2. Подготовка Primary-сервера

Предупреждение

Все команды предполагают PostgreSQL 16 на Ubuntu/Debian. Для других версий и систем пути могут отличаться.

Шаг 1. Настройка postgresql.conf:

# /etc/postgresql/16/main/postgresql.conf

# --- Обязательные параметры ---
listen_addresses = '*'              # слушать все интерфейсы
wal_level = replica                 # минимум для потоковой репликации
max_wal_senders = 5                 # кол-во реплик + запас
wal_keep_size = 2GB                 # хранить WAL для отстающих реплик

# --- Рекомендуемые ---
max_replication_slots = 5           # по слоту на каждую реплику
hot_standby = on                    # разрешить SELECT на standby
hot_standby_feedback = on           # standby сообщает primary о своих запросах

# --- Архивация (опционально, но настоятельно рекомендуется) ---
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

# --- Синхронная репликация (опционально) ---
# synchronous_standby_names = 'standby1'
# synchronous_commit = on

Шаг 2. Создание роли для репликации:

-- Подключение к primary
CREATE ROLE replicator
    WITH REPLICATION LOGIN PASSWORD 'strong_replication_password_2024';

-- Проверка
SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'replicator';

Шаг 3. Настройка pg_hba.conf:

# /etc/postgresql/16/main/pg_hba.conf

# Разрешить подключение для репликации со standby-сервера
# TYPE   DATABASE        USER         ADDRESS              METHOD
host     replication     replicator   192.168.1.0/24       scram-sha-256

# Если standby-сервер имеет фиксированный IP:
# host   replication     replicator   192.168.1.20/32      scram-sha-256

Шаг 4. Создание слота репликации (рекомендуется):

-- Слот гарантирует, что primary не удалит WAL, нужный standby
SELECT pg_create_physical_replication_slot('standby1_slot');

-- Проверка
SELECT slot_name, slot_type, active FROM pg_replication_slots;

Шаг 5. Перезапуск / перезагрузка:

# Если менялись shared_preload_libraries или listen_addresses — restart
sudo systemctl restart postgresql

# Если менялись только pg_hba.conf или другие параметры — reload достаточно
sudo systemctl reload postgresql

2.3. Настройка Standby-сервера

Шаг 1. Остановить PostgreSQL на standby:

sudo systemctl stop postgresql

Шаг 2. Удалить существующие данные и сделать базовую копию:

# Очистить каталог данных standby
sudo rm -rf /var/lib/postgresql/16/main/*

# Сделать базовую копию primary → standby
sudo -u postgres pg_basebackup \
    -h 192.168.1.10 \
    -p 5432 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp \                   # формат: plain (прямая копия файлов)
    -Xs \                   # метод передачи WAL: stream (параллельно)
    -P \                    # показать прогресс
    -R \                    # автоматически создать standby.signal и настройки
    -S standby1_slot \      # использовать слот репликации
    -C                      # создать слот, если не существует

Флаг -R автоматически создаёт два ключевых элемента:

1. Файл standby.signal — маркер, указывающий, что это standby-сервер
2. Строку подключения в postgresql.auto.conf:

# Автоматически добавлено pg_basebackup -R
primary_conninfo = 'user=replicator password=strong_replication_password_2024 host=192.168.1.10 port=5432 sslmode=prefer'
primary_slot_name = 'standby1_slot'

Шаг 3. Проверить и при необходимости скорректировать postgresql.conf на standby:

# /etc/postgresql/16/main/postgresql.conf (standby)

# Разрешить read-only запросы к standby
hot_standby = on

# Standby отправляет feedback primary, чтобы тот не удалял нужные данные
hot_standby_feedback = on

# Порт (можно оставить 5432 если серверы разные, или изменить для теста на одной машине)
port = 5432

Шаг 4. Запустить standby:

sudo systemctl start postgresql

# Проверить логи
sudo tail -20 /var/log/postgresql/postgresql-16-main.log
# Должно быть:
# LOG:  entering standby mode
# LOG:  redo starts at 0/3000028
# LOG:  consistent recovery state reached at 0/3000100
# LOG:  started streaming WAL from primary at 0/4000000 on timeline 1

2.4. Проверка работоспособности

На primary:

-- Состояние WAL-sender процессов
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication;

-- Слоты репликации
SELECT
    slot_name,
    slot_type,
    active,
    active_pid,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_size
FROM pg_replication_slots;

На standby:

-- Информация о восстановлении
SELECT
    pg_is_in_recovery()          AS is_standby,
    pg_last_wal_receive_lsn()    AS received_lsn,
    pg_last_wal_replay_lsn()     AS replayed_lsn,
    pg_last_xact_replay_timestamp() AS last_replayed_at,
    now() - pg_last_xact_replay_timestamp() AS replication_delay;

Тест репликации:

-- На primary: создаём таблицу и вставляем данные
CREATE TABLE repl_test (
    id      SERIAL PRIMARY KEY,
    message TEXT,
    ts      TIMESTAMP DEFAULT now()
);
INSERT INTO repl_test (message) VALUES ('Hello from primary!');

-- На standby: проверяем (через секунду)
SELECT * FROM repl_test;
-- Должна быть строка 'Hello from primary!'

-- На standby: попытка записи должна завершиться ошибкой
INSERT INTO repl_test (message) VALUES ('test');
-- ERROR:  cannot execute INSERT in a read-only transaction

2.5. Синхронная vs. Асинхронная репликация

По умолчанию потоковая репликация асинхронна — primary не ждёт подтверждения от standby. Это быстрее, но при падении primary последние транзакции могут быть потеряны.

Синхронная репликация гарантирует, что транзакция подтверждена на standby перед ответом клиенту:

# postgresql.conf (primary)

# Указать, какие standby являются синхронными
# Формат: FIRST N (name1, name2, ...) или ANY N (name1, name2, ...)
synchronous_standby_names = 'FIRST 1 (standby1)'

# Уровень синхронности:
# on            → ждать flush на standby (по умолчанию)
# remote_apply  → ждать replay на standby (самый строгий)
# remote_write  → ждать write (но не flush) на standby
synchronous_commit = on

# На standby: задать имя application_name
# postgresql.auto.conf или primary_conninfo
primary_conninfo = '... application_name=standby1'

Сравнение режимов:

Параметр	async	remote_write	on (sync)	remote_apply
Потеря данных при падении primary	Возможна	Минимальна	Нет	Нет
Задержка COMMIT	Нет	Минимальная	Средняя	Наибольшая
Чтение свежих данных на standby	Задержка	Задержка	Задержка	Мгновенно
Доступность при падении standby	Есть	Блокирует primary!	Блокирует primary!	Блокирует primary!

Критический момент

При синхронной репликации, если все синхронные standby недоступны, primary перестанет принимать транзакции (COMMIT будет висеть бесконечно). Это цена консистентности. Решения:

1. Использовать ANY N с несколькими standby: synchronous_standby_names = 'ANY 1 (standby1, standby2)'
2. Настроить автоматический перевод в async при падении standby (Patroni делает это)
3. Использовать таймаут: statement_timeout для клиентских сессий

2.6. Каскадная репликация

Standby может сам быть источником для другого standby, снижая нагрузку на primary:

PRIMARY ──► STANDBY_1 ──► STANDBY_2 ──► STANDBY_3
                       └──► STANDBY_4

Настройка на STANDBY_2:

# postgresql.auto.conf (standby_2)
# Подключаемся не к primary, а к standby_1
primary_conninfo = 'host=standby1_ip port=5432 user=replicator password=... application_name=standby2'

2.7. Ручной Failover

Если primary выходит из строя, standby можно «промотировать» (promote) до нового primary:

# Способ 1: утилита pg_ctl
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# Способ 2: SQL-функция (PG 12+)
SELECT pg_promote(wait => true, wait_seconds => 60);

# Способ 3: создать файл-триггер (устаревший способ)
# touch /var/lib/postgresql/16/main/promote

После promote:

-- Проверка: сервер больше не в recovery
SELECT pg_is_in_recovery();
-- false

-- Новый timeline (увеличился на 1)
SELECT pg_walfile_name(pg_current_wal_lsn());
-- 00000002...  (timeline 2)

-- Теперь запись разрешена
INSERT INTO repl_test (message) VALUES ('I am the new primary!');

После промоушена

1. Старый primary НЕЛЬЗЯ просто запустить — он будет думать, что он primary, и данные разойдутся (split-brain).
2. Старый primary нужно либо пересоздать как standby через pg_basebackup, либо использовать pg_rewind (если wal_log_hints = on или используются data checksums).
3. Все клиенты должны переключиться на новый primary (через VIP, DNS, или PgBouncer).

2.8. pg_rewind — быстрое восстановление бывшего primary

pg_rewind позволяет «отмотать» бывший primary до точки расхождения и переподключить его как standby, без полного pg_basebackup:

# Предварительно: wal_log_hints = on ИЛИ data checksums должны быть включены
# Проверка:
sudo -u postgres psql -c "SHOW wal_log_hints;"
sudo -u postgres psql -c "SHOW data_checksums;"

# Остановить бывший primary
sudo systemctl stop postgresql

# Выполнить rewind
sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/16/main \
    --source-server='host=new_primary_ip port=5432 user=replicator password=...' \
    --progress

# Создать standby.signal
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

# Настроить подключение к новому primary
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << EOF
primary_conninfo = 'host=new_primary_ip port=5432 user=replicator password=... application_name=old_primary'
primary_slot_name = 'old_primary_slot'
EOF

# Запустить
sudo systemctl start postgresql

---

3. Логическая (Logical) репликация

3.1. Обзор

Логическая репликация передаёт логические изменения (INSERT, UPDATE, DELETE) на уровне строк, а не физические WAL-записи. Это даёт принципиально другие возможности.

┌─────────────┐    Логические изменения    ┌──────────────┐
│  PUBLISHER   │ ────────────────────────► │  SUBSCRIBER   │
│  (источник)  │      INSERT/UPDATE/       │  (получатель) │
│              │      DELETE по строкам     │               │
│  Port 5432   │                           │  Port 5433    │
└─────────────┘                           └──────────────┘

Ключевые отличия от потоковой репликации:

Характеристика	Streaming (физическая)	Logical (логическая)
Единица репликации	Весь кластер	Отдельные таблицы / столбцы
Направление записи	Standby = read-only	Subscriber = read/write
DDL (CREATE TABLE и т.д.)	Реплицируется	НЕ реплицируется
Версии PostgreSQL	Одинаковые мажорные	Разные мажорные (PG 10+)
Кросс-платформенность	Одна архитектура	Разные архитектуры / ОС
Фильтрация	Невозможна	По таблицам, столбцам, строкам
Использование subscriber	Только SELECT	Полноценная база (R/W)
Sequences	Реплицируются	НЕ реплицируются
Large objects	Реплицируются	НЕ реплицируются

Когда использовать логическую репликацию?

• Мажорное обновление PostgreSQL с минимальным downtime (PG 15 → PG 16)
• Консолидация данных из нескольких баз в одну аналитическую
• Выборочная репликация — только определённые таблицы или столбцы
• Разные схемы на publisher и subscriber (дополнительные индексы, материализованные представления)
• Кросс-версионная совместимость
• Мультимастер (с ограничениями и осторожностью)

3.2. Архитектура: публикации и подписки

Логическая репликация работает по модели Publisher → Subscriber:

1. Publisher (издатель) — сервер-источник. Создаёт публикацию (publication) — набор таблиц, изменения которых отправляются.
2. Subscriber (подписчик) — сервер-получатель. Создаёт подписку (subscription) на публикацию. При первом подключении выполняет начальную синхронизацию (копирование существующих данных), затем получает изменения в реальном времени.

Внутренний механизм:

• Publisher использует logical decoding для преобразования WAL в поток логических изменений
• Каждая подписка создаёт replication slot на publisher для отслеживания позиции
• Изменения передаются через walsender → logical replication worker на subscriber

3.3. Настройка Publisher

Шаг 1. Параметры postgresql.conf:

# /etc/postgresql/16/main/postgresql.conf (publisher)

# ОБЯЗАТЕЛЬНО: logical для логической репликации
wal_level = logical

# Должно быть достаточно для всех подписчиков
max_replication_slots = 10
max_wal_senders = 10

# Для декодирования WAL нужны worker-процессы
max_worker_processes = 16   # по умолчанию 8, увеличить если много подписчиков

Шаг 2. Настройка pg_hba.conf:

# Разрешить подключение для репликации
host    all             replicator   192.168.1.0/24    scram-sha-256
host    replication     replicator   192.168.1.0/24    scram-sha-256

Шаг 3. Создание роли:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_password';
GRANT CONNECT ON DATABASE source_db TO replicator;

-- Для PG 16+ достаточно гранта на таблицы:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO replicator;

Шаг 4. Перезапуск (если менялся wal_level):

sudo systemctl restart postgresql

Шаг 5. Создание тестовых таблиц и данных:

-- На publisher
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category    VARCHAR(100),
    price       NUMERIC(10,2) NOT NULL,
    stock       INTEGER DEFAULT 0,
    updated_at  TIMESTAMP DEFAULT now()
);

CREATE TABLE orders (
    id             SERIAL PRIMARY KEY,
    product_id     INTEGER REFERENCES products(id),
    customer_email VARCHAR(255) NOT NULL,
    quantity       INTEGER NOT NULL,
    total_amount   NUMERIC(10,2) NOT NULL,
    status         VARCHAR(20) DEFAULT 'pending',
    created_at     TIMESTAMP DEFAULT now()
);

CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  VARCHAR(100),
    action      VARCHAR(10),
    record_id   INTEGER,
    changed_at  TIMESTAMP DEFAULT now(),
    details     JSONB
);

-- Наполнение данными
INSERT INTO products (name, category, price, stock) VALUES
    ('Ноутбук Lenovo ThinkPad', 'Электроника', 85000, 50),
    ('Монитор Dell 27"',        'Электроника', 32000, 120),
    ('Клавиатура Keychron K8',  'Периферия',   9500,  200),
    ('Мышь Logitech MX Master', 'Периферия',   7500,  150),
    ('Наушники Sony WH-1000',   'Аудио',       28000, 80);

INSERT INTO orders (product_id, customer_email, quantity, total_amount, status) VALUES
    (1, 'ivan@example.com',    1, 85000, 'confirmed'),
    (3, 'maria@example.com',   2, 19000, 'shipped'),
    (5, 'alex@example.com',    1, 28000, 'pending'),
    (2, 'elena@example.com',   1, 32000, 'delivered');

Шаг 6. Создание публикации:

-- Публикация всех таблиц
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;

-- ИЛИ публикация конкретных таблиц
CREATE PUBLICATION shop_pub FOR TABLE products, orders;

-- ИЛИ публикация с фильтром по столбцам (PG 15+)
CREATE PUBLICATION products_pub
    FOR TABLE products (id, name, category, price);
    -- stock и updated_at НЕ будут реплицироваться

-- ИЛИ публикация с фильтром по строкам (PG 15+)
CREATE PUBLICATION electronics_pub
    FOR TABLE products
    WHERE (category = 'Электроника');

-- Проверка
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Управление публикациями:

-- Добавить таблицу в существующую публикацию
ALTER PUBLICATION shop_pub ADD TABLE audit_log;

-- Удалить таблицу из публикации
ALTER PUBLICATION shop_pub DROP TABLE audit_log;

-- Изменить параметры
ALTER PUBLICATION shop_pub SET (publish = 'insert, update, delete');
-- Доступные операции: insert, update, delete, truncate

-- Удалить публикацию
DROP PUBLICATION shop_pub;

3.4. Настройка Subscriber

Шаг 1. Создать идентичную структуру таблиц на subscriber:

-- На subscriber (другой сервер / другая БД)
-- DDL НЕ реплицируется, поэтому таблицы нужно создать вручную!

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category    VARCHAR(100),
    price       NUMERIC(10,2) NOT NULL,
    stock       INTEGER DEFAULT 0,
    updated_at  TIMESTAMP DEFAULT now()
);

CREATE TABLE orders (
    id             SERIAL PRIMARY KEY,
    product_id     INTEGER REFERENCES products(id),
    customer_email VARCHAR(255) NOT NULL,
    quantity       INTEGER NOT NULL,
    total_amount   NUMERIC(10,2) NOT NULL,
    status         VARCHAR(20) DEFAULT 'pending',
    created_at     TIMESTAMP DEFAULT now()
);

-- Можно добавить дополнительные индексы, которых нет на publisher
CREATE INDEX idx_orders_customer ON orders (customer_email);
CREATE INDEX idx_products_category ON products (category);

Копирование структуры

Быстрый способ скопировать структуру таблиц без данных:

pg_dump -h publisher_host -U postgres -s --no-owner source_db | \
    psql -h subscriber_host -U postgres target_db

Флаг -s — только схема (structure), без данных.

Шаг 2. Настройка postgresql.conf на subscriber:

# postgresql.conf (subscriber)

# Воркеры для логической репликации
max_logical_replication_workers = 8  # по умолчанию 4
max_worker_processes = 16

# Параллельные воркеры для начальной синхронизации
max_sync_workers_per_subscription = 4  # PG 16+: для параллельного копирования

Шаг 3. Создание подписки:

-- На subscriber
CREATE SUBSCRIPTION shop_sub
    CONNECTION 'host=192.168.1.10 port=5432 dbname=source_db user=replicator password=repl_password'
    PUBLICATION shop_pub
    WITH (
        copy_data = true,          -- скопировать существующие данные (по умолчанию true)
        create_slot = true,        -- создать слот на publisher (по умолчанию true)
        enabled = true,            -- сразу активировать
        synchronous_commit = off,  -- async commit для скорости начальной загрузки
        streaming = on             -- потоковое декодирование транзакций (PG 14+)
    );

Шаг 4. Проверка:

-- На subscriber: статус подписок
SELECT
    subname,
    subenabled,
    subconninfo,
    subpublications
FROM pg_subscription;

-- Статус репликации для каждой таблицы
SELECT
    srsubid::regsubscription AS subscription,
    srrelid::regclass        AS table_name,
    srsubstate               AS state,
    -- Расшифровка state:
    -- 'i' = инициализация
    -- 'd' = копирование данных
    -- 'f' = завершено копирование, ожидание sync
    -- 's' = синхронизировано
    -- 'r' = готово к потоковой репликации
    srsublsn                 AS lsn
FROM pg_subscription_rel;

-- На publisher: проверка слотов
SELECT
    slot_name,
    plugin,
    slot_type,
    active,
    confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- На publisher: статус WAL-отправителей
SELECT
    pid,
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    replay_lsn
FROM pg_stat_replication;

3.5. Тестирование репликации

-- =====================
-- На PUBLISHER:
-- =====================

-- INSERT
INSERT INTO products (name, category, price, stock)
VALUES ('USB-хаб Anker 7-in-1', 'Периферия', 4500, 300);

-- UPDATE
UPDATE products SET price = 79000, stock = 45
WHERE name = 'Ноутбук Lenovo ThinkPad';

-- DELETE
DELETE FROM products WHERE name = 'Мышь Logitech MX Master';

-- =====================
-- На SUBSCRIBER (через 1-2 секунды):
-- =====================
SELECT id, name, price, stock FROM products ORDER BY id;
-- Должны отражаться все три изменения

3.6. Управление подписками

-- Приостановить подписку (без удаления слота)
ALTER SUBSCRIPTION shop_sub DISABLE;

-- Возобновить
ALTER SUBSCRIPTION shop_sub ENABLE;

-- Обновить параметры подключения
ALTER SUBSCRIPTION shop_sub
    CONNECTION 'host=new_primary port=5432 dbname=source_db user=replicator password=new_password';

-- Обновить публикацию (например, после добавления таблиц)
ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION;

-- Обновить публикацию с копированием данных новых таблиц
ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION WITH (copy_data = true);

-- Удалить подписку (также удалит replication slot на publisher)
DROP SUBSCRIPTION shop_sub;

3.7. Конфликты и их разрешение

В отличие от streaming replication, при логической репликации subscriber — это полноценная БД с возможностью записи. Это может привести к конфликтам:

-- =====================
-- Пример конфликта: дублирование PRIMARY KEY
-- =====================

-- На subscriber: вставляем строку с id=100
INSERT INTO products (id, name, category, price) VALUES
    (100, 'Локальный товар', 'Тест', 1000);

-- На publisher: вставляем строку с тем же id=100
INSERT INTO products (id, name, category, price) VALUES
    (100, 'Товар с publisher', 'Тест', 2000);

-- Репликация ОСТАНОВИТСЯ с ошибкой!
-- В логах subscriber:
-- ERROR: duplicate key value violates unique constraint "products_pkey"
-- DETAIL: Key (id)=(100) already exists.

Варианты разрешения:

-- Вариант 1: Удалить конфликтующую строку на subscriber
DELETE FROM products WHERE id = 100;
-- Репликация автоматически возобновится

-- Вариант 2: Пропустить транзакцию (PG 15+)
ALTER SUBSCRIPTION shop_sub SKIP (lsn = '0/A1B2C3D4');
-- lsn берётся из сообщения об ошибке

-- Вариант 3 (PG < 15): изменить origin advance
-- Найти origin
SELECT * FROM pg_replication_origin;

-- Пропустить до определённой позиции
SELECT pg_replication_origin_advance(
    'pg_16399',  -- имя origin из pg_replication_origin
    '0/A1B2C3D4' -- LSN для пропуска
);

-- Вариант 4: Пересоздать подписку
DROP SUBSCRIPTION shop_sub;
-- Исправить данные
CREATE SUBSCRIPTION shop_sub ...;

Предотвращение конфликтов

Лучше предотвращать конфликты, чем разрешать их:

1. Не пишите в реплицируемые таблицы на subscriber (или только в столбцы, которых нет в публикации)
2. Используйте разные диапазоны для последовательностей: на publisher — нечётные, на subscriber — чётные
3. Добавьте ON CONFLICT логику на уровне приложения
4. Для мультимастера используйте BDR (Bi-Directional Replication) от 2ndQuadrant/EDB

3.8. Особенности и ограничения

Sequences (последовательности)

Логическая репликация не синхронизирует последовательности. После failover значения sequence на subscriber будут старыми:

-- На publisher: проверить текущее значение
SELECT last_value FROM products_id_seq;
-- 105

-- На subscriber: sequence может быть на 1 (или на начальном значении)
SELECT last_value FROM products_id_seq;
-- 1

-- Решение: периодически синхронизировать вручную
-- На publisher:
SELECT
    'SELECT setval(' || quote_literal(sequencename) || ', ' || last_value || ');'
FROM pg_sequences
WHERE schemaname = 'public';

-- Скопировать вывод и выполнить на subscriber

DDL (изменения структуры)

-- DDL на publisher НЕ реплицируется!
-- На publisher:
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(5,2);

-- На subscriber: нужно выполнить тот же DDL вручную!
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(5,2);

-- Затем обновить подписку:
ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION;

TRUNCATE

-- TRUNCATE реплицируется по умолчанию (начиная с PG 11)
-- Но можно отключить:
CREATE PUBLICATION shop_pub FOR TABLE products
    WITH (publish = 'insert, update, delete');
    -- truncate НЕ указан — не будет реплицироваться

3.9. Практический пример: миграция между версиями PostgreSQL

Один из главных use case логической репликации — обновление с PG 15 на PG 16 с минимальным downtime:

┌──────────────────┐                    ┌──────────────────┐
│  PG 15 (старый)  │  logical repl.    │  PG 16 (новый)   │
│  publisher       │ ─────────────►    │  subscriber      │
│  Порт 5432       │                    │  Порт 5433       │
│  РАБОЧАЯ БАЗА    │                    │  РЕПЛИКА          │
└──────────────────┘                    └──────────────────┘

         После переключения:

┌──────────────────┐                    ┌──────────────────┐
│  PG 15 (старый)  │                    │  PG 16 (новый)   │
│  ВЫКЛЮЧЕН        │                    │  РАБОЧАЯ БАЗА    │
│                  │                    │  Порт 5432       │
└──────────────────┘                    └──────────────────┘

Порядок действий:

-- ============================
-- 1. На PG 15 (publisher)
-- ============================
ALTER SYSTEM SET wal_level = 'logical';
-- Перезапуск PostgreSQL

CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- ============================
-- 2. На PG 16 (subscriber)
-- ============================

-- 2a. Скопировать структуру
-- pg_dump -h pg15_host -s source_db | psql -h pg16_host target_db

-- 2b. Создать подписку
CREATE SUBSCRIPTION migration_sub
    CONNECTION 'host=pg15_host port=5432 dbname=source_db user=replicator password=...'
    PUBLICATION migration_pub;

-- 2c. Дождаться полной синхронизации
-- Мониторить:
SELECT * FROM pg_subscription_rel;
-- Все таблицы должны быть в состоянии 'r' (ready)

-- ============================
-- 3. Переключение (кратковременный downtime)
-- ============================

-- 3a. Остановить запись на PG 15 (application → read-only)

-- 3b. Дождаться финальной синхронизации
-- На PG 15:
SELECT pg_current_wal_lsn();
-- На PG 16: подождать, пока confirmed_flush_lsn достигнет этого значения

-- 3c. Синхронизировать последовательности
-- На PG 15 собрать значения, на PG 16 установить через setval()

-- 3d. Удалить подписку на PG 16
DROP SUBSCRIPTION migration_sub;

-- 3e. Переключить приложение на PG 16

-- 3f. Выключить PG 15

---

4. Мониторинг репликации

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

-- ============================================
-- Универсальная проверка состояния репликации
-- (выполнять на primary / publisher)
-- ============================================

-- 1. Статус WAL-sender процессов
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    -- Отставание в байтах
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)    AS send_lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)   AS write_lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)   AS flush_lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)  AS replay_lag_bytes,
    -- Отставание по времени
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY application_name;

-- 2. Слоты репликации и их размер
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS pending_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- 3. Неактивные слоты (ОПАСНО — накапливают WAL!)
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal
FROM pg_replication_slots
WHERE NOT active;

Неактивные слоты репликации

Неактивный слот заставляет PostgreSQL бесконечно хранить WAL-файлы, что может привести к заполнению диска и падению сервера. Всегда мониторьте неактивные слоты и удаляйте ненужные:

-- Удалить неактивный слот
SELECT pg_drop_replication_slot('abandoned_slot_name');

-- Автоматическая защита (PG 13+):
-- postgresql.conf:
-- max_slot_wal_keep_size = 10GB   # ограничить WAL, хранимый слотами

4.2. Мониторинг на standby / subscriber

-- На standby (streaming replication):
SELECT
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn() AS received,
    pg_last_wal_replay_lsn() AS replayed,
    pg_last_xact_replay_timestamp() AS last_replayed_time,
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

-- На subscriber (logical replication):
SELECT
    subname,
    received_lsn,
    latest_end_lsn,
    latest_end_time,
    EXTRACT(EPOCH FROM (now() - latest_end_time)) AS lag_seconds
FROM pg_stat_subscription;

-- Статус синхронизации таблиц
SELECT
    srsubid::regsubscription AS subscription,
    srrelid::regclass AS table_name,
    srsubstate AS state
FROM pg_subscription_rel
ORDER BY srrelid::regclass::text;

4.3. Алерты на основе lag

-- Создать функцию проверки здоровья репликации
CREATE OR REPLACE FUNCTION check_replication_health()
RETURNS TABLE (
    replica_name    TEXT,
    lag_seconds     NUMERIC,
    lag_bytes       BIGINT,
    status          TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        r.application_name::TEXT,
        EXTRACT(EPOCH FROM r.replay_lag)::NUMERIC,
        pg_wal_lsn_diff(pg_current_wal_lsn(), r.replay_lsn),
        CASE
            WHEN r.replay_lag IS NULL THEN 'UNKNOWN'
            WHEN r.replay_lag < interval '5 seconds' THEN 'OK'
            WHEN r.replay_lag < interval '30 seconds' THEN 'WARNING'
            ELSE 'CRITICAL'
        END::TEXT
    FROM pg_stat_replication r;
END;
$$ LANGUAGE plpgsql;

-- Использование
SELECT * FROM check_replication_health();

---

5. Высокая доступность (HA) — инструменты и архитектуры

5.1. Компоненты HA-кластера

Для production-среды одной репликации недостаточно — нужен автоматический failover. Типичный HA-кластер PostgreSQL состоит из:

                     ┌──────────────┐
                     │   Клиенты    │
                     └──────┬───────┘
                            │
                     ┌──────▼───────┐
                     │  PgBouncer   │  ← Пул соединений + маршрутизация
                     │  (или HAProxy│
                     └──────┬───────┘
                            │
              ┌─────────────┼─────────────┐
              │             │             │
       ┌──────▼──────┐ ┌───▼────────┐ ┌──▼───────────┐
       │   Primary    │ │  Standby 1 │ │  Standby 2   │
       │  (R/W)      │ │  (R/O)     │ │  (R/O)       │
       └──────┬──────┘ └───┬────────┘ └──┬───────────┘
              │             │             │
       ┌──────▼─────────────▼─────────────▼──┐
       │            Patroni / etcd            │  ← Координация и failover
       │          (DCS — consensus)           │
       └─────────────────────────────────────┘

Компонент	Роль	Примеры
DCS (Distributed Consensus Store)	Хранение состояния кластера, выбор лидера	etcd, Consul, ZooKeeper
HA Manager	Мониторинг, автоматический failover	Patroni, repmgr, pg_auto_failover
Connection Pooler	Пул соединений, маршрутизация R/W и R/O	PgBouncer, Pgpool-II, Odyssey
VIP / DNS	Единая точка входа для клиентов	keepalived, AWS ELB, DNS failover
Backup	Резервное копирование и PITR	pgBackRest, WAL-G, barman

5.2. Patroni — стандарт де-факто для HA

Patroni — Python-фреймворк для управления HA-кластером PostgreSQL. Он автоматизирует всё: инициализацию реплик, мониторинг, failover, switchover и восстановление.

Архитектура Patroni

       ┌────────────┐    ┌────────────┐    ┌────────────┐
       │   Node 1   │    │   Node 2   │    │   Node 3   │
       │ PostgreSQL  │    │ PostgreSQL  │    │ PostgreSQL  │
       │ + Patroni   │    │ + Patroni   │    │ + Patroni   │
       └──────┬─────┘    └──────┬─────┘    └──────┬─────┘
              │                 │                 │
              └─────────────────┼─────────────────┘
                                │
                         ┌──────▼──────┐
                         │    etcd     │
                         │  (кластер)  │
                         └─────────────┘

Каждый узел Patroni:

• Запускает и управляет локальным PostgreSQL
• Регулярно обновляет свой «heartbeat» в etcd
• Участвует в выборе лидера (primary)
• При потере лидера инициирует failover (promote одного из standby)
• Автоматически переконфигурирует бывший primary как standby (после восстановления)

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

# /etc/patroni/patroni.yml (Node 1)

scope: postgres-cluster      # имя кластера
name: node1                  # имя этого узла

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.11:8008

etcd3:
  hosts:
    - 192.168.1.51:2379
    - 192.168.1.52:2379
    - 192.168.1.53:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576    # 1 MB — максимальное отставание для failover
    synchronous_mode: false              # true для синхронного режима

    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: on
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 2GB
        hot_standby_feedback: on
        wal_log_hints: on       # обязательно для pg_rewind
        logging_collector: on
        log_directory: /var/log/postgresql
        log_filename: postgresql-%Y-%m-%d.log

  initdb:
    - encoding: UTF8
    - data-checksums     # обязательно для pg_rewind

  pg_hba:
    - host replication replicator 192.168.1.0/24 scram-sha-256
    - host all all 192.168.1.0/24 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256

  users:
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
    replicator:
      password: repl_password
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.11:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin

  authentication:
    superuser:
      username: postgres
      password: postgres_password
    replication:
      username: replicator
      password: repl_password
    rewind:
      username: replicator
      password: repl_password

  parameters:
    shared_buffers: 2GB
    effective_cache_size: 6GB
    work_mem: 64MB
    maintenance_work_mem: 512MB
    max_connections: 200

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Команды patronictl

# Статус кластера
patronictl -c /etc/patroni/patroni.yml list
# +--------+--------+---------+---------+----+-----------+
# | Member | Host   | Role    | State   | TL | Lag in MB |
# +--------+--------+---------+---------+----+-----------+
# | node1  | ...11  | Leader  | running |  1 |           |
# | node2  | ...12  | Replica | running |  1 |         0 |
# | node3  | ...13  | Replica | running |  1 |         0 |
# +--------+--------+---------+---------+----+-----------+

# Плановое переключение (switchover) — без потери данных
patronictl -c /etc/patroni/patroni.yml switchover
# Выбрать нового лидера из списка

# Принудительный failover (если leader недоступен)
patronictl -c /etc/patroni/patroni.yml failover

# Перезагрузка конфигурации PostgreSQL
patronictl -c /etc/patroni/patroni.yml reload postgres-cluster

# Перезапуск PostgreSQL на конкретном узле
patronictl -c /etc/patroni/patroni.yml restart postgres-cluster node1

# Пауза автоматического управления (для обслуживания)
patronictl -c /etc/patroni/patroni.yml pause

# Возобновление
patronictl -c /etc/patroni/patroni.yml resume

# Изменение параметров DCS
patronictl -c /etc/patroni/patroni.yml edit-config

5.3. PgBouncer — пул соединений

PgBouncer — лёгкий пулер соединений, который стоит между клиентами и PostgreSQL. Он критически важен для HA, потому что:

1. Клиенты подключаются к PgBouncer, а не напрямую к PostgreSQL
2. При failover PgBouncer переключает соединения на новый primary
3. Он ограничивает число реальных соединений к PostgreSQL (экономия ресурсов)

# /etc/pgbouncer/pgbouncer.ini

[databases]
# Маршрутизация: все подключения к "mydb" идут на текущий primary
mydb = host=192.168.1.11 port=5432 dbname=mydb

# Read-only пул для балансировки нагрузки
mydb_ro = host=192.168.1.12,192.168.1.13 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Режим пулинга
pool_mode = transaction    # transaction | session | statement
# transaction — оптимален для большинства приложений
# session     — для prepared statements и LISTEN/NOTIFY

# Лимиты
max_client_conn = 1000     # макс. клиентских подключений к PgBouncer
default_pool_size = 25     # пул на пользователя/БД
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Таймауты
server_connect_timeout = 3
server_login_retry = 3
query_timeout = 300
client_idle_timeout = 3600

# Логирование
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# Админская консоль
admin_users = pgbouncer_admin

# userlist.txt — формат: "user" "password"
# Генерация хеша:
psql -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'app_user';"

Подключение к админской консоли PgBouncer:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

# Полезные команды
SHOW pools;           # состояние пулов
SHOW stats;           # статистика
SHOW servers;         # серверные соединения
SHOW clients;         # клиентские соединения
SHOW databases;       # конфигурация баз
RELOAD;               # перечитать конфиг без остановки
PAUSE mydb;           # приостановить пул (для switchover)
RESUME mydb;          # возобновить

5.4. Эталонная HA-архитектура

Для production-окружения рекомендуется следующая архитектура:

                        ┌─────────────────┐
                        │    Клиенты      │
                        └────────┬────────┘
                                 │
                  ┌──────────────┼──────────────┐
                  │              │              │
           ┌──────▼─────┐ ┌─────▼──────┐ ┌─────▼──────┐
           │ HAProxy /  │ │  HAProxy / │ │  HAProxy / │
           │ PgBouncer  │ │  PgBouncer │ │  PgBouncer │
           │ (Node 1)   │ │  (Node 2)  │ │  (Node 3)  │
           └──────┬─────┘ └─────┬──────┘ └─────┬──────┘
                  │              │              │
           ┌──────▼─────┐ ┌─────▼──────┐ ┌─────▼──────┐
           │ PostgreSQL │ │ PostgreSQL │ │ PostgreSQL │
           │ + Patroni  │ │ + Patroni  │ │ + Patroni  │
           │ (Primary)  │ │ (Standby)  │ │ (Standby)  │
           └──────┬─────┘ └─────┬──────┘ └─────┬──────┘
                  │              │              │
                  └──────────────┼──────────────┘
                                 │
                  ┌──────────────┼──────────────┐
                  │              │              │
           ┌──────▼─────┐ ┌─────▼──────┐ ┌─────▼──────┐
           │   etcd      │ │   etcd     │ │   etcd     │
           │  (Node 1)   │ │  (Node 2)  │ │  (Node 3)  │
           └─────────────┘ └────────────┘ └────────────┘

HAProxy для автоматической маршрутизации

HAProxy использует REST API Patroni для определения текущего primary:

# /etc/haproxy/haproxy.cfg

global
    maxconn 1000

defaults
    mode tcp
    timeout connect 5s
    timeout client 30m
    timeout server 30m

# Запись (R/W) — только primary
listen postgres_rw
    bind *:5000
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server node1 192.168.1.11:5432 check port 8008
    server node2 192.168.1.12:5432 check port 8008
    server node3 192.168.1.13:5432 check port 8008

# Чтение (R/O) — все реплики + primary
listen postgres_ro
    bind *:5001
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server node1 192.168.1.11:5432 check port 8008
    server node2 192.168.1.12:5432 check port 8008
    server node3 192.168.1.13:5432 check port 8008

# Статистика HAProxy
listen stats
    bind *:7000
    mode http
    stats enable
    stats uri /

Клиенты подключаются:

• Запись: postgresql://app_user:password@haproxy-host:5000/mydb
• Чтение: postgresql://app_user:password@haproxy-host:5001/mydb

---

6. Сценарии: пошаговый failover

6.1. Плановый switchover (без потери данных)

Используется для обслуживания primary-сервера (обновление ОС, замена оборудования):

# 1. Проверяем состояние кластера
patronictl -c /etc/patroni/patroni.yml list

# 2. Инициируем switchover
patronictl -c /etc/patroni/patroni.yml switchover \
    --leader node1 \
    --candidate node2 \
    --scheduled now

# 3. Patroni автоматически:
#    a) Блокирует запись на node1
#    b) Ждёт, пока node2 догонит WAL
#    c) Promote node2 до primary
#    d) Переконфигурирует node1 как standby (pg_rewind)
#    e) Обновляет DCS (etcd)
#    f) HAProxy автоматически переключает трафик

# 4. Проверяем
patronictl -c /etc/patroni/patroni.yml list
# node2 теперь Leader, node1 — Replica

6.2. Аварийный failover

При аварийном падении primary Patroni автоматически:

Время 0s:   Primary (node1) падает
Время 3-10s: Patroni на node1 перестаёт обновлять leader key в etcd
Время 10-30s: Leader key истекает (TTL)
Время 30-40s: Patroni на node2/node3 обнаруживает отсутствие лидера
              → Выбирается новый лидер (наименьшее отставание)
              → pg_promote на выбранном узле
              → Обновляется DCS
Время 40-45s: HAProxy обнаруживает нового primary через /primary endpoint
              → Переключает трафик
Время 45-60s: Приложение восстанавливает соединения
              → Кластер полностью работоспособен

Общее время переключения: 30-60 секунд.

6.3. Восстановление бывшего primary

# Когда node1 возвращается в строй:

# Patroni автоматически попробует:
# 1. pg_rewind (если wal_log_hints=on и checksums)
# 2. Если pg_rewind не удался — pg_basebackup (полная копия)

# Мониторинг процесса:
patronictl -c /etc/patroni/patroni.yml list
# node1 — State: creating replica
# ... ждём ...
# node1 — State: running, Role: Replica

# Если нужно вручную пересоздать:
patronictl -c /etc/patroni/patroni.yml reinit postgres-cluster node1

---

7. Сравнительная таблица подходов к репликации

Критерий	Streaming Replication	Logical Replication	Patroni + Streaming
Сложность настройки	Средняя	Средняя	Высокая
Автоматический failover	Нет	Нет	Да
Гранулярность	Весь кластер	Таблицы / столбцы / строки	Весь кластер
Запись на реплику	Нет	Да	Нет (на standby)
Кросс-версионность	Нет	Да	Нет
DDL-репликация	Да	Нет	Да
Задержка	Минимальная	Минимальная	Минимальная
RPO (потеря данных)	0 (sync) / малая (async)	Зависит от настройки	0 (sync) / малая (async)
RTO (время восстановления)	Минуты (ручной)	Не применимо	30-60 сек
Типичное применение	DR, read scaling	Миграция, интеграция, ETL	Production HA

---

8. Рецепты и советы для продакшена

8.1. Чек-лист перед запуском репликации

✅ wal_level = replica (или logical)
✅ max_wal_senders >= количество реплик + 2 (запас)
✅ max_replication_slots >= количество реплик
✅ Создана роль с правом REPLICATION
✅ pg_hba.conf разрешает подключение для репликации
✅ Настроен WAL archiving (pgBackRest / WAL-G)
✅ wal_log_hints = on (для pg_rewind)
✅ hot_standby_feedback = on (на standby)
✅ Мониторинг replication lag настроен (Prometheus + Grafana / Zabbix)
✅ Алерты на неактивные слоты
✅ Тест failover выполнен в staging-среде

8.2. Мониторинг: Prometheus + pg_exporter

# Ключевые метрики для Grafana-дашборда:

# Отставание репликации (секунды)
pg_stat_replication_replay_lag_seconds

# WAL, накопленный в слотах
pg_replication_slot_wal_retained_bytes

# Количество WAL-sender процессов
pg_stat_replication_count

# Статус recovery (1 = standby, 0 = primary)
pg_is_in_recovery

# Время последнего replayed transaction
pg_last_xact_replay_timestamp

8.3. Типичные проблемы и решения

Проблема	Симптомы	Решение
Диск заполнен WAL	No space left on device	Удалить неактивные слоты; увеличить max_slot_wal_keep_size
Реплика сильно отстаёт	replay_lag > 10 min	Увеличить wal_keep_size; проверить I/O и сеть standby
Sync standby недоступен	Primary «зависает» (COMMIT не отвечает)	Добавить ANY 1 с несколькими standby; Patroni управляет автоматически
Конфликт на standby	ERROR: canceling statement due to conflict	hot_standby_feedback = on; увеличить max_standby_streaming_delay
Слот не удаляется	replication slot is active	Остановить подписку или WAL-sender; затем pg_drop_replication_slot()
Logical repl. конфликт	duplicate key на subscriber	Удалить дубликат; или ALTER SUBSCRIPTION ... SKIP

---

9. Упражнения

Задание 1: Streaming Replication на одной машине

Настройте потоковую репликацию между двумя инстансами PostgreSQL на одном компьютере (разные порты: 5432 и 5433):

1. Инициализируйте primary на порту 5432.
2. Создайте роль для репликации, настройте pg_hba.conf.
3. Выполните pg_basebackup для создания standby.
4. Запустите standby на порту 5433.
5. Проверьте репликацию: создайте таблицу и вставьте данные на primary, прочитайте на standby.
6. Выполните ручной failover (promote standby).
7. Подключите бывший primary как новый standby через pg_rewind.

Задание 2: Logical Replication с фильтрацией

1. Создайте базу shop_source (publisher) и analytics_target (subscriber).
2. В shop_source создайте таблицы: products, orders, customers, order_items.
3. Создайте публикацию только для orders и order_items с фильтром WHERE status IN ('confirmed', 'delivered').
4. Настройте подписку в analytics_target.
5. Проверьте: записи со статусом pending не должны появляться на subscriber.
6. Добавьте столбец в таблицу на обоих серверах, обновите подписку.

Задание 3: Мониторинг и анализ

1. Настройте streaming replication (из задания 1).
2. Создайте нагрузку на primary (INSERT 1 000 000 строк в цикле).
3. Наблюдайте за pg_stat_replication: отслеживайте replay_lag, sent_lsn, replay_lsn.
4. Создайте представление v_replication_dashboard, показывающее все ключевые метрики.
5. Добавьте искусственную задержку на standby (recovery_min_apply_delay = '30s') и наблюдайте изменение lag.

Задание 4: комплексная HA-архитектура (продвинутый)

Разверните с помощью Docker Compose полноценный HA-кластер:

1. 3 узла PostgreSQL + Patroni
2. 3 узла etcd
3. HAProxy для маршрутизации R/W и R/O трафика
4. PgBouncer для пулинга соединений

Выполните:

• Плановый switchover через patronictl
• Аварийный failover (остановите контейнер с primary)
• Проверьте, что приложение автоматически переключается
• Верните упавший узел и убедитесь в автоматическом восстановлении

---

10. Полезные ресурсы

Официальная документация:

• PostgreSQL: Streaming Replication
• PostgreSQL: Logical Replication
• PostgreSQL: WAL Configuration

Инструменты HA:

• Patroni — GitHub
• Patroni Documentation
• PgBouncer
• pgBackRest
• WAL-G

Практические руководства:

• PostgresPro: Репликация
• Zalando: Patroni best practices

---

Итоги урока

Вы изучили два основных механизма репликации PostgreSQL и инструменты для обеспечения высокой доступности:

• WAL — фундамент надёжности и репликации; понимание его структуры критически важно для любой HA-настройки.
• Streaming Replication — побайтовая копия всей базы в реальном времени; идеальна для disaster recovery и read scaling; standby работает только на чтение.
• Logical Replication — выборочная репликация на уровне строк; незаменима для миграций между версиями, кросс-платформенной интеграции и ETL-сценариев.
• Patroni + etcd — стандарт де-факто для автоматического failover с временем переключения 30-60 секунд.
• PgBouncer + HAProxy — маршрутизация клиентских соединений и пулинг, необходимые для прозрачного failover на стороне приложения.

Выбор между streaming и logical репликацией зависит от конкретной задачи: для полного HA-кластера используйте streaming replication с Patroni, для миграций и выборочной синхронизации — logical replication.