Добрый день. Готовим свой кластер на PostgreSQL 11.8 (PG), состоящий как минимум из 3 машин (Linux) с PG: мастера и 2 реплик, где в postgresql.conf параметр synchronous_standby_names имеет значение '*'.
Т.е. любая первая из реплик, применившая транзакцию, является синхронной.
Потенциально могут появиться другие реплики (но 100% асинхронные), которые территориально сильно удалены (в этом случае пропишем список реплик в ANY()).
Еще есть машина (FreeBSD) с NFS-каталогом для архивных WAL и резервных копий кластера и отдельных БД (pg_dump).
Имеется балансировщик haproxy (Linux), с двумя (применительно к PG) backend'ами и frontend'ами.
Первый frontend в haproxy слушает порт 5432/tcp, и все что приходит на него отправляет на backend, где в качестве сервера указан единственный адрес мастер-сервера PG и порт 5432.
Второй frontend в haproxy слушает порт 5433/tcp, и все что приходит на него отправляет на backend, где в качестве серверов указаны реплики, а также мастер-сервер, но с минимальным весом. В этом backend'е для всех серверов указан порт 5432.
Присутствие мастера в backend реплик обусловлено тем, чтобы балансировщик в случае большой сетевой нагрузки на реплики, мог отправлять запросы на чтение в том числе и мастеру.
Итого разработчики ПО имеют 2 контекста для работы с БД: один - "запись/чтение", другой - "только чтение". В connectionString обоих контекстов указан адрес балансировщика. Вся разница - в указанных портах (5432 - "запись/чтение, 5433 - "только чтение").
Разрабатываемое ПО в подавляющем большинстве случаев - это веб-приложения (ASP.NET). ORM также используется, за исключением тех моментов, где важна скорость.
В простейшем случае, если мы вносим изменения в БД из приложения, то используем контекст "запись/чтение". Если читаем - то контекст "только чтение". При таком подходе велика вероятность того, что вставив запись на мастере и прочитав список всех записей с реплики, пользователь, несмотря на честное сообщение об успешно добавленной записи, не обнаружит новой записи среди всех показанных. Т.е. мастер получил подтверждение от реплики 1, а балансировщик направил клиента к реплике 2, на которой данные еще не успели примениться (сетевые задержки, высокая загрузка CPU и т.п.).
При подготовке отчетов (fastreport) такое поведение будет являться нормальным. Но вот при редиректе со страницы добавления/изменения записи на индексную страницу (web), это является недопустимым.
Как решение - применение флага о прошлом изменении данных текущим пользователем (если изменялись, то читаем с мастера, если нет - то с одной из реплик). Т.е. метод Index в контроллере приложения анализирует необязательный параметр (флаг изменения данных), и применяет тот или иной контекст. В настоящее время это сводится к написанию одной строки в методе, а контекстом по-умолчанию является контекст "запись/чтение".
Таким образом, если разработчик в приложении забудет вызвать метод, возвращающий контекст, или задавать значения флагу (при редиректе с других веб-страниц), все будет железно работать (и запишем и прочитаем). Только вот особо забывчивые будут долбать только мастера.
Все три сервера PG установлены на 2 гипервизорах в одном помещении. Даже если разработчик просто тупо будет сразу читать данные с любой из реплик, после вставки их на мастере (даже не используя флага об изменении данных), то, скорее всего, 99.99% всех случаев не приведут к описанной ситуации, если это веб-приложение. Пока от контроллера до клиента дойдет ответ, а после запрос на индексную страницу в бэкенде, а там внутри метода запрос к СУБД... С огромной долей вероятности все будет хорошо. Но если приложение - десктопное, где на интерфейс нет никаких накладных расходов, вероятность считать не все данные - большая.
Особенно если добавляем в кластер асинхронную реплику, которая территориально удалена: территориально удаленный пользователь сделает INSERT на далеком мастере, и получит ответ быстрее или одновременно с тем, что данные только долетают от мастера до отдаленной реплики (но с которой такому клиенту гораздо выгоднее читать).
Отчего рождается вопрос: каким образом можно реализовать так, чтобы обращение к любой из реплик 100% принесло актуальную информацию? Т.е. чтобы программист знал, что он может использовать контекст "только чтение" без использования флага о том, что пользователь только что изменял данные?
Может проблема уже решена или решения не существует?

Maxim Boguk, спасибо, про это знаю.
Но, думаю очень медленно работать все будет. Особенно учитывая территориально удаленную реплику, которая задумывается как раз по той причине, что удаленным клиентам с нее выгоднее читать. Вообще, в документации по PG я не нашел ответа, за исключением нескольких синхронных реплик.
Тут скорее параметр synchronous_standby_names что-то вроде
FIRST 1 (реплика1, реплика2).
Но тогда выходит, что железно мы можем читать только с реплики 1.
В конфигурации балансировщика в бэкенде реплик должны быть указаны только реплика1 и сам мастер.
Вторая реплика, хоть и является сервером горячего резерва (wal_level = hot_standby), фактически будет использоваться только как теплого (запросов на чтение к ней не будет вообще). Если "реплика1" будет выключена, синхронной в приоритете станет "реплика2", однако в балансировщике, в бэкенде реплик реплика2 не указана. Возможно нужно крутить сам балансировщик в плане указания резервного сервера (только когда основные не доступны) в его бэкенде. Что-то такое я вроде даже делал когда-то (для другой задачи). Но тогда мастер-сервер следует удалить из бэкэнда реплик балансировщика (иначе переход на резервный, с точки зрения балансировщика, сервер никогда не наступит, т.к. доступен сам мастер).
Остается открытым вопрос с удаленной репликой. Делать ее синхронной - катастрофа как по времени подтверждения от нее, так и в случае потери связи с ней.
remote_apply уже применен. Кластер организован в одном помещении на виртуальных машинах, с быстрым каналом связи между собой, на двух гипервизорах. Думаю негативное влияние remote_apply в этом случае сильно нивелируется.
Но тогда получается, что территориально удаленная реплика - не имеет права на жизнь. Весь смысл тогда теряется.
Уже голову сломал.

Если контекст уничтожается после каждого запроса (db.Dispose()), то ничего не мешает. Я не о подключении к серверу и продолжении работы на нем (Например, с помощью psql). Равно как не мешает и использование 2 контекстов одновременно (я о приложениях):

using (MyDbContext db = new MyDbContext())
using (MyDbReadonlyContext dbReadOnly = new MyDbReadonlyContext()){ }

Т.е. сессия заканчивается с запросом. Чтобы не плодить кучу коннектов можно pgbouncer использовать.
Использовать 2 контекста можно, но не совсем удобно. Придется смириться и использовать учитывая информацию о том, что делал пользователь в прошлом действии. Если сосед вносил изменения в БД, а спустя 1 сек коллега открыл эти записи и не обнаружил изменений - это не страшно, с этим можно мириться (спустя 1-10 сек изменения увидят все). Проблема в том, чтобы изменения увидел сам автор. И тут без информирования контроллера о том не изменял ли пользователь в прошлом действии данные, не обойтись.
Можно, например (сейчас придумалось) ввести приватную СТАТИЧЕСКУЮ переменную в контроллере, значение которой распространится на все экземпляры контроллера, в которую писать время когда в БД осуществлялись изменения (разумеется, отследить изменения можно только если они вносились из самого приложения, а не напрямую в БД), и видя, что программист использует вызов контекста "только чтение", а в статической переменной время менее 10 сек от текущего - подставлять вместо контекста только чтение контекст чтения с мастера. Тогда точно никто не сможет увидеть устаревших данных с реплики.
Нужно, наверное, будет организовать потокобезопасный доступ к этой статической переменной (особенно если вынести ее за пределы контроллера, и менять которую будут вообще при любых изменениях в БД в приложении (из любого его действия)), чтобы не возникло конфликтов при попытке одновременной записи в нее из нескольких параллельных потоков.
В общем, решения есть, но все они несколько костыльные.



При таком подходе глюков и количества конфликтов будет больше чем при описанном сценарии, IMHO, конечно.

Похоже это самое ближайшее к цели. Но использовать будет сложно. Я еще подумаю как бы это можно было упростить. Т.к. большинство запросов (95% и более) - это ORM. Если использовать ORM для вызова хранимок, то тогда весь смысл использования ORM теряется (хотя, в большинстве случаев - это правильный путь). Для чего-то тяжелого мы так и поступаем. И придется, похоже возвращать не один набор данных, а более, если сама хранимка, помимо работы по изменению в БД, должна еще что-то вернуть. Т.е. полезные данные плюс указанными вами результат, который следует сохранить в сессии. EF Core умеет вызывать хранимки PG, но не те, которые возвращают более 1 набора данных. Тогда только через dataReader.
Позже подумаю как это все можно подружить. Если что-то получится, напишу.