Logical Replication — это механизм репликации данных в PostgreSQL, который позволяет передавать изменения данных (INSERT, UPDATE, DELETE) из одной базы данных в другую. В отличие от потоковой (физической) репликации, логическая репликация работает на уровне строк и команд, а не на уровне файлов WAL (Write-Ahead Log).

Другими словами, логическая репликация в PostgreSQL — это механизм асинхронной репликации на уровне строк, который подходит для репликации между разными инстансами PostgreSQL и для синхронизации отдельных таблиц.

Как работает Logical Replication?

Когда речь идет о логической репликации, используются следующие понятия

Публикация (Publication)

В основной (публикующей) базе (publisher) создается publication — специальная сущность, указывающая, какие таблицы и изменения должны передаваться.

Подписка (Subscription)

В принимающей базе (subscriber) создается subscription, которая получает изменения от публикации и применяет их в своих таблицах.

Передача данных

Когда в publisher происходят изменения, они локально фиксируются в WAL на физическом уровне, а логический декодер передает изменения подписчику через специальный логический поток.

Применение изменений

Подписчик (subscriber) получает транзакции и применяет их в той же последовательности, что и на публикации.

Настройка Logical Replication в PostgreSQL

1. Включение логической репликации

Для работы необходимо установить несколько параметров в postgresql.conf:

wal_level = logical

max_replication_slots = 10

max_wal_senders = 10

После этого следует перезапустить PostgreSQL.

2. Создание публикации на основном сервере (Publisher)

CREATE PUBLICATION my_publication FOR TABLE my_table;

Опции:

• FOR TABLE my_table — указывает, какие таблицы включены в репликацию.
• FOR ALL TABLES — репликация всех таблиц.

После создания, можно проверить список публикаций:

SELECT * FROM pg_publication;

3. Настройка подписки на подписчике (Subscriber)

На подписчике (subscriber) создается такая же таблица my_table, как на publisher.

Затем добавляется подписка:

CREATE SUBSCRIPTION my_subscription

CONNECTION 'host=192.168.1.100 dbname=mydb user=replicator password=secret'

PUBLICATION my_publication;

где:

• CONNECTION — параметры подключения к publisher.
• PUBLICATION my_publication — подписываемся на указанную публикацию.

Проверить статус подписки можно командой:

SELECT * FROM pg_subscription;

Преимущества Logical Replication

• Гибкость: можно реплицировать только определенные таблицы или даже только определенные изменения (например, только INSERT).
• Мультинаправленная репликация: можно передавать данные между несколькими серверами.
• Не блокирует базу: в отличие от физической репликации, не требует полного копирования базы.
• Можно реплицировать между разными версиями PostgreSQL (например, из 13-й версии в 15-ю).

Ограничения Logical Replication

• Не реплицирует DDL (изменения структуры таблиц, например ALTER TABLE).
• Не реплицирует SEQUENCES (нужно вручную синхронизировать SERIAL и BIGSERIAL).
• Не подходит для полного отказоустойчивого кластера, так как подписчик не является точной копией публикации (он может изменять данные независимо).

Репликация связанных таблиц

PostgreSQL позволяет реплицировать данные между связанными таблицами, включая те, которые связаны внешними ключами (Foreign Keys). Логическая репликация работает на уровне отдельных таблиц, позволяя выбирать конкретные таблицы для репликации. Важно учитывать несколько моментов:

• Если вы хотите реплицировать таблицу, связанную с другой таблицей через внешний ключ, обе эти таблицы должны быть включены в конфигурацию логической репликации. В противном случае целостность данных может нарушиться.
• Порядок репликации. При репликации связанных таблиц важно следить за порядком, чтобы сначала были вставлены записи в родительские таблицы, а затем — в дочерние. Это поможет избежать ошибок целостности данных.
• Триггеры и ограничения. Убедитесь, что триггеры и ограничения, обеспечивающие целостность данных, работают корректно на стороне подписчика. Это особенно актуально для внешних ключей и уникальных ограничений.
• Согласованность данных. Поскольку логическая репликация является асинхронной, существует небольшая задержка между операциями на источнике и подписчике. Это нужно учитывать при работе с зависимыми данными.
• Конфигурация репликации. Для настройки логической репликации вам потребуется указать список таблиц, которые будут реплицироваться. Например, если у вас есть две таблицы orders и order_items, связанные внешним ключом, обе эти таблицы должны быть указаны в конфигурации публикации.

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

-- Создание публикации на сервере-источнике

CREATE PUBLICATION my_publication FOR TABLE orders, order_items;

-- Подписка на сервере-подписчике

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=source_host port=5432 user=rep_user password=password dbname=my_db' PUBLICATION my_publication;