Вводная

Предположим, что мы настроили асинхронную репликацию на двух хостах с PostgreSQL. С какой проблемой могут столкнуться потенциальные пользователи? На мой взгляд самой очевидной является отставание реплики от ведущего мастера. Не страшно, если юзер (или его клиентское приложение) открыло BEGIN. В этот момент может разорваться соединение, и все незакомиченные изменения будут откачены.
Но возможна ситуация, когда ведущая СУБД выдаст пользователю COMMIT, резко упадёт, а на слейв не успеет дойти подтверждение транзакции. Тогда при восстановлении реплики эти данные попадут под ROLLBACK (так как на самом деле на ведомую ноду передаются не конечные данные, а вся последовательность действий), а пользователь будет ошибочно считать, что его данные были успешно сохранены.

Синхронная репликация

Решим эту проблему в два счёта! Переходим в PGDATA (по умолчанию для Windows это %ProgramFiles%\PostgreSQL\9.4\bin) и запускаем утилиту подключения к СУБД:

psql.exe -h <ip-мастера> -U postgres

Указываем IP или DNS реплик, запись с которыми будет синхронизирована. Можно использовать параметр "*", так как в случае использования физической репликации количество подчинённых хостов будет ограничено выделенными для них слотами:

ALTER SYSTEM SET synchronous_standby_names = '10.10.1.2, 10.10.1.3'; 

Задаём мастеру уровень синхронизации:

ALTER SYSTEM SET synchronous_commit = 'on';

Все изменения попадут в файл %PGDATA%\postgresql.auto.conf и перекочуют в основной postgresql.conf после перезапуска службы СУБД. Но нам не терпится активировать изменения и мы пересчитываем конфигурацию встроенной функцией, которая возвращает «t» в случае успеха:

SELECT pg_reload_conf(); # 

Примечание: указанные выше настройки на работающие реплики внести нельзя — за эти проверки большое спасибо предусмотрительному сообществу разработчиков postgres. Не все параметры, например max_replication_slot, можно активировать без перезапуска СУБД (некоторые вообще приходится менять на этапе компиляции исходников)).

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

SELECT (slot_name, active) from pg_replication_slots; 

Что мы в итоге получили?

Технически теперь COMMIT транзакции первым оказывается на реплике). Появляется возможность читать данные со слейва, разгружая основной сервер. В качестве бонуса — селекты пользователей будут вносить изменения только на ноде, к которой было обращение.

При больших нагрузках скорость работы клиентов снизиться: здесь скажется задержка ведомой нод при подтверждении записи транзакций. «Потери» минимизируются тюнингом постгреса и грамотной планировкой работы с БД. Например, при записи данных с низким приоритетом есть возможность задать параметр сессии:

SET synchronous_commit = 'local'; 

Параметр принимает заданное значения до окончания соединения. Значение off, на мой взгляд, используется только для откровенного мусора.

Отказ слейва.

В случае выхода из строя подчинённой ноды ведущий сервер будет бесконечно долго ждать подтверждения записи проведённой транзакции. Выходов из этой ситуации три:

1. Научить пользователей\приложение в случае повисания транзакции отменять её и переводить свои сессии в режим локальной записи.
   
   
2. Подключить к мастеру вторую слейв-ноду, создав для неё второй физический слот, так как в PostgreSQL подтверждение записи транзакций достаточно от одного подчиненного хоста. В случае если требуется уложиться в схему из двух физических машин с операционками linux-like, то можно второй слейв поставить на компьютер мастерa, указав другой порт работы службы. На Windows поставить 2 постгреса через инсталлятор не получится — установщик корректно предлагает открыть 5433 порт (5432 уже занят первой СУБД), создаёт уникальное имя для второй службы, но стартует её с ошибкой.
   
   
3. Написать свою программу, которая будет отслеживать состояние передачи данных с мастера на слейв-хост, о чём я и расскажу во второй части статьи.

PostgreSAM

Программа представляет собой однопоточную утилиту, которая локально проверяет через пользовательский интерфейс postgres состояние репликации данных. Из-за упрощения логики работы перед запуском достаточно указать только пароль учётной записи postgres.


В случае если мастер теряет соединение со слейвом, утилита выключает на ведущем сервере параметр синхронизации данных и пересчитывает конфигурацию. На экран выводиться сообщение о внесённых изменениях. Когда связь со слейвом будет восстановлена, можно нажать клавишу «ОК» — PostgreSAM переконфигурирует ведущий сервер в исходное состояние. Утилита так же работает на слейве: сообщение о недоступности мастера будет выводиться, но изменений в работу ноды внесено не будет. На работе пользователей перезапуск конфигурации никак себя не проявляет: соединение не обрывается, а «повисшие» транзакции успешно завершаются.
Для связи с СУБД используется библиотека Npgsql — .NET провайдер для PostgreSQL. Для её корректной работы так же требуются библиотека Mono.Security — так как программка не требует установки, то обе библиотеки с исполняемым файлом есть в архиве. Если вы планируете использовать эту библиотеку в своем проекте, то необходимо добавить её в сборку и объявить:

using Npgsql;

Теперь нам доступен класс NpgsqlConnection с конструктором, принимающим по умолчанию строковый параметр:

"Server=<ip_или_dns>;User Id=<пользователь>;Password=<пароль>;Database=<база>;Timeout=<секунды>;"

Если пропустить Database, Timeout, то будет использоваться БД, идентичная имени пользователя, и таймаут в 20 секунд. После этого появляется возможность вызывать встроенную в класс функцию Open — откроется соединение с нужной нам БД. Далее используется класс NpgsqlCommand:

NpgsqlCommand <имя_объекта> = new NpgsqlCommand(<команда_SQL>, <имя_объявленного_NpgsqlConnection>);

В случае ошибки установления соединения или неправильного выполнения переданной на СУБД команды создаётся исключение, которое рекомендую отлавливать комбинацией try-catch. Данные выполненных запросов считываются при помощи связки объектов классов NpgsqlCommand (создавая объект, необходимо указать SQL-запрос и строку подключения), а так же NpgsqlDataReader:

NpgsqlDataReader <имя_переменной> = <созданный_объект_NpgsqlCommand>.ExecuteReader();

Для созданной переменной предусмотрена функция Read(). К плюсам Npgsql я бы отнёс предусмотренную поддержку работы по SSL-соединению, а так же возможность передачи запросов и получения ответов в виде plain-текст. В минусы сразу попадают exeption'ы — хотя в NpgsqlCommand есть CheckConnectionState, всё-равно в случае ошибки соединения генерируется InvalidOperationException.

В конце выполнения команд СУБД необходимо закрывать соединение функцией класса NpgsqlConnection — Close().