Проблемы логической репликации PostgreSQL на Windows 10

Проблема с зависанием процесса создания логической репликации в PostgreSQL на Windows 10 является известным явлением, особенно при работе с большими базами данных. Основная причина - конфликты при автоматическом создании слота репликации во время выполнения команды CREATE SUBSCRIPTION.

Содержание

• Причины зависания при создании подписки
• Проблема падения воркера с кодом выхода 1
• Настройка параметров PostgreSQL для решения проблем
• Дополнительные решения и рекомендации
• Проверка прав доступа и безопасности
• Заключение

Причины зависания при создании подписки

Основная проблема заключается в том, что при выполнении CREATE SUBSCRIPTION без предварительной настройки слота репликации происходит взаимоблокировка процессов. Исследования показывают, что это распространенная проблема логической репликации в PostgreSQL, особенно на Windows-системах.

Основные причины зависания:

1. Конфликт при создании слота репликации - автоматическое создание слота во время CREATE SUBSCRIPTION может приводить к взаимоблокировке с другими процессами
2. Ограничения Windows на одновременные подключения - механизм репликации может конфликтовать с ограничениями операционной системы
3. Блокировка таблиц издателя во время синхронизации - большие базы данных (ваш случай - 650 МБ с миллионами записей) приводят к длительным блокировкам

Рекомендуемый подход, как вы уже обнаружили, - ручное создание слота:

-- Сначала создаем слот вручную
SELECT pg_create_logical_replication_slot('im_sub', 'pgoutput');

-- Затем создаем подписку без автоматического создания слота
CREATE SUBSCRIPTION im_sub 
CONNECTION 'dbname=op_market_2 host=localhost port=5432 user=postgres password=postgres' 
PUBLICATION im_pub
WITH (copy_data = true, create_slot = false);

Проблема падения воркера с кодом выхода 1

Ошибка “logical replication worker exited with exit code 1” указывает на аварийное завершение фонового процесса репликации. Это может вызываться несколькими факторами:

Основные причины аварийного завершения:

1. Превышение таймаутов - процесс может завершаться при превышении времени ожидания ответа от издателя
2. Нехватка ресурсов - Windows-система может ограничивать доступные ресурсы для фоновых процессов
3. Баги версий PostgreSQL - в версиях 10-14 известны проблемы с логической репликацией

Решения для стабилизации воркера:

1. Увеличьте таймауты в postgresql.conf:

   ini

   wal_sender_timeout = 60s  # Увеличить с 60 секунд по умолчанию
   max_logical_replication_workers = 4
   max_worker_processes = 8
   

2. Проверьте параметры памяти:

   ini

   shared_buffers = 1GB
   effective_cache_size = 3GB
   work_mem = 64MB
   

3. Проверьте наличие блокировок в процессе репликации:

   sql

   SELECT * FROM pg_locks WHERE relation IN (
       SELECT oid FROM pg_class WHERE relname IN ('link_accessories', 'categories')
   );
   

Настройка параметров PostgreSQL для решения проблем

Для корректной работы логической репликации на Windows 10 рекомендуется проверить и настроить следующие параметры:

Критические параметры в postgresql.conf:

# Логическая репликация
max_logical_replication_workers = 4
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 2

# Таймауты
wal_sender_timeout = 60s
wal_level = logical
max_wal_senders = 5
logical_decoding_work_mem = 64MB

# Производительность
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 64MB
maintenance_work_mem = 256MB

Параметры подключения в pg_hba.conf:

# Разрешаем репликацию
host    replication    postgres    127.0.0.1/32    scram-sha-256
host    replication    postgres    ::1/128         scram-sha-256
host    all            postgres    127.0.0.1/32    scram-sha-256

Важно: После изменения конфигурационных файлов необходимо перезапустить службу PostgreSQL.

Дополнительные решения и рекомендации

1. Разделение процесса создания подписки

Для больших баз данных рекомендуется пошаговый подход:

-- Шаг 1: Создать публикацию без таблиц для теста
CREATE PUBLICATION im_pub_test;

-- Шаг 2: Создать подписку с минимальным набором
CREATE SUBSCRIPTION im_sub_test 
CONNECTION 'dbname=op_market_2 host=localhost port=5432 user=postgres password=postgres' 
PUBLICATION im_pub_test
WITH (copy_data = false, create_slot = false);

-- Шаг 3: Добавлять таблицы постепенно
ALTER PUBLICATION im_pub_test ADD TABLE link_accessories;
ALTER PUBLICATION im_pub_test ADD TABLE categories;

2. Мониторинг состояния репликации

-- Проверка состояния подписки
SELECT * FROM pg_subscription;

-- Проверка состояния воркеров
SELECT * FROM pg_stat_activity WHERE backend_type = 'logical replication worker';

-- Проверка слотов репликации
SELECT * FROM pg_replication_slots;

3. Использование инструментов диагностики

Включите расширенное логирование для диагностики:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Проверка прав доступа и безопасности

На Windows-системах часто возникают проблемы с правами доступа для фоновых процессов PostgreSQL:

1. Права пользователя службы

Запустите службу PostgreSQL от имени пользователя с правами администратора:

# Проверка текущего пользователя службы
sc queryex postgresql

# Изменение пользователя службы (через Services.msc)
net stop postgresql
net start postgresql /user:Administrator

2. Проверка прав на файлы

-- Проверка прав пользователя PostgreSQL на объекты
SELECT 
    schemaname,
    tablename,
    has_table_privilege('postgres', tablename, 'SELECT') as can_select,
    has_table_privilege('postgres', tablename, 'INSERT') as can_insert,
    has_table_privilege('postgres', tablename, 'UPDATE') as can_update,
    has_table_privilege('postgres', tablename, 'DELETE') as can_delete
FROM pg_tables 
WHERE schemaname = 'public' AND tablename IN ('link_accessories', 'categories');

3. Разрешения Windows Defender

Добавьте пути к данным PostgreSQL в исключения Windows Defender:

• C:\Program Files\PostgreSQL\14\data
• C:\Program Files\PostgreSQL\14\bin

Заключение

Проблемы с логической репликацией в PostgreSQL на Windows 10 связаны с несколькими факторами:

1. Автоматическое создание слота репликации часто приводит к взаимоблокировкам - всегда используйте ручное создание слота с последующим созданием подписки с create_slot = false

2. Падение воркера с кодом выхода 1 обычно вызвано нехваткой ресурсов или превышением таймаутов - увеличьте wal_sender_timeout и проверьте системные ресурсы

3. Windows-специфические ограничения требуют особого внимания к правам доступа и настройке сервиса PostgreSQL

Рекомендуемые действия:

• Всегда создавайте слот репликации вручную перед созданием подписки
• Настройте параметры PostgreSQL для работы с большими объемами данных
• Мониторьте состояние воркеров и слотов репликации
• Проверьте права доступа пользователя PostgreSQL на все реплицируемые объекты

Для вашей ситуации с базами данных 650 МБ и миллионами записей предложенный обходной путь с ручным созданием слота является наиболее надежным решением.