Логическая репликация PostgreSQL с pglogical на Debian 11

Введение

PostgreSQL — свободная реляционная СУБД с акцентом на расширяемость и соответствие SQL. pglogical — расширение, реализующее логическую потоковую репликацию на уровне базы данных по модели publish/subscribe: вы реплицируете конкретные наборы таблиц или базы, а не весь кластер целиком.

В этом руководстве шаг за шагом показано, как настроить логическую репликацию между двумя Debian 11-серверами с PostgreSQL 15 и pglogical: подготовка репозиториев, установка, конфигурация WAL для логической декодировки, создание узлов publisher/subscriber, включение таблиц в репликацию, тестирование и отработка типичных проблем.

Важно: pglogical работает на уровне баз данных и требует прав суперпользователя для создания узлов и синхронизации. Для физической репликации или репликации всего кластера рассмотрите альтернативы (см. раздел «Альтернативные подходы»).

Что вы получите из этого руководства

• Полная пошаговая инструкция по установке PostgreSQL 15 и pglogical на Debian 11.
• Настройка конфигурации PostgreSQL для логической репликации.
• Пример создания пользователя, базы и таблицы, публикации и подписки.
• Проверка корректности репликации и процедуры отладки при проблемах.
• Чек-листы, шаблоны конфигураций, рекомендации по безопасности и мониторингу.

Требования

• Два или более сервера с Debian 11 (пример ниже использует два сервера).
• Ненулевой пользователь с sudo/ root-привилегиями.
• Прямой сетевой доступ между серверами по внутренним IP (порт 5432).

Пример инфраструктуры, используемой в руководстве:

Быстрая последовательность действий

1. Настроить /etc/hosts на всех серверах.
2. Добавить официальный репозиторий PostgreSQL и ключ GPG.
3. Установить postgresql и postgresql-15-pglogical.
4. Настроить firewall (ufw) — разрешить SSH и порт 5432 для доверенной сети.
5. В postgresql.conf установить wal_level = ‘logical’ и shared_preload_libraries = ‘pglogical’.
6. В pg_hba.conf добавить разрешения для подключения и репликации.
7. Создать суперпользователя и базу (appuser/appdb), включить расширение pglogical.
8. На publisher создать node и replication set, добавить таблицы.
9. На subscriber создать node, создать подписку и дождаться синхронизации.

Настройка /etc/hosts и репозитория PostgreSQL

Выполните шаги на всех серверах.

Откройте файл hosts для редактирования:

sudo nano /etc/hosts

Добавьте строки (замените IP-адреса на свои):

192.168.5.25    pgdg01
192.168.5.26    pgdg02

Сохраните и закройте файл.

Установите базовые зависимости:

sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates

Добавьте ключ GPG и репозиторий PostgreSQL:

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/pgdg.gpg > /dev/null 2>&1
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Обновите индекс пакетов:

sudo apt update

После добавления репозитория вы увидите его в списке.

Установка PostgreSQL и pglogical

Установите PostgreSQL (репозиторий предоставляет несколько версий; в примере используем 15):

sudo apt install postgresql

Проверьте статус службы:

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

Вы должны увидеть “enabled” и “active (running)”.

Установите пакет pglogical для PostgreSQL 15:

sudo apt install postgresql-15-pglogical

После установки переходите к конфигурации.

Настройка UFW

Установите ufw и разрешите SSH:

sudo apt install ufw
sudo ufw allow OpenSSH

Разрешите доступ к порту PostgreSQL 5432 только из доверенной подсети (пример 192.168.5.0/24):

sudo ufw allow from 192.168.5.0/24 to any port 5432

Включите фаервол:

sudo ufw enable

Проверьте статус:

sudo ufw status

Вы должны увидеть статус Active и правила для OpenSSH и 5432.

Конфигурация PostgreSQL для репликации

WAL (Write-Ahead Logging) обеспечивает устойчивость данных. Для логической репликации необходим wal_level = ‘logical’, а также загрузка расширения pglogical в параметре shared_preload_libraries.

Подключитесь к psql под юзером postgres:

sudo -u postgres psql

Проверьте текущие параметры:

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

Обычно по умолчанию wal_level = ‘replica’.

Выйдите из psql командой \q.

Откройте конфигурацию PostgreSQL для редактирования (пример для версии 15):

sudo nano /etc/postgresql/15/main/postgresql.conf

Установите listen_addresses на локальный и внутренний IP сервера. Пример для pgdg01:

listen_addresses = 'localhost, 192.168.5.25'

Для pgdg02:

listen_addresses = 'localhost, 192.168.5.26'

Добавьте параметры для логической репликации (на обеих нодах):

max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'

Сохраните файл.

Отредактируйте pg_hba.conf, чтобы разрешить подключения от доверенной подсети (пример 192.168.5.0/24):

sudo nano /etc/postgresql/15/main/pg_hba.conf

Добавьте в конец:

# IPv4 local connections:
host    all             all             192.168.5.0/24          scram-sha-256
# replication privilege.
host    replication     all             192.168.5.0/24          scram-sha-256

Сохраните и закройте.

Перезапустите PostgreSQL, чтобы применить изменения:

sudo systemctl restart postgresql

Проверьте, что PostgreSQL слушает на нужном IP/порту:

ss -tulpn | grep 5432

Подключитесь к psql и ещё раз проверьте wal_level:

sudo -u postgres psql
select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
\q

Вы должны увидеть wal_level = logical и shared_preload_libraries содержащий pglogical.

Создание пользователя и базы для pglogical

pglogical работает на уровне базы данных и требует аккаунта с правами SUPERUSER для инициализации узла.

Подключитесь к psql:

sudo -u postgres psql

Создайте суперпользователя и базу данных (пример):

CREATE ROLE appuser WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'Str0ngP4ssw0rd';
CREATE DATABASE appdb WITH OWNER appuser;

Проверьте списки ролей и баз:

\du
\l

Подключитесь к базе appdb и включите расширение:

\c appdb
CREATE EXTENSION pglogical;
\dx

Настройка репликации таблиц через pglogical

В pglogical вы создаёте provider node (на publisher), replication set и добавляете в него таблицы. На subscriber создаёте node и подписку.

Настройка pgdg01 как Publisher

Подключитесь к базе appdb на pgdg01 и создайте тестовую таблицу teachers:

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

Добавьте данные:

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES
(1, 'Alice', 'Wonderland', 'Math'),
(2, 'Bob', 'Rista', 'Physics'),
(3, 'John', 'Bonas', 'English');

Проверьте:

select count(*) from teachers;
select * from teachers;

Создайте provider node (пример):

select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

Создайте replication set и добавьте таблицу:

select pglogical.create_replication_set('replication_set');
select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);

Настройка pgdg02 как Subscriber

На pgdg02 создайте файл паролей для автоматической аутентификации (выполняется от имени пользователя postgres):

sudo -u postgres nano /var/lib/postgresql/.pgpass

Добавьте строки (замените пароли/юзеров на свои):

pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg01:5432:replication:appuser:Str0ngP4ssw0rd
pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg02:5432:replication:appuser:Str0ngP4ssw0rd

Сделайте файл доступным только для владельца:

sudo chmod 0600 /var/lib/postgresql/.pgpass

Перезагрузите конфигурацию PostgreSQL:

sudo -u postgres psql
select pg_reload_conf();
\q

Подключитесь к базе appdb на pgdg02 и создайте структуру таблицы (схема должна совпадать):

sudo -u postgres psql
\c appdb
CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);
select count(*) from teachers;
select * from teachers;

Создайте node и подписку на pgdg02:

select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');
select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

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

select * FROM pglogical.show_subscription_status();
SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Проверьте, что данные синхронизировались:

select count(*) from teachers;
select * from teachers;

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

На publisher вставьте дополнительные строки:

sudo -u postgres psql -U appuser -h pgdg01 -d appdb
INSERT INTO teachers(id, first_name, last_name, subject)
VALUES
(4, 'Ian', 'Gibson', 'Geography'),
(5, 'Tom', 'Riddle', 'Biology'),
(6, 'Jared', 'Dunn', 'Chemistry');

Проверьте локально и на subscriber, что данные появились:

select count(*) from teachers;
select * from teachers;

Если репликация не работает — смотрите раздел «Отладка и типичные ошибки».

Отладка и типичные ошибки

Пошаговая методика диагностики при проблемах с pglogical:

1. Проверка сетевого соединения: ping по hostnames и telnet/ss на 5432.
2. Логи PostgreSQL (обычно /var/log/postgresql/postgresql-15-main.log) — ищите ошибки с pglogical, wal, репликацией.
3. Проверка статуса подписки:

select * from pglogical.show_subscription_status();
select * from pglogical.show_subscription_status(false);

4. Убедитесь, что в pg_hba.conf разрешено подключение от адресов provider/subscriber.
5. Проверьте, что файл .pgpass корректен и имеет права 0600.
6. На subscriber выполните SELECT pglogical.wait_for_subscription_sync_complete(‘subscription1’) чтобы дождаться начальной синхронизации.
7. Сравните схемы таблиц: несовпадение типов/порядка колонок может приводить к ошибкам.
8. Если WAL не генерируется: проверьте wal_level, shared_preload_libraries и наличие необходимых слотов репликации.

Примеры ошибок и решения:

• Ошибка подключения — проверьте firewall, pg_hba.conf и .pgpass.
• “permission denied” — убедитесь, что пользователь appuser имеет SUPERUSER (требуется для pglogical версии, использованной в примере).
• Несоответствие схемы — создайте таблицы на subscriber заранее с той же структурой.

Альтернативные подходы и когда применять их

• Встроенная логическая репликация PostgreSQL (pg_create_logical_replication_slot / CREATE PUBLICATION / CREATE SUBSCRIPTION). Подходит для стандартного функционала репликации на уровне таблиц; не требует pglogical.
• Физическая (streaming) репликация — реплицирует весь кластер (wal shipping). Лучше для standby, failover и точных копий кластера.
• Bucardo — трибьют/многомастерная репликация; полезна для сложных сценариев с конфликтами.
• Slony-I — устаревший триггерный репликатор.

Выбор:

• Если нужна репликация отдельных таблиц/баз и более тонкий контроль — логическая (pglogical или встроенная).
• Если нужна репликация всего кластера и быстрый switchover — физическая.

Ментальные модели и эвристики

• Разделение ответственности: pglogical = логика на уровне базы и таблицы; физическая репликация = зеркалирование кластера.
• Синхронизация данных — асинхронный процесс: ожидайте задержку, особенно при большой нагрузке.
• Всегда тестируйте на копиях данных перед внедрением в продакшен.

Чек-лист перед вводом в эксплуатацию

• Репозиторий PostgreSQL добавлен на всех узлах.
• PostgreSQL 15 и пакет postgresql-15-pglogical установлены.
• listen_addresses настроен, wal_level = logical, shared_preload_libraries содержит pglogical.
• pg_hba.conf содержит правила для соединений и replication.
• UFW настроен: SSH + 5432 для доверенной подсети.
• Созданы user appuser (SUPERUSER) и база appdb.
• Расширение pglogical установлено в appdb на всех узлах.
• Созданы provider node, replication set и подписка.
• Выполнены тестовые вставки и проверена синхронизация.
• Настроен мониторинг и бэкапы.

Playbook: восстановление синхронизации (короткий план)

1. Проверка сети и логов микросервиса PostgreSQL.
2. Проверка статуса подписки и слотов:

select * from pglogical.show_subscription_status();
select * from pg_replication_slots;

3. При необходимости пересоздать подписку: DROP SUBSCRIPTION + CREATE SUBSCRIPTION (учтите, что при DROP подписки данные на subscriber сохраняются, но начальная синхронизация повторится, если включён synchronize_data).
4. Если требуется восстановление полного состояния, можно удалить таблицы на subscriber и пересоздать подписку с synchronize_data := true.

Безопасность и GDPR

• Используйте scram-sha-256 в pg_hba.conf (пример выше) вместо md5.
• Храните .pgpass с правами 0600.
• Шифруйте трафик PostgreSQL (TLS) при прохождении через ненадёжные сети.
• Минимизируйте права пользователей: для продакшен-сценариев рассмотрите роли с минимально необходимыми привилегиями и используйте отдельного суперпользователя только для инициализации расширений.
• Для соответствия GDPR: хранение реплик данных в других юрисдикциях требует оценки соответствия; а также настройте ротацию и удаление данных в соответствии с политиками.

Мониторинг и метрики (рекомендации)

Следите за следующими показателями:

• Задержка репликации (lag) — по времени и по WAL position.
• Размер WAL и скорость генерации WAL.
• Количество слотов репликации (max_replication_slots) и занятые слоты.
• Состояние подписок pglogical.show_subscription_status().

Пример SQL для простой проверки задержки:

SELECT now() - pg_last_wal_receive_lsn(pg_current_wal_lsn());

(Встроенные функции зависят от версии PostgreSQL; адаптируйте под вашу версию.)

Шаблоны конфигурации и сниппеты

pg_hba.conf (пример):

# allow local connections
local   all             postgres                                peer
# allow appuser from internal network
host    all             appuser         192.168.5.0/24          scram-sha-256
# replication
host    replication     all             192.168.5.0/24          scram-sha-256

Файл /var/lib/postgresql/.pgpass (пример):

pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd

(Права 0600)

Команды создания узлов/подписок (соберите в скрипт deploy_pglogical.sh при необходимости):

select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');
select pglogical.create_replication_set('replication_set');
select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);

и на подписчике:

select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');
select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

Критерии приёмки

• Данные, вставленные на publisher, видны на subscriber в течение ожидаемого окна репликации (SLA).
• pglogical.show_subscription_status() показывает состояние replicating или synced.
• Слоты репликации не остаются в состоянии error.
• Логи не содержат критических ошибок связаных с wal или pglogical.

Частые ошибки и как их избежать

• Неправильные права в .pgpass → ошибки аутентификации. Устанавливайте 0600.
• Несопоставимые схемы таблиц между издателем и подписчиком → синхронизация может завершиться с ошибкой; всегда проверяйте совпадение типов и nullability.
• Отсутствие параметра shared_preload_libraries = ‘pglogical’ → расширение не загрузится.
• Ограничение max_replication_slots или max_wal_senders → увеличьте в конфиге, если нужно.

Когда pglogical не подходит (контрпример)

• Если нужен полнокластерный hot-standby (физическая репликация подходит лучше).
• Если требуется трансформационная репликация с конфликт-резолюцией между несколькими мастерами — нужны специализированные решения, например Bucardo.
• Если нет возможности предоставить SUPERUSER для init — pglogical старых версий требует суперпользователя.

Рекомендации по масштабированию

• Для репликации на множество подписчиков создавайте отдельные replication sets и подписки; следите за количеством слотов.
• Используйте балансировщики нагрузки (HAProxy) для распределения чтений между нодами, но учитывайте логику согласованности данных.
• Для больших таблиц планируйте начальную синхронизацию в «тихое» окно: synchronize_data создаёт полную копию.

Короткая сводка и дальнейшие шаги

Вы установили PostgreSQL 15 и pglogical, сконфигурировали логическую репликацию отдельной таблицы между двумя Debian 11-серверами и проверили работу на примере таблицы teachers. Дальше можно:

• Добавить дополнительные таблицы или базы в replication_set.
• Настроить TLS для защищённого соединения между узлами.
• Внедрить мониторинг и алёртинг по задержке реплики.
• Проработать сценарии failover/rollback и автоматизации восстановления.

Полезные ссылки

• Официальная документация PostgreSQL — https://www.postgresql.org/docs/
• Документация pglogical — ищите актуальную версию на сайте разработчика расширения.

Итог: pglogical — удобный инструмент для логической репликации отдельных баз/таблиц. Он подходит для миграции данных, распределённых приложений и сценариев, где не требуется полная копия кластера.