В прошлых уроках мы разбирали физическую репликацию: реплика побайтово повторяет WAL основного сервера и остаётся его точной копией. Это надёжно, но негибко - нельзя выбрать отдельные таблицы, нельзя реплицировать между разными версиями PostgreSQL, нельзя писать на реплику. Логическая репликация снимает эти ограничения: она передаёт не блоки данных на диске, а сами строки - что вставили, что обновили, что удалили. В этом уроке разберём механику PUBLICATION/SUBSCRIPTION, типовые сценарии (миграция с минимальным простоем, разнос данных по узлам) и пройдёмся по экосистеме высокой доступности: Patroni, Citus, pgpool. Цель - понять не только как настроить репликацию в postgresql, но и когда какой инструмент брать.
Как это работает
В основе логической репликации лежит механизм logical decoding. Сервер-источник читает свой WAL, но не отдаёт сырые байты, а прогоняет их через плагин-декодер pgoutput, который превращает низкоуровневые записи в понятные события уровня строк: INSERT таблицы X с такими-то значениями, UPDATE по такому-то ключу. Эти события публикуются через объект PUBLICATION.
На стороне получателя создаётся SUBSCRIPTION. Она открывает специальный слот репликации на источнике (чтобы тот не удалял ещё не доставленный WAL) и запускает два фоновых процесса: walsender на источнике и apply worker на приёмнике. Apply worker применяет полученные изменения обычными INSERT/UPDATE/DELETE - именно поэтому таблицы на двух серверах могут физически отличаться: разный набор индексов, разные неучаствующие столбцы, даже разная мажорная версия PostgreSQL.
Ключевое требование - идентификация строк. Чтобы применить UPDATE и DELETE, приёмнику нужно понять, какую именно строку трогать. По умолчанию используется первичный ключ. Если PK нет, придётся задать REPLICA IDENTITY FULL, и тогда в WAL пишется вся старая версия строки целиком - это дорого и медленно. Поэтому таблицы без первичного ключа - главный источник боли в логической репликации.
Важно понимать границы: логическая репликация переносит DML, но не переносит DDL. Создание новой таблицы, добавление колонки, новый индекс - всё это нужно накатывать на приёмник руками или своей оснасткой. Также по умолчанию не реплицируются TRUNCATE до явного указания и не передаются последовательности (sequence), то есть значения IDENTITY и serial на приёмнике придётся выставлять отдельно при переключении.
SQL и примеры
Возьмём демобазу Авиаперевозки. Допустим, аналитике нужны только две таблицы - flights и airports - на отдельном сервере, а не вся база.
На сервере-источнике публикуем нужные таблицы:
Код: Выделить всё
-- публикуем только две таблицы, а не всю базу
CREATE PUBLICATION analytics_pub
FOR TABLE bookings.flights, bookings.airports;
-- посмотреть, что входит в публикацию
SELECT * FROM pg_publication_tables
WHERE pubname = 'analytics_pub';
Код: Выделить всё
-- приёмник подключается к источнику и тянет данные
CREATE SUBSCRIPTION analytics_sub
CONNECTION 'host=src_host dbname=demo user=repl password=secret'
PUBLICATION analytics_pub;
Код: Выделить всё
-- на приёмнике: статус воркеров подписки
SELECT subname, srrelid::regclass, srsubstate
FROM pg_subscription_rel r
JOIN pg_subscription s ON s.oid = r.srsubid;
-- srsubstate: 'r' = готова и реплицируется
-- на источнике: отставание подписчика в байтах
SELECT application_name,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
Код: Выделить всё
-- только вылеты из Шереметьево (PG 15+)
CREATE PUBLICATION svo_pub
FOR TABLE bookings.flights
WHERE (departure_airport = 'SVO');
Частые грабли
- Таблица без первичного ключа: UPDATE/DELETE либо не реплицируются, либо требуют REPLICA IDENTITY FULL с просадкой производительности.
- Забыли про DDL: добавили колонку на источнике, на приёмнике её нет - apply worker встаёт с ошибкой, и слот начинает копить WAL, заполняя диск источника.
- Брошенная подписка: удалили приёмник, но слот на источнике остался. Неактивный слот удерживает WAL бесконечно. Чистите через pg_drop_replication_slot.
- Конфликты на приёмнике: если в реплицируемую таблицу кто-то пишет локально, может возникнуть нарушение уникальности и репликация остановится.
- Последовательности не едут сами - после переключения IDENTITY и serial на новом сервере отстают, и новые вставки ломаются по дубликату ключа.
- wal_level должен быть logical, max_replication_slots и max_wal_senders заданы с запасом, иначе подписка просто не стартует.
- До PostgreSQL 16 реплику нельзя было использовать как источник публикации, а слоты не переживали failover - это исправили в 16 и 17 соответственно.
Понадобятся два кластера PostgreSQL (можно два экземпляра на разных портах).
- На обоих в postgresql.conf выставьте wal_level = logical и перезапустите серверы.
- На источнике создайте таблицу orders (id int primary key, amount numeric) и наполните парой строк.
- На приёмнике создайте таблицу orders с такой же структурой, но пустую.
- На источнике выполните CREATE PUBLICATION p1 FOR TABLE orders.
- На приёмнике создайте SUBSCRIPTION на источник и проверьте, что начальные строки скопировались.
- Вставьте новую строку на источнике и убедитесь, что она появилась на приёмнике через секунду.
- Удалите таблицу orders на приёмнике (не удалив подписку) и посмотрите в логах источника, как растёт неактивный слот - затем аккуратно уберите подписку и слот.
- Чем логическая репликация принципиально отличается от физической и какие новые возможности это даёт?
- Почему таблице нужна REPLICA IDENTITY и когда приходится ставить FULL?
- Что произойдёт со слотом репликации, если подписчик надолго пропадёт, и чем это грозит источнику?
- Какие объекты НЕ переносятся логической репликацией автоматически и как это учесть при миграции?
- В каких задачах вы выберете Patroni, а в каких Citus, и почему это разные инструменты?
- Что изменилось в логической репликации в PostgreSQL 16 и 17 относительно версии 15?
